2 Replies Latest reply on Nov 12, 2013 6:07 AM by rudihoremans

    Multiline field

    rudihoremans

      Title

      Multiline field

      Post

           In an inherited solution I have (amongst others) these two fields:

           FIELD_ID = unique ID number

           FIELD_ID_related = unique IDs related to this product (including the unique ID number of FIELD_ID) (text field with numbers on separate lines)

           I would like to copy the values in "FIELD_ID_related" to all records whose "unique ID number" is contained in the filed "FIELD_ID_related".

           Exemple:

           FIELD_ID = 12345

           FIELD_ID_related =

           67890

           87896

           85964

           12345

           There is a record with FIELD_ID = 67890.

           I would like a script or calculation to copy the four lines of FIELD_ID_related for record 12345 to the filed FIELD_ID_related of record 67890.

           No manual work, there are 125,000 records....

           Rudi Horemans

        • 1. Re: Multiline field
          philmodjunk

               This is sometimes called a multi-value key and is used as a kind of "short cut join table" for implementing a many to many relationship by using it as a match field in relationships. Used in that manner, a record in the related table will be linked if it's match field matches to any one of the listed ID numbers.

               If that is how this field is being used, why do you want to combine the ID's in this fashion? It could produce fields with very long lists of ID numbers and will affect how any relationships based on this field function.

               In most cases where I'd encounter such values, I'd give careful consideration to the possibility that the data needs to be moved into records of a related "join" table where you have one record for each value as it provides additional flexibility when working with this data.

               That said, you can set up this self join relationship:

               YourTable::FIeld_ID_Related = YourTable 2::Field_ID

               and use it in this script:

               Show All Records
               Go to Record/Request/Page [First]
               Loop
                  Go To Related Record [Show only related records; From table: YourTable 2; Using layout: "YourTable 2" (YourTable 2) ]
                  Replace Field Contents [No Dialog ; YourTable 2::Field_ID_Related ; List ( YourTable 2::Field_ID_Related ; YourTable::Field_ID_Related ) ]
                  Go to Layout [original layout]
                  Go to Record/Request/Page [next ; exit after last]
               End Loop

               This code combines the data already present in the field with the new data from the parent record. It does not attempt to filter out duplicate values in this list of IDs.

          • 2. Re: Multiline field
            rudihoremans

                 Thanks for the technique. With a few adaptations (due to specifics of my database), this works flawlessly.