I am currently working on a program for a warehousing and distribution company. I have created three databases, one contains a list of records representing goods (items) which have been delivered to the warehouse and the quantities and dates of these deliveries. another a list of records representing goods (items) which have been distributed from the warehouse and the quantities and dates of these. The third file contains a list of all items (items cannot be recorded as delivered or distributed without first existing in this file) and the quantities of these items (which is calculated by subtracting the number of items distributed from the number of items delivered). The problem is i want to make the program so that the user may recal how much stock there was on any given date. I have used the smart ranges technique and this is fine. However the user specifies what date they wish to view in a global field and then runs a script, the script enters the date into a date field for every single record within their find request and then calculates stock based on deliveries-distributions before and upto the specified date.

What i want to do is just call the relationship for all records based on the global field instead of copying it to all fields since this strikes me as a messy way of doing it. Only we all know that filemaker doesn't allow relationships to global fields!

Any ideas anyone?

Hi Ninja,

You cannot use a global on the match side of a relationship, but I think if you flip it around a bit, this will work.

Try putting a Date Received in your PO Line Items, and a Date Shipped in your Invoice Line Items. It is necessary for these date fields to be regular, stored fields and not calculated from the related PO/Invoice, as these will be used for the match key in the relationships. You will likely need to use a script to update these line items dates as orders are shipped and received (or use a mechanism to update them all daily.)

You might decide to treat committed items as unavailable, and therefore use a Date Sold in Invoice Line Items instead of Date Shipped. This could then be populated as an invoice is finalized.

Now you will still use a global date for the relationship, but on the parent side (in the Item file.) Set up the smart ranges relationship using a range on the parent side from 0 to gDate. The match keys will just use the Date Shipped or Date Received, with the ItemID.