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:
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.