Write to an Exsisting Row

no, set relation to email, that would get you to the row with that email, then just set only columns you wanna change, rest of them leave custom.
i assume that User ID is users email here, so there is no point to set it to the same email… unless you are relating to promotion ID. lol… than… looks ok to me

Yes, I am actually relating it to the promotion ID, the thing is a user can redeem/use more than one promotion and no user information is on the promotions sheet to relate back to the redemptions sheet.

Jen - perhaps you can use a similar technique as I used here?

If you look at my example, and substitute “promotions” for “announcements”, then it’s almost the same. The trick is the dynamically built joined list of UserID’s. That can be used (indirectly) to create a relation back to the Users table.

Thanks Darren! So I can’t just use the promotions sheet and redemptions sheet for this? The user profile sheet has to be included as well?

The initial ‘redeemed’ data adds perfectly with the add row action to the sheet, then comes the addiiton of the ‘used’ data which would just need to be tied to the same user AND promo. When I added the ‘used’ column, it actually wrote to the first instance of the promotion, which was for another user and not the user who clicked ‘used’ for that promotion.

This is what I have set up as a relation in the promotions sheet:
kk

Then when I add the used date data to the redemptions sheet, it writes to the first column which is tied to that promo, instead of last which is the user I am signed in as.

Are you trying to implement what I had suggested here…?

That’s essentially a “3 table” solution: Users, Promotions & Redemptions
So yes, there needs to be a relation between Users & Redemptions, as that’s how you track which users have redeemed which promotions.

The link I gave you earlier today is an alternative “2 table” solution, which doesn’t require a separate Redemptions table. The differences:

  • 3 Table Solution:
    • Easier to understand and implement
    • But, costly in terms of row count. The Redemptions table requires one row per user, per Promotion redeemed. So for example, imagine you have 100 promotions and 100 users, and every user redeems each of the 100 promotions. That’s 10,000 rows.
  • 2 Table Solution:
    • More complicated and slightly more difficult to implement.
    • But, much more efficient in terms of row count. Only one row per Promotion is required, regardless of the number of users. So 100 promotions and 100 users (or 100,000 users) will only use 100 rows.

You probably won’t like me saying this, but the easiest way to solve that would be to apply row owners, so that each user only has access to their own redemptions :slightly_smiling_face:

1 Like

Yes, I was actually using the method you mentioned previously. Defnitely want to keep my rows.

In the two-table method, where does the info of which promotion was redeemed, then used for each user go? The User Profiles or Promotions Sheet?

In your Promotions table. If you read the post I linked to - everywhere I mention Announcements table, think of that as your Promotions table. It’s a bit tricky, so you might need to study it a bit. But I’m pretty sure it can be applied to your scenario.

1 Like

Jen… just applying my solution to your scenario, it would be something like this:

  • User taps a promotion to view it: you write their UserID to a User Specific Column, and then show them the promotion details (View Details)
  • User taps a button to redeem the promotion: set a User Specific boolean to mark that promotion as redeemed for that user (so they can’t redeem it twice), and add their UserID to the list of users that have redeemed that promotion. NB. This “list” is actually a Basic Text column. How it’s built is a bit tricky. You can copy my demo and have a look, but essentially here is how it works:

  • Read By: In your scenario this would be “Redeemed By”. A comma separated list of users that have redeemed the promotion. I used UserID (which is the RowID from my Users table), but you could use email address if you want.
  • tp-first-user: This is a template column that just contains the UserID from the user specific column (the user that is currently viewing the offer)
  • tp-subsequent-user: same as above, but joins the current UserID to the list in “Read By”
  • ite-new-read-by: this decides which of the previous two templates to use when updating the “Read By” column. If Read By is empty (this is the first user), then use tp-first-user, otherwise use the one for subsequent users.
  • st-read-by: This takes the joined list in the Read By column, and splits on the comma.
  • rel-read-by: Uses the Split Text column (st-read-by) to create a multi-relation to the Users table. This gives you a list of users that have redeemed this offer
  • rup-read-by: A rollup (count) on the above multi-relation. This gives you a count of how many users have redeemed this offer
1 Like

I kind of want to keep the promotions sheet and those who uses it separate. How about if I create a redemptions sheet, copying the Promotion ID only, and then have all the uses on redemptions write to the single row as you mentioned?

Yes, I think that should work. Although it might get a bit more complicated when you try and link users to promotions - you’ll probably need an extra relation or two.

1 Like

I also want to track the day and time a promotion was redeemed and used, with a list in one cell, it would be more difficult to line this up to a specific user right?

mmm yes, that does add an extra layer of complexity.
It might be possible to extend my example to also create a joined list of timestamps, but I’m not sure if they would match up correctly with the UserID’s. I’d need to experiment with that a bit.

The alternative is to return to the earlier solution, but then you’re bumping into the row count challenge again.

I’ll have a fiddle and see if I can get it to work with timestamps. Will let you know.

1 Like

Thank you! I will keep digging as well.

I guess I could get rid keeping track of the dates when the promo is used and redeemed, might make it easier?
@Darren_Murphy

haha yes, it would.
Sorry I haven’t had time to look into this any further, and I don’t really have time right now.
Which solution are you currently leaning towards?

NP! Im thinking:

  • Add PromotionName and Row ID and name to sheet by array formula
  • When the user hits redeem, their email address writes to the same row as the promo Row ID
  • When they hit used, it writes to the same row as the promo Row ID

When you say name there, do you mean user name? If so, does that mean you plan on having one row per user, per promotion?

Sorry miss typed. It would be promotion row ID and promotion name.

So one row per a promotion, then have all the redeemers and users added to the same row as a joined list??

ah, okay - that makes more sense.
So that would be an extension of my second example.
You’d have two joined lists, and two relations back to your Users table.
One relation would give you the list of users that have redeemed the promo, and the other would give you the list of those that have used it.

1 Like