1 2 Previous Next 22 Replies Latest reply on Apr 28, 2014 6:50 AM by MichaelVoccola

    Equipment QTY Available for a Given Date Range

    MichaelVoccola

      Title

      Equipment QTY Available for a Given Date Range

      Post

      Goal: In a portal displayed on an "Invoice" showing records from "Inventory" include a field displaying the current available QTY for the date range on the current "Invoice" (Invoice::StartDate  and  Invoice::EndDate)

       

      The portion of the database in question includes the following tables:

      Inventory

      Invoice

      LineItems

       

      Currently, there is a portal on the invoice to add items from the table "InventorySearch", a T.O. of "Inventory", which is currently related through a SearchFilter type relationship. Each Invoice has StartDate & EndDate fields.

       

      Items are not tracked individually; LineItems::QTY is the QTY of that particular Item# on an Invoice.

       

      Below is a link that begins to cover this topic.

      Finding available dates (equipment rental program)

        • 1. Re: Equipment QTY Available for a Given Date Range
          philmodjunk

          Hmmm, this is the key complication from the original thread: "Items are not tracked individually;"

          The thread you refer to was originally set up to check to see if "ditch digging machine #124" was available, not to check to see how many ditch digging machines are available for the specified date.

          You'll need to modify the relationships by some sort of category field instead of an itemID--that uniquely identified each item in the original solution and then compare the total number of items in inventory to the total available to determine if the specified number of items can be rented over the interval specified on the rental invoice.

          How are you specifying which item (and the qty) to be rented on this rental invoice. I can see that you have a line items table and presumably a portal to it on your invoice layout, but what field in your line item table identifies the item(s) to be rented on that line?

          And where do you record the date range over which the items will be rented? In Invoices? (makes sense for data entry but complicates the availability checking against the line items records.)

          • 2. Re: Equipment QTY Available for a Given Date Range
            MichaelVoccola

            In the Inventory table, each record has the following fields:

             

            • ItemID
            • TotalQTY (total number of said item that we own)
            • Category
            • SubCategory
            • Other fields not applicable to this thread

             

            Each specific item does not have a unique ID; it is setup as such: "We have a total of 10 Yamaha M7CL's in stock". There is not an ItemID for each Yamaha M7CL, just the group of 10.

            The date range is stored in Invoice::StartDate & Invoice::EndDate

            LineItems are related to Invoices through the InvoiceID field (present on both tables)

             

            If you need a screen shot of anything, I can post that too.

            • 3. Re: Equipment QTY Available for a Given Date Range
              MichaelVoccola

              Also, the total QTY on each invoice is stored in LineItems::QTY.

              • 4. Re: Equipment QTY Available for a Given Date Range
                philmodjunk

                Just from a business management perspective, I'd think you'd want to log unique ID's for each machine on each rental invoice as a way to track any issues (such as who returned a machine in damaged or unserviced condition) with the specific rental of a specific machine, but maybe you do that elsewhere and in any case, it isn't germaine to your current question.

                First problem is to get the dates to be part of the tables where we need them to be inorder for the records in your inventory tables to be able to produce an availability count for each item in inventory. Someone else may have a better idea, but I think we'll need to use a script to manage the values in these additional date fields in order to get this portal to work. We can do that with some script trigger controlled scripts using triggers set on the invoice's start date and end date fields.

                Define a pair of date fields, gInvStartDate, gInvEndDate in the Inventory field. OnObjectSave triggers on the invoice date fields should be set to run a script to update these global fields so that a change in date on the invoice is reflected in the availability checking done in the portal to inventory.

                Define a pair of date fields, ItemStartDate and ItemEndDate in LineItems. Define them to use a looked up value setting to auto-enter the matching dates from the Invoice date field. (You need them in the line items table so we can include the fields in a relationship). Since it's possibile that you may have to modify the date fields on the invoice, the same OnObjectSave triggered script that updates the global fields in Inventory should also check for any existing line items records and update them to match.

                Now we can set up relationships between Inventory and LineItems that match on date ranges. These will be very complex, though and I wonder how well calculations based on these relatiosnhips will update. Be prepared to include script steps that use commit record, refresh window [flush cached join results] to force things to update after changing a date.

                • 5. Re: Equipment QTY Available for a Given Date Range
                  philmodjunk

                  First Relationship is pretty straightforward:

                  Inventory::ItemID = LineItemsByEnclosure::ItemID AND
                  Inventory::gEndDate > LineItemsByEnclosure::ItemEndDate AND
                  Inventory::gStartDate < LineItemsByEnclosure::ItemStartDate

                  Count ( LineItemsByEnclosure::ItemID ) will return the number of items reserved for the currently specified date range in gEndDate and gStartDate when the start date comes before the Itemstartdate and the end date comes after the item end date.

                  The problem here is that the same lineItem record can end up "counted" by more than one of these relationships. I changed the inequality operators in the above relationship to eliminate some of that double counting, but some double counting is still possible.

                  Inventory::ItemID = LineItemsByStartDate::ItemID AND
                  Inventory::gStartDate > LineItemsByStartDate::ItemStartDate AND
                  Inventory::gStartDate < LineItemsByStartDate::ItemEndDate

                  Count ( LineItemsByStartDate::ItemID ) will return the number of items reserved for the currently specified date range in gEndDate and gStartDate when the start date comes falls on the interval from Itemstartdate to itemenddate.

                  Now define this calculation field, cLineItemsList, set to return text, as:

                  List ( LineItemsByStartDate::LineItemID )

                  LineItemID is a serial number field set up to unqiuely identify each line item record.

                  Now for the last relationship which must be modified not to count any items already counted in the previous relationship:

                  Inventory::ItemID = LineItemsByEndDate::ItemID AND
                  Inventory::gEndDate > LineItemsByEndDate::ItemStartDate AND
                  Inventory::gEndDate < LineItemsByEndDate::ItemEndDate AND
                  Inventory::cLineItemsList ≠ LineItemsByEndDate::LineItemID

                  Now this expression in a calculation field defined in Inventory will compute the number of items available:

                  QtyOnHand - Count ( LineItemsByEnclosure::ItemID ) - Count ( LineItemsByStartDate::ItemID ) - Count ( LineItemsByEndDate::ItemID )

                  • 6. Re: Equipment QTY Available for a Given Date Range
                    LaRetta_1

                    Hello there Michael Voccola!!

                    It may be necessary to use Refresh Window [ Flush Cached ] here but I caution against it.  There are always techniques (usually via good structure or trigger) to force refresh without using it.  Most people are unaware of how it even works or they would not suggest it so freely.  In all my years of designing, I have only had to flush cache join 5 or so times.  I'll skip further discussion and let this link explain it for me:

                    http://www.digitalfusion.co.nz/weetbicks/ditch-those-flush-caches-use-cartesian-join-instead/42/

                    Mike, if you are structured improperly (and I believe you are) then you will move at snail pace through your design and it will be many-more times complex than needed.  I have read through both posts and I am very concerned on the direction you are heading.  This post and the other does not provide what I would really need to see to make good recommendations ... your current working file.  :^)

                    • 7. Re: Equipment QTY Available for a Given Date Range
                      philmodjunk

                      LaRetta,

                      I share your concern, but haven't figured out an alternative due to the fact that you have to check date ranges in order to determine availability for each item in inventory.

                      The OP appears to have these tables and relationships:

                      Invoices----<LineItems>---Inventory

                      But keep in mind that these are rental invoices not sales invoices so items leave inventory only for specified periods of time--and that's where it gets messy.

                      I welcome any approach that works and also simplifies/updates more smoothly than this is likely to.

                      I have only had to flush cache join 5 or so times.

                      I'm curious, do you have an alternative to the refresh/flush needed when the user selects/enters data in a field used in a portal filter expression? It's the only way I can find to get the portal to properly update to show the correct records in that specific case.

                      I agree that it should not be used if an alternative cannot be found, but seem to find the need for it much more often than you do... If nothing else, the "flash" that comes when the window refreshes is annoying and to be avoided.

                      I'll be reading the article you referenced with interest...

                      • 8. Re: Equipment QTY Available for a Given Date Range
                        philmodjunk

                        Thans LaRetta, learned something new. Doesn't change my use of Refresh Window in some areas as I use it only when other options fail, but including a cartesian join with other more typical options is something new and potentially very valuable as a much better alternative to the refresh/flush step.

                        • 9. Re: Equipment QTY Available for a Given Date Range
                          MichaelVoccola

                          Attached is a view of the relationships I have in place.

                           

                          Most of the tables are not named according what I have been describing; but I have translated them into a more universal naming scheme in writing for the sake of this forum.

                          For translation:

                          Invoices = Components

                          LineItems = LineItems

                          Items = Inventory

                           

                          As you can see, there are also many other tables and T.O.'s, but those are the primary concerns for this post.

                           

                          Pardon any examples of disorganization in that relationship view; I am the only one developing this file for now, so I know where it all is.

                          • 10. Re: Equipment QTY Available for a Given Date Range
                            philmodjunk

                            attached is a view of the relationships I have in place.

                            That needs to be an image file of types Jpg, bmp or gif or we won't be able to see it. You can also upload files to a file sharing site and post the download link here.

                            Pardon any examples of disorganization in that relationship view; I am the only one developing this file for now, so I know where it all is.

                            Until you go back and look at it a month later and can't figure out what you did and why! Wink (I speak from painful personal experience.)

                            You might find the methods outlined in this article a useful way to produce a better orgainzed relationships graph:  http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

                            • 11. Re: Equipment QTY Available for a Given Date Range
                              MichaelVoccola
                               I updated the file type from .tiff to .jpeg
                              • 12. Re: Equipment QTY Available for a Given Date Range
                                MichaelVoccola

                                To add to the complication:

                                As is shown in the relationship graph above, each Event (EventOverview) can have multiple Components, and each Component, multiple LineItems. Think of an event as "2012 Acme 2012 Leadership Meeting", and the Components as "General Session", "Awards Dinner",  "Breakout Room A" etc... The equipment is added to the Components to keep everything tidy, and also to get appropriate pricing per Component. StartDates/EndDates for equipment rental also vary by Component.

                                This portion works great for our day-to-day event management, where we offer full-service event support. The other trick is straight equipment rentals, where there is basically a single Component, but no related event. Initially, I had planned on having a seperate table for "Rentals" with it's own LineItems table. This equipment availability portion seems to make that near impossible.

                                So here is the thought: When creating a new "Job", bring a user to a screen where they can select "New Event" or "New Rental". If they choose event, it makes a new record in the "EventOverview" table where they can add components and other information. If they choose a rental, it makes a component with no reference to an event. At this point in the script, Components::EventID is given the value of "Rental", rather than the ID from an Event Record. Then, the layout displaying upcoming "Jobs" in a portal from EventOverview, could display a seperate portal showing records from Components when "Components::EventID = "Rental"

                                This would still store Rentals items in the same LineItems table as Events without modifying existing relationships.

                                With this method, it seems I could pull off the additional functionality without needing to add complicated steps to this already complicated availability problem. I just want to be sure I am correct on this before proceeding.

                                This project is getting very deep very suddenly. This is my first FileMaker project; I haven't taken any courses or discussed FileMaker in depth outside of these forums, so I want to be sure I am not digging myself a hole on this next few steps (which is almost the last few steps!)

                                Thank you to everybody for your help thus far!

                                • 13. Re: Equipment QTY Available for a Given Date Range
                                  philmodjunk

                                  Keeping all rented items in the same lineItems table is essential, so you have a good approach there, though I might be inclined to generate an event record of type "rental" rather than entering "rental' in the ID field...

                                  Barring a better suggestion using a simpler structure, I'd try using what I outlined, but if you find you aren't getting prompt screen refreshes, then include the cartesian join element described in the article LaRetta recommended to each of the three relationships and see if that works as advertised. Refresh/Flush would then be the fall back if it didn't.

                                  • 14. Re: Equipment QTY Available for a Given Date Range
                                    MichaelVoccola

                                    "Define a pair of date fields, ItemStartDate and ItemEndDate in LineItems. Define them to use a looked up value setting to auto-enter the matching dates from the Invoice date field. (You need them in the line items table so we can include the fields in a relationship). Since it's possibile that you may have to modify the date fields on the invoice, the same OnObjectSave triggered script that updates the global fields in Inventory should also check for any existing line items records and update them to match."

                                     

                                    Would using a calculated field  ( = Invoice::StartDate etc..) in LineItems::StartDate & LineItems::EndDate yield the same result?

                                    1 2 Previous Next