SBO: Formatted Search

[Former Member]

Hi all

I have a little problem with formatted search function.

I try to write a formatted search in the order document

which translate the docdate field to a string.

for exa. if the date is 20/03/2004- i want to translate it to: March 20, 2004 in another UDF field.

this is the query I am using:

SELECT convert(nvarchar(20), convert(datetime,$[ORDR.DOCDATE],101), 107) and it is not working, does anyone have an idea ???

Mark

[Former Member]

From SQL Server Help: "The style parameter of CONVERT provides a variety of date display formats when converting datetime data to char or varchar."

It will not use your conversion format when translating the string to a date, only the other way.

Could you use the substring() function to rebuild the string? It might start:

select convert(nvarchar(20), convert(datetime, substring($[ORDR.DOCDATE], 5, 2) + substring...

[Former Member]

Hi Bruce,

the error isn't in the convert, but in the access to the date field. I guess you want to get the actual date of the document BEFORE it's stored in the database. In order to do so, you have to get the value of the form field. The syntax for that is comprised of Item No., Variable No. and Type for Header info and Item No., Column No. (or Name in case of UDF) and type for table info. e.g. : $[$4.1.0] for access to the BP-Name or $[$10.1.0] for the DocDate. The parameter can be 0 (no formatting), currency (e.g. EUR, PLN... from 123,45 EUR) and number (e.g. 123,45 from 123,45 EUR).

Hope that helps,

Dierk

[Former Member]

Hi Dierk and Bruce,

Thank you for helping me.Actually, I've solved this problem by another way. There is a bug in the SBO. I work with date format DD/MM/YY, but formatted search get the DocDate in that format and consider it in the format MM/DD/YY. I've converted date to string,cut it, pull parts in another order and made back conversions. Now it's work. After all, I received help from another source and there was the same format Dierk wrote about($[$10.1.0}). What is the secret language? Where I can get the description of this language?

Best regards

Mark

[Former Member]

I don't think it is a bug, in your case you may need to use $[ORDR.DOCDATE.DATE], or as Dierk mentioned, $[$10.1.DATE] is even better.

Regarding the syntax of $[$10.1.DATE], Dierk also mentioned clearly: The first number is the Item Id in the form, the second one is the Column Id, and the last one is the Data Type. You can enable the debug information through Tools -> User Tools -> Display Debug Information, then put your cursor on any screen element, you will see these information on the status bar.

Xuechen