Has anyone discovered a reliable way to do Row Numbers (sequential numbers for each row in a table) inside Glide–meaning not using the Google Sheets method of ARRAYFORMULA and ROW. I’d be fine using this method if I was using Google Sheets, but I’ve moved over to a Glide Tables for a particular ledger where I need considerably more rows than Sheets can handle and no lag. But I need Row Numbers.
@david many months ago there was a brief chat about this in another thread (I tried to find it). You seemed to be questioning whether such a feature was needed, or perhaps how important it was compared to RowID. You asked for use cases. A few folks gave you some, and I said I’d get back to you. Well, tonight I released a feature in my app, using a calculation method to create Row Numbers and it was an absolute fail.
My app has a collectibles feature. My members are collecting limited edition badges… basically NFTs except rather than a Blockchain, it’s a Glide Table. I need to number them. It’s an important part of collectibles. I’m using the following method: I have a Relation bundling up all claimed transactions in the Table of a specific collectible… then I have Rollup counting those up… then I have a Math Column adding 1 to that total (I call that column NextRowNumber). And so when someone claims a collectible, a new transaction is added to that ledger, and it stamps whatever is currently in the NextRowNumber column into a new column called EditionNumber. Then that new transaction increases the Rollup count and the NextRowNumber columns… rinse and repeat.
Should work, right? And it usually does… except when 50 people all attempt to claim the collectible within 20 seconds. Glide doesn’t update columns fast enough, and half of the transactions get stamped with the same Edition#.
Can we please have a RowNumber column? Pretty please? Just like the RowID column–autoupdating–but just sequential numbers. Or has someone come up with a more reliable solution to this?
yeah, I’ve used an almost identical technique in a number of apps, and that race condition is a real gotcha. It’s fine for low volume, where rows are added infrequently. But once you ramp the traffic up, then all bets are off. I’m not aware of a reliable workaround.
I think the underlying issue here is that Glide transactions are not atomic.
I wonder if there’s some kind of third party integration that could be used as a sort of intermediary to slow down the transactions. So rather than the action creating a new row of data, it sends it all to something that then sends it all back to create the row and only sends the next in the queue after some confirmation that the previous one was received and added to the ledger. Really just some kind of queue system to slow down a flurry of transactions being added to a table all at once. Even that would suffice. But Glide doesn’t seem to have anything like that, does it?
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
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.
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
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.
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.
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.
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.
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
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.
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
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.
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.
