Combining field data under certain conditions

[Rebecca]

I have converted a spreadsheet to FMP. I have duplicate records that have a field with different information. I want to combine the info in the field together, if the ID number is the same. How do I go about this?

Example: have Id number 123456: record has "Information A" in field "Type"

Also have ID number 123456: record has 'Information B" in field "type"

 

I want field "Type" to say: "Information A, Information B" for my ID number 123456.

[comment]

Here's one way (out of many) it could be done: 

1. Import the records into a new table, where the ID field is validated as Unique, Validate always.
2. Define a relationship between the two tables matching on the ID field;
3. Replace the contents of the Type field in the new table with a calculated result =

Substitute ( List ( OldTable::Type ) ; ¶ ; ", " )

 

[Rebecca]

That is working perfectly. Now I have to take it to another step:

Sample data is:

ID number 123456

Type: desert

contains: sand

contains: rocks

contains: water

Type: ocean

contains: fish

contains: salt

contains: coral

 

Is there a way to do this list but keeping desert and ocean separate lists?

 

[comment]

I am afraid you have lost me at this point. I don't understand the meaning of:

6 hours ago, Rebecca said:

contains: sand

or what should the end result look like.

 

I also should have said this from the very beginning: are you sure you want to lump your data into a single field? It will make it very difficult to work with.

Going with your first example, the ideal arrangement would be to have 2 tables: a parent table where each ID is a unique record (that would be the new table you have created) and a child table of Types where each Type would be a related record (that would be your "old" table). Any field that describes the ID should be in the new table, and only in the new table. Any field that describes the type should be in the Types table, and only in the Types table.

 

[Rebecca]

I'm attaching a file with real data. See if this makes more sense.

 

thank you for all your help

Sample.xlsx

[comment]

54 minutes ago, Rebecca said:

See if this makes more sense.

Not really, I am afraid. Is there any connection between Type and Accommodation? Or are they two independent attributes of ID?

 

[Rebecca]

I'm attaching in FMP format. So I have Student ID number multiple times. Then type can be:

scheduling
presentation
Setting

I'm not combining these.

But those 3 types can be present multiple times.

What I'm trying to achieve is if there's 2 presentations for a student ID, combine those. But keep presentation as a type separate from scheduling, setting, etc.

A good example is 3994401

 

Hope this helps.

thank you for your patience.

504s.fmp12

[comment]

You did not answer my question:

5 hours ago, comment said:

Is there any connection between Type and Accommodation? Or are they two independent attributes of ID?

 

[Rebecca]

On 9/15/2020 at 4:10 PM, comment said:

Yes those accommodations are of a specific type.

 

[comment]

Then maybe you should consider a structure of:

Students -< Accommodations >- Types

where Accommodations is a join table between Students and Types.