Combining data from like fields into one field

[dukane24]

My data source has three fields that have the same type of data.  They were split into three fields because our database UI does not have a multiple selection option for names.  This allows the user to select multiple people (up to three).  Let's call them Employee_1, Employee_2 and Employee_3.

I would like to add code to the load script to combine these fields into one Employee field.  I have thought about perhaps concatenating the fields into one, then using the Subfield function, but this seems messy and I end up with extra separators when there are blank fields.

Is there a simple way that fields can be joined?

I appreciate any suggestions you may have.

Thanks!

[Clever_Anjos]

What you want to happen when all Employee_1, Employee_2 and Employee_3 are filled?

[dukane24]

Hi Clever Anjos,

Then I would like for any one of those Employee names to be a match for that particular row.  And if another criteria were chosen for that row, then all three would be a match.

[Clever_Anjos]

One approach would be another table with a field Employee that is linked to your table by an ID.

SearchTable:
LOAD
  ID, // ID to your table
  Employee_1 as Employee
resident yourtable;
Concatenate
LOAD
  ID, // ID to your table
  Employee_2 as Employee
resident yourtable;
Concatenate
LOAD
  ID, // ID to your table
  Employee_3 as Employee
resident yourtable;