Caspio can only handle a "one-to-one" or a "one-to-many" relationship in a table field. As a result, creating a "many-to-many" relationship requires creating a third table.

Competing database programs (even those that are less developed than Caspio) allow a table field to easily hold references to multiple other entries in another table -- i.e. a native many-to-many field. (Webflow, Knack, Bubble are other ones I've looked at, and they all had it).

Increasingly, the world works in a "many-to-many" way. For example...
- A blog post will have many categories
- An article may have multiple authors
- An event may have multiple locations
- An individual may work for multiple employers

If someone wanted to create these many-to-many relationships in Caspio, they currently have two options.

Option 1: create a third table that sits in the middle of the two tables that need to have a many-to-many relationship. Limitations are:
- Cumbersome and complex to set up and manage
- Creating the many-to-many relationships during data entry requires establishing a separate entry form away from the main table entry form (since an entry form only applies to one table). This slows and complicates the data entry process.
- Searching functionality on the many-to-many table requires workarounds. Let's say you have a search datapage for a table. That search datapage cannot refer to the many-to-many table since it will produce duplicates of results. So, you have to build multiple search pages.
- To display the many-to-many relationship in a list or details datapage, you have to insert it as a iframe list datapage within the embedded list/details datapage. The iframe html formatting is very hard to get to match to the Caspio datapage formatting, so you need to therefore build the entire details page using html blocks.

- If you have a dataset that needs multiple types of many-to-many relationships, the problems above compound in a very significant way.

Option 2: use Caspio's List datatype. This datatype has some elements of a many-to-many relationship, and has been a lifesaver as otherwise I couldn't have gotten a basic version of my application to work. However it's an incomplete buildout, which Caspio itself noted in the release information 2 years ago. The limitations are:
- There is no way to bulk replace or update the list fields in the table. The "search and replace" function does not work on this field. Nor do Tasks. So if you have a database with thousands of entries, and need to change a blog tag for example, you need to either:
a) Go into each entry individually and change it; or b) Convert the datatype into text, do the search and replace, and then convert it back to list. This however is very tricky. When you convert the datatype it deletes any datapage reference, so all those need to be rebuilt. (I suppose you could duplicate the table, convert the list field to text, search and replace, convert back, then trade out the tables and hope nothing got screwed up.) Also, you can't use a comma in the list field, because that's what Caspio uses to recognize the separate elements of the list field. If you have a category called "Cats, Dogs, and Mice" when you convert it to text and back to list, you now have three separate categories: "Cats" "Dogs" "and Mice". Additionally, the search and replace functionality is very tricky, even with text, since it will replace fragments of words within entries. For example if you have "Big Green Building" and "Green Building" as two categories, and you do a global search and replace of "Green Building" only, it will impact both "Big Green Building" and "Green Building" even though you only want it to apply to the latter.
- The list datatype can't be used in "Tasks" so you can't build a task to do something. For example, if in all the blogs with a tag of "Cats" you also later decide to have it also say "Animals"... again you have to go into each entry and manually add it.
- In a search and report datapage, it forces you to list all of the list category options in the dropdown or listbox field -- you can't go in and set custom search options if you only want to show a selection of the categories, or order them a certain way. Again this is an issue in case you don't want to show every single option in the list field, in case you are developing some. To get around this you need to separate the search and report datapages using parameters (where the first is a submission form with virtual fields, and the second a report datapage that catches the search entries via parameters). This gets around it but then opens up another list of limitations and headaches.

I have an application where multiple data pages need to be viewed by 4-5 different types of users. I would like to use record-level security to restrict what data that page displays based on the logged-in user. I cannot use OpenID as an authentication method for this application.

Since Caspio's current authentication scheme only allows you to filter based on one field in the authentication, I would have to duplicate the page for each role that needs access to that page's content.

Could the authentication scheme allow for multiple fields to be used to determine whether a user has access to something. For example,

(
dataSource.field == authenticationSource.field1 OR
dataSource.field == authenticationSource.field2 OR
dataSource.field == authenticationSource.field3 OR
dataSource.field == authenticationSource.field4
)

The application will take ~50 data pages. Without this feature, that quickly scales up to ~180 pages

For more context, see my comment in this other thread: http://ideabox.caspio.com/forums/164206-caspio-bridge/suggestions/16251463-record-level-security