Hi, I am trying to do the following.

I have a one to many relationship between Inventory and Invoices tables. They are connected as follows:

(Table Name)Field Name

(Customers)Customer_ID to (Sales Orders)Customer_ID

(Sales Order)Po_Number to (Line Items)Po_Number

(Line Items)Inventory_Number to (Inventory)Inventory_Number

Table Sales Orders Contains the Date field which is set to automatic date creation upon record creation.

I want to set the following calculation/script

Current_Date (gets the current system date)

Age_Date (Does the calculation Current_Date - Days)

Days field is a global field that contains number 20

So if the current date is 6/21/07 the Age_Date would return 6/1/07

Then I want to find all records in the Sales_Order that are between Current_Date and Age_Date.

Then there is a quantity field in Sales_Order table.

New field called qtyPerformance would calculate: Sum(Quantity) of all the records between Current_Date and Age_Date in Sales_Order.

New field called Performance would calculate

If qtyPerformance >= 50, Hot, Average

I have gotten as far as to set up all of the fields and field calculations. My problem is that I do not know how to set up a calculation that would find the rang of records between certain dates as mentioned above. I want it to be dynamically updated rather then having to run the script every time to get the results.

Hope I made sense, if not let me know, I'll try to improve. I'm a FM Novice, running 3 weeks now so please take it easy on me and thank you.

P.S.

Summary:

Current_Date (Calculation: Gets the current date)

Days (Global Number: contains value 20)

Age_Date (Calculation: Current_Date - Days)

Range_Record_Search (Script - finds all records between Current_Date and Age_Date)

QtyPerformance (Sums field Qty in found set)

Performance (Calculation: If qtyPerformance >= 30, HOT, Average)

Thank you.

I got it to work for one record with this calculation:

If ( Invoices::Invoice_Date ≤ GetCurrentDate and Invoices::Invoice_Date ≥ GetAgedDate ; Sum ( LineItems::Item_Quantity_In_Stock );0)

Now how would I modify that so that it would work for all of the records in that date range.

As it is with the calculation above it adds to quantity for all of the subsequent records however if I change the date in record two out of range it will not update the calculation as it is solely based on the first record date.