Skip to main content

Creating New Table to Expand Multiple Select into Separate Rows

  • June 22, 2021
  • 4 replies
  • 69 views
D
Forum|alt.badge.img

Please Help!
I have a table the contains my workers and a variety of information on each of them. They also are linked to projects that they are assigned as such.
Worker Project
A 1 2 3
B 2
C 2 3
Project is a multiple select column that links to a project table that shows top level detail on each project.
I would like to output to a new table that expands each of these records to provide detail on each workers interaction with the specific project as shown below:

Worker Project
A 1
A 2
A 3
B 2
C 2
C 3

I have attempted to use “create records from multiple select” and junction tables to no avail. I have approximately 500 workers and 200 projects and do not want to create records for every possible occurrence as my data is very sparse. Thanks in advance for your help as I attempt to navigate,
Damien

4 replies

D
Forum|alt.badge.img
  • Damien_Ashley
  • Author
  • New Participant
  • June 22, 2021

Any help here would be great!

Kamille_Parks11
Forum|alt.badge.img+27

How do you mean? You could create the necessary junction table records fairly painlessly if you do the following:

In your Workers table:

  • Create a link to another record field pointing at a (presumably) empty junction table
  • Copy the entire {Project} column into that link field.

In your Junction table:

  • Create a new link to another record type field that will point to the Projects table. Copy the entire first column into this field.
  • Add a Lookup field that gets the {Worker}. Convert this field into a link to another record type.
  • Convert the first column into an Autonumber or Formula to get unique record names.

In both Projects and Workers tables

  • Delete the fields that link these tables to each other, or convert them into Lookups pointing to the connection to the Junction table. Do all future linking through your junction table.
D
Forum|alt.badge.img
  • Damien_Ashley
  • Author
  • New Participant
  • June 22, 2021

How do you mean? You could create the necessary junction table records fairly painlessly if you do the following:

In your Workers table:

  • Create a link to another record field pointing at a (presumably) empty junction table
  • Copy the entire {Project} column into that link field.

In your Junction table:

  • Create a new link to another record type field that will point to the Projects table. Copy the entire first column into this field.
  • Add a Lookup field that gets the {Worker}. Convert this field into a link to another record type.
  • Convert the first column into an Autonumber or Formula to get unique record names.

In both Projects and Workers tables

  • Delete the fields that link these tables to each other, or convert them into Lookups pointing to the connection to the Junction table. Do all future linking through your junction table.

Kamille,
Thanks for answering! I was looking to have this table update as projects and workers were added and thus do not want to copy just the data that already exists. Additionally, my data is sparse with 500 workers and 200 projects, but only about 1000 interactions to capture, rather than the 100,000 that a junction table gives me.
Thanks in advance for any more insight you can provide,
Damien

M
Kamille_Parks11
Forum|alt.badge.img+27

Kamille,
Thanks for answering! I was looking to have this table update as projects and workers were added and thus do not want to copy just the data that already exists. Additionally, my data is sparse with 500 workers and 200 projects, but only about 1000 interactions to capture, rather than the 100,000 that a junction table gives me.
Thanks in advance for any more insight you can provide,
Damien


You do not have to make every single possible variation. If your junction table only needs 1000 interactions then only make 1000 records.

Terms & ConditionsAccessibility statement