Reverse Many-to-Many

[tom_smith]

Hi,

so I have a table called Software, which has a Title and a table called Category which has a Title too. 

Each item of software, for example "Audacity" can have multiple categories "Audio, Cross Platform" etc. 

On the Audio category screen, I'd like to reverse lookup all the Software items that have an Audio item in its Category column (note there can be more than one).

I've tried ...

FILTER("Software", [_THISROW][Title] [Category] ) ...and set the field type to List or EnumList... I've tried using IN, and SPLIT.

This is why for me, AppSheet REALLY SUCKS. Doing foreign_keys when there's only one item is easy peasy, it seems to handle that bit brilliantly, but connecting many-to-many fields in both directions is like the first use case for what any low code tool should do well, and yet I seem to be pushed towards creating fugly spreadsheet formulas (that I coulda done with actual fugly spreadsheet formulas) and the whole point of this tool it to make it easier. 

I tested NoCoDB this week, and it handles this with flair. 

Can anyone help me understand how to make my Category screen display a list of Software items have a given Category. I failed on this last time I tried AppSheet (watched lots of videos, read documentation etc) and still failed. And it looks like I'm about to stumble at the first hurdle again.

Tbh, these virtual tables could be created for me automatically - with all the horrible formulas, and type settings etc... or on a click of a button. It seems it'd be easier to delete them where I don't want them, rather than create them (by hand). It's a bloody common need... of any many-to-many joined tables isn't it?

Like I said, this thorny issue - a tool to handle many-to-many relationships easily is what any tool claiming to be better than a spreadsheet should do with elegant ease.

Thanks.

[Aurelien]

Hi @tom_smith 

Assuming your table "Software" has the Category column set with type EnumList, base Type Ref, source table "Audio".

What about this expression in a new virtual column of the table "Audio" ?

FILTER("Software", 
  IN([_THISROW],[Category] )
)

Or, alternatively:

FILTER("Software", 
  IN([_THISROW], SPLIT([Category], " , " )
)

[tom_smith]

Thank you... I'll try that now...

I was just coming back to say this was where I was up to...

SELECT(
Software[Category],
(IN[_THISROW].[Title] ,[Category])
)

...and I'd tried it with split previously... 

One sec 🙂

[tom_smith]

Did you miss a bracket... or did I foul it up?

I added ...

FILTER("Software",
IN([_THISROW], SPLIT([Category], "," ))
)

...and got

Column Name 'Softwares' in Schema 'Category_Schema' of Column Type 'List' has an invalid app formula '=IN([Title],Software[Category])'. The type of the app formula 'Yes/No' does not match the column type 'List of Text'.

... it seems to be returning Yes/No... I don't then have to run an IF() over this do I? 

[Aurelien]

yes, I forgot a bracket. 

Here is the correct expression, as you spotted it:

FILTER("Software",
  IN(
    [_THISROW], SPLIT([Category], "," )
  )
)

About this part:

IN([Title],Software[Category])

I don't get what this is about ? 

Keep in mind that, if you set your columns according to my initial suggestion:

the looked up part is the key-column. Not the title.

I suggest you read this documentation:

FILTER() - AppSheet Help

IN() - AppSheet Help

[tom_smith]

This may be the issue... I don't have a join-table... So in Software, there is a comma separated list of Category (Title)

[Aurelien]

extra bracket.

You missed my answer saying that there is one more bracket indeed.

And the other one saying you need to write " , " instead of "," (extra spaces around the coma)

and the one asking for screenshots

[tom_smith]

With regards to the Key column, I begun to get something to work when I used keys, but in the Software table, I ended with comma-separated list of Ids i9a23624, i9fd51b3 etc... and thought "Well that's ugly and useless" .

If one is storing Ids like that - tbh honest, you'd be better off working in the command-line in MySQL / SQLite etc..

I'm hoping to find a better solution than SQL that could be easily explained to someone less technical than I... and I can't even figure it out with a good few hours trying different "logical" approaches... 

[tom_smith]

Tried 

FILTER(Software[Category], (IN([_THISROW], SPLIT([Category], ",") )))

got

Function 'FILTER' should have exactly two parameters, a table name and a filter condition

[Aurelien]

FILTER(Software[Category], (IN([_THISROW], SPLIT([Category], ",") )))

should become
FILTER("Software", 
  IN(
    [_THISROW], 
    SPLIT([Category], " , ")
   )
)

add spaces around the coma.

And please read the documentation I provided about FILTER() 🙂

[tom_smith]

It'd be handy if the type of column switched depending on what you'd got ,so you didn't have to..

a. Get the formula correct
b. Know what type that needs to be in order to display.

[tom_smith]

None of those solution work... complaining either the number of brackets is wrong or Yes/No doesn't match or some other error...

[Aurelien]

Can you share a screenshot ? 

expression formula

+

table structure

[tom_smith]

So this is what I have... a super simple many-to-many ...

I have read the documentation for FILTER and SELECT and REF_ROWS and it shouldn't be this hard... should it?

[Aurelien]

Thanks.

Can you share screenshot from your app editor, view Data>Columns ?

[tom_smith]

Do you mean these?

[Aurelien]

Yes, indeed.

1st point

Can you please:

Software: 

untick key for title, tick key for id.

Category:

untick key for title, tick key for id.

Key column (id, here) is for getting a row identification. It should not be displayed to user, and its initial value should be set with UNIQUEID().

OR

you may prefer removing your "id" columns from your sheets and regenerate your tables structure.

It may be confusing at first. I suggest you read this to get familiar with these concepts:

Configure column properties - AppSheet Help

What is a key? - AppSheet Help

2nd point

if you set the formula expression as discussed above in the column "Softwares" of table "Category", with type: List, base type Ref.

Do you get the expect result ?

[tom_smith]

If I am correct, there isn't ANY documentation about doing this. They are all many-to-one examples, if I'm right.

[tom_smith]

FILTER("Software", 
  IN([_THISROW], SPLIT([Category], " , " )
)

returns...

Expression 'FILTER("Software", IN([_THISROW], SPLIT([Category], " , " ) )' was unable to be parsed: Number of opened and closed parentheses does not match.

[tom_smith]

So I got it working by altering the types.

BIG THANKS @Aurelien !!

I think Type and Element Type were causing me the issues... once I swapped them, the Software listing populated... 

[tom_smith]

For completeness - this is the formula (which you gave me)... it was the Types buggering it up (I think)

FILTER("Software", IN([_THISROW], SPLIT([Category], " , ")) )

[tom_smith]

OH HANG ON! 

Doesn't work with multiple items, so for example, Python is listed in the Coding category, but Regular Expressions aren't because its category(s) is Data,Coding

[Aurelien]

"Data,Coding" ==> "Data , Coding" should fix your issue (extra blank spaces around the coma)

[tom_smith]

Hi,

thanks to your help, I have sort of got it working. Although I'm not sure how well I could do it again, if you know what I mean.

But now I run into this related problem, which is a side-effect of working with joined data.

This is a video of me prattling on about how you can't create objects "at both ends" of the join.

https://drive.google.com/file/d/1slHvbf4c77X5I_mvOTJT-qRqjoWuUsIS/view?usp=sharing

[Aurelien]

Thanks for the video.

In software, column Category, you may want to go in the section "Suggested Values" and type this expression:

Category[id]

 That will suggest all of the items from the category.

Otherwise, that will only suggest some items that have already been entered.

I would even go for the "Valid_If" section instead of "suggested value", but not sure about how this will behave with an EnumList type.

Anyway, I suggest you try this expression in one field or the other, you'll get what you expect.

About friendly displaying on the Sheets, you are correct: that's not easy. But:

1) your users are not supposed to access this part, so...should be ok

2) if you prefer getting the title instead, you may get rid of the column [id], as suggested earlier, and select the column Title as your new key-column on both tables.

[tom_smith]

So ... it worked THANK YOU!

And then I got to here, essentially doing the same thing but the other way around...

https://drive.google.com/file/d/1Y1Vh0TWDXGLdMr2-eStLO2ZPRib3YyPk/view

And I thought, oh, I just go and do what Aurelien suggested but backwards, but when I got there, there isn't a similar SUGGEST field to fill in. 

I guess the issue here is that I'd want TWO options... 

a. Add a new item to the Software table and then add that to this Category's software list
b. Choose an existing item from the  Software table and add that to this Category's software list.

At the moment it only does "a".

Thanks for your help...  it's really appreciated. But my guess is that at this point I'm expecting too much and there would just have to be "I'm afraid it doesn't work like that, you have to work this way" angle...

Tom

[Aurelien]

About this last question, I leave it to @Steve because I don't know how to do this, and if it's even feasible.

Maybe, instead of using an "Add" button, using a LINKTOFORM()...in which you could prefill some informations. But again, I don't know.

[tom_smith]

Thanks...

[Steve]

---

@tom_smith wrote:

I tested NoCoDB this week, and it handles this with flair. 

---

Then why aren't you using that?

[tom_smith]

Because AppSheet is a Google product and we are Google institution. It integrates with Apps Script for which we have access to a HEAP of scripters. We run Apps Script courses etc.

Going off-piste with regards to tools has a massive cost, in terms of evaluation, GDPR, hosting and maintenance and training.... and that's when it's free/open source... it gets worse when it's a paid for product because THEN you need to know that this paid for product actually does what you hope/think it does when you initially try it....when used in anger by thousands of people.

IMO Google have evaded trying to fix the ELEPHANT IN THE ROOM about dealing with related data with regards to Google Sheets... Spreadsheets haven't changed much since the late 70s.  Punk had already collapsed. It seems that once you outgrow 2D data, you either learn full on web development for a few years or go home, or struggle.

In my job, I frequently meet people who have lovely clean spreadsheets, some have even figured out "join tables" to enable a one-to-many arrangement, but they are unhappy with them, and instinctively know that there MUST be a better way of doing this. 

There's a tool called Coda.io that looks interesting, but often these tools don't have a pricing model that goes, so you want 30,000+ users but only 50ish editors will really use it, they will change over a year (as people get to know the tool, or leave or change posts) and we don't have wads of cash for what will be light use. Maybe I should look at that again.

My use case is that people want a simple database/sheet solution for managing a few thousand rows at max... cleanly... there are so many teams solving their own problems, often poorly with spreadsheets that could do with a tool that meets their aspirations.

So AppSheet remains a contender, but with whatever tool I'm trying out, I have to factor in, how easy/intuitive it is to teach to people without a background in databases, coding etc... And for me, AppsSheet REALLY falls down at the the very bit I want most... tidy relations. Maybe that's just me though, bringing baggage that keeps tripping me up. 

[Suvrutt_Gurjar]

Just in case it helps. Typically in AppSheet many to many relationships are implemented using bridge tables.

You may want to take a look at the following tip and trick that has interesting relevant discussions and sample apps.

Help with many-to-many reference and bridge table - Google Cloud Community

[tom_smith]

Ah OK.  

I attempted to run and copy the app and got a gazillion errors (Drive file(s) not available) but in "Under the hood" I'm looking at the project and maybe  I can reverse engineer this. 

It seems that you may have solved what I was looking for, and knowing they're called Bridge Tables is very useful.

It would be useful to see an ideal structure for your sheets. I'd hoped that maybe we could perhaps avoid "Bridge Tables", but of course, I understand why they're the *best* solution to handling joins. Once I know what AppSheet "likes" it will be easier to massage data before importing it into AppSheet. It'd be good if I could import my tables - and IF it's in the right format (for joins etc) then it builds an app that works. That would be killer app time. 

Thanks a lot. I'll go and take a look at that and see how I get on.

[Suvrutt_Gurjar · Accepted solution]

Oh sorry to know that the sample app is giving errors.

Sometimes the sample apps do not work as over a period time the creator may have moved some underlying table etc.

Here is another example of bridge table in the sample app below.

https://www.appsheet.com/templates/An-app-for-managing-customers-products-and-orders?appGuidString=8...

Order details is a bridge table between "Orders" and "Products" table. Each order can have many Products and each product can have many orders.

[tom_smith]

Wow! What a friendly and helpful community. Thank you Suvrutt, that looks a deceptively simple project that has the sophistication and exactly what I was attempting lurking.

Thank you (all) very much. Looks like I know what I'm doing next week.

Have a great weekend,

Tom

[SkrOYC]

I took the time to read all of this.

Two suggestions from my experience that I have given to other people as well.

1. Forget about your technical knowledge. Forget almost everything you know and read the docs extensively. If you find something there that's similar to what you already know, awesome! But don't try to go the other way around, applying your current knowledge to do something inside AppSheet that just won't work the way you knew it, learn the AppSheet way.
2. Think about your spreadsheet/woksheet as a database now, so don't make it "user friendly", make it "AppSheet friendly". If categories are ID like this "53f3ds2d , ca2c9sc0 , 4s2s4c62" it's OK, because AppSheet needs and understand that. Don't bother to make it "Audio , Sound , Coding" if that makes it worse for AppSheet to understand your data. Again, NO ONE should be using your worksheet, they should be using your App

To solve the Many to Many stuff is quite simple:

• Make EnumList basetype Ref on both sides.
• Make sure to configure Suggested Values or Valid_If with a list of ID/Keys from the corresponding table, like this. TableA on it's Foreign key to Table B: TableB[KeyColumn]. The same the other way around
• Create a Virtual Column on each table that links the data from the other on using a Filter() expression like this:

# On Table A
------------
FILTER(
  "TableB",
  IN(
    [_THISROW],
    [ForeignKeyColumnToTableA]
  )
)
------------
# On Table B, the same but the other way around
FILTER(
  "TableA",
  IN(
    [_THISROW],
    [ForeignKeyColumnToTableB]
  )
)

In my experience, the "Add" button under the Inline View (the list view inside the detail view that made thanks to the virtual column added previously) works fine if you are using the Key values instead of other field like you are doing. I mean that it should look like this "53f3ds2d , ca2c9sc0 , 4s2s4c62" instead of "Audio , Sound , Coding".

Finally, if you ask me, I find AppSheet way of handling many-to-many waaay easier than tradicional platforms, and it's because the way AppSheet processes a list of values inside a single field/column. In other words, thanks to EnumList

---

FILTER() - AppSheet Help
IN() - AppSheet Help
What is a key? - AppSheet Help
UNIQUEID() - AppSheet Help
List expressions - AppSheet Help
Use virtual columns - AppSheet Help
Reference views - AppSheet Help

[tom_smith]

Thank you @SkrOYC for taking the time and providing a clear template/rule of thumb guide to creating complex data... and giving me THE way of working that I know will work...

Refs + EnumList seemed the way to go , but Suggested Values/Valid_ifs would never have occurred to me as a core concept of achieving this, so thanks...

I was also tantalisingly close with the FILTER calls... I had maybe something like this..

FILTER(
"TableB",
IN(
[_THISROW].[ForeignKeyColumnToTableA]
)
)

...and a dozen variations of.

So... onwards and upwards. Thank you everyone for being so helpful.

Tom