Row Numbers

Unfortunately, no. At least not if you’re only using Glide tables. There was a bit of excitement when the API Column was released into staging a few months ago, but that appears to have been shelved.

If you’re using GSheets, then there are tricks that can be employed to address race conditions. This is something I’ve had to deal with recently, and I described the approach we took here :arrow_down:

Essentially, we did as you suggested, which was to use an intermediary (in this case, Integromat) as an indirect arbitrator.

I see. Integromat doesn’t work with Glide Tables. So either you get the speed and volume of Glide Tables or all the nuanced calculations and integrations of Google Sheets… but not the best of both worlds.

yeah, I (almost) always start a new app with at least one GSheet. Even if the rest of the app is Glide Tables, I still have that GSheet there as a backup just in case it’s needed for situations like this.

2 Likes

Tonight was the first release of this feature. It was pretty well received and about half of the editions of the collectible were claimed within a very short period of time and the rest will probably go over the coming days. I’m doing it a bit differently from what you did in your example. Rather than prerecorded collectibles that they claim, my members just mint new collectibles with each claim, adding a new row record each time. And then I have a condition set to turn off minting when a certain number of editions have been claimed. But now I’m realizing that in the future if this gains steam, and certainly even now if I were to release a very limited number of something (say 5 editions), potentially way more people than intended could end up claiming it before Glide would get the message to turn off minting.

hmm, I was just about to suggest that you might be able to combine Jeff’s UTC solution with my approach, but… no. The more I think about it the more I’m convinced that the only way to address race conditions (within current Glide constraints) is to use an external reference point. And when you’re working with Glide tables only, you don’t have that option. @Jeff_Hager said he was going to mull over this, maybe he’ll pull a rabbit out of the hat. It wouldn’t be the first time :wink:

I was just saying in that other thread that I came here tonight to figure out two things Timezones (and specifically getting GMT from local time) and this particular issue. Once again, Jeff has me leaving with at least something.

tbh, speed isn’t really a concern with this kind of thing. It’s just the sheer volume of transactions you have with an in-game economy. I fear having that all in GSheets will eventually be way too laggy or just eventually stop working. I wonder if there’s a way to have the initial work done in GSheets and then somehow copied to new rows in a Glide Table. Meh. It’s late here and I’m just making up random nonsense. Thanks for your help.

1 Like

I do believe that if you had just a single GSheet, then you could solve your problem. I’m not sure what your current user journey looks like, but it would need to be a two step process, with a short delay in between each step. Here is what I’m envisaging:

  • When a user taps a button to claim a collectable, you put them into a “holding pattern”
  • The button that they tap sends a webhook to Integromat with two values: the UserID, and the ID (RowID?) of the item they are attempting to claim.
  • Integromat writes 3 values into a new row in your GSheet: the UserID, the ItemID, and a now() time stamp. NB: This is the key - that time stamp comes from Integromat, NOT from any of your users. So it’s a single point of reference.
  • You build a multi-relation from your Glide Table to the GSheet, using a template that combines the UserID and ItemID
  • Then you use a rollup to take the earliest time stamp through that multi-relation.
  • The next step is to build a single relation back to the GSheet using a template that combines the UserID and the ItemD
  • Then a Lookup through that relation to fetch the actual time stamp value for each user that’s tried to claim the item.
  • You now have two time stamp values in your Glide Table: 1) The earliest time stamp via the rollup, and 2) The actual time stamp for each user, via the lookup
  • Only one of your users actual time stamps will match the earliest, so that user becomes your winner.
  • Present that user with a button that adds the new row, with the incremented ID, and present the rest with a “bad luck, try again next time” message.

The rows in the GSheet are merely transactional, and are only needed until such time as the process for each item is completed, so you could clean those out on a regular basis.

4 Likes

Whoa. This works. I just did it in my head, and yeah that will totally work. And I can probably write a Google Script to clean it out during slow periods.

So minting becomes a two step process: “Request the next edition!” and if they are successful “Congrats Edition #5 is avail, tap here to mint it!”

The UX is a little clumsy, especially if like 100 people are trying. I could see most of them being like, “F this, I’ll try later” but maybe that’s okay.

One small clarification… the Glide Table that has the Template Column and the Multi-Relation to the GSheet… I currently have two Glide Tables:

Collectibles
CollectiblesClaims

The Collectibles Table is where single line entries representing the collectible as a whole are recorded. So this is just all the collectibles in the game, the number of Editions available, Name, description, time it was created, etc.

The CollectibleClaims Table are transaction of the actual claim of the collectible by various users. Each row includes an incremental ID Number stamp, a timestamp (which currently is all over the place cause I’m using Glide’s local time stamp), User IDs, Transaction IDs, and Collectible IDs (which Relate to the same ID in the Collectible Table).

So I’m using a Template column in the Collectibles Table that references the Collectible ID of that row and the signed in users UserID. I use that to validate whether that signed in user ended up being the winner in a race between a bunch of folks that all submitted around the same time. If they are the winner, they get a new button that allows them to add a new row to the CollectionClaims Table with the incremental ID.

That’s correct, yeah?

The only snag, what if two people happen to press that second button at the same time. Isn’t that its own new race? Like user one gets distracted and doesn’t hit the mint button, and in that time user two tried again, and this time successfully got that mint button.

Perhaps Integromat also sends back a boolean value that remains in place until user one has pressed that second button, and while in place all other users need to wait. Then it toggles when the transaction is added to CollectionClaims, and users get a message that they can try again. But what if the person has a heart attack after they’ve won the race but before they press the button? The whole system comes to a halt.

I think I probably just need to play with it. But it’s certainly very promising.

yep, that all sounds right.

In theory that shouldn’t happen, because you can only have one winner. ie. only one users timestamp will ever match the earliest timestamp.

Yeah, that’s a good point. In the scenario I’ve been working on (with @Mark_Turrell), we give users an option to back out. If they take that option, then we clear the values in their row in the GSheet (via the single relation), which allows the next in line to claim the prize.

In your case, you could probably also introduce a time out (to guard against the heart attack scenario) by setting a column somewhere X mins into the future, and then work that into your logic.

Ah, of course, even if someone does try again, they’ll still get the “Sorry try again” until winner is no longer the earliest timestamped person. Perhaps rather than “Sorry Try Again”, everyone other than the winner gets something that tells them they’re a certain number in queue. And as each subsequent winner claims the collectible, their row is deleted and the next user in line gets the claim button and everyone’s message updates to tell them they are closer to the front of the line.

The timing out part of it though is the part I need to figure out. Glide doesn’t have anything that would delete that earliest timestamp row on a timeout… timeout would have to be a visibility condition on a button that users would push to delete that row. A little clumsy. So much button pushing. But it does seem like this would at least assure us the transactions truly would be in order.

Man, thank you for this. Really. This has been fantastic.

1 Like

I just thought of something else… why Integromat? Why not just write the value to the GSheet directly and use Google Scripts to add that timestamp? Is Integromat faster? Doesn’t Glide pull data from updated GSheets every several minutes if the sheet was edited outside of Glide. If Glide adds the rows to the sheet, and GScripts adds the Now(), I have to imagine it would be the same result and probably be pulled in faster. Am I missing something there?

Yeah. You could do it with GAS. We just used Integromat because we already had a bunch of scenarios running. Interestingly though, during testing the same button was triggering both the Integromat scenario and a Set Columns in the GSheet. And consistently, the time stamp from Integromat would arrive well ahead of the Set Columns sync from Glide. This was a Private Pro app, with background sync enabled. So based on this, I’d say it’s actually quicker to write a value to a GSheet from Glide via an Integromat web hook, than it is directly via Glide :wink:

But, YMMV, so I’d recommend doing some testing for yourself to see which works best for your use case. Either way, it’s the same principle so the end result should be the same.

1 Like

I don’t think you’ll need any extra button pushing. If you’re using the future time stamp to control visibility, then once the users local time is greater than that time stamp, then the button should just disappear without them doing anything at all.

With a little bit of creativity, you probably gamify this and make it quite engaging for your users :slightly_smiling_face:

Still thinking about this, but the whole problem is the disconnected nature of glide apps with their own local copy of the database, rather than everything only referencing the central database. I agree we need something to number rows and I highlighted my thoughts in this feature request post:

My best thought (which may not exactly cover limited minting of collectables and allow some to slip through but maybe that can be accounted for) is to create another table to function as a work table. When I say work table, I mean a table with a preset number of rows and a rows that are numbered. If you’ve seen my calendar app, it will make more sense. That calendar is a table with around 30 to 40 rows that are numbered sequentially, but with some math or single value columns, can ultimately represent any possible month of any possible year. That way, an unlimited number of year/month combinations can be represented by those 30-40 rows. So a work table is a table with static rows that dynamically changes, either per user if you use user specific data, or globally with basic column data.

One thing you could consider, is to create another table that’s similar to the CollectibleClaims table, but whenever you create a new collectible, you manually add the number of rows with the collectible ID and a sequential number. Then you create a relation using the collectible ID to the CollectibleClaims table. Finally, you create a series of single value columns to get a user’s claim using the row number to get the row however many positions from the start of that relation. Something like this, but instead of a hard number, you set the Row setting to the row number column.
image

This would pull all those claims into the new table with a set row number. Any additional claim attempts would just be ignored and wouldn’t pull into that new table. The only problem is timing. For a moment, if 2 people claimed a collectible at the same time, they may see that they both received edition #1 and until the data sync back to glide and back to the user again, then one of them will jump to edition #2. In that case, it may help to introduce some sort of timed delay before they see which edition # they get.

Still may not be a perfect solution and will essentially double your row count, but hopefully it may be something you can work with.

1 Like

@John_Cabrera just curious, how did you get on with this one?

I’m still working on it. It’s actually more complicated than we originally discussed. The reason this method works well for your use case is that it seems you “pre-mint” your collectibles and the race is about who can claim them first. In my version, the claim and mint process are the same. The collection set only exists in the abstract and when people claim them, the system mints one and numbers it and gives it to them… up until the set has reach its max limit (100 max editions, 75 max editions, etc).

The problem with a line for folks claiming is that technically everyone is claiming the same one. So while the earliest person (by timestamp) gets the chance to claim first (and everyone else gets a “Try Again” message), a potential problem arrises if that first person fails to claim it after being there first. All subsequent claims have to wait for whoever is in first position to press that second button. If we had auto-actions that didn’t require a user to tap again, I could make it all one action from the get go and I wouldn’t be concern. But it’s that second click that could potentially hold up the whole line. And with some collection sets having as many as 100 editions, there are just too many chances for that to happen. A timeout on that first person is also a little weird, because it means the rest of the folks in line still need to wait for that timeout to occur, and in general it just overcomplicates the system.

So I’ve come up with an interim solution, that should work while our member base is relatively manageable.

I currently have a two step process as you’ve described. The first part of the process I call the Mint Step. In this, users press a button to “mint” an edition. Doing so, creates a new record in my main Glide Table for Collectible Transactions as well as in a Google Sheet I called the Mint Sheet. The Glide table has all the relevant info about the collectible (Claimant ID, Collectible ID from the Collectible table, Transaction ID, etc) except it leaves the Edition Number blank for the time being. The Mint Sheet only gets three pieces of info: Claimant ID, Collectible ID, and total number of editions in the set “EditionCount”. I don’t even need to add a date, since I can simple use the sheet order itself as the line order. Then in another column called Edition#, I have a Formula (added by Google Scripts, since it doesn’t work as an Arrayformula) that numbers each of the rows sequentially based on the Collectible ID–so starts counting again at #1 when a new Collectible ID appear, but continues the count from any previous Collectible ID… That formula looks like this:

=CountIf(B$2:B2,B2)

B2, of course being my column with the Collectible IDs, and the 2 in the last two B2s of the formula incrementing to match the current ROW().

Then I have another Boolean column in the Mint Sheet called Claimed, and another called CurrentCount with an Arrayformula that counts the number of specific CollectibleIDs with a Claimed value of TRUE. In Glide, I am relating these tables the same way you described… using a Template column in both to create a ClaimantID_CollectibleID relation.

So after a member Mints a Collectible in the set (and sits for a few seconds with an animated gif of a loader wheel), they get a second button that asks them to claim it. This doesn’t prevent anyone else from Minting and Claiming before they do, even if they have an earlier Edition# in the Mint Sheet. If they do claim it, it sends a TRUE value to that Claimed column and the CurrentCount goes up. It also sets the EditionNumber column in the Glide Table for the Collectible Transaction with the Edition# (using the relation between the sheets).

When the CurrentCount equals the EditionCount, I have a Google Script written to delete all the Rows with that Collectible (reducing total Row Count in the sheet). Unfortunately, I can’t delete before then, since the Edition# Arrayformula needs all the most recent collectibles to calculate the correct sequential Order of Editions. This means if any Collection Set has not been fully minted, all of its previously minted editions do take up space in that Google Sheet (not ideal), and even if all are minted, if they all aren’t claimed (less likely but still a possibility), the Google Script won’t delete them and they could end up taking up space.

However, the latter could be manually alleviated by an admin. If I noticed that a collection has been fully minted but somebody got confused and left the page before pressing that second Claim button, I can just claim it form them, send that TRUE value and stamp their Glide Table transaction with the official Edition#. If, in the future, Glide offers actions on screen navigation or timed interval or something, I could automate that part too.

There’s probably even a Google Script I could write to populate that Edition# column with a permanent number, and if so, I could delete the row as soon as it was claimed. But my brain is taxed enough by what I have so far.

Right now, worst case scenario… we have 250 Collectibles being claimed in the app at the same time all with a Max EditionCount Limit of 100 (probably something I’ll set just to be safe for now), and all of them only have 99 of them minted. That would put me pretty much at the 25k sheets row limit. But the chances of that happening at this point in my product development is pretty much nil.

One thing I am considering is Collectibles having both a Max EditionCount AND an expiration. So the Collection locks either if the max number of editions have been minted OR a certain date has passed. I can then use that second expiration date parameter to tell Google Sheets to clean out that Collectible. Of course, the danger there is people could trick the app by changing their system time.

Nothing is perfect at this point, but I think this is the best I can manage.

2 Likes

Now that I think of it, I guess I could use Google Scripts to validate a set expiration date and send some additional TRUE value to another Boolean column called Expired. The user wouldn’t be able to change the date for Google Scripts the way changing their system clock can affect conditions in the app. And if a collectible set expired, an admin could then claim any outstanding minted but unclaimed collectibles which would then trigger GScripts to delete all rows with that collectible.

Still within the realm of possibility that 250 different collectible sets are created around the same time all with max editions of 100 and all are 99% claimed before any of them expire. But again, chances of it happening are just too low right now. Even lower with expirations.

I don’t have the faintest idea what you guys are talking about but when I ran into this issue I’d never found a spreadsheet interesting enough to create.

So since I couldn’t order them numerically, but I could alphabetically, I simply added an a or aa or b in front of the , then found a copy paste solution and kept it moving.

Idc if I’m an idiot, Im about the paper I got shit to do

1 Like

for some non-code minds… row ID is a mystery :wink:
When I was a child … I started with BASIC … where you have lines numbered, and you relate to them…
then PASCAL came… and it took me view days to wrap my head around that… that you can relate to just a name… no line numbers…

I was 13 years old

I understand. that many users come here, just with google sheets understanding how to relate values… it is row number and column number…
but in coders world… it is the full address of that cell, which have nothing to do with rows and columns

2 Likes