5 Replies Latest reply on Oct 15, 2012 12:09 PM by philmodjunk

    calc summary by overlapping date ranges (Rental Inventory cont'd 2)



      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"

           Orig: http://forums.filemaker.com/posts/387235006a?page=1

           Cont. 1: http://forums.filemaker.com/posts/7c1ba7be21


      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.

        • 1. Re: calc summary by overlapping date ranges (Rental Inventory cont'd 2)

               There are two ways that I know of to check for overlapping date ranges. One is to use three different relationships with inequality operators--complex to implement to say the least, or you generate a return separated list of dates and use them as match fields. If even one date in the list matches the records are linked.

               A custom function can take two parameters, Date1 and Date2 and produce a return separated list of dates as its result.

               Say date1 = 10/1/2012 and date2 = 10/3/2012 for a given record.

               custom function returns:


               Say date3 = 10/3/2012 and date4 = 10/5/2012

               custom function returns:


               If you use result for date1,date2 on one side of the relationship and date3, date4 result for the other match field, the two records link given that the date in red appears in both fields.

          • 2. Re: calc summary by overlapping date ranges (Rental Inventory cont'd 2)

                 So I should put the result into a text field on the line item, which would just be a list of all the dates that the line item is "active".  I had been doing some other work with dynamic portal filtering and seemed to find that partial matches were not causing a relation, but maybe that was a partial word match, opposed to a match of whole words within a string?  I guess the question is, what is the true definition to satisfy the "=" relationship?

                 Thanks, I'll start working on this now and report back.

            • 3. Re: calc summary by overlapping date ranges (Rental Inventory cont'd 2)

                   Return separated lists of values, when used as match fields in a relationship set up an "or" relationship where any value in the list can match to the value on the other side of the relationship.

              • 4. Re: calc summary by overlapping date ranges (Rental Inventory cont'd 2)

                     Resolved.  Thanks for the elegant solution I was looking for!  I knew all the options I knew were using way too much force.  For the forum reference I'll share...

                     It's the first function I ever wrote, but I found a script to borrow from for a framework. 

                     Custom function:


                                 dCount = EndDate-StartDate;
                                 newHeader = If(DateList ≠ "" ; DateList & ¶ );
                                 NewStartDate = StartDate + 1
                             dCount < -1; "";
                             dCount = -1; DateList;
                             ListDates(NewStartDate ; EndDate; NewHeader & StartDate)

                     the match field is cConcurrentDateList= ListDates(StartDate;EndDate;"")

                     The relation is to a copy of line items cConcurrentDateList=, line item ID≠, and Asset Type ID=

                     Line Items::cConcurrentQty=sum(Line Items Concurrent::Qty)

                     On the orders portal to Line items, I add the field cConcurrentQty with conditional formatting to make the background red when the concurrent count plus the qty count of the line item is greater than the amount in inventory.  I'll probably also write a calculation or script to make an alert when you go to the order record or make a change that says at least one line item on the order has a conflict of over assigned quantity or the need to subrent the additional items.


                • 5. Re: calc summary by overlapping date ranges (Rental Inventory cont'd 2)


                       I use this method for appending items to a list. It really has no advantages over what you've done except, in my opinion, it looks a bit "cleaner":

                       ListDates ( newStartDate ; EndDate ; List ( DateList ; StartDate ) )