2 Replies Latest reply on Apr 4, 2014 11:57 AM by ffdstudios

    Searching text field in calculation



      Searching text field in calculation


           Hello!  I am using Filemaker Pro 12 on my Mac and have a question about a calculation field.  What I'm trying to do is have the grand total field perform an equation (offer a discount) if the address 1 field contains the words "Maxwells Green".  I have two tables, Customers::__pkCustomersID (parent table) and Events::_fkCustomersID (child table) linked with those fields in a 1 to many relationship.  The customers field I want searched is Customers::Address1.  The field I have created to perform the calculation is Events::GrandTotal.  We are offering a discount to people who live at Maxwells Green, and because I want to make the discount automatic, I was hoping that once the addresses is typed in (ex 7 Maxwells Green) into the Address1 field, that the discount would automatically apply (20% off).  I do not know what to use in the calculation to search the Address1 field to look for Maxwells Green.  Something like  "If address1 contains 'Maxwells Green', perform equation with discount, otherwise perform equation without discount".    Is this even possible in a calculation field or should I think about creating a script to search the address1 for Maxwells Green and populate a 'hidden' field with a 1 and then reference that field in the calculation?  Trying to think this through and curious to see if it's possible to do this in a calculation field.  Thanks! 

        • 1. Re: Searching text field in calculation

               If ( PatternCount ( Customers::Address 1 ; "Maxwells Green" ) ; Do what you want when customer is from Maxwells Green here ; Do what you want when they aren't here )

               PatternCount and If are functions that you can look up in FileMaker Help. Case could be used in place of IF here.

               But you may want to rethink that a bit. At some point in the future, you may want to change the "rules" for who get's this discount. Such a change will require redefining this calculation each time. There are ways to set up matching to a table of values so that you can edit the records in this table to control who get's a discount so that updates become a data entry task instead of a database developer job.

          • 2. Re: Searching text field in calculation


                 Yes, down the road (in the next month or so) I am going to create a discount table for that reason, but for now I just need to get this database up and running and that's the only discount being offered, so it'll do.  Thanks for the input and help, I definitely appreciate it!