Concatenating key fields

If a single key field is insufficiently unique to accurately relate two tables, you can create a computed field in each table that concatenates two or more key fields, and relate the tables using the computed fields. You can also use this method when joining tables, but there is no requirement that you do so because joining allows you to select more than one key field per table.

As with single key fields, concatenated key fields must have an identical data structure and data format in the two tables being related.

To concatenate key fields:

  1. Open the parent table and select Edit > Table Layout.
  2. Click Add a New Expression .
  3. Enter a Name for the concatenated key field.
  4. Click f(x) to open the Expression Builder.
  5. Build an expression using two or more key fields and the Add operator (+).

    For example: vendor_ID + location_code


    You can concatenate only character key fields. If necessary, use ACL functions to convert non-character data prior to concatenating.

  6. Click OK.

    If you get an “Expression type mismatch” error, one or more of the key fields are probably not character key fields.

  7. Click Accept Entry and click Close to exit the Table Layout dialog box.
  8. Open the child table and repeat the same steps to add an identical concatenated key field to the child table.
  9. Relate the two tables using the concatenated key field.
Related concepts
Using multiple key fields
Using multiple key fields in combination
Related tasks
Relating tables
Modifying relations

(C) 2015 ACL Services Ltd. All Rights Reserved.