1 Reply Latest reply on Apr 28, 2015 5:56 AM by philmodjunk

    Conditional Sums Using Drop Down Status

    alimagator

      Title

      Conditional Sums Using Drop Down Status

      Post

      Still a noob and working through some tutorials but can't figure this one out:

      I'm trying to build a shopping list type setup and give the items on the list a status of "Buy" or "Wishlist" by pop up menu. I have a calculation field that should be doing a SUM based on this status, but it only seems to acknowledge the status in the first portal row and then does a full Sum of everything regardless of the status.

      What am I missing here?

      Screenshot_2015-04-28_01.03.54_copy.jpg

        • 1. Re: Conditional Sums Using Drop Down Status
          philmodjunk

          What you are missing is how calculations that reference data in a related record function.

          If ( ItemList::status = "Buy"

          is your reference to data in a related table. When you put such into a calculation field, the expression accesses the value of the first related record. The value of all other related records is ignored.

          Sum ( ItemList::Itemsubtotal )

          If you were to look it up in help, sums a value over all related records. The If function that you wrote only controls whether the function sums all related record or is not used to sum anything.

          And this is exactly the results that you report.

          To selectively sum data from a related table you have to use a different approach:

          Set up a relationship that matches only to the records you want to sum. In this case, that would either be two relationships to two Tutorial: What are Table Occurrences? of ItemList or one relationship where you "select" for one category or the other by setting a match field. In either case, you would add a second match field to the relationship that matches to the status field in ItemList.

          Use a filtered portal where the portal filter filters for a particular status. You use a summary field defined in ItemList and a one row portal. But keep in mind that this is a "display only" method of getting these sub totals. If you want to use the computed values in additional calculations, use a different method.

          If you are using FileMaker 12 or newer, the executeSQL function can use SQL to query ItemList and compute a total for a specified value in status.

          Caulkins Consulting, Home of Adventures In FileMaking