Need help designing simple solution.

[Francis P.]

Hi all,

I have a very simple table with the following fields (product , from_location , to_location , quantity and date). How can I create a three column report that to shows totals received, issued and on hand for each location.

Thanks.

[comment]

This, surprisingly, is not going to be simple. Do you also have a table of Locations (where each location has a unique record) and a similar table for products?

[Francis P.]

Yes I have both these tables.

[comment]

Well, to produce a report for a single location, you'd find all transactions affecting the location (whether from or to) and sort them by product, then by type (incoming or outgoing). To calculate the type, you need to have the selected location's ID in a global field or variable. Hopefully, the rest is obvious.

Another approach is to have the selected location's ID in a global field in the Products table. Then you can have two relationships linking Products to Transactions, matching on both ProductID and LocationID.

[Francis P.]

How do I get the selected location ID in a global field or variable? I know how to create global fields.

[comment]

Assuming the report process is scripted, and that the script is called from the selected location's record, start the script by:

SetField [ AnyTable::gLocationID ; Locations::LocationID ]

or

SetVariable [ $$locationID ; Locations::LocationID ]