Anuviel Posted June 7, 2007 Posted June 7, 2007 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.
Anuviel Posted June 7, 2007 Author Posted June 7, 2007 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.
Recommended Posts
This topic is 6439 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now