How to figure QTY ordered over 6 months

[jwnacnud]

I am using the Purchase Order Tracker solution program that came with FMP8. I have modified it a bit and I have a quick question. (it will be quick for you)

I have to determine popularity of a given item. A lookup, not a calculation, because I need historical reference as well.

We place items on a purchase order to be ordered. Each line can contain one item. I have a Purchase Order table and an ItemsOrdered table.

When we order an item, we specify the item and the quantity ordered.

I need one more field (the lookup) that will tell me how many of that item I have ordered over the last 6 months (I can change the time frame later)

Edited November 6, 2008 by Guest

[Oldsneekers]

My solution assumes that each type of item has a unique serial number so that wigetA has for example ID number 334 and wigetB has 335 etc.

1. There needs to be a field that contains the amount purchased. If your structure is such that a single record can only represent 1 item (so that if I wanted 2 of the same thing you would make two records) then you need to make a number field where '1' is auto entered. If, on the other hand, you already have an AmountField, great. (Be sure the amount field is a number field)

2. In that ItemsOrdered table with the AmountField, create a summary field formatted as the TOTAL OF the AmountField. Do not check running total.

3. Make a relationship between two copies of the ItemsOrdered table using the itemID number as the match field. Lets call the original ItemsOrdered table CopyA and the second CopyB.

4. On the layout formated to CopyA, place the Summary field but that summary field needs to be from table CopyB.

Thus: when the records are displayed, the summary field will show the sum of the amounts ordered matching the itemID number. I tested this and works. I didn't test the date filter (looking back only 6 months) but if the CopA has a global number field for a time period and a nonglobal number MonthsFromNowField (or if that can't be index because 'now' is flexable, make a MonthsFromCompayStart field or you can use the record's OrderedDate) then set these as an additional relationship parameter set to be Months > Global. Then create a scrip that will automate the Global field entry to enter x number of months you want to look back.

I hope this is clear enough.

Good luck