on 09-26-2008 7:49 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
11 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.