Issue with many to many

[SvenV]

Hi all,

I’m trying to figure out how to implement a many to many relationship. I need to be able to select multiple categories for a company.

From the data point of view I think I have figured it out. I have a junction table with two id columns. One for organization id and one for category id.

In my organization table I do a relation to that junction table, a lookup and again a relation to display the categories in the row on the organization.
That seems to work if I simulate it in the data sheets.

I’m also able to display the categories coming from the organization table.

However, I’m kind of stuck for the add and edit screens.
On the add screen I can show all the categories but how can I achieve that rows per selected category will be saved in the junction table.

For the edit screen I would also like to have all the categories displayed and only the rows that match from the junction table selected. I would like to work with a choice (multiple options) in chip form.

Thanks for your help.

[Jeff_Hager]

Get rid of the junction table. Add a choice component to the add and edit screens like you want. Check the box to allow selecting multiple. Add a Split Text column to split the resulting selected choices into an array. Use that array to establish your relation.

[SvenV]

That’s an option too idd. Coming from a software development background it doesn’t feel that good not working with the row id though. What is someone changes the name of a category?

[Jeff_Hager]

Change your choice component to write the Row ID value of each chosen category instead of the category name.

[SvenV]

Thanks for the help. Better solution indeed than junction table. Less complicated.