I am trying to add a flow field to the Item table that looks up a value in one of my company custom table (commodity price).
The custom table denominates what in the Item table is referred a No_ as “Commodity Code”
The custom table can have a commodity code (item number multiple times) with different dates.
I would like to retrieve the commodity code with the most current date.
Is this possible to achieve using a flow field?
My current calcformula in the Item table looks like:
Lookup(“Commodity Price”.“Contract Cost” WHERE (Commodity Code=FIELD(No.)))
I did also select the column OnlyMaxLimit in the table filter part of the flowfield thinking that this might help retrieve the latest value, but it does not seem to have any effect.
Unfortunately we are somewhat limited in what we can request in a flowfield. In this case I would suggest adding a quick function to the Item table that would take in an ItemNo as a parameter and return the Contract Cost as per the example. This function can be called from the page or report as required to present the information to the user.
CommodityPrice.RESET;
CommodityPrice.SETCURRENTKEY(“Commodity Code”,“Posting Date”);
CommodityPrice.SETRANGE(“Commodity Code”, ItemNo);
IF CommodityPrice.FINDLAST THEN
EXIT(CommodityPrice.“Contract Cost”);
Yes, this code would work from the Item Page, the reason I suggest putting this in the table is that it can easily be reused on the Item List or other pages based on the item table.
If you create a function called GetCommodityPrice in the Item Table you can simply call it in the source expression as GetCommodityPrice(“No.”) this will call the function sending the current item number.
I would make a small change, place the code in OnAfterGetRecord since you are able to navigate on the page Previous and Next. At the beginning of the code section I would add ContractCost := 0; to ensure that the cost is not accidentally misstated on the item card if a contract cost cannot be found for another record when navigating but the cost was found for the previous record.