This is a public Forum  publicRSS

Forum post

    Scott Fields
    "IF" statement question
    Forum post posted November 14, 2011 by Scott Fields , last edited February 9, 2012
    254 Views, 8 Comments
    Title:
    "IF" statement question
    Post:

    I am trying to write an IF statement that I can pull the last 2 characters off a 11 digit serial number and use that to have the database auto enter a certain value. How would I write that?

    Answer

     

    • PhilModJunk

      If ( right ( serialNumberField ; 2 ) = "23" ; "CertainValue" ; self )

      This assumes that you are defining an auto-enter calculation and I've arbitrarily chosen "23" as the "last two digits" that cause the specified value to be assigned. I use Self to keep the value in this field unchanged if the last two digits do not equal "23".

    • raybaudi

      If (
      Right ( YourTable::YourSerialNumber ; 2 ) = "put here your condition" ; "put here your Certain Value"
      )

    • Scott Fields

      Can I assume that I can change the right to left if I need the last 2 digits of the serial number?

    • PhilModJunk

      Use whichever works. In my dictionary, right would return the last two digits. Left would return the first two digits.

    • Scott Fields

      Ok. I got that working. Now I need to do multiple entries with different criteria.

    • PhilModJunk

      A calculation might not be the best option. Looking up values from a related table may work with much more flexibility.

      If you do want to try this as a calculation, use a case function in place of the If function.

    • Scott Fields

      Ok. So after looking into it you are right. A case function would most likely serve my needs better. I need the last 2 digits of Field 1 of layout 1 to reference layout 2 Field 1 and put the information from Layout 2 Field 1 & Field 2 into Layout 1 in it's own field. If needed i can give the exact field info I need. I'm just not sure how to right the Case Function/Script.

    • PhilModJunk

      Hmmm, whenever I see a poster refer to layouts like this, I get a bit concerned. Layouts refer to table occurrences, which in turn refer to tables. Different layouts can refer to the same table occurrence and different table occurrences can also refer to the same table. See the potential for confusion? (Table occurrences are the "boxes" created in manage | Database | relationships so that you can define relationships that link your various tables.)

      I still think looked up value settings where a related table serves as the source of values to be looked up is much more flexible. Should you need to change the values looked up, you can simply edit the records in this related table instead of needing to redefine your case function.

      With either method, I'd like to see a more detailed description than "Field 1" and "Field 2" before I go into more detail. Can you give an example with actual data?