11 Replies Latest reply on Feb 4, 2010 1:00 PM by philmodjunk

    Omitting records based on the value of a summary field in a subsummary part

    nutcracker

      Title

      Omitting records based on the value of a summary field in a subsummary part

      Post

      [FMPro10, MacOSX 10.5]

       

      I'm wanting to remove records from a found set based on the value of a summary field INSIDE a sub-summary part after sorting. 

       

      I'm have a script that looks like this:

       

      script

       

      That generates a inventory report like this:

       

      inventory report 

       

      The records are sorted by the line_item item_id so each item (BABY PAUA, AFFIRMATION EARRINGS) is grouped and the summary fields calculated from this grouping. 

       

      The stock column shows the number of items the customer has in stock (sum_of_quantity_in_stock). It's the ordered items quantity - the sold items quantity, like this:

       

      calcs 

       

      sidenote: line_items is used for both order and invoice line_items, hence the need for quantity_sold and quantity_ordered to check the IsEmpty of invoice_id (true if the line_item is an invoice_line_item instance).

       

       In the inventory report above you can see that the AFFIRMATION EARRINGS stock count is 0. I don't want this line to appear in the report. Only when the stock (sum_of_quantity_in_stock) in the subsummary part is > 0 should the sub-summary part be included.

       

      Any ideas on how to do this?

       

      Thanks,

      Mike 

       

        • 1. Re: Omitting records based on the value of a summary field in a subsummary part
          deltatango
            

          it seems the easiest thing to do would be to add another parameter to your search:

           

          quantity_in_stock > 0 

          • 2. Re: Omitting records based on the value of a summary field in a subsummary part
            nutcracker
              

            Hi deltatango - thanks for the quick response. Unfortunately that doesn't work as hoped because it eliminates records that have a negative quantity_in_stock (circled in yellow below). These are the records that are related to invoices (blue ticks below). The positive quantity_in_stock records belong to orders.

             

             

            found_set 

             

            So its the sum_of_quantity_in_stock that I need to search against. But its a summary field and I need to search it after the report is generated.

             

            Is this even possible in FMP?

             

            Thanks,

            Mike 

            • 3. Re: Omitting records based on the value of a summary field in a subsummary part
              deltatango
                

              Then do a constrain search after you perform find and do the following:

               

              Omit Records when quantity_in_stock = 0 

              • 4. Re: Omitting records based on the value of a summary field in a subsummary part
                philmodjunk
                  

                Yeah, summary fields don't evaluate in find mode and that keeps you from omitting items with a zero balance.

                 

                I'm assuming this is an inventory log type file where your summary report has mutiple records for each item--each record documenting a given change in inventory. Is that correct?

                 

                You probably have a price list table where one record = one item. If not, create one. (if you have multiple records for each item in your price list table with effectivity dates to document price changes, you can still use that file if you set things up right.)

                 

                Link this table to your inventory table by ItemID.

                Add calculation fields that use the Sum() function to compute the subtotals for each item.

                 

                Now you can perform a find on this table with criteria that excludes a zero balance to drop those items off the report.

                • 5. Re: Omitting records based on the value of a summary field in a subsummary part
                  nutcracker
                    

                  Reply to deltatango:

                   

                  Maybe I'm missing something but I can't see that achieving what I want.

                   

                  Here is the found set:

                   

                  found set

                   

                  In order to get the result in the report that I want I would need to remove the bottom two records (item_id = 180). But it's the summarized value of sum_of_quantity_in_stock that needs to be checked to see if it is > 0. Is there any way to perform the summarization within the script and then constrain the find based on the value of sum_of_quantity_in_stock? 

                  • 6. Re: Omitting records based on the value of a summary field in a subsummary part
                    deltatango
                       You can create the calculations that Phil talked about and then run the find on those fields.
                    • 7. Re: Omitting records based on the value of a summary field in a subsummary part
                      nutcracker
                        

                      I'm assuming this is an inventory log type file where your summary report has mutiple records for each item--each record documenting a given change in inventory. Is that correct?

                       
                      Essentially correct. The line_items table tracks changes in inventory with the quantity_in_stock field calculating the stock adjustment that the record accounts for. I've renamed the quantity_in_stock field to quantity_stock_adjustment as the old name was misleading (sorry deltatango). Here's the graph...
                       
                      graph
                       
                      Whenever the line_item has an invoice_id then it's an invoice_line_item, otherwise its a order_line_item. The quantity_stock_adjustment for invoice_line_items will always be negative. For order_line_items it'll always be positive. I'm tracking the stock held by a customer (retail shop) so an order from a customer adds items to their stock. When I create an invoice it means they have sold items, so an invoice_line_items will subtract items from stock.
                       
                       You probably have a price list table where one record = one item. If not, create one. (if you have multiple records for each item in your price list table with effectivity dates to document price changes, you can still use that file if you set things up right.)
                       
                      Yes, that table is called items and whenever a line_item is created from the item (when building a order or invoice) the price from the item is looked up and copied to the line_item. This way prices of items can change without altering order/invoices.
                       
                      Link this table to your inventory table by ItemID. Add calculation fields that use the Sum() function to compute the subtotals for each item. Now you can perform a find on this table with criteria that excludes a zero balance to drop those items off the report.
                       
                      The line_items table (inventory table) has an item_id field so each line_item belongs to an item. I've created a calculation field in my items table that sums the quantity_stock_adjustment from related line_items. The result is as expected but this doesn't take into account various customers. The stock I'm trying to calculate is just for a specific customer, hence line three in the script:
                       
                      script 
                       
                      Each line_item has a customer_id so each line_item belongs to a customer.
                       
                      I need the sum of line_items -> quantity_stock_adjustment to be grouped/limited by the line_items -> customer_id too.
                       
                      Hope that makes some sense.
                      Mike 

                      • 8. Re: Omitting records based on the value of a summary field in a subsummary part
                        philmodjunk
                           That definitely makes things more of a challenge. I'm a bit puzzled by your description though: If you are only finding inventory changes that apply to a specific customer, how on earth can your summary fields correctly compute a stock level? (Since such a stock level has to sum up all inventory adjustments not just those from a given customer's invoices.)
                        • 9. Re: Omitting records based on the value of a summary field in a subsummary part
                          nutcracker
                            

                          Hi Phil and thanks for digging into this - greatly appreciated.

                           

                           If you are only finding inventory changes that apply to a specific customer, how on earth can your summary fields correctly compute a stock level? (Since such a stock level has to sum up all inventory adjustments not just those from a given customer's invoices.)

                           

                          By using multiple subsummaries I'm able to generate this:

                           

                          inventory report 

                           

                          So I'm sorting and using subsummary parts to group by:

                           

                          line_item -> customer_id

                          line_item -> item_id

                           

                          Here's the report layout for line_items:

                           

                          layout 

                           

                          I've found the GetSummary function that might help me out because it returns the value of a summary field and can be used in a calculation. I'll investigate that now. Any further pointers would be helpful too.

                           

                          Thanks,

                          Mike 

                           

                           

                          • 10. Re: Omitting records based on the value of a summary field in a subsummary part
                            philmodjunk
                              

                            Thanks for the added detail, but it leaves the question unanswered. Let's look at what you would see if the body part were added so you could see the individual invoice records in the report.

                             

                            Cust 1

                              Red Widgets sub totals: 6 sold  balance 9

                                Red Widgets      5 sold   bal: 10

                                Red Wdigets      1 sold   bal:  9

                             

                            Cust 2

                              Red Widgets sub totals: 3 sold  balance ?

                                Red Widgets      2 sold   bal: ?

                                Red Wdigets      1 sold   bal: ?

                             

                            When you are looking at the same product for two or more customers a simple summary field isn't likely to compute the same balance due to the sub summary part forcing the field to evaluate as a sub total over only those records labeled with the same customer ID.

                             

                            See the problem? For your total sold, a simple summary works, but your balance on hand will have to be the total of all the inventory for this item--not just a specific customer and if you refer to the balance summary field in a calculation, you get the total of all your changes--not just those for a specific item.

                            • 11. Re: Omitting records based on the value of a summary field in a subsummary part
                              philmodjunk
                                

                              Here's what I would experiment with:

                               

                              I'd link a Table Occurrence of your Inventory Log table to a second TO of the same table by the ItemID field--this will link a given record to all inventory changes for the same item.

                               

                              Then I can define a calculation field that uses Sum(RelatedTObyItemID::cBal) to compute the current balance on hand. You will be able to perform a find on this field, but it's unstored, so be prepared to wait a bit for it to evaluate.