Hey there I’m building a rewards app where users get 5% cashback when shopping in-store.
I’ve created a sheet with all transactions which lets me know which shop, which user, and how much.
I then need to relate these totals back to each user so their wallet reflects their transactional change.
When they have got enough cash in their account they can go and redeem it in-store, which then credits this from their account to the business’s account, thus the need for relational math across diff sheets (transactions, users)
Looks fairly straightforward…
- Just create a multi-relation column from your Users sheet to your Transactions sheet, joining the user email addresses.
- You can then use a Rollup column to get a sum of transactions per user via that relation
- And then perform additional calculations on that rollup column as needed.
thanks Darren, a need for some clarification.
this multi-relation column from the users sheet, what is this displaying in the transaction sheet beyond who the transaction was for? (which is already there)
Which sheet does the rollup live in? Preferably in the user sheet as a ‘total wallet’ column
thank you
The multi-relation column is just used to create a link between the two sheets. It needs to be a multi-relation, because you want to match one user to many transactions. This column should be in your Users table.
The rollup column will also be created in your Users table. It is used to fetch the transaction data that you need (sum per user) via the relation column.
Here’s a couple of references:
1 Like
Thanks Darren, this is great and I understand it conceptually, but when I think of what to put in the multi-relational column the only thing I get working is the email address of the merchant which is good at showing the number of transactions (by showing the merchants’ email address over and over) but doesn’t allow for a rollup against it as there are no figures? I thought it would be a good idea to create a multi-relation to the dollar figures so the rollup would work, but I can’t think how to do it because there is nothing to ‘relate’ it to in the user column?
A relation is to establish a connection from one table to another using a common value (such as email address). A relation returns all related ROWS. It may show a particular column value, but that just shows that it found a match. It’s not a particular column it returns, it’s still connected to the entire row. The rollup column can use the relation (connection) to then allow you to summarize the values in any particular column from those related rows.
2 Likes
Ah now I get it, and it’s working! Thanks so much for spending the time to assist.
1 Like
