Add Multiple Values (Categories) Per Entry (Business)

[dmarlowe]

I have a table of businesses with all of their information. I need to allow each business to have multiple categories and each category to contain multiple businesses. I understand this is a many to many relationship and have found multiple topics in this forum that explain the table relationships and implementation. What I can't seem to find is an easy way to add/update entries in this setup. I would like to have the ability to add or update a business and select which categories it should be in within a single form. Ideally, it would be great to have a list of checkboxes where I could check which categories the business should be included in and it would submit the appropriate category IDs to the associated table. Is there any way to do this?

[Vitalikssssss]

Hi @dmarlowe,

I believe you should create mediatory table as explained in Many-To-Many tutorial video.

Finally, you should use this mediatory table as a Datasource for Tabular report with "Inline insert" option enabled.

This method will allow you to submit more than one record with different ID`s from businesses and categories.

You should consider "List String" datatype if you would like to have check-box type of selection, however you will not be able to create relationship as described above. 

Hope this helps.

Regards,

vitalikssssss

 

[Ed727]

I am having problems with this many-to-many area as well. The tutorial mentioned here and the documentation don't address it.

 

Yes you can create a joining table to form the many to many relationships. But all sorts of problems arise, and I haven't as of yet found any solutions.

 

The main problem arises from the fact that a Caspio form (or "datapage") is tied to only 1 specific table or view.   And therefore if you're searching based on a many-to-many relationship, your search form is then tied to the view that has been created off the many-to-many joining table.  Using the example mentioned above, a table of businesses which are connected to a table of subject tags:

 

1) If you want your user to be able to search or filter based on multiple subject tags, they'll get duplicate results, since some of the business may contain the multiple subject tags.  That's because the search form is searching a view which is based on the many-to-many joining table.  You also clearly couldn't "display all" on that page, since there would be tons of duplicates.

2) If you have multiple many-to-many relationships for a dataset, then that means multiple joining tables, and multiple search forms.  You can't create unified search functionality. The user would have to go to a different search form for each many-to-many relationship.

 

There are some other problems as well:

1) When entering data, you'd need to create a separate entry forms each type of many to many relationship. So, if your business has 2 many to many relationships, then that's 3 entry forms total (1 to create the business; 1 to create the first many to many relationship; 2 to create the second many to many relationship).

2) To display the many to many fields for the business (for example you'll want to list the subject tags when you show the business details), you need to embed them as an iFrame.  Then it takes some work to fiddle with the formatting to make everything look consistent.

 

It would be great if I'm missing something here, since that would allow me to overcome hurdles I'm facing with many-to-many.

 

 

[IvanaDotCom]

Hello, the video clearly shows the answer to your question, a hug!