2 Replies Latest reply on Mar 18, 2012 12:26 AM by VincentH

    Replace field content based on input



      Replace field content based on input


      Hi all,


      I've 2 tables one containing a fix list of values, the other is table a containing all the "changable" data.

      Table 1: (the 3-letter data corresponds with the 4-letter data and vs)

      3-letter        4-letter
      AAA             ABCD
      BBB              ABCE
      ...                  ....

      Table 2:
      TextField  -  4-lettercode

      Table 1 and 2 are relationally linked by 4-lettercode.


      In the end TextField needs to have a 4-letter code. But on data entry I would like to have the possibility to enter the 3-letter code wich automaticly changes it to the 4-letter code on leaving the TextField. Ofcourse nothing needs to be done when the field is entered by a 4-letter code.


      Any suggestions?


        • 1. Re: Replace field content based on input

          You could use the OnObjectValidate trigger to perform a script that uses the data entered (if length = 3 ) to perform a find on the first table to find, and enter the corresponding 4 letter code. But you should be able to set this up with an auto-entered calculaition:

          First, in Manage | Database | relationships, make a new table occurrence of table 1 by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as Table1By3Letter.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Add it to your relationships like this:

          Table 2::textField = Table1By3Letter::3LetterField

          Then you can create this auto-enter calculation:

          If ( Length ( Self ) = 3 ; Table1By3Letter::4LetterField ; self )

          and then clear the "do not replace existing value..." check box.

          • 2. Re: Replace field content based on input

            Works like a charm.

            Thank you.