3 Replies Latest reply on May 14, 2014 9:46 AM by philmodjunk

    Calculation based on found set



      Calculation based on found set



           I would like to create a calc field that is "F" when a record is in the found set and blank otherwise.  I need this calc to remain even when I move to a new layout.  I tried building calc based on Get(RecordNumber) with no joy.

           Any ideas?

        • 1. Re: Calculation based on found set

               What version of FileMaker are you using?

               And when you "move to a new layout" will the layout be based on the same table occurrence in every case where you need this or will there be layouts that are not based on the same table occurrence? (Presumably, you'll be going to layouts based on other TO's, but let's be sure...)

          • 2. Re: Calculation based on found set

                 FileMaker 13 Pro

                 I will be moving to a layout with a different table occurance but the same layout everytime.  It will be a related table.

                 For some background, I should explain what I'm trying to do.  I have a table, lets call it Table A that has a many to many relationship with Table B.  The two are connected through LineItem_AB table.  On the Table A layout there is a portal that lists all related records in Table B.  Lets say I do a find on Table A and get 4 records in my found set.  My boss didn't want to "step through" each of the 4 records to see the corresponding records in TableB.  He would like to just see single list of TableB records related to the 4 found TableA records. Because the specific TableA record in not as important as the fact that it met our find criteria.

                 I still think it is important to know which TableA record matched the criteria to give us the Table B record.  So I wanted to provide my boss the list he desires but with the Table A information.  So I created a Dummy table with only one record with a value of "F".  I link that to the desired calc field in Table A and create a portal from Dummy to Table B. 

                 I know that a simple GoToRelatedRecords script that would take the user to a list of TableB records related to the 4 TableA records.  But then we would lose the info regarding the specific 4 found TableA records.  I also know that I could write a script that would set a txt field to "F" in each of the found sets and to blank in the others but I thought a calculation field that does that automatically would be cleaner.

            • 3. Re: Calculation based on found set

                   What I had in mind won't work in your context as it would produce an unstored calculation field and that won't produce the needed match field for your relationship.

                   I suggest an alternative: Pull up a set of the join table records that match any one of the records in your found set. Display them in a List view and you can then link to data in either table A or table B to show the relevant info you want in your list. If this produces undesirable duplicates, a sub summary layout part could be used to "condense" them, but this will also prevent showing data from more than one related record in table A or B so this may not be desirable in any case.

                   In FileMaker 13, you can quickly get a return separated list of values from a found set using the new "list of" summary field that they added to this release. You can then use this list of values in a relationship to the join table to get the needed found set:

                   Define sIDList as a summary field that lists the primary key field for your table.

                   Set Field [Table A::MatchList ; Table A::sIDList ]
                   Go to Related Records

                   Then will take you to a found set of join table records if you add another occurrence of the join table and link it to MatchList.