Newbies Rebecca 0 Posted September 14, 2020 Newbies Share Posted September 14, 2020 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
comment 1,775 Posted September 14, 2020 Share Posted September 14, 2020 Here's one way (out of many) it could be done: Import the records into a new table, where the ID field is validated as Unique, Validate always. Define a relationship between the two tables matching on the ID field; 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 Rebecca 0 Posted September 15, 2020 Author Newbies Share Posted September 15, 2020 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
comment 1,775 Posted September 15, 2020 Share Posted September 15, 2020 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
Newbies Rebecca 0 Posted September 15, 2020 Author Newbies Share Posted September 15, 2020 I'm attaching a file with real data. See if this makes more sense. thank you for all your help Sample.xlsx Link to post Share on other sites
comment 1,775 Posted September 15, 2020 Share Posted September 15, 2020 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 Rebecca 0 Posted September 15, 2020 Author Newbies Share Posted September 15, 2020 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
comment 1,775 Posted September 15, 2020 Share Posted September 15, 2020 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? Link to post Share on other sites
Newbies Rebecca 0 Posted September 17, 2020 Author Newbies Share Posted September 17, 2020 On 9/15/2020 at 4:10 PM, comment said: Yes those accommodations are of a specific type. Link to post Share on other sites
comment 1,775 Posted September 17, 2020 Share Posted September 17, 2020 Then maybe you should consider a structure of: Students -< Accommodations >- Types where Accommodations is a join table between Students and Types. Link to post Share on other sites
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now