1 2 Previous Next 15 Replies Latest reply on May 26, 2014 1:34 PM by DanielShanahan

    calculating inventory total

    fmk4

      Hi,

       

      I have an warehouse application that requires a report to show the total number of item for each stock types by date of entry

       

      Inventory table

       

       

      item descriptiondate of entryquantity
      apple21/5

      4

      orange20/51
      pear20/52
      pineapple20/53
      apple20/52
      apple21/51
      pear21/55

       

       

       

      1. If I run the report for 21/5 I would like to get the following report

       

       

      item decriptiondate of entryquantity
      apple21/55
      pear21/55

       

      1. If I run the report for 20/5 I would like to get the following report

       

      item descriptiondate of entryquantity
      orange20/51
      pear20/52
      pineapple20/53
      apple20/52

       

      2. What is the best way to get the above report?

       

      I tried using summary field but the result is calcuated real time and I cant use it for further calcuation of the summary field.

        • 1. Re: calculating inventory total
          DamianKelly

          Create a sub summary report based on the inventory table. You will need leading sub summary sections for item description and maybe the date of entry (I would suggest this ought to be top). You will not need a body part. Within the inventory table create a sub summary field for total of quantity. Now add the field to the sub summary sections.

           

          If you sort by date then quantity you will get something close. If you find the records (which might be tricky in a lyout with no body, you might need to do that in another layout with the same base table then go to the report) for a given date that will match exactly what you want from a report sense.

          • 2. Re: calculating inventory total
            fmk4

            Hi,

             

            The issue is with the sub summary report is, if I were to use sub summary, I cannot further process the resultant sub summary field result, example, if apple - orange < 5, Field "Warning" = yes

            • 3. Re: calculating inventory total
              DamianKelly

              Something like that?

              • 4. Re: calculating inventory total
                wimdecorte

                fmk4 wrote:

                 

                 

                I tried using summary field but the result is calcuated real time and I cant use it for further calcuation of the summary field.

                 

                Do you want it calculated real-time and ue the result in further calculations?  Careful with that if you were thinking of using calculated fields.  Very quickly these are going to be become a real performance hog.

                If it fits in your workflow, calculate these on-the-fly through scripts and store the results statically (in regular number fields, not calc fields).

                 

                As to how to create the report: ExecuteSQL() would be a good candidate.

                • 5. Re: calculating inventory total
                  fmk4

                  I need it to be calculated real time, ie at the point of the report generation. If certain product are below a preset limit, I need to highlight it (hence the need to process the sub summary field).

                   

                  I could calculate the results on the fly thru scripts and store them statically, but there are thousands of records, it would take time to generate these report on the fly. Is there any other way?

                  • 6. Re: calculating inventory total
                    PSI

                    Hilighting a product that hits a preset limit has nothing to do with a sub summary. Preset limit should be a number field and the hilighting could just be conditional formatting based on whether the Qty In Stock less than or equal to the preset limit.

                     

                    As Wim said you need stored fields.

                     

                    John

                    • 7. Re: calculating inventory total
                      DamianKelly

                      Absolutely Wim! Live stock calcs in all but the smallest of systems are rash. There are some issues to be careful of, such as record locking preventing the writing of stock updates. For example user 1 has sku 1 open, user 2 tries to book out some (maybe to a PO line). User 2 can not mark the origional stock record's inventory figure.

                       

                      There are a couple of ways around this. I like having another table with a 1::1 relationship for the stock figure. This cures a significant proportion but not all, the stock figure table can still be locked. So I tend to add another table for tansactions, within that table I have an applied field. The users interaction will try and apply the transaction, if they suceed then the transaction is marked as applied, if not then I let them proceed. This means the stock figure is incorrect but I use the server and a scheduled script to clean up data

                       

                      The incorrect stock figure is acceptible with in the business logic of our organisation (its very rare but we would rather live with the stock not updating straight away than not be able to create an order, as with all things you need to know your failure modes and understand the risks) but may not be ok with you, best to check. The user is made aware of the stock issue using a conditional format based on a fast query (ie a query on SKU and unapplied transactions, this is quick as the applied flag and sku are indexed) and can try and force an update too.

                       

                      You can still have a live calc for stock that will not slow the sku table if you have that in another table with a 1::1 relationship (infact putting slow calcs in another table can give some monster speed ups). As a triple check the server will check the stored vs the calcs once per night. Thats not a quick script.

                      • 8. Re: calculating inventory total
                        fmk4

                        Hi Damian,

                         

                        Yes I can generate the report as per your example, but what if I need to hid apple if apple < 5 in the report?

                        • 9. Re: calculating inventory total
                          DamianKelly

                          you could do something like:

                           

                          GetSummary ( total ; description )

                           

                          Or a self join on description and Sum (inventorySelfJoin::quantity)

                           

                          But these will start slow and if you have a lot of records get very slow. Then glacial.

                          • 10. Re: calculating inventory total
                            fmk4

                            Hi Guys,

                             

                            1. If I do a search for date 21/5 I would get the following result

                             

                            item descriptiondate of entryquantity
                            apple21/5

                            4

                            apple21/51

                             

                             

                            2. If I do a summary (sorted by item) report on the above I would get

                             

                            item descriptiondate of entryquantity
                            apple21/5

                            5

                             

                            3. What if I dont want to display any item that is less then 10 in quantity?

                            In this case, If I run the report again, the report will show up empty.

                             

                            I couldnt figure this out as I couldnt use the summary field calculation to restrict the display of item.

                            • 11. Re: calculating inventory total
                              erolst

                              fmk4 wrote:

                              3. What if I dont want to display any item that is less then 10 in quantity?

                              In this case, If I run the report again, the report will show up empty.

                               

                              I couldnt figure this out as I couldnt use the summary field calculation to restrict the display of item.

                               

                              I assume you mean: any item that is less than 10 in aggregated quantity …?

                               

                              Throw away (i.e., omit) everything that's below your threshold. One way to do that would be to apply the Fast Summary method.

                               

                              Assuming you have the fields quantity, sTotalOfQuantity and sCountOfPrimaryKey (self-explaining), you could write a script along the lines of:

                               

                              … all your finding and sorting steps

                              Set Variable [ $threshold; Value:10 ] // or read e.g. from a global field

                              Go to Record/Request/Page [ First ]

                              Loop

                                Set Variable [ $groupSum; Value:GetSummary ( Events::sTotalOfQuantity ; Events::cItemName )

                                Exit Loop If [ $groupSum ≥ $threshold ] #optional, if you've applied a resort by sTotalOfQuantity, ascending; if so, then everything from up here doesn't require checking

                                Set Variable [ $groupCount; Value:GetSummary ( Events::sCountOfPrimaryKey ; Events::cItemName ) ]

                                Set Variable [ $nextGroupAt; Value:Get ( RecordNumber ) + $groupCount ]

                                Set Variable [ $isLastGroup; Value:$nextGroupAt - 1 = Get ( FoundCount ) ]

                                If [ $groupSum < $threshold ]

                                  Omit Multiple Records [ $groupCount ] [ No dialog ]

                                Else

                                  If [ not $isLastGroup ]

                                    Go to Record/Request/Page [ $nextGroupAt ] [ No dialog ]

                                  End If

                                End If

                                Exit Loop If [ $isLastGroup ]

                              End Loop

                               

                              PS: On the topic of reorder: is there a way to e.g. re-sort by sQuantity descending, but sort by name ascending?

                              • 12. Re: calculating inventory total
                                beverly

                                This is what the GetSummary () function could be used for. As an unstored calculated field, it may be slower.

                                 

                                Or perhaps the ExecuteSQL() function to FIND the matching records. Then report on the found set. There are several ways to use the results of the eSQL as a interim result to get to those records. It depends on what you need to display (what should the report be, for example).

                                 

                                -- sent from my iPhone4 --

                                Beverly Voth

                                --

                                • 13. Re: calculating inventory total
                                  fmk4

                                  I did the following to get it working

                                   

                                  Copy the data over to another table and did the calculation in the table instead of summary report.

                                   

                                  Run the report based on this table.

                                   

                                  I am not sure if this will hold up against 100K table size, as compared to the Fast Summary and GetSummary() as described above?

                                  • 14. Re: calculating inventory total
                                    erolst

                                    fmk4 wrote:

                                    Copy the data over to another table and did the calculation in the table instead of summary report.

                                    Run the report based on this table.

                                    Calculating aggregates for groups of records simply takes time, no matter how you do it. Why add the conceptual and processing overhead of copying the data to another table first?

                                    fmk4 wrote:

                                    I am not sure if this will hold up against 100K table size […]

                                    I assume you're running these reports against a subset of records (e.g. for a date range), not the total count?!

                                     

                                    To answer your question yourself, why not try both methods (probably better not with a 100k set …)?

                                    1 2 Previous Next