I have a complex CRM App developed. From the Client Screen users can open a Tasks and Notes Form and add tasks and notes to the client record. These are stored in a different worksheet in the same Google Sheet as client data as they are multivalued i.e. multiple tasks and notes per client.
I include the client email to identify which client the note or task belongs to.
Under the form button I need to display a list of tasks or notes. I have added a Collection from the respective worksheet under each button. Problem is I cant find a way to compare the client email from the client record with the client email on the task (or note) so it just displays all tasks or notes in the sheet.
Any ideas how to provide a list of tasks and notes for just the current client. These need to be editable as well when clicked.
You should be able to create a relation in the Clients table that links the email to the email in the tasks/notes table. Then source your collection from that relation.
1 Like
I created the relation column but what do you mean by “Then source your collection from that relation.”
When I add a collection the only option is to specify which table it comes from. I cant see anywhere to relate to a relation in the Clients table. Im missing something I think
In order to use a relation as the source of a Collection, the relation must be in the same table that the screen is attached to (or in the User Profile table). Assuming that is true, then when you set the source of the collection you should first see a list of tables. At the end of the list, you should see any relations/queries that can be used.
4 Likes
“the relation must be in the same table that the screen is attached to”
The Clients Screen attaches to the Clients table. I have a relation there between the email on the Clients table and the email on the Tasks table. If I create a Collection I can see a list of tables in the google sheet , the Users Table and User Profile. There are no relations listed.
If I create the relation in the Tasks table I can select this relation from the list. You need a filter = Client Email Relation is not empty to produce a collection list.
So this approach will work it seems
Thanks again Darren!
OK. One more thing. I deleted the Email relation from the Clients table and it stopped working so put it back in and that made it work. Delete the one from the tasks table and stopped working again. It seems you have to have a relation columns between the linked columns in both tables for it to work properly.
That sounds like you have something set up wrong, or are misunderstanding. You only need one relation for it to work. Just make sure it’s a multiple relation.
Relations are not dependent on being set up in both tables. A relation should work just fine in one table.
1 Like
Just checked again. If I remove the Tasks to Client relation the collection displays nothing. When I put it back it works perfectly.
Might need some screenshots because something doesn’t sound right.
Happy to send, but its working.
Tell me what you want shots of and I will send them soon
Probably shots of your relation configurations and shots of your collection configuration for starters.
Happy to provide login credentials if you want
Configs for the two relation columns and the Tasks Collection
Okay, your relation in the Tasks table (Client Email Relation) isn’t actually required for the Collection to work. However, as that’s a single relation back to your Clients table, you would use that to retrieve Client attributes such as Name, Avatar, etc (via Lookups), so that those attributes could be used as Collection labels. So it makes sense to have it there.
Does that make sense?
Yep understood. But trust me, if I remove the reverse relation column im the Tasks table the Collection no longer provides any resutls. Do do do do, do do do do!!! 
I don’t doubt you, but that’s not normal. There will be a reason for that, but difficult to say what it is without seeing your App. Do you have any filters on the Collection?
1 Like
