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

    ID a single letter in a field  for calculation

    David_DMD

      Title

      ID a single letter in a field  for calculation

      Post

      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,

      Smile

      David_DMD

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

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

          Google "case statement filemaker"
          https://www.google.com/search?q=case+statement+filemaker

          Purpose
          Returns one of several possible results based on a series of tests.
          Format
          Case(test1;result1{;test2;result2;...;defaultResult})
          Parameters
          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



          http://help.filemaker.com/app/answers/detail/a_id/4554/~/calculation-creation-tips


          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,
          test2,
          Result if true,
          test3,
          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
            philmodjunk

            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.