12 Replies Latest reply on Jun 5, 2012 2:37 PM by hobbiesdeveloper

    Calculation Field Problem

    hobbiesdeveloper

      Title

      Calculation Field Problem

      Post

      Hello,

      This is my second day trying to solve this problem with getting the calculation working on a calculation field called "Processing Quantity", i have included the screen shot of the layout where the Processing Quantity field is located.

      I'm trying to get the If ( product_ORDER::Status = "Processing" ; Sum( OrderLineItem 2::Quantity ) + 0 ; "0" ) calculation to work which is set for the "Processing Quantity" field.

      It's supposed to get the sum of all the order line items::Quantity with the order status in "Processing" but what it's currently doing is getting the sum of all the Order Line Items with the same Product ID, even if the  Order is not in the Processing status.

      Please let me know how this can be solved. I tried to get the Relationship table working so that only the orderlineitems get summed up with the processing state and they have to be just the same item.

      Here are the screenshots.

      Screenshot1.jpg

        • 1. Re: Calculation Field Problem
          hobbiesdeveloper
          /files/b609ef78d3/Orders_Screenshot.jpg 1280x720
          • 2. Re: Calculation Field Problem
            hobbiesdeveloper
            /files/146c5fa96b/Relations_Chart.jpg 1280x720
            • 3. Re: Calculation Field Problem
              hobbiesdeveloper

              Hello,

              This is my second day trying to solve this problem with getting the calculation working on a calculation field called "Processing Quantity", i have included the screen shot of the layout where the Processing Quantity field is located.

              I'm trying to get the If ( product_ORDER::Status = "Processing" ; Sum( OrderLineItem 2::Quantity ) + 0 ; "0" ) calculation to work which is set for the "Processing Quantity" field.

              It's supposed to get the sum of all the order line items::Quantity with the order status in "Processing" but what it's currently doing is getting the sum of all the Order Line Items with the same Product ID, even if the  Order is not in the Processing status.

              Please let me know how this can be solved. I tried to get the Relationship table working so that only the orderlineitems get summed up with the processing state and they have to be just the same item.

              Here are the screenshots.

              • 4. Re: Calculation Field Problem
                philmodjunk

                Using if with sum like this means that you either get the sum of all related items via the relationship to OrderLineItem 2 or you get 0. It does not sum selectively. In order to sum only lineItems where the order status in the parent record is "Processing" will require using a relationship that filters by the processing status--not a simple thing given that the status is not in line items or a summary field in OrderLineItem could be displayed in a filtered one row portal to show the total (requires Filemaker 11 or later.)

                Assuming that you have Filemaker 11 or later and given the relationships you have in place here, I'd add a one row portal to Product_Order_LINEITEM to your layout and give it this portal filter:

                product_ORDER::Status = "Processing"

                Define a summary field in the lineitem table to compute the total of your quantity field. Put the summary field from Product_Order_LINEITEM in your one row filtered portal. (You can make portal borders invisible so this one row portal looks like any other field on the layout.

                Note that this is a "display only" solution. If you need to use this total in a calculation or a script, you'll need to set up a filtered relationship instead.

                • 5. Re: Calculation Field Problem
                  hobbiesdeveloper

                  I will be using this total in a calculation and script, could you please let me know how I could setup the Filtered Relationship?

                   

                  Thanks.

                  • 6. Re: Calculation Field Problem
                    philmodjunk

                    That won't be easy to do. The status value is in Orders, not line items. You'd need an indexed field in line items that correctly lists the current status in the related order field. At first glance, you'd think you could use a calculation field for this, but such a calculation field cannot be anything but an unstored calculation and this won't work. An auto-entered calculation won't work either as a change in status in Orders won't update the value in line items. Best I can think of is to add a text field in LineItems named status. Add an OnObjectSave script trigger on the Orders::Status field that then finds the related line items records and updates this field with the same value as that currently entered in Orders::Status.

                    If you can get that working....

                    Define a calulation field, constProcessing, in Products to return the text: "Processing".

                    Modify the relationship to Order_LINEITEM 2 to be:

                    PRODUCTS::_kp_ProductID = order_LINEITEM 2::_kf_ProductID AND
                    PRODUCTS::constProcessing = order_LINEITEM 2::Status

                    Now Sum ( order_LINEITEM 2::Quantity ) will compute the some of those Line Item records where the Order status = "processing".

                    • 7. Re: Calculation Field Problem
                      hobbiesdeveloper

                      I will give that a try.

                      • 8. Re: Calculation Field Problem
                        hobbiesdeveloper

                        It's showing a ? in the Processing Quantity field for some reason.

                        I have the Sum( OrderLineItem 2::Quantity ) in the Processing Quantity Calculation field.

                        I have linked the __kp_ProductID ( Product) = _kf_ProductID (OrderLineItem 2) AND status_Processing ( Product ) = Status (OrderLineItem 2)

                        I have also included a orderlineitem status field which confirms that all the order line items are in the Processing State just like the Order.

                        Not sure why it's giving me a Question mark in the Calculation Processing Quantity field?

                         

                        Please help.

                        • 9. Re: Calculation Field Problem
                          philmodjunk

                          How exactly did you do this?

                          I have also included a orderlineitem status field which confirms that all the order line items are in the Processing State just like the Order.

                          It can't be a calculation field it has to be an indexed/stored value.

                          • 10. Re: Calculation Field Problem
                            hobbiesdeveloper

                            I created a field in the OrderLineItem Table called Status with a Calculation of OrderLineItem = orderlineitem_ORDER::Status, should i just convert it to a Stored Index Value instead of a calculation. If yes, how would i make it updated automatically when the order status changes?

                            • 11. Re: Calculation Field Problem
                              philmodjunk

                              You need to read my original instructions on this again.

                              Such a calculation CANNOT be defined as a stored/indexed calculation field and thus cannot be used in this relationship. I described using a text field that would be updated via trigger controlled script as this produces the needed indexed field for this relaitonship to work. I'd prefer not to use this method, but don't have an alternative to suggest here.

                              • 12. Re: Calculation Field Problem
                                hobbiesdeveloper

                                No problem. I though you might have had a different alternative.

                                Thanks for your help