9 Replies Latest reply on May 17, 2016 2:46 PM by Jade

    Help with a Calculation / Script

    PaulCarroll

      Hi Folks

       

      I am in the process of adapting the Inventory solution to suit my needs for an equipment management system.

       

      Essentially, I am creating a packing list form as an extension to the database so that for each job we send out we can create a packing list of equipment required.

       

      I am looking to create a calculation / script so that the following occurs.

       

      Assuming I have 20 of item A and 30 of item B "in stock"

       

      On 25th May I have a job requiring 12 A's and 4 B's.

      The Job is out from 25th until 30th May

      On 27th May I have another job that requires 6 A's and 14 B's. This Job is for 4 days duration.

      If I had another job on 29th requiring 8 A's I would not have sufficient in stock.

       

      I need to reference the "Date Out" and "Date Returned" from the packing list so that the database will warn if we are over booked on certain items.

       

      This is probably quite simple but I am still quite new to scripting etc so any pointers gratefully received.

       

      Many thanks

        • 1. Re: Help with a Calculation / Script
          siplus

          It's not that simple.

           

          Ideally you should have a function or a script that gets 2 date parameters and an item ID, giving you back the minimum of item "ID" you have in that period.

           

          There's an interesting article marginally touching the problem here.

          • 2. Re: Help with a Calculation / Script
            PaulCarroll

            That does sound complicated as you say.

             

            I wonder if a simpler solution could be found.

             

            Would I be able to link 20 items being included on a packing list with the transactions field already included in the solution. If entry of 20 x A was recorded as a transaction "out" and return of same became an "In" then I would always have an accurate stock count of whats available.

             

            I am not sure how I might begin to set this up but, assuming this is achievable it would negate the need to reference the date ranges in the original question.

             

            Is that making any sense?

            • 3. Re: Help with a Calculation / Script
              siplus

              If the "job is out for n days" has a fixed duration, i.e. n = constant, it can be simplified, otherwise I'm afraid not.

              • 4. Re: Help with a Calculation / Script
                PaulCarroll

                OK.

                 

                Sorry if I'm not giving up here but if n = constant makes the process easier, does n multiplied by a given quantity go back to being complicated? i.e.. If n = 1 day can i not then have a separate way of selecting number of days?

                • 5. Re: Help with a Calculation / Script
                  siplus

                  That would collide with

                   

                  Original post: need for 2 dates, hence my first post, and

                  Next post: negate the need to reference the date ranges

                  • 6. Re: Help with a Calculation / Script
                    Jade

                    Sounds like you need a "Reservations" table by equipment and by date.  Each time you create a packing list, you add the quantity needed for each piece of equipment to each day record in that table…checking that the sum of the quantities for that day does not exceed the equipment total.

                    • 7. Re: Help with a Calculation / Script
                      PaulCarroll

                      siplus

                       

                      Yes, it would.

                       

                      However, I am simply trying to look for a solution that is not over complicating matters.

                       

                      Apologies if the subsequent posts don't follow the same questions - when the original idea was seemingly difficult I realised i needed to change thought process and try to find an easier way.

                      • 8. Re: Help with a Calculation / Script
                        PaulCarroll

                        Jade - That sounds like a workable approach.

                         

                        Would you think that would be difficult to set up?

                        • 9. Re: Help with a Calculation / Script
                          Jade

                          It will take a bit of patience, testing, and time but not overly difficult.  (I aways say that at this stage ;-)

                           

                          I assume you already have an equipment table with an equipment id and a packing list table with its id.

                           

                          Roughly, the relationship graph might look something like this:

                           

                           

                          Packing_List—< Reservations >—Equipment

                           

                          Reservations is a many-to-many join table with these fields:

                          Packing_List_ID_fk

                          Equipment_ID_fk

                          Date

                          Quantity

                          Running Total, Summary, or…??

                           

                          When you add equipment to the packing list you need to add a record to Reservations for each day in the packing list's date range.

                           

                          When you change an equipment quantity in the packing list, you must find those records that match the equipment id and date and then change those quantities in Reservations.

                           

                          When you delete a packing list or equipment within the packing list, you must delete those matching records in the Reservations.

                           

                          You will need a means (script) to validate the reserved quantities. I haven't figured out whether a running total, sum, summary or find matching records could be used to compare the sum of each day's reservations against the equipment quantities on-hand.