8 Replies Latest reply on Feb 3, 2012 1:49 PM by CourtneyBird

    If script help



      If script help


      I'm new to File maker pro and am wrestling with writing scripts.

      I have three fields. The first field is called "AGE", the second field is called "SEX" and the third is called "DIVISION"  The division category is determined by given age ranges and sex. So I assumed that I should use the "IF" function. What I would like to do is enter a person's age in the "AGE" field and "SEX" field and when I hit the tab key to got to the next field after entering the person's sex, that the proper division goes in the Division field. I assumed that I'd use nested if's since there are several divisions. They are as follows:

      AGE:>=14<=39 and SEX ="M" returns "M1439" in the division field

      AGE:>=40<=49 and SEX ="M" returns "M4049" in the division field

      AGE:>=50<=59 and SEX ="M" returns "M5059" in the division field

      AGE:>=60<=69 and SEX ="M" returns "M6069" in the division field

      AGE:>=70 and SEX ="M" returns "M7099" in the division field

      AGE:>=14<=39 and SEX ="F" returns "F1439" in the division field

      AGE:>=40<=49 and SEX ="F" returns "F4049" in the division field

      AGE:>=50<=59 and SEX ="F" returns "F5059" in the division field

      AGE:>=60<=69 and SEX ="F" returns "F6069" in the division field

      AGE:>=70 and SEX ="F"returns "F7099" in the division field

      To do this does not sound like rocket science. I have used if functions in Excel and in Access, so it really is more of proper syntax and there are few examples that I could find that would give me any guidance. I worte a starter if script without nesting ifs just to test it, but nothing happens when I run the script -- not even an error message. I tried setting a script trigger in the sex field to "onobject enter" so see if that would do anything -- nothing.

      I'm sure there are missing elements, but I'm stumped.

      Here is the stript called Assign Divisions that I have written:

      If(${Combined Marathon-Half-CCC}::AG ≥ 14 ≤ 39 and ${Combined Marathon-Half-CCC}::S  = "M";${Combined Marathon-Half-CCC}::DIV = "M1439";${Combined Marathon-Half-CCC}::DIV="Null")

      End If

      If that had worked, then I would have nested the other if options in the script.

      The other part of this is that I want to be able to run this script globally so that it will assign the proper division to every record so that if I import a batch of records, I can assign divisions to the entire batch.

      A screenship of the table is included.

      Thanks for any help. I just need to get aimed in the right direction.




        • 1. Re: If script help

          Don't use nested If's. While that can be made to work, it's much more complex than other methods you can use.

          With division as a calculation field, you can use a case function:

          Case ( AGE > 14 and AGE < 40  ; SEX & "1439" ;
                      AGE < 50  ; SEX & "4049" ;
                      AGE < 60  ; SEX & "5059" ;
                      AGE < 70  ; SEX & "6069" ;
                      AGE > 70 " ; SEX & "7099" ;
                      /* else */ "Error, too young"

          However, this locks you into a calculation. Changing age intervals or the codes returned require rewriting your calculation. I'd add a related table linked to this table in a relationship:

          YourTable::AGE = Divisions::AGE AND
          YourTable::SEX = Divisions::SEX

          Then Division can be defined as a text field in Divisions. You can either add the Dvision field from Divisions to your layout or you can define a text field in your original table and set it up with a looked up value field option to copy the value of Divisions::Division. With the first option, any changes to the Divisions::Division field in a record in that table automatically shows up in matching records in your table. With the second the changes do not automatically appear and thus past records stay unchanged, but new records show the change in the data. With either approach changes to Division data now becomes a simple process of editing records in the Divisions table (where you would have one record for each division.)

          • 2. Re: If script help

            Thank you very much for your very helpful response. I tried the case function and it works very well with one exception. When I was entering the code in the case funtion, the progam (filemaker pro 11) complained about the default end --  /* else */ "Error, too young" When I enter a persons's age as 8, it returns "1439"

            The code I trascribed is :

            Case ( AG ≥ 14 and AG  < 40 ; S &  "1439" ; AG  < 50 ; S &  "4049" ; AG  < 60 ; S &  "5059" ; AG  < 70 ; S & "6069" ; AG ≥ 70 ; S &  "7099" ;  /* else */ "Error, too young" )


            • 3. Re: If script help

              Make sure that the AG field is of type number (0r a calculaiton field that returns number as it's return type). I'd guess that it is of type text and this would keep this calculation from evaluating correctly. You can check a field's type in manage | Database | Fields.

              • 4. Re: If script help

                (And us Victorian Prudes would suggest changing the field name 'SEX' for 'Gender'.  But maybe that's because I once handed out a form for a young lady to complete, and in the box beside the Field Label 'Sex' she had written, "Once, in Brighton."  Later on another guy wrote "Yes please".  So ever since then...)

                • 5. Re: If script help

                  In response to PhilModJunk, I took your suggestion and converted the Age field to a number field but that didn't work.

                  In response to Sorbsbuster, I agree that is a problem when folks want to be cute, but "Gender" is so PC and blah. And "Sex" takes up less room than "Gender" in the field heading and is straight foward and unambiguous, particularly when the field length is 1 character. Anyway I'm a traditionalist. Our language is cluttered with this stuff -- "unisex" is a lot clearer than "gender-neutral" If I came upon a "gender-neutral" restroom, I'd wonder, before entering, what my options were going to be! Anyway whether its "Gender" or "Sex" I'm just trying to assign divisions...

                  • 6. Re: If script help

                    Ooops, should have suggested:

                    Case ( AG ≥ 70 ; S &  "7099" ;
                               AG ≥ 60 ; S & "6069" ;
                               AG ≥ 50 ; S &  "5059" ;
                               AG ≥ 40 ; S &  "4049" ;
                               AG ≥ 14 ; S &  "1439" ;
                               /* else */ "Error, too young"

                    • 7. Re: If script help

                      Just for the fun of it, I also came up with this calculation:

                      Case ( AG  ≥ 70 ; S & "7099" ;
                                 AG ≥ 40 ; S & Let ( d = Div ( AG ; 10 ) ; d & "0" & d & "9" ) ;
                                 AG ≥ 14 ; S & "1439" ;
                                /* else */ "Error too young"

                      • 8. Re: If script help

                        To PhilModJunk: I tried your first option above (ooops... should have suggested...) and that works like a charm. Thank you very much for your help!