9 Replies Latest reply on Oct 21, 2013 10:15 AM by philmodjunk

    script that finds all records that field A is Greater than a field B in FileMaker Pro?

    FernandoSantos

      Title

      script that finds all records that field A is Greater than a field B in FileMaker Pro?

      Post

           How do I write a script that finds all records that field A is Greater than field B in FileMaker Pro?

           Database name:Calculater

           Fields Names:

           A

           B

           I'm using:

           Show all Records

           Enter Find Mode[]

           SetField [Calculater:A; ">" & Calculater::B

           Perform Find[]

           Doesn't work... Can you please help me?

           Best

            

        • 1. Re: script that finds all records that field A is Greater than a field B in FileMaker Pro?
          IT_User

               That should be working.  Is it giving an error or wrong results from the find?

               Here is a post by PhilModJunk:

               http://forums.filemaker.com/posts/3a02f9dc28

          • 2. Re: script that finds all records that field A is Greater than a field B in FileMaker Pro?
            FernandoSantos

                 Thanks for your answer.

                 Error:

                 This action cannot be performed because this field is not modifiable.

                 Field A and Field B are calculater type, but we're in find mode....

            • 3. Re: script that finds all records that field A is Greater than a field B in FileMaker Pro?
              philmodjunk

                   Better check that mode. You won't get it if you are in find mode.

                   Are both fields non global fields in the same record?

                   Thus you want to find a record where Field A is 4 and Field B is 2 and also a record where Field a is 1 and Field B is 0 ?

                   The script as written won't work for that, but it also should not produce the error message that you have reported.

              • 4. Re: script that finds all records that field A is Greater than a field B in FileMaker Pro?
                FernandoSantos

                     Thanks Phil

                     I've checked Find mode on script - correct as Enter Find Mode

                     Correct - both fields non Global in the same record.

                     I tried with a record with Field A =4 and Field B = 2 and other record with Field A=1 and Field B=0 as you sugested. Gave me an error: The provided find criteria is not valid. Enter a valid request before proceeding.

                     I've tested with both Global fields as well, Global Fields type calculater (Global Field A = Field A and Global Field B = Field B and A Global Result field numeric type, where I Set Field the calculation Global Field A & ">" & Global Field B) just to try....

                     Doesn't work as well. (error: The provided find criteria is not valid. Enter a valid request before proceeding.)

                     Best

                      

                • 5. Re: script that finds all records that field A is Greater than a field B in FileMaker Pro?
                  philmodjunk

                       I have made no suggestions at this point, only asked for clarification.

                       I don't think that your script is generating the error that you report. I suspect that something else is tripping that error after this script or part of the script is performed. Try putting a show custom dialog step just before Enter find mode and a second immediately after perform find[]. I predict that your error will appear either before the first custom dialog appears or after the second one appears. Perhaps a script trigger is being tripped by the script shown...

                       But the script that you have specified won't work. The contents of non global fields are empty while in find mode so this script step:

                       SetField [Calculater:A; ">" & Calculater::B ]

                       only enters the lone inequality operator ">" into field A. If field B were global, that would not be the case, but it does not appear that field B can be global as you need it to store a different value in every record. You'll need to do the comparison in browse mode and then set up find criteria that searches for the results of that comparison.

                       Define the field cAcompareB as

                       A > B

                       Select Number as the result type.

                       Now use this script:

                       Enter Find Mode[]

                       SetField [Calculater:cAcompareB ; 1 ]

                       Perform Find[]

                       And show all records is not needed in this script nor does it have any effect on the results of your first script.

                       For more examples of scripted finds, see: Scripted Find Examples

                  • 6. Re: script that finds all records that field A is Greater than a field B in FileMaker Pro?
                    FernandoSantos

                         Thanks Phil for your answer

                         I did the script you told me to do and it worked. But in this case it put a "1" even on records that Field B is 0 because Field B is smaller than Field A. Field B is a calculation field that I need after the calculation with the valoues calculated on present record. Than I want to know which records have Field A greater than the value of field B of present record. I need the find criterie respect the values of 3 Fields not only one, as I ask you. When I know to do the script to one result I'll kwow to do for other 2 fields as well.

                         I tried a new script that works well but I in this script I as you can see use a Copy to clipboard the value of field B and than I pasted there and works.

                         I need to compare 3 fields and this way I only can use one clipboard Copy value (Field B, I need Field C and Field D values as well).

                         Show All Records

                         Copy[Select; Calculater::B]

                         Enter Find Mode []

                         Insert Text[Select; Calculater::A; ">"]

                         Past [Calculater::A]

                         Perform Find []

                          

                         Do you see the situation?

                         Best

                          

                          

                          

                    • 7. Re: script that finds all records that field A is Greater than a field B in FileMaker Pro?
                      philmodjunk

                           Use set variable in place of copy. Set Variable [$B ; value: Calculator::B]

                           Use set field in place of the insert and paste steps:

                           Set field [Calculator::A ; ">" & $B ]

                           When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                      • 8. Re: script that finds all records that field A is Greater than a field B in FileMaker Pro?
                        FernandoSantos

                             Thanks Phil

                             It already works as I want

                             Great

                             Thanks a lot

                             Best

                        • 9. Re: script that finds all records that field A is Greater than a field B in FileMaker Pro?
                          philmodjunk

                               Yes, but copy should not be used like this as it destroys any data the user may have previously copied to the clip board.