calc summary by overlapping date ranges (Rental Inventory cont'd 2)
I am writing as an extension of previous posts dealing with the query "what is my available inventory between Start and End"
My Solution Regarding the OP
My tables are:
Orders--< Line Items>-- Asset Types--< Assets
Each asset will be serial tagged so that I can track coming in, going out, repairs etc. Total inventory of an asset type is simply a sum of asset records linked to it, which of course could just be a normal field with a user entered value.
I have solved the OP problem in this manner- set via scripting two global fields gQueryStart and gQueryEnd as the order time stamps of the current order record. In Line Items, set a calculation cConflictQty=If(time overlap;Line Item::Qty;0)
Add two relationships Orders>--X--<Asset Types--<Line Items where line items is linked to Asset Type ID
Create a calculation field in Asset Types cQtyAvailable=cAssetTypeQty - sum(Line Items::cConflictQty)
On the Orders Layout create a portal to the second Asset types table, which will show all records due to the X relation. Then simply add the fields Asset Types::Name and cQtyAvailable.
My question and post given what I have done above.
The short version of my question is how to create a relationship between two tables based on an overlap of date ranges. OR how can I create a calculation (I don't know one) which is to sum the values of a related field when a calculation is true (i.e. sum the line item quantity when the conflict calculation returns true)
What I really need is for every line item to run the conflict calculation. What if I change an order date to start a few days earlier, what will warn me that the order now overlaps with another order? Or what if i change the quantity on an existing order. The ideal answer is that each line item has a field of the available quantity during the time frame. But wait, there's more! Each line item has it's own Start and End time, as nothing says every item on the order will come and go at exactly the same time, some pieces may leave earlier, or come back earlier.
The base of the problem is that you can't create relationships based on full calculations (as you can a filtered portal), in this case, what I need is a relationship between line items and line items where the dates overlap and the asset type ID is the same and the line item id is not the same. While I've been looking at some of the filtered portal summary solutions (e.g. http://www.filemakerhacks.com/?p=2293) I don't see an elegant way to use the summary field in a calculation, because the calculation won't take the filter of the portal into account (correct??).
Solutions I have considered:
I could show the filtered portal summary and leave it to the user to see that quantity in use is greater than total inventory (not why we use databases!)
I could probably grab the contents of the filtered portal summary field via scripting, but that seems a little brutish for every line in every order.
I could script through my existing system by going to each line in the order, setting the global query dates, and pulling the available quantity, again it's a little brut force.
I could create a very bloated relationship that is Line Items>--Dates--<Line Items on the thinkng that the two ranges of dates for a line item and a conflicting line item would have to match on at least one date. But then there is actually many to many relationship between line items and dates unless I add an Asset Type ID to Dates. Creating a relationship like this probably creates the most elegant solution as far as calculations and field values (rather than step scripting) however it means that the number of records in Dates would be the sum of the number of days rented of every line item. That also seems like a waste. Not to mention that I would have to script the creation of all of those records, or just have a record for every day a rental could happen (40 years?) times the number of asset types. Seems like a lot of records.
Or as I said in the short version, I could create a calculation I don't know yet which is to sum the values of a related field when a calculation is true (i.e. sum the line item quantity when the conflict calculation returns true)
Sorry this is so long winded, but I swear the first response to every question is "Explain your situaion more" so there you go. Any ideas? Elegance is of course my goal. Many thanks for your time and thoughts.