This question has had no reply for too long. A question about the contacts table in September showed that the project needs a general position.
The ODM does not accept a list of values in one cell. Each cell holds one value. When a source field holds several values and the matching ODM field holds one, the values go into separate rows.
Applied to the three options:
- Options 1 and 2 (keep the first or the last value) are not acceptable. They drop data and leave no record that data were dropped.
- Option 3 (expand into several rows) is the correct approach. It is the only option that keeps every source value.
Martin identified a real problem with option 3. When a row is copied to carry several values, other tables that refer to its primary key point only to the first copy, and the other copies are never referenced. This shows that the values belong in a table designed to hold them.
The model has three such tables: sampleRelationships, polygonRelationships and protocolRelationships. Each has its own primary key and records a subject, a relationship and an object, so every row can be referenced. A fourth was proposed for contacts in September, in topic 222.
For a mapping, the rule is therefore to expand into rows when the target table can hold them and no other table refers to the copied rows. When other tables would refer to them, a relationship table is missing. Please report that as an issue rather than work around it.
In the example, “Hospital” and “Drainage Pipe” belong to two different ODM fields, sampleShed and siteType, so no option is needed. Many apparent multi-value cases are two concepts stored in one source column, and are best separated first.