on 2024 Aug 28 6:29 PM
Dear all,
If we make a sales order in SAP B1, I would like to see, by pressing SHIFT+F2 to see what the BP bought in the last 180 days and the quantities.
What query should I make, to see the list?
Please advise.
Thanks in advance
Regards Mark
Request clarification before answering.
Hi Marc,
try this
SELECT
[RDR1].[ItemCode]
,[RDR1].[Dscription]
,SUM([RDR1].[Quantity]) AS [Sum]
,MAX([ORDR].[DocDate])
FROM
[ORDR]
INNER JOIN [RDR1] ON [RDR1].[DocEntry] = [ORDR].[DocEntry]
WHERE
[ORDR].[CardCode] = $[$4.0.0]
AND
[ORDR].[DocDate] Between DATEADD(dd,-180,GetDate()) AND GetDate()
GROUP BY
[RDR1].[ItemCode],[RDR1].[Dscription]
I recommend
so you can make so small adjustments on your own.
Keep in mind that the item-description in a document, can differ from ItemName
regards Lothar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
there were a small typo in the query, try
SELECT
[RDR1].[ItemCode]
,SUM([RDR1].[Quantity]) AS [Sum]
FROM
[ORDR]
INNER JOIN [RDR1] ON [RDR1].[DocEntry] = [ORDR].[DocEntry]
WHERE
[ORDR].[CardCode] = $[$4.0.0]
AND
[ORDR].[DocDate] Between DATEADD(dd,-180,GetDate()) AND GetDate()
GROUP BY
[RDR1].[ItemCode]
Save it
Go to your desired Field press Shift+Alt+F2
Place the query.
Close the form, open it again. Choose a BP. Press Shift + F2 in the field
If the query returns only one line it fills the field automatically.
regards Lothar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Mark,
yes for sure. The syntax is for using it as FMS. If you want to test a FMS. (Shift+F2 is a FMS)
Save the Query
Open the desirerd Form with data (sales order in that case which has a CardCode)
Then go through the menu to the query. The variable
will be filled with CardCode
regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear Lothar,
I mean a query what will be helpfull for all clients. So we don't need to change it manualy.
I used to have a query, that when i pressed Shift+F2 in a sales order on the line of 'item description', I got a list of what that particular client bought in the last 180 days, with quantities.
Hope you understand what I mean.
Thanks in advance.
regards
Hi Mark,
try this
SELECT
[RDR1].[ItemCode]
,SUM([RDR1].[Quantity]) AS [Sum]
FROM
[ORDR]
INNER JOIN [RDR1] ON [RDR1].[DocEntry] = [ORDR].[DocEntry]
WHERE
[ORDR].[CardCode] = '$[$4.0.0]'
AND
[ORDR].[DocDate] Between DATEADD(dd,-180,GetDate()) AND GetDate()
GROUP BY
[RDR1].[ItemCode]
regards Lothar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
15 | |
7 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.