Citizenship Status can support custom Data Sources, which is defined in the Field Settings module.
The value defined in Field Settings will be stored on the Person record.
This lookup table resolves the value of the Data Source with the label.
el_lookup_citizenship_statues correlations to the user-citizenship-us-status field. While this field has a system data sources by default, you also have the ability to create your own, "Regular", data sources to support the values used by your institution. This functionality is core to Element451's data model, but presents a unique challenge for these lookup tables and the other models that join to them.
Regular data sources do not have value validation, such as a rule preventing non-unique values. As such, you may configure to a data source to have two rows with the same value. While not recommended in most use cases, it can be useful for integrations.
Lookup tables are then used in dimensional models to convert data values saved on the contact into data labels that are human friendly. Our preferred method for joining is the left join for its high performance. However, when one student's citizenship status value matches two rows in the lookup table, the student is duplicated in the results.
To solve this, we've added a value_occurrence column to the lookup tables listed. This column uses the row_number() function to count each occurance of a specific value in the table. The first value is numbered 1, the second 2 and so on. When joining to the lookup table, you'll now be join on value_occurrence = 1 to prevent duplications.