11 Replies Latest reply on Apr 15, 2012 2:12 PM by ericjlindholm

    Rental Inventory Script Help

    ericjlindholm

      Title

      Rental Inventory Script Help

      Post

      I use filemaker to keep track of rental inventory and labor schedule for live production events.  I want to adjust my dropdown list to not include items or personal that are already commited to confirmed events scheduled during the same time frame.  I am sure it has to do with running a report and a summary field.  I just cant quite figure out where to start.  Thank you for your time. 

        • 1. Re: Rental Inventory Script Help
          philmodjunk

          Sounds like you need a conditional value list and not a simple one either. Without knowing the structure of your tables and how they are related, it will be difficult to suggest exactly how you might set up such a conditional value list.

          The trick is in setting up a relationship so that your list of values uses a range of dates to filter out unavailable items and personnel.

          A filtered portal where you select items from the portal might be easier to set up for these two lists.

          • 2. Re: Rental Inventory Script Help
            ericjlindholm

            i have provided a screen shot of my relationships and my line item protal.  Im not sure i can use a filtered portal becase i am already using one for line items.

            • 3. Re: Rental Inventory Script Help
              ericjlindholm
              /files/6732ff81bf/fm_relationship_layout.jpg 926x550
              • 4. Re: Rental Inventory Script Help
                philmodjunk

                Each box in the above screen shot is called a table occurrence. Every layout refers to one of these in Layout setup | Show records from. Every portal refers to one of these in portal setup | Show related records from.

                Your portal would appear to refer to Line Items. In the layout shown, to what occurrence does it refer in layout setup? Event Details?

                What is the difference between a record in "line items" and "Sub Rents"?

                Does a record in Inventory represent a single item or groups of items? (I'd guess groups so that you can rent 50 chairs to one event and 200 to another...)

                Do you have two fields of type date that report the beginning and ending dates of an event? (assuming multi-day events are possible...)

                 Im not sure i can use a filtered portal becase i am already using one for line items.

                You can add any number of portals to the same layout so that is not a problem. Finding room for it on your layout, could, however, be an issue. It's just that a filtered portal may work better for listing who and what is available for a given event.

                • 5. Re: Rental Inventory Script Help
                  ericjlindholm

                  Your portal would appear to refer to Line Items. In the layout shown, to what occurrence does it refer in layout setup? Event Details?

                  Line Items refers to event details ( event details could be compared to invoices the the invoices template)  

                  What is the difference between a record in "line items" and "Sub Rents"?

                  Subs rents is inventory i need to rent from another company to in order to meet an order.  the Sub rents table contains details about when i pick up, drop off, and pay for this piece of gear.  Im am also trying to figure out display the subrented equipment in line items but I am having difficulties.  

                  Does a record in Inventory represent a single item or groups of items? (I'd guess groups so that you can rent 50 chairs to one event and 200 to another...)

                  An inventory item represents an item.  exe "microphone"  I have a "stock level" field that tells me how many I own and a available field that would ideally calc how many are not commited to "confirmed" events during a given time frame.  

                  Do you have two fields of type date that report the beginning and ending dates of an event? (assuming multi-day events are possible...)

                  Yes. event start date and event end date.  also there is an event status field that when marked "confirmed" should reserve selected inventory.  

                  • 6. Re: Rental Inventory Script Help
                    philmodjunk

                    I'm not sure I know what you meant to say with this:

                    Line Items refers to event details ( event details could be compared to invoices the the invoices template) 

                    I'm asking about the layout shown, not the tab panel nor the portal nor the table occurrence named Line Items. I'm asking what name you see when you switch to layout mode and select layout setup, then check the "show records from" drop down in the window that pops up. For now, I'll assume that I'd see Event details if I had your file and checked that setting.

                    In your database, availability is something that can change with the date context specified. Check me on these examples to see if they match your business practices.

                    You have a confirmed Event scheduled for 2/5/2012 to 2/7/2012 (mmddyyyy). Right now, it's 1/31/2012 so the items needed for that event are available for an event scheduled for 2/1/2012 to 2/4/2012 or for an event scheduled to start after 2/7/2012.

                    Items reserved for that event would not be available for events with the following date ranges:

                    1/31/2012...2/6/2012 (End date falls on interval)
                    2/6/2012...2/10/2012 (Start date falls on interval)
                    1/31/2012...2/10/2012 (Neither start nor end dates fall on interval, but Start date precedes start and end date follows end)

                    The fact that one record in inventory can represent multiple items complicates the availability issue. The number of available items of a given ID can vary literally on a day by day basis as events begin and end.

                    But you seem to be emphasizing "confirmed" status here. Is it possible that you want to make the items unavailable from the moment the event that requires them is confirmed through the event's end date?

                    • 7. Re: Rental Inventory Script Help
                      ericjlindholm
                      The layout shown shows records from table"event details".  the Portal in that layout displays related records from table "line items".

                      If I follow what you are saying above, I believe you are correct.  I will try to explain in better detail again to make sure we are talking about the same thing.

                       

                      My inventory records have a "Stock Level" field that indicates how many of an item we own.  it also has a QTY available field that should recaculate at any particular date.    

                      -inventory record "mixer" exixts with "stock level" =10

                      -I have 3 event records with a status "confirmed"

                      event 1 - 2/10/2012 to 2/11/2012 needs 1 mixer  (1 line item related to this event and inventory item "mixer" exisits)

                      event 2 - 2/9/2012 to 2/12/2012 needs 3 mixers (3 line item related to this event and inventory item "mixer" exisits)

                      event 3 - 2/11/2012 to 2/11/2012 needs 2 mixers (2 line item related to this event and inventory item "mixer" exisits)

                      -Pending event changed to confirmed for 2/10/2012 to 2/11/2012 lists 5 mixers

                      (-there are a sum of 6 line item records related to "mixer" during any time between current "event record"s start and end date.)

                      (-in table inventory record, "qty available" should show 4 and an error will be tiggered if more than 4 mixers are commited to the new event.)

                      -> show error

                       

                       

                       

                      • 8. Re: Rental Inventory Script Help
                        philmodjunk

                        I Follow that description, but shouldn't it be possible to set up a confirimed event that uses say 7 mixers if the event dates are from 2/12/2012 to 2/13/2012?

                        Thus availability isn't simply a matter of a single date, but a date range and availability levels will need to change for each such date range specified.

                        • 9. Re: Rental Inventory Script Help
                          ericjlindholm

                          Yes that is correct.

                          • 10. Re: Rental Inventory Script Help
                            philmodjunk

                            I've worked with others on this issue before and wanted to be sure we are on the same page before trying to work out a solution. In cases where you have one record for each item (Say you are renting out rooms instead of equipment), this is a bit simpler, the need to compare the total reserved for each item over a specified date range to the total in inventory makes this a bit trickier.

                            While it may be possible to use a drop down list for this, I'm leaning towards a filtered portal that lists items available for a specified date range with a count of how many are available. This avoids indexing issues that may make a conditional value list of available items tricky to implement. Filter fields set up with the portal can make it possible to "prune" the list of available items down (enter "mixer" in a field and get a list of different types of available mixers...) and clicking a row in the portal can add that item to your list of equipment in a second portal.

                            Does that sound feasable in terms of your layout design?

                            I'm thinking in terms of these basic relationships:

                            Event----<EquipmentList>-----EquipmentInventory    (---< means one to many)

                            With additional table occurrences added to this "backbone" to make the equipment selection portal work.

                            Let me know if this makes sense and I'll move forward in greater detail in my next post.

                            • 11. Re: Rental Inventory Script Help
                              ericjlindholm

                              thought it might be useful to see an update of my layout.  there is now a searchable selecter portal  that i would like the qty available to display in.