Many To Many Relationships - Filtering Data and Weird Behavior (Bug?)

[mkbw]

I looked into how to handle Many to Many relationships here, in the documentation, and watched a number of videos. A lot of ugly work-arounds was my impression but also I didn’t find a clear/concise explanation on what’s supported/reliable/suggested from Glide itself.

So, I created a sample app to test out this functionality. My sample app exhibits some weird behavior.

The basics:

• I have Geographic Zones and a table called zones. Functionally, this serves as a multi-select list for dropdown list for the UI and serves as a join table.
• I have tasks and a table called tasks. Each task belongs to one and only one Geographic Zone.
• I have users and a table called users. A user belongs to one or many zones.
• I sought to filter the a Collection of Tasks to only zones that a user is related to when they view the collection.
• If a user has one assigned zone that user should only see the data for their zone.
• If a user has two zones that user should see the data for those zones only and on and on…

So - what happened?

In the data view user “Bob” related to two zones shows no relations and lookup returns null.

In the UI edit view “Bob” can and is related to two zones.

In the UI view (no edit) “Bob” shows no relations from the relation field or lookup field but from a plain text field the string of the RowIDs of these relations do appear. Similarly, in the list view of users the same behavior (limited to posting 5 screenshots hence no screenshot).

The actual goal here was ** I seek to filter the a Collection of Tasks to only zones that they are related to.** and guess what? It worked.

So… all of this begs to ask is this a bug or did I find a gap that’s not supposed to work but sort of does??

[Jeff_Hager]

A comma delimited list is not useful for creating relations because it’s a single text string value that will not have an exact match. You need to first convert a comma delimited list into an array using a Split Text column. Then you can use the Split Text column when building your relation.

The reason it worked for you in the last screenshot is not a bug. In fact it’s because you used a filter on a collection that uses ‘included in’ which can search for a string of text in a longer string of text.

If you add a Split Text column to create an array from your comma delimited list of ID’s and change your relation to use that Split Text column, then your relation and Lookups will return values. Likewise, your collection in your last screenshot should probably be sourced from a relation instead of a table. In that case you won’t need a filter.

Also, you’ll probably need a Joined List column to get a comma delimited list of Zones out of the relation.

[mkbw]

Thanks @Jeff_Hager.

Nothing weird or buggy about this indeed. As you pointed out I failed to notice that I was filtering based on a delimited text field. No bueno. Thought I had perhaps found something clever, not the case.

This does almost feel like a step backward with the complexity of extra fields in WYSIWYG software rather than just having an UI driven ERD tool to set table relationships like what CrystalReports and Filemaker have.