Relate records based off of a multi select field

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
I have two tables. The parent table is "Checklist", and the child table is "Checklist Items". On "Checklist" I have a field called type that lists options such as:

Checklist Type 1
Checklist Type 2
Checklist Type 3
Checklist Type 4

and so on. On "Checklist Items" table I have a multi select field with these same options, but more than one can be selected. The idea here is that some of the items apply to more than one checklist type. 

I have created a relationship so that each "Checklist" can have many "Checklist Items". 

I am having trouble getting the two tables to "relate" to one another. For instance, on the "Checklist" table if a user creates a record that is "Checklist Type 1" I would like a report to display that lists all "Checklist Items" that have this option selected, even if they also have other types selected. 


Any thoughts on how to make this work properly?
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
you probably need a many to many relationship, a join table that sits between checklist table and checklist items table.
Photo of Andreonna

Andreonna

  • 1,040 Points 1k badge 2x thumb
That's kind of what I was thinking. Bu I am unsure how to set up this third table.