2 Replies Latest reply on Jul 21, 2012 9:31 PM by philmodjunk

    ID a single letter in a field  for calculation



      ID a single letter in a field  for calculation


      I have a field called Sales codes. It has a drop down list consisting of four letters each defining a type of sale. they are M for a manufactured item, P for a purchased part, F for Field service and E for Equipment thats used not new. Each of these would represent a different commission percentage paid to the sales person. What I don't know how to do is; In a calculation, using an if statement or whatever type of statement, be able to id the idividual letters. The "P and E"will get the same % M is different and so is the F.      

      I tried

      If ( Sales Code = "P" or "E" ; Job Profit * .1 ; 0 )  no luck.

      So how would I get my commission field to recongnize each individual letter?

      I have FMP 11 advanced


      Thanks for any help in advance,



        • 1. Re: ID a single letter in a field  for calculation

          Case is most often used rather than a nested If Statement.

          Google "case statement filemaker"

          Returns one of several possible results based on a series of tests.
          test - any text or numeric expression.
          result - result corresponding to the expression.
          Parameters in curly braces { } are optional.
          Data type returned   text, number, date, time, timestamp, container


          You can type returns within a calculation to help you organize the calculation. FileMaker Pro ignores normal keyboard returns within calculation formulas. For example, you can organize a Case statement like this:

          Case (test1,
          Result if true,
          Result if true,
          Result if true,
          Result if false

          In complicated formulas, this can help a great deal. You can also use it to separate out complicated elements of large formulas.

          Search these forums only   https://www.google.com/search?q=case+statement+site%3Aforums.filemaker.com

          • 2. Re: ID a single letter in a field  for calculation

            Just adding to that...

            If ( Sales Code = "P" or "E" ; Job Profit * .1 ; 0 )

            would work if written like this:

            If ( Sales Code = "P" or Sales Code = "E" ; Job Profit * .1 ; 0 )

            You may also want to consider setting up a lookup table where you have one record for each type with one field for the sales code and one for the commission rate. With a relationship that matches by salescode, a field in your original table can use a looked up value setting to copy the appropriate rate from this look up table.

            With that setup, you can add/remove/change sales types by editing records in this table and you can also modify the commission rates for each and the change will not affect existing records only the new ones created after you make the change.