9 Replies Latest reply on May 18, 2012 10:29 AM by philmodjunk

    Calculation issue regarding inventory



      Calculation issue regarding inventory


      I have a fairly detailed database with clients, transactions, products and lineitems (joined by relationship, etc). 

      step 1 - I select a client, which shows a list of products on a lineitems portal [client_LINEITEMS]

      step 2 - I create a transaction report to deliver 4 products (code 222) and 2 products (code 333) listed on a transaction portal [transaction_LINEITEMS]

      step 3 - QUESTION - how can I keep a running total of the material delivered to the client for code 222 and code 333 in this example, keeping in mind that each client will have multiple transactions associated to it each year

      GOAL - I want to be able to do monthly, quarterly reports of how many products each client purchased, using the above method to also establish frequencies, inventory, costs, etc

      Please help with this, I am stuck!!

        • 1. Re: Calculation issue regarding inventory

          It would help to see how your relationships are set up in more detail. I would have set them up as:


          where "transactions" functions as a sales invoice with a portal to Lineitems to list the products purchased in that transaction.

          From what you posted, however, I'm not sure that this is what you really have here.

          If it is, a summary report based on the LineItems table can be set up to provide a list of items purchased with subtotals for each product type and client that should do what you describe.

          • 2. Re: Calculation issue regarding inventory

            I used to have a separate service report and a separate invoice report. Our business has changed quite a bit and now I am working on 1 report to have multiple duties called a 'transaction report' (still in beta).

            The client has a list of potential products and active products; active products have a checkbox. When a transaction is created, the client ID carries into the transaction report (relationship kp_clientID [client] and kf_clientID [transaction]) and the checkboxed items are carried over with setfield and setvariables (I did this because certain clients have different taxes, discounts, fuel charges - this way, we approve the client setup page and lock it down and do not need to approve individual transactions).

            The initial client_LINEITEM portal uses product codes from client_LINEITEM::kf_ProductID which enters then enters the product name from client_lineitem_PRODUCT::ProductName, the pricing/fuel/taxes and discounts are taken from the client_LINEITEM and put into transaction_LINEITEM portal where enter a sell|quantity and have all the calculations for the final invoice. 

            My difficulty is that many times we deliver product (empty containers that are not billable) and are used in a continuous circle. We deliver 2 empty items, pick up 2 full items but sometimes we deliver more than we pick up and vice versa. I want to know a total of what we deliver, and what we pick up based on the selected date range. This information is only entered on the transaction_LINEITEM portal from ipads on the road. When I find the cilent, I may have 8 transactions all with different quantities. I really want to be able to view the totals back in the clients main page inside the client_LINEITEM portal. 


            Please let me know what else is needed for your help?

            Thanks in advance Michael

            • 3. Re: Calculation issue regarding inventory

              I create a transaction report to deliver 4 products (code 222) and 2 products (code 333) listed on a transaction portal [transaction_LINEITEMS]

              Does that mean that you have created two records in the LineItems table? one for 4 222 products and one for 2 333 products?

              If so, my description of a summary report based on the line items table will work.

              • 4. Re: Calculation issue regarding inventory

                Sorry to go on, you are right, multiple records in the lineitems table. When I go into the lineitems table layout, I can separate with a find search the lineitem_transaction_CLIENT::kp_ClientID and it pulls up all the transactions associated along with individual product codes. When I combine the search to select the client and a specifac product it narrows my results and the summary field stock_supply|client provides the right number.

                I am at a loss how to make this work so that it appears on the client_LINEITEMS portal while being a unique summary for each indivdual product being shown.

                I can think of messy work arounds but nothing that is clean. I really want to be able to scroll from client to client and see the total quantity of items purchased. Later I will work on the transaction section to select dates and combine it with charts, etc. but first I need to solve this problem.

                Thanks again - I attached a simply cut and paste of what I am dealing with.

                • 5. Re: Calculation issue regarding inventory

                  Why do you need to see this in the portal? A report showing this data for one or multiple clients can be produced from a summary report based on the line items table.

                  The method described so far, won't work in a portal and getting a portal to work like that will take a fair amount of effort to set up.

                  If a customer purchases 2 of product A and 3 of Product B on one transaction and 4 of Product A on a second transaction, do you want to see this in your portal?

                  Product A    6
                  Product B    3

                  • 6. Re: Calculation issue regarding inventory
                     Yes I am hoping to see 6 and 3. The information is more for internal viewing which is why the portal is the cleaner way as you can scroll from record to record and see the details at a glance. Any ideas?
                    • 7. Re: Calculation issue regarding inventory

                      Yet a single mouse click can pop up a summary report for the current client, it can even be in a floating window that you close when you want to move to the next client..

                      The challenge to making this happen in a portal is that you can set up a sub summary part like you can in a summary report layout to combine multiple records into a single entry and subtotal. Instead, you have to have a single record for each subtotal with a relationship that links to the correct set of related records in line items.


                      Client::ClientID = Client_products::clientID

                      Client_products::ProductID = Client_LineItems::ProductID AND
                      Client_PRoducts::ClientID = Client_LineItems::ClientID

                      client_products is a new table that you have to keep correctly updated with scripts such that each time a client buys a product for the first time, you add a new record. Client_LineItems is an occurrence of LineItems, but with a ClientID field added so that the Client_Products records can match to only line item records created for a given client. A clientID fields with a looked up value setting should be able to copy this value from the Transactions table at the time the line item record is added to your table.

                      • 8. Re: Calculation issue regarding inventory

                        Just about to get started, the portal sounds like it is going to end up changing the look I need. I really only have space for 2 boxes on the same line as my other lineitems. From what I understand I will not be able to accomplish this.

                        To make a summary report, how do you suggest I do it so that only the products associated with a particular client appear? keeping in mind that I will be adding or deleting products from clients as the time goes. I am figuring a related search of client_TRANSACTION::kf_ClientID (which will pull up all associated transactions for that particular client), then do a sub-summary based on products that are returned from the related search.

                        Do you have improvements on this?

                        Thanks again

                        • 9. Re: Calculation issue regarding inventory

                          I'd Use Go to Related Records to pull up all line items for a given client. (Given the client---<transactions---<lineitems relationship, you can pull up the line items records in one step this way.

                          I'd also include checks that halt the script if there are no related records in line items to pull up for the current client.

                          Summary fields in line items can count and total values in that table.

                          If you delete the body layout part and just include a sub summary part "when sorted by" your product ID, you can produce a list of products, one row for each product with counts, totals etc for each product included.