How to get latest value from a flowfield

Hello.

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.

Would someone please offer some insight?

Thank you very much.

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”);

EXIT(0);

I just need to be able to display the variable in a text-box in the Item Card page.

Would this code work if I place it in the Item Card page instead that the Item table?

Thank you!

Or if I add this code to the Item table, can I assign the returned value to the text-box in the Item Card page?

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.

This is an excellent code example you have shared with me Tim.

I was able to pretty much copy and pasted in the Item Card code (for now)

What I did was:

  1. Created a field in the Item Card page and name it “Contract Cost” with a SourceExp of ContractCost

  2. Created 2 global variables: ContractCost Decimal, and CommodityPrice Record subtype table CommodityPrice

  3. Use your code on the: OnOpenPage() event.

CommodityPrice.RESET;

CommodityPrice.SETCURRENTKEY(“Commodity Code”,Date);

CommodityPrice.SETRANGE(“Commodity Code”, “No.”);

IF CommodityPrice.FINDLAST THEN

ContractCost := CommodityPrice.“Contract Cost”;

What do you think? Does it seem the right order of steps?

Thank you very much.

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.

Excellent! Thank you very much Tim!