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
Oh I’ll try this and when I set the column it knows to add as new the users who redeem to the same cell?
@Jeff_Hager do you have any other recommendations on how to this?
As a recap, I have promos within my app which a user has to click a button to redeem and click a button to use. I would like to keep track of which users redeem AND use specific promos on a redemption sheet. If date and time used could be tracked, that would be great too.
Thanks!
The only way I’d consider doing it is to have a Redeem button that would write the Promotion ID and user ID/email to a Redemptions sheet, along with a date/time that it was redeemed. That information would either come from the promotion details that were linked from a business or come from a relation/lookups on the business sheet that are linked to the promotions sheet. Once they have a matching redemption record, then instead of a redeem button you would show a Use button. When they click on the Use button it would use a single relation to update the used date through a single relation.
I’m personally not a fan of using ‘included in’ and joined lists to track multiple multiple users of a row. Maybe if it’s only a few, but it feels too vulnerable to me. I think writing a row for each redemption for each user is better database design.
It would really be beneficial in the future if you start to include more screenshots or video of your app screens and app flow. It’s hard to visualize exactly what your app flow is and where you are running into roadblocks.
2 Likes
Thanks Jeff! Yes, this is what I have set up. I am getting stuck on the part where the used data writes to the same user and promo row. When I do it, it writes to the first mention of the promo, but for a different user.
Also, you don’t think I will run into a problem of redemptions taking up too many rows in the app?
I have a screen-shot of the redemption sheet above:
Promo ID
User Email
Promo Data THEN…
Date & Time Redeemed
Date & Time Used
How are you relating to the redemption data? If you are only relating using the promo ID, then of course it’s only going to write to the first mention of that promo. What do you need to find the correct promo and user? That’s right, a template of both the promo and the user in both sheets. An easier method would be to simply apply Row Owners to the redemption sheet, so any relation to it will only find the rows for the signed in user, but without knowing enough about your app, that may or may not be a viable option. Back to the template, it should be easy to create that template in the redemptions sheet since you should already have the data there. To get the same thing in the promo sheet, just create a similar template that joins the promo ID and the user email from the user profile. A template of promo-user linked through a relation to a template of promo-user will get you to the correct row and update that correct row with the ‘used’ date
As far as row usage, yes that method will use more rows. That’s something you have to consider. More features, will introduce more complexity, which will bring upon more row usage. Trying to work around it will further complicate things. I’m only sharing how I would prefer to handle it. You could use single promo redemption rows with user specific columns to store all user info in one row for each promo, but you lose the ability to access all user data in those user specific columns. You could try stringing all users/dates into single columns, but you lose some sense of security and it becomes extremely complicated to parse out that data in a usable manner. Trying to take shortcuts to save a few rows will only bite you in the long run. If you use the method I described, I would probably plan for periodic cleanup of old promos and redemptions to keep row usage down.
3 Likes
Ok yes! I was thinking of possibly doing a clean-up every couple of months, or moving it to another sheet for old promotions which is not used in the app.
I think I figured out the template part. I created a Promo & User Template in both sheets and then a relation in the promo sheet. Does this seem right?
I have this to add row to the redemption sheet when a promo is redeemed:
And for the set columns when a promo is used:
*Do I need to add the User ID in this case?
Yes, that looks right.
I don’t think there’s any need to refill the User ID column when updating the Used Date/Time, since it already should be filled, but really wouldn’t affect anything.
1 Like
Worked perfectly! Thank you Jeff! This was one of the last big feature I needed for my app.
Thank you for your help as well @Darren_Murphy !
3 Likes