4 Replies Latest reply on Nov 24, 2011 11:59 AM by creek

    Problem with the calculation.

    creek

      Title

      Problem with the calculation.

      Post

      I don't know how to calculate some values. (I use FMP 11)

      I have 2 tables (Orders and Shapes). Orders is in a relationship with Shapes.

      Copy database in the attachment.

      Eeach order covers items of one shape, specify the amount of items ordered and receives status (NEW or OLD).

      I'd like to know:

      1. OLD orders. (xSummaryStatusOLD)
      2. NEW orders. (xSummaryStatusNEW)

      3. Number of items of each shape ordered (orders with NEW status) (TotalAmountNEW)
      4. Number of items of each shape ordered (orders with OLD status) (TotalAmountOLD)

      http://www.4shared.com/file/ug3OOr9l/Sums.html

      Could you help me?

        • 1. Re: Problem with the calculation.
          mgores

          to get a count of how many old orders there are you can define a calculation field to be Count (Orders::Status = "old"), you can do the same for new.

           

          To get the number of items for each you can define a summary field to be the Sum(Qty ordered) and place it in a 1 line portal filtered to show what you want, old or new.

          • 2. Re: Problem with the calculation.
            philmodjunk

            you can define a calculation field to be Count (Orders::Status = "old")

            I believe that expression will return either 1 ( true ) or 0 ( False ) depending on whether the status of the first related record is or is not "old".

            Define a calculation field in Orders as: If ( Status = "old" ; 1 )

            Take the count of some of that calculation field.

            If you have FileMaker 11, you can also use a summary field defined in Orders with a one row filtered portal that filters for only new or old orders to get the same two counts. (Same summary field can be used in both portals.)

            • 3. Re: Problem with the calculation.
              LaRetta_1

              you can define a calculation field to be Count (Orders::Status = "old")

              I believe that expression will return either 1 ( true ) or 0 ( False ) depending on whether the status of the first related record is or is not "old".

              Count ( Orders::Status = "old" ) is not even valid syntax.  Count() function cannot contain anything but field name.

              1. OLD orders. (xSummaryStatusOLD)
              2. NEW orders. (xSummaryStatusNEW)
              3. Number of items of each shape ordered (orders with NEW status) (TotalAmountNEW)
              4. Number of items of each shape ordered (orders with OLD status) (TotalAmountOLD)

              Where should these four requirements be viewed?  What is the User doing and why does the User need all of this information at once?  I ask because this smacks of a simple grouped, summarized report.  If you wish to see this information in a portal or when scrolling through Orders or Shapes while in Browse mode then we need to know exactly what should be displayed and where.

              I will make one assumption and that is, when in Shapes, you want to see the number of NEW and OLD orders.  If you wish to view only ONE portal on your Shapes layout then you can create a global text field in Shapes (maybe called gStatus) and attach your 'New Value List 2' to it as checkbox.  Your relationship would then be modified to add Orders::Status = Shapes::gStatus.  Place this checkbox above your Orders portal on the Shapes layout and it will filter depending upon selection.  You can add an auto-enter REPLACE calculation to the global with: 

              Case ( IsEmpty ( Self ) ; ValueListItems ( Get ( FileName ) ; "New Value List 2" ) ; Self ) ... so that it will contain both values if empty.

              The idea is to use summary fields WHENEVER POSSIBLE over Sum() or Count().  Summaries can be used many different ways; they are much more flexible than Count() or Sum() which are tied statically from one table occurrence to one related table.  Summary fields can be used up the relational chain from the table regardless of the TOG.  So Orders should have the following summary fields:  sCountOrders (count of OrderID) and sTotalAmount (total of Amount).  Once the portal of Orders is in place on Shapes layout (with the relationship including Status), take both your summary fields from Orders and place them on your Shapes layout. 

              With more information, we could be even more specific (and probably shorter, LOL) on providing items 3 and 4.

              • 4. Re: Problem with the calculation.
                creek

                Thank you for your tips. I had a special case.

                 

                I found a solution

                 

                If someone wants to know.

                 

                For questions 1 and 2 - I used the function "COUNTIF".


                http://fmforums.com/forum/topic/62910-countif-function/page__hl__countif__fromsearch__1

                For questions 3 and 4 - I used the function "TypeSumFiled".

                http://www.briandunning.com/cf/894

                I don't have the FM Advanced. I had to modify the code and put in "Specify Calculation"