cancel
Showing results for 
Search instead for 
Did you mean: 
Read only

UDF Setup history item list BP ordered

MarkOenema
Explorer
0 Kudos
737

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

Accepted Solutions (1)

Accepted Solutions (1)

LoHa
Active Contributor

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

https://www.google.com/search?q=mastering+sql+queries+for+sap+business+one&sca_esv=5e78618b726d8b1c&... 

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

Answers (3)

Answers (3)

LoHa
Active Contributor
0 Kudos

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

2024-09-03 07_06_06-Answer to Question - SAP Community.png

Place the query.

Close the form, open it again. Choose a BP. Press Shift + F2 in the field

2024-09-03 07_08_10-Answer to Question - SAP Community.png

If the query returns only one line it fills the field automatically.

regards Lothar

MarkOenema
Explorer
0 Kudos
Dear Lothar, Great this is what I needed, could you only add item description in the list, and maybe last date ordered? Thanks in advance.
LoHa
Active Contributor
0 Kudos

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)

FMS Test.png

Then go through the menu to the query. The variable 

 

will be filled with CardCode

regards

MarkOenema
Explorer
0 Kudos

Dear Lothar,

It keeps on popup a screen where we need to select BP group.

Isn't it possible to get just a list of last 180 days order products and quantities if I want to make a salesorder?

Please advise.

 

MarkOenema
Explorer
0 Kudos

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

LoHa
Active Contributor
0 Kudos

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

MarkOenema
Explorer
0 Kudos

Dear Lothar,
Unfortunately it doesn't work. 

The query adept itself and gives a failure

MarkOenema_0-1724939601967.png

Please advise.

Regards