An activity is carried out by 1 user (1:1) (the user is the owner of the activity)
When I create a screen on the activities table I can add rows and automatically get the current user assigned as the owner.
But at the same time (!) I want the user to be linked to all activities. How can I best do that? Add the activity to the user on submit with a “set value” action?
Or add a query column to the user where I select the activities matching the user’s email address (which is recorded as the owner in each activity). Is that fast?
If I’m understanding this correctly, you don’t actually need to do anything at all with relations or queries.
Just add a Collection and set the target as the Activities table, and row owners will take care of who sees what.
Non-owned rows won’t even exist on each users device.
You could of course create a Query as you described, and then a Joined List via the Query. But if your only filter condition is “give me all rows owned by the user”, then the Query column is completely superfluous. You can go straight to the Joined List. Just be aware that it might appear that this doesn’t work in the Data Editor. But that’s because all rows are shown in the Data Editor, whereas on user devices only owned rows will be present.
I am not concerned about the UI at all. There will be no view of the relevant activity rows. Plus, they selection criteria is not just the owner (relation to user in activity).
A joined list requires a relation to exist, ie. the owner (user table) would need to have a multi-relation to all its activities. But how to set that up? That was my initial question. How to set it up when I’m adding activities on a collection bound to the activities table?
I want to set up a query form: as a user enter eg a date range and get a chart rendered based on matching activities you engaged in (you own). No collection needed, I’d say.
No, it doesn’t. That’s just one way to create a joined list. You can create a joined list by directly addressing a column in any table (which is what I was suggesting), or even an array in the same table. However, I missed the point that you will have additional filter conditions, so…
Okay, yes for this a query is probably the way to go.
You’ll just need two columns to hold your start and end dates, then use those in the query column.
If you’re putting these in your User Profiles table, then they can be regular columns, otherwise they should be user specific.
The filter conditions in the query column should be something like:
Activity Date is after This Row->Start Date, and
Activity Date is before This Row->End Date
You could add an extra filter condition to match the User, but again - that isn’t necessary if your Activities table has row owners.