9 Replies Latest reply on Oct 21, 2011 8:21 AM by philmodjunk

    calculation in reports

    Jean-LucBouchard

      Title

      calculation in reports

      Post

      Hi, 

      I have something somewhat trivial to do but I had brain surgery 2 weeks ago to remove a tumor (seriously) and I know it'll give me headaches to try to do/troubleshoot.

      So I was wondering if someone could help me.

      I have products that can be sold individually, in sub-boxes or in master boxes.  For example, product X can be sold by the unit, in subs of 6 or in master of 36 (which contains 6 subs, each with 6 units).

      I need to send a packing slip to my warehouse and in order for them to be effective and cost me less in handling, I need to provide them with the simplest answer.

      I would like to only have one sku per product, so having 3 sku (one for single unit, one for subs and one for master) won't work.

      Instead, I would like a formula that looks at the number of products ordered and produce the simplest answer. (with the assumption that the sub and masters size are already defined in the DB).

      So, if we take the case of product X, (which can be sold by the unit, in subs of 6 or in master of 36 (which contains 6 subs, each with 6 units).) and that a client buys 6, I would like a calculated field/fornula that would return "1 sub-box" as a result (instead of 6 units).

      -If someone buys 7, I would be looking for "1 sub box and one single unit" as a result

      -If someone buys 36, I would be looking for "1 master box" as a result

      -if someone buys 42, then "1 master box and 1 sub box" as a result

      -if someone buys 43, then "1 master box, 1 sub box and 1 single unit" as a result

      Would anyone have suggestions?

      Thanks

      JL.

        • 1. Re: calculation in reports
          philmodjunk

          Let ( [q = QuantityOrdered;
                   Mb = Div ( q ; MasterBoxCount ) ;
                   Sb = Div ( q -  ( Mb * MasterBoxCount ) ; SubBoxCount ) ;
                   Unit = q - ( Mb * MasterBoxCount + Sb * SubBoxCount )
                  ];
                  If ( Mb ; Mb & " master box " ) & If ( Sb ; Sb & " Sub Box " ) & If ( unit ; Unit & " Unit" )
                  )

          Note that with scripting, the same basic algorithm could be used to rewrite a single line item into up to three line items with each entry a different packaging configuration and quantity. I would think that a better approach here as this would enable better reporting of packaging material consumption and cost for each order.

          • 2. Re: calculation in reports
            Jean-LucBouchard

            thanks for this!

             

            I wasn't able to get the code to work; the result would always be "? master box, ? Sub Box, ? Unit

             

            Any idea?

             

            Thanks

            • 3. Re: calculation in reports
              philmodjunk

              I tested this in a test file before I posted it so I know that it works.

              Sounds like either your MasterBoxCount or SubBoxCount fields are either empty or zero at the time this expression evaluates. (Divide by zero will result in the ? like this.)

              • 4. Re: calculation in reports
                Jean-LucBouchard

                hmm, I made sure these fields had data but will investigate more fully tonight.  The problem tends to be between the chair and the keyboard (I'm talking about my chair and keyboard).

                 

                Thanks again.

                 

                JL.

                • 5. Re: calculation in reports
                  mgores

                  Also check that they are defined as number fields (that one has bitten me more than once).

                  • 6. Re: calculation in reports
                    Jean-LucBouchard

                    I got it to work... It was indeed between the chair and the keyboard.

                     

                    I had the calculated field in my invoice table, instead of my invoice_line_item table... 

                    Thanks for the help!

                    • 7. Re: calculation in reports
                      RickWhitelaw

                      This isnot about FM. It's about brain surgery. Four years ago I had a serious "TBI". I am lucky in that I have recovered 100% of my cognitive abilities. It took a lot of work. I wish you the same success.

                       

                      RW

                      • 8. Re: calculation in reports
                        Jean-LucBouchard

                        thanks for the kind words.  In my bad luck (the tumor is cancerous), I was very lucky as it was in the parietal region, so my only side effects are loss of motor skills on the left side for a few weeks (i'm typing with one hand for now...).  It should all come back.

                         

                        Phil, you wrote:

                        ---

                        Note that with scripting, the same basic algorithm could be used to rewrite a single line item into up to three line items with each entry a different packaging configuration and quantity. I would think that a better approach here as this would enable better reporting of packaging material consumption and cost for each order.

                        ---

                        How would you go about doing that?  I've been reading on it and can't find anything.

                         

                        Thanks

                         

                        JL.

                        • 9. Re: calculation in reports
                          philmodjunk

                          I'll assume that you have the basic/classic table and relationships typical of an invoicing system:

                          Invoices----<LineItems>-----Products

                          Where a portal to lineItems is used to list the items purchased. I'm also assuming that three fields in Products provide the box counts for each of the three boxes used.

                          I'm going to leave LineItems unmodified but use the records in LineItems to generate similar records in a "PackingList" table, but customized to meet the requirements of your packaging system so that single line items generate up to three Packing List Records. I'll related PackingList to Invoices exactly the same as I would LineItems. (And with a few changes, this script can be used to replace the line item record with packaging specific entries, but by keeping two tables, you have your items listed "as ordered" and "as packed"--which may be useful in some situations and in this case makes it easy to compare the original records to those created by this script.)

                          #Pull up the LineItems record for the current order on a LineItems Layout so we can loop through them, generating PackingList records
                          If [ LineItems::_fk_InvoiceID //Make sure there are LineItems records for this invoice ]
                          Go To Related Record [Show only related records; From table: LineItems; Using layout: "LineItems" (LineItems)]
                          Go To Record/Request/Page [first]
                          Loop
                              Set Variable[$ProductID ; LineItems::_fk_ProductID ]
                              Set Variable [$InvoiceID ; LineItems::_fk_InvoiceID ]
                              #Create MasterBox entry in PackingList if one is required
                              Set Variable [$MB ; Div ( LineItems::QuantityOrdered ; Products::MasterBoxCount ) ]
                              IF [$MB]
                                  Go To Layout [PackingList ]
                                  New Record/Request
                                  Set Field [PackingList::ContainerType ; "MasterBox" ]
                                  Set Field [PackingList::_fk_InvoiceID ; $InvoiceID ]
                                  Set Field [PackingList::_fk_ProductID ; $ProductID ]
                                  Set Field [PackingList::Qty ; $MB ]
                                  Go To Layout [LineItems]
                              End If
                              #Create SubBox entry in PackingList if one is required
                              Set Variable [$SB ; Value: Div ( LineItems::QuantityOrdered -  ( $MB * Products::MasterBoxCount ) ; Products::SubBoxCount ) ]
                              IF [$SB]
                                  Go To Layout [PackingList ]
                                  New Record/Request
                                  Set Field [PackingList::ContainerType ; "SubBox" ]
                                  Set Field [PackingList::_fk_InvoiceID ; $InvoiceID ]
                                  Set Field [PackingList::_fk_ProductID ; $ProductID ]
                                  Set Field [PackingList::Qty ; $SB ]
                                  Go To Layout [LineItems]
                              End If
                              #Create UnitBox entry in PackingList if one is required
                              Set Variable [$Unit ; Value: LineItems::QuantityOrdered - ( $MB * Products::MasterBoxCount + $SB* Products::SubBoxCount ) ]
                              IF [$Unit ]
                                  Go To Layout [PackingList ]
                                  New Record/Request
                                  Set Field [PackingList::ContainerType ; "Unit" ]
                                  Set Field [PackingList::_fk_InvoiceID ; $InvoiceID ]
                                  Set Field [PackingList::_fk_ProductID ; $ProductID ]
                                  Set Field [PackingList::Qty ; $Unit ]
                                  Go To Layout [LineItems]
                              End If
                              Go To Record/Request/Page [Next ; Exit after Last ]
                          End Loop