Question on FlowField

kenl

Hello,

I want to find the Sum Qty for a location on a specify Country.

I add a flow field in table Item:

Sum("Item Ledger Entry".Quantity WHERE (Item No.=FIELD(Item No.)))

On Table "Location", there is a field "Country". How can I set the filter on Location table?

something like:

Sum("Item Ledger Entry".Quantity WHERE (Item No.=FIELD(Item No.) where "Item Ledger Entry".Location = Location."Code" And Location."Country" = "US" ))

Please advise.

kine

It is not possible, you need to have the field country directly on Item Ledger Entry if you want to do that...

kenl

Thanks for your reply.

:-s

Oh.. Flow field is nice for drill down and automatically update. But this limitation (not able to link more than 2 tables) is very a problem.

I try to add a "Country Code"(flowField, Lookup from Location) to "Item Ledger Entry", but it fail since we cannot add flowField to key.

themave

in version 4.0 there is a country code in the item ledger entry table.

you would need to add it to the Stock Keeping Unit table
you could make a non-printing report to do a one time fill of the SKU table with the Item table "Country code"

Then modify the Inventory FlowField in the SKU table to include "country code"

Sum("Item Ledger Entry".Quantity WHERE (Item No.=FIELD(Item No.),Location Code=FIELD(Location Code),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Drop Shipment=FIELD(Drop Shipment Filter),Variant Code=FIELD(Variant Code),Country Code=FIELD(Country Code)))

Or create a new flowField in the SKU table just for this information

-Sum("Item Ledger Entry".Quantity WHERE (Item No.=FIELD(Item No.),Location Code=FIELD(Location Code),Country Code=FIELD(Country Code)))

This should give you the information you are looking for, I believe.