cancel
Showing results for 
Search instead for 
Did you mean: 

Query on date

Former Member
0 Kudos

Hi All

Well i want to display a date in "delivery date" field in purchase order to be 20 days more then what we are selecting in "posting date", can you please suggest me some query which will solve my problem

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Rahul,

This should work if you put a formatted search on the delivery date with this query:

[code]select dateadd(day, 20, $[$10.0.Date])[/code]

Hope it helps,

Adele

Former Member
0 Kudos

hi

thx a lot, your solution worked for me but still tell me it is only working if i press "shiftf2" on "delivery date" . can it be displayed directly without being pressing "shiftf2".

Thanks a lot for your concern

barend_morkel2
Active Contributor
0 Kudos

Yes Rahul,

Go to the field (Delivery Date).

Open the formatted search window.

check the box on "Auto refresh"

Choose posting date in the following drop down box

(select either refresh regularly or display saved values)

Former Member
0 Kudos

hey barend

i am doing the same thing as it is quite obvious but still there is a problem . i dont know why?

Former Member
0 Kudos

Hi, can tell me, $[$10.0.Date], what's 10.0 ???? and what do it do ? that place is Table Name, Is 10.0 TableName ? Thanks.

barend_morkel2
Active Contributor
0 Kudos

Andy.

There are 2 ways to get data from fields:

1. You can refer to fields in a screen template using the syntax, $[Table name.Field name]. The table name is the name of the table belonging to the screen template, for example, OINV for the invoice entry screen.

2. The system is able to uniquely identify each field of a document via the field index and the field column.

The syntax is then $[$Field index.Field column.NUMBER/CURRENCY/DATE/0]

You use the NUMBER parameter if the field concerned contains an amount and a currency key, and you want to extract the amount only.

You use the CURRENCY parameter if the field concerned contains an amount and a currency key, and you want to extract only the currency key.

You use the DATE parameter if the field concerned is a date field and you want to use it for calculations.

(Thus for our example "10" is the field ID of the posting date field (on the order form), and "0" is the column ID - seeing as this is a edit field and not matrix there is no columns - thus 0 is the default)

Former Member
0 Kudos

Thank Barend, but how to know field ID number,column ID on the form.

former_member184566
Active Contributor
0 Kudos

Hi Andy,

To get the info Barend suggest you go into sbo, open a A/R Invoice. then on the top left there is view. Clkick on it and go to display debug information (view->debug information). then if you hover your mouse over the field on a form it will then display it in the left bottom of sbo. It gives a lot of info and can is the way you'll get the info to perform the above. But once makingt it it will not work from the query window. You must save it and then link it to a field on the form as a formatted search and then execute it. If you go to any field on a form and pres shiftaltF2 you will see a window comes up to do it. An example for Customer ref field on a invoice is "SELECT $[$14.0.0]", that will display what is in customer ref in the field it's linked to.

Hope this helps

Hope this helps.

Answers (1)

Answers (1)

barend_morkel2
Active Contributor
0 Kudos

Hi Rahul,

You can run a formatted search in the field or run the query through the sdk.

Your query to add 20 days to the selected date should look like this

SELECT DATEADD(day, 20, getdate())

now getdate() can be replaced with the Posting Date

$[$10.0.DATE]

<b>(Sorry Adele got in before me)</b>

Message was edited by: Barend Morkel

Former Member
0 Kudos

I am using dd/mm/yy format.whenevr I enter Posting date , it adds 20 to month. Pls Help me.

Former Member
0 Kudos

Hi,

Use this statement:

SET DATEFORMAT dmy

This asigns the date format you are using, then use the previous SQL.

Regards,

Ibai Peñ