NOT Using JOIN table for relationships.

[tom_smith]

I have a data model like this. Tools can have multiple Tags.

Tools have a Name and URL and a Tags EnumList  that works fine. See me editing a Tool's tags here. I can select multiple tags for each Tool.

Going the other way, I created a virtual field for Related Tools. Where, when viewing a Tag, you can see which Tools belong to that Tag. You can see below, Related Tools is an EnumList, with a Base ref of Ref of which is Tool and an expression of...

SELECT(Tools[ID], IN([_THISROW].[ID], Tools[Tags]) )

...and it almost works, but returns a list of the Tool's name (which is cool) but I'd prefer a listing I could view like cards, each one it's own thing... if you get what I mean.

Any ideas?

Tom

[AleksiAlkio]

Create a Card view for Tools and select the type as Ref.  It should then show it.

[tom_smith]

If I do that I get this... 

I'm worried that my idea (in Tools) to store Refs to Tags without an intermediate JOIN Table, sort of Tool_Keywords, is causing the issue.  Is it? My sheet looks like this...

Working backwards from Tags (to show Related Tools) , I need to say, all the Tools (as Refs) with this Tags' ID in the Tool's Data... is this possible?  

Is there any other info / screens I can share that might help? Or am I just conceiving this badly?

Thanks for your help...

Tom

[tom_smith]

When I changed the Related Tools to Ref, it broke, but if I change it to List it works, but only pulls back the Tool IDs.... but ideally I'd like them displayed like a Deck

My Related formula isn't working too, as it's bring back ALL the Tools IDs (each tool only has two or three tags).

[tom_smith]

Almost there... I'm not sure if changing it Card View made a difference or not, but I think I've clarified that a. My approach of 

SELECT(Tools[ID], IN([_THISROW].[Related Tools], Tools[Tags]) ) 

is bringing back ALL the Tools (presumably because the TOOL's ID is in the column of Tool's Tags... whereas I want it to return the Tools rows where the Tool's ID is in that Tool's row's Tags.

I tried adding a Data Validity to this, like this,

IN(Tools[ID], [Related Tools])

Thinking, once it has selected all the Tools, it then would on a row-by-row basis figure out that it needs to display a subset of tools and not all of them. Didn't work.

My Virtual column is like this (which so nearly works)

Every time I've tried AppSheet this one to many issue has beat me... and I've given up. I really want to get there this time.  I feel like I'm so close...argh!

[AleksiAlkio]

It sounds like you would need to use..
SELECT(Tools[ID],IN([_THISROW].[ID],[Tags]))

[tom_smith]

 Thank you!  This has worked... I tried roughly a gazillion random-ish combos for the query and didn't hit on this one.  I'm struggling a little to reflect and understand how your query works, so I can replicate it... (and do the same reverse connection in Tools so that I can jump from a tool to a relative Tag) For example...

SELECT(Tools[ID],IN([_THISROW].[ID],[Tags]))

... so in this case, [_THISROW] is defined by the first argument of the SELECT is it (i.e Tools[ID]..?) and so [_THISROW] is a Tool, rather than a Tag?

I can't get it... 

I kinda assumed (wrongly I think) that [_THISROW] might refer to each row of Tags (since Related_Tools is "in" Tags as it were... the above query seems to be saying...

select the tools who's ID's match this row's ID in Tags ????

...and yet, in Tags is a list of Tag IDs... so I can't quite get how this returns correctly, it seems a leap of faith or magic...  because [_THISROW].[ID] isn't in [Tags] ... because surely [_THISROW].[ID] is a Tool ID? ... isn't it?

Thank you so much for your help... I'm sure once I understand the structure of the syntax, which seems different from sql and/or code... 

Tom

[AleksiAlkio]

When using SELECT() expression in table "Tags", you want to show with your evaluation from which table you are reading the value from a specific column.. is it from Tools or Tags table. That you will do with the [_THISROW]. It refers to this row's key column in the table where the virtual column is.

If you write the formula like SELECT(Tools[ID],IN([ID],[Tags])), it would search records from the Tools table where the ID column is part of the Tags list and you would probably have a empty list as you are reading IDs from the wrong (Tools) table.

[scott192]

Create a slice on your tools table and call it 'ToolCards'
Create a new Reference View and call it 'ToolCards_Inline'.  Point the view at your 'ToolCards' slice.  Make this a Card view and configure the card anyway you want.
On your Tags table create a new VC called 'Related ToolCards'  :  Use the formula
SELECT(ToolCards[ID],IN([_THISROW].[TagID],[Tags]))
On your Tags_Detail view, add your newly created 'Related ToolCards' VC.
You should now have a list of inline cards of all tools that have that tag.

[tom_smith]

Thanks @scott192 I think this kinda equivalent and overlapping @AleksiAlkio solution and is the route I was heading down... but because of the creation of a Slice somehow imparts more of what the query is doing...

Aleksi's : SELECT(Tools[ID],IN([_THISROW].[ID],[Tags]))
Yours: SELECT(ToolCards[ID],IN([_THISROW].[TagID],[Tags]))

I notice you called a Tag's ID column TagID... that isn't part of being in a slice is it? It looks like a convention you maybe use that makes total sense to me... in that in doing so, it might become more transparent that [_THISROW] is a Tag row, and not any other table, like Tools.

[scott192]

Hi Tom, the reason I said [TagID] in my expression was because I did not know which field in your Tags table was your Key.  My naming conventions are to use the tablename with ID at the end...so ToolsID, TagsID, EventsID etc.  All of those would be the key field on the table.
Now, it is possible that you used the tag name as the key for your tags table as I am sure there won't be two tags with the same name...however, if I was making the same sort of app, I would still use a UNIQUEID() in a TagID column.  It's just personal preferences I guess 🙂

[tom_smith]

Sorry to bang on... but my next problem is kind of the same in reverse... Except it's not... 

I simply want to nicely list the Tags each Tool has (it's just a column in Tools with a list of IDs) ... At the moment it displays the right data but textually...

But what I want to display is something closer to the edit form for Tags, like this...

Thing is, this is a different problem, in that it's definitely getting the correct data, and can display it how I want to... but I'm coming at the data from the other end. I want to make it a List item then define the base type but it won't let me do that. A textual link would be fine, but the edit form is better.

Again, thanks for your help. 

Tom

[tom_smith]

Do I have to create an additional Virtual Column, that looks in my Tags column and someone turns the ids into Refs perhaps?

[RedVox]

This problem could be solved with a couple clicks if AppSheet would enable this feature.  Please give it a thumbs up. 

In short, AppSheet should make it easy for us to create bi-directional, multi-selectable table relationships, just like AirTable and Zoho Creator do.  

[tom_smith]

So @RedVox  do I have to create an inbetweeny JOIN table? I was hoping to avoid that.... seems so old fashioned... and the resulting sheets are really daunting to even look at (I'm thinking I may teach AppSheet if I can understand it myself).

Or does someone have a resource out there (ideally with some code / data samples ) that I can refer to and crib from... AppSheet has so many different places where one can alter stuff that knocks on is weird places that it has become a process of having a hunch, making a tweak, saving my project, and repeat... 

[tom_smith]

Yeah

[tom_smith]

SOLUTION?!!!! I THINK!

I think I have solved this (I maybe be speaking too soon) .... I can't believe this was sooooo hard.... but maybe this will help someone else....

https://youtu.be/puWx3FEgGKs

[tom_smith]

So for completeness, and if I mess up my app as I continue to work on it 🙂

Tags > Related Tools expression is: 
SELECT(Tools[ID],IN([_THISROW].[ID],[Tags])) - for type List, element type Ref

Tools> Related Tags expression is:
UNIQUE(SELECT(Tags[ID], IN([ID],[_THISROW].[Tags]))) for type List, element type Ref but this is only for display/View purposes, there's also a Tools>Tags EnumList, with a base type Ref onto Tags.

So they're slightly different depending on which way you're going I guess.

[scott192]

Your Tools>Related Tags expression could be simplified to the expression
[_THISROW].[Tags]

Just [_THISROW].[Tags].  This would return a List of Refs that you can display as an inline view.

[RedVox]

Nice video!  Your solution works, it just doesn't display your child records in the nested display we normally want to see.  I believe a join table would be required if you wanted the children to be presented as children.  Also, I think this approach is still one-way when it comes to creating new children.  

[tom_smith]

Aargh! @RedVox you're right... my solution only handles viewing the data, not presenting a meaningful Form to edit the same data. 

I've marked my previous post as Not the solution

How do people manage this situation?  It's hardly rocket science it should "just happen" in my view. It's very basic data relationships.

[scott192]

I just want to project into the future a little and give you a warning about data analysis of your app structure.  Should you wish to analyse your data you will discover that storing your tags on each tool as an enum list is going to cause you a lot of headaches when using an external analysis tool.  Grouping and aggregation will be a nightmare...trust me I've been there 😁

The better structure is as you have hinted at is to have a third table called ToolTags  that will combine a tool and a tag...one row for each tag that is attached to a tool.  Not only will this structure serve you well in the event that you ever wanted to abandon or delete tags from your tags table, but the aggregation and analysis between the tags and tools could be completed easily in any data analysis package external to appsheet.

You could try this immediately using your current data structure by trying to draw a bar chart or pie chart on your tools table and count how many times certain tags have been used...try adding this chart into the google sheet...(actually don't try it as you will become frustrated!).

[AleksiAlkio]

😂😂

[tom_smith]

Hi,

Thanks for the advice... I suspected that the *best* way would be to have a sql-like JOIN table but thought two things

1. It'd be cool if AppSheet handled all that behind the scenes
2. It might be easier to teach AppSheet because most people aren't familiar with sql, but are totally ok with the concept so relationships.

I just had a quick go at reimplementing my project with sheets Tools, ToolsTags and Tags and sort of ran into the same problems, either the wrong type of item was displaying, i.e in the related tags of a tool was displayed the correct number of items but showing the tool's name (rather than the tags name). And some REF_ROWS items seem to be automatically generated but not do the right thing.

I seems to me that once again AppSheet has beaten me. If it takes fancy formulas, a collection of inline views and filter jiggery pokery nonsense to create what should be, for me, a core feature of AppSheet ...i.e  two way one to many relationships that are intuitive (and in fact the default - a one-to-one is just a limted version of a one-to-many).

Thanks for your help everyone, I've just wasted about three or four days and I can't make what would be the simplest app imaginable. I can't imagine how anyone is using this in real situations... I'm so disappointed.