1 Reply Latest reply on Dec 2, 2013 6:02 AM by philmodjunk

    If Statement Add / Subtract

    Annette

      Title

      If Statement Add / Subtract

      Post

           Hi, 

           I have two Tables, we will call 1 and 2.  They are related by Table 1PK to the Table 1FK in Table 2.

           I have a count field on table 1, which I have set to when a record is created it automatically set to 0.

           On a layout based on table 1, I have a portal for table 2 with a button that will run a script.  I want the script to basically do the following.

           If field1 says Assessment and date field is greater than the current date add 1 to the count field in table 1

           if field1 says assessment, the date is either today or earlier and field 3 says DNA then subtract 1 from the count field in table 1

           If field 1 says Treatment and the date is greater than current date add 1 to the count field in table 1

            

           Clearly I'm doing something wrong when writing the statement because it is not subtracting the 1 when needed. 

           Thanks for the help!

        • 1. Re: If Statement Add / Subtract
          philmodjunk

               apparently, Field 1 is defined in table 2. You have a one to many relationship and your "count field" is defined in table 1. When you define such a calculation in table1 that refers to the value of a field in table2, it only refers to the value of this field from the first related record. Values in other related records are not referenced.

               You need a calculation that selectively "counts" different records based on the values of different fields in your set of related records.

               ExecuteSQL is one option to use if you are using FileMaker 12.

               If not, you'll need 3 more Tutorial: What are Table Occurrences? of table 2 to link to table 1, each related with a different set of match fields in order to match to records of the specified values so that you can add the counts for assessment and treatment and subtract the count for DNA using the count function.