Formatted Search on Item Master

[Former Member]

Experts,

I have a client who sells commodities. They need their Pricelists to be updated with each Purchase, because of fluctuating Prices.

If I can get the following Formatted Search to work, I believe that Iu2019ll have a solution:

I created a UDF on the Item Master Inventory tab rows, which I called OITW.U_Test

I need to populate this UDF with the following calculation: (OITW.OnHand * OITW.AvgPrice) + (T1.OnOrder * (Sum of all the quantities on open POu2019s)) / (OITW.OnHand + T1.OnOrder)

Here is the logic behind it: This UDF must show a modified Moving Average. Because POu2019s does not update the AvgPrice (only the Goods Receipt PO) this is cause for concern for this client who might have Sales Orders for Items that is not in stock yet, and posted as a lower AvgPrice.

Here is what I have done so far:

Select T0.ItemCode, T1.OnHand, T1.AvgPrice, T1.OnOrder, 
	T1.OnHand*T1.AvgPrice as 'InStock Value', 
	T1.OnOrder* ?????? as 'PO Value' 
From OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode 
	INNER JOIN POR1 T2 ON T0.ItemCode = T2.ItemCode

Any help will be appreciated.

Marli

[Former Member · Accepted solution]

Marli,

I doubt your idea could work properly. First of all, how will your update this UDF?

Thanks,

Gordon

[Former Member · Accepted solution]

Gordon,

I thought that this can be updated everytime the Ordered Qty changes.

Marli

[Former Member · Accepted solution]

FMS may only work on the current form. You have to go through SDK to achieve what you projected.

[Former Member · Accepted solution]

Gordon,

I tested this and you are right, to use a FS will not work because of the refresh issue.

I'm now working on a Stored Procedure that will update this UDF when a PO is Added or Updated. My experience with Stored Procedures is limited to True or False statements. (If this is true, do not add the Document, if it is false add the document)

My question is: Can I populate the UDF, with a Value according to my query, with a Stored Procedure?

Thanks for your help.

Marli

[Former Member · Accepted solution]

No. That is prohibited unless the UDF is in UDT. You may read through SAP Note 896891 Support Scope for SAP Business One to know more.

[Former Member · Accepted solution]

Gordon,

As I was testing this, I also found that a UDT will be better.

Thanks for the info, I'll make sure not to use a UDF in this Stored Procedure.

Marli