3 Replies Latest reply on Sep 17, 2013 1:01 PM by philmodjunk

    Scripting Question



      Scripting Question


           I am fairly new to filemaker and i have a script that is giving me fits. I think it is simple for an advanced user so I apologize ahead of time if it seems minuscule. 

           I am trying to have a script that searches2 fields and then changes the value of another field. The problem I am having is that when it searches my second field it is looking for the exact words. 

           Bascially where is says Product I need it to search every thing in the product field for ADDVANT which is the first set of charectors in some of the fields the complete field content would look like this. 

           Here is my if statement.


           If (Company = "North American Life"  and  Product  ≠ "ADDVANT"; Premium*Split;
           Here some of the field content. 



                ADDVANT 20 PT2

                ADDVANT 20 P2

           Thank you ahead of time for any help you can provide. 


        • 1. Re: Scripting Question

               Your if function example uses parenthesis--which is typical of a calculation, not a script. Are you sure that this is part of a script?

               You are correct that Product  ≠ "ADDVANT" will be true for each of the examples that you post because the text in the field includes more than just the ADDVANT text.

               PatternCount ( YourField ; "Addvant" ) will return a nonzero and thus True result in every case where YourField contains "Addvant".

               But if you are searching a table for this data, it would make more sense to use either a scripted find to pull up a found set of records matching the specified find criteria or a relationship that matches only to the records sought  rather than an If step in a script if that is what you are doing here.

          • 2. Re: Scripting Question

                 Again my apologies as I am new, you are correct it is not a script It is a summary field. I am basically trying to change the value of a field called commission based of criteria returned from the the if statement. 

                 If the statement finds the word ADDVANT anywhere in the field PRODUCTS. I want it to change the value of another field.

                 It works great if I use a specific Product name like "ADDVANT-20-PS2" however I want a loser search as there are many variations of ADDVANT products being entered.

                 Please let me know if that clarified what I am trying to do a little more.  

            • 3. Re: Scripting Question

                        you are correct it is not a script It is a summary field

                   Well, it's not a summary field, but a calculation field. Fields of type summary cannot contain a calculation.

                   Your existing calculation could be modified in one of two ways:

                   a) If (Company = "North American Life"  and  Left ( Product ; 7 )  ≠ "ADDVANT"; Premium*Split;

                   b) If (Company = "North American Life"  and  Not PatternCount ( Product  ; "ADDVANT" ); Premium*Split;

                   a) only looks at the first 7 letters so it is a good choice only if the "Addvant" text is only found as the first 7 letters. b) will find "Addvant" anwhere in the text in the product field.

                   But I'm guessing that there are many more If functions here than shown. If so, a Case function will make for a better option in almost all cases. You can look up this function in FileMaker Help or any training materials you have in your possession.

                   And there is a very good chance that such a calculation field is not the best approach in the first place as a relationship could easily be a better option here in most situations that I can imagine.