User based query

[Former Member]

Hi,

How do we write a query for last 3 quotation for a particular item and a particular customer created by a particular user.

I want to create a single for all users and assign it as formatted search.

Rgds

[former_member583013 · Accepted solution]

Rajeev,

That is one impressive query you got from Gordon. But unfortunately it is missing the ORDER by to get the LAST 3 Quotations.

Also the ItemCode's field reference is 38.1.0 not 38.0.0...which is also corrected below

SELECT TOP 3 T0.DocNum FROM [dbo\].[OQUT\] T0

Inner Join [dbo\].[QUT1\] T1 ON T1.DocEntry = T0.DocEntry

Where T1.Itemcode = $\[$38.1.0] and T0.CardCode = $\[$4.0.0] and T0.UserSign = $\[USER]

ORDER BY T0.DocNum DESC

It will now depend on which column you would like to set this at the rows.

The Query will return only the Document Number of the Past 3 Sales Quotes, if you need other details you need to add them to your SELECT

Suda

[Former Member · Accepted solution]

Hi Suda/Gordon,

Thanks for the query.Can you tell me how do we get $[$38.0.0] for itemcode and $[$4.0.0] for cardcode in the query.

Rgds,

Rajeev

[former_member583013 · Accepted solution]

Please check my previous reply one more time..

It is actually $\[$38.1.0] which refers to the ItemCode field when you are on the form. Dynamic Form field / column reference to get values from an active form

$\[$4.0.0] refers to the CardCode field

Check..View System Information from the View Menu and when you mouse over the CardCode and ItemCode columns on the Status Bar

For CardCode you see............................[Form=xxx Item=4 Variable=xxx..............\]

For ItemCode you see.............................Form=xxx Item=38 Column=1 Row=xx Variable=xxx..............\]

The syntax is $\[$Item.0.Type] for Header level Fields and $\[$Item.Column.Type] for row level fields

[Former Member · Accepted solution]

How do we get the type .Is it by default 0 or any other values are possible.

[former_member583013 · Accepted solution]

Type parameter has options

Number, Date, Currency

Example: If you notice the Unit Price column you have the Currrency symbol also included in the value. To extract just the number part exluding the currency symbol you will use the .Number type

$\[$38.14.Number]

Please go this document on defining Formatted Searches which explains it all

https://websmp102.sap-ag.de/~form/sapnet?_FRAME=CONTAINER&_OBJECT=011000358700000709722007E

[Former Member]

Please try this one:

SELECT Top 3 T0.DocNum from OQUT T0
Inner Join QUT1 T1 ON T1.DocEntry = T0.DocEntry
Where T1.Itemcode = $[$38.1.0] and T0.CardCode
 = $[$4.0.0] and T0.UserSign = $[USER]
ORDER BY T0.DocNum Desc

Thank you Suda for correction. I just typed in without checking the final. I will take more cautions in my posting from now on.

Gordon