Jump to content

Combining field data under certain conditions


Recommended Posts

  • Newbies

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.

Link to post
Share on other sites

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 ) ; ¶ ; ", " )

 

Link to post
Share on other sites
  • Newbies

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?

 

Link to post
Share on other sites

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.

 

Link to post
Share on other sites
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?

 

Link to post
Share on other sites
  • Newbies

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

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.