2 Replies Latest reply on Aug 19, 2015 8:27 AM by AndrewClancy

    Total based upon fields in another table from multiple records?



      Total based upon fields in another table from multiple records?


      So I've been banging my head against the wall trying to figure out how to setup something like this: https://www.youtube.com/watch?v=ShjAjB4Sxvg

      where my calculation field will look through several fields in another table (I believe a "child" table, though I am new to filemaker and could be wrong about how it's related... ) and then find and add the relevant fields based upon another field in the same table (so for instance if the Name field in my parent table match's the Name 1 field in the child table it will add Name 1 Amount and so on). However when trying to make this work the calculation field will only consider the fields in the record whose ID matches it's own. So for the first record in the parent table instead of checking Name 1 fields in all the records of the child table, it will only look at the first record. 

      In the above linked tutorial the instructor indicates that simply referencing a field from the child table in the calculation will make it be considered an array and consider all of the values from all of the records. Do you think perhaps it's the fact that I've related the table based off of the IDs? Because I really cannot figure out why the tutorial is having such different results.

        • 1. Re: Total based upon fields in another table from multiple records?

          Sounds like you have a single record where you really should have several so that you only have one name field and associated amount to a given record. Then you can set up a relationship that matches by two pairs of fields, on for ID and one for name.

          Otherwise, you are looking at a fairly complex case function where the clauses of the Case function compare the name to the related Name1, Name2... fields with the result clauses then returning the value of the associated amount field.

          Case ( NameField = RelatedTable::Name1 ; RelatedTable::amount1 ;
                      NameField = RelatedTable::Name2 ; RelatedTable::amount2 )

          • 2. Re: Total based upon fields in another table from multiple records?

            I'm not sure where I could make one record several and still serve my purposes, but I think I may have confused the issue by simplifying too much. Let me give you some better context. I have two tables, an Ingredients table (the table where the total field resides) and a Batches table which contains Name 1 and Name 1 amount (actually Ingredient 1 and Quantity Used 1) and Ingredient 2 and Quantity Used 2 and so on. The idea is to, with the total field, keep track of the amount of the ingredient the people using the database have on hand, based off of the original quantity and how much of each ingredient is used.

            The calculation I've been trying to use goes something like this:

            Original Total Quantity - If (Ingredient 1 = Ingredient Name; Quantity Used 1; 0) -  If (Ingredient 2 = Ingredient Name; Quantity Used 2; 0) and so on...

            The issue with this is, as mentioned, that it's taking only Ingredients and Quantities Used from the record with the same serial number as the Ingredient. Since multiple batches could use some of the same ingredients I'm trying to find a way to make sure the total is calculated based off of fields that fit the bill in all records of Batches.