3 Replies Latest reply on Sep 3, 2014 10:32 AM by philmodjunk

    Best method to calculate an ending from a starting quantity on items

    JeffBroderick

      Title

      Best method to calculate an ending from a starting quantity on items

      Post

      I am building an FM Go solution.

      There is a table called Load. There will be a truck that is loaded with a Load::Qty, Load::Item, and Load::Description filled out. There is a separate TO that creates a relationship of Load Sheet to Load Items through the Line Items table.

      I created a field, Load::Status with a checkbox for "Starting" and "Ending". The first record would be checked as "Starting" and the next one would be "Ending".

      Throughout the day, items will be sold on invoices.  At the end of the shift, the actual quantities would be filled in for the ending amounts not sold. For now, the sales don't affect the starting and ending loads. There will be reporting later to compare the sales with the physical counts that are done from starting and ending loads.

      My dilemma.  I'm trying to figure out the best way to either report or calculate the difference between starting and ending load.  It's probably simple and I'm not adept yet and haven't run into this type of problem yet.

      Should "Ending" be a different table or layout? Should I create a different layout and make calculation fields to subtract starting from ending?

       

       

       

       

        • 1. Re: Best method to calculate an ending from a starting quantity on items
          philmodjunk

          Can you map out your tables and relationships for us? You can use a screen shot of Manage | Database | relationships or type them up in a format similar to the first post found here: Common Forum Relationship and Field Notations Explained

          What exactly does an "ending" entry represent? Product on hand that has not been ordered? Ordered product that has not yet shipped?

          It sounds like you are trying to manage two different types of "inventory": The "virtual inventory" that represents what product you would have on hand if all ordered product were shipped out and the "physical inventory" that represents all product currently on hand--whether ordered or not. That sound about right?

          • 2. Re: Best method to calculate an ending from a starting quantity on items
            JeffBroderick

            Thanks PhilModJunk,

            I always find that it's not as easy to convey what I think makes sense.  I guess I made it over complicated and I appreciate your help, in this and all the posts I have read in which you have commented.

            This is the portion that pertains to the Load Sheet:

            Load--------<LineItems>--------LoadItems  (LoadItems is a TO of Items)

            Load::_pk_Load_ID = LineItems::_fk_Load_ID

            LoadItems::_pk_item_ID = LineItems::fk_item_ID

            The Ending entry represents a physical count by a route driver after he has sold to the route with the items during the day.

            In this case, the Load Table (loading of the truck) "Starting" and "Ending" doesn't really impact inventory as much as allow for a match later to see if all the sales from the invoices match the balance of the items left over at the end of the day.  Later, I plan to have a sales report by item that can be matched to the balance of the starting and ending load.

            The LoadSheet layout contains a portal from Line Items.  Line items stores the serial _fk_Load_IDand allows the creation of records with Qty in LineItems, Item Number and Description from Items.

            The driver fills the truck with the items to satisfy his route and enters the Qty put in the truck by Item number and description.

            Throughout the day, receipts for cash or invoices are created and when the truck returns to the warehouse, another physical count of items is made as the truck is offloaded - the items include live bait.  The Ending count is logged. The only time the Load sheet is accessed is filling in the morning and emptying at the end of the shift.

            The "physical inventory" part of it is not critical because with the exception of packaged items, a lot of the product might be something like a 5 gallon bucket of minnows which are scooped with a net before loading and dropped back into a water tank if not sold. The main purpose of the Load Sheet is to add a layer of complexity for his route drivers to help control theft by omission or duplication of cash tickets, something that will be eliminated anyway with a digital copy that is locked down after the first print.

            As I am typing this, I think I  have found a solution staring me in the face.

            My initial problem was having a layout where the "Starting" load was put on one record and the "Ending" was put on another record, designated by a checkbox - I was having trouble trying to subtract the starting from ending on two records in the same layout.

            I am thinking now that I can put a field on the LineItems that has Ending Qty and add the fleld Ending Qty to the portal.  Then, maybe I can make a summary layout that shows the difference between the two amounts or on that same layout using a calculation field for LineItems::Ending Qty minus LineItems::(starting)Qty

            Sorry for the lengthy nature.  If there is a better or different strategy, I'd like to hear about it.

            Thanks very much

            • 3. Re: Best method to calculate an ending from a starting quantity on items
              philmodjunk

              The Ending entry represents a physical count by a route driver after he has sold to the route with the items during the day.

              Aha! so this is what the driver has left on the truck when returning to base. That clears things up a lot.

              I think that you are on the right track. One field for qty loaded another for qy remaining and a calculation field to report the difference makes since.

              And theft should become difficult if you set this up to manage the end of shift "cash out" of the driver's receipts in a way that matches what is actually on the truck against actual funds received...