Hey good folks.
I need help writing an expression that will give me the smallest value not in a list (if thatโs even possible
I have a Table called โPINPOINTSโ, which has a column called โGeotag Labelโ
The โGeotag Labelโ column has all number values (1,2,3, etcโฆ)
What Iโd like is for the initial 'Geotag Label" value to be the smallest number not currently in the list.
This way, if i delete value โ56โ from the Label column, the next row i add will return value โ56โ
Something like
"Between MAX(The Pinpoints[Geotag Label]) & MIN(The Pinpoints[Geotag Label]),
give me MIN(NOT(IN(The Pinpoints[Geotag Label]))),
else gimme MAX(The Pinpoints[Geotag Label] + 1) "
(bad formula, i know. but just an example of what i need)
Can anyone offer any ideas?
Solved! Go to Solution.
If youโd prefer to fill in the gaps from below, the alternative for that is:
Virtual column Geotag Label + 1: ([Geotag Label] + 1)
Available value:
ANY(
SORT(
LIST(1)
+ The Pinpoints[Geotag Label + 1]
- The Pinpoints[Geotag Label]
)
)
Iโm not sure what youโre going for with the middle part thereโฆ
why not just use MAX(The Pinpoints[Geotag Label]) + 1
Itโs just a label right? A counter? So why all the middle stuff?
I attached a screenshot of my spreadsheet.
See how 113 is missing? That was a record i deleted (old records i no longer need will get deleted).
I want the next row i add to this sheet to have โ113โ as the Geotag Label value, not the max value of the column. (the new row will essentially replace the old oneโs position)
For various reasons, i do not want gaps in the numbers in this column.
(The โGeotag Labelโ title is a misnomer, it is essentially a number column that i use to sort the spreadsheet.)
I hope that helps clarify the reason for my original request. If not, feel free to ask more questions.
Hmmmโฆ challenging. How to take a list and find where things stop being in sequenceโฆ hmmmmโฆ
You need to take your list, find the min and max - construct a complete list of those numbersโฆ then subtract YOUR list from the master listโฆ giving you a list of everything missing.
Then you can find the minโฆ and youโve got your value.
But how to dynamically construct the โMaster Listโ??? Thatโs tough.
@MultiTech_Visions yes exactly. Iโm trying to retrieve a value that is essentially non-existent as far as Appsheet is concerned. I donโt know how/or even if, I can discern that value.
Hi @Mic_L_Angelo,
In the screenshot shared by you, the Geotag label number is less by one as compared to the corresponding row number of the sheet.
Could you please add if it is default combination. This means ideally the Geotag label should always be less by one than the corresponding row number?
@Suvrutt_Gurjar thatโs just a coincidence. I sorted the rows by the label number in the screenshot to clarify my example.
The Geotag Label number has no explicit relationship to the row number.
Hi @Mic_L_Angelo,
Thank you. Got it. Since the Geotag label numbering seems to follow the sequential number pattern, is it correct to assume that if there are 150 rows in the table and if not a single Geotag label is missing, the ideal max Geotag label number would be 149?
Yes exactly. @Suvrutt_Gurjar
How about instead of completely deleting old records you pseudo-delete them by setting a [deleted] column to TRUE, which filters them out of views.
Then you can select the MIN() geotag label from the pseudo deleted records, re-use the geotag label, and then have an onsave event which fully deletes the old record if necessary.
If the geotag label is currently your primary key column then the process will have to be slightly different but still possible.
Alternatively,
@Jonathon thanks for the suggestion.
I suppose I could do that. But to be honest, Iโm not convinced if itโs worth it.
It seems your solution would require adding a โDeletedโ yes/no column. It couldnโt be virtual because i would need to change it manually.
This sheet is tied to a Google App script that runs a bunch of calculations, adding another column would mean re-jiggering the script, which would be a pain on my end.
This issueโs not a big enough deal for me to go through that. If i can solve it with an expression, Iโll just let it be.
Mic, refer to my edit:
Alternatively,
- create a workflow rule which triggers on deletes and adds the geotag label to a table of available geotag values
- add an onsave action to find and clear these values when they are reused
This method would not require adding a [deleted] column to your worksheet, but would require the addition of another table to track the free geotag labels.
@Jonathon sorry, didnโt see your recent edit.
Yeah, i could do that too. i donโt think i would want to add a another table just to solve this.
This is more of a โluxury featureโ than anything. I can work around it if canโt be solved with an expression.
Alternatively, if there was a way to keep the Geotag Label sequential,
If i delete #113, then #114 becomes #113),
that would be preferable.
If you are okay with geotag labels constantly reordering themselves, then you could achieve that by using a RANK() function based upon a created datetime within your spreadsheet.
Unfortunately, as you have noted, AppSheet does not support iterative functions client side. As such, there is no better way of doing what you need that iโm aware of.
@Jonathon Iโll look into RANK() and let you know if i come across anything.
And no problem, all good. I wasnโt expecting this to work easily, but figured i would ask nonetheless.
Thanks for the suggestion tho. Much appreciated.
@Jonathonโs solution is practical.
Just for my understanding, may I ask a couple of more queries when you delete a Geotag number, does it mean deleting the record itself or just the Geotag number fromthat column. Also if it is deleting the record through AppSheet, does it mean that recordโs row remains empty in the spreadsheet or do you immediately use say G Script to delete those empty rows.
@Suvrutt_Gurjar I delete the entire row through Appsheet, which just โclearsโ it (as i imagine you know already)
My Google Script has a โRemove All Empty Rowsโ function which actually deletes later on.
The Google Script is a time-based trigger, so it doesnโt run immediately after the row is cleared out.
Hi @Mic_L_Angelo,
Thank you. Got it. Yes, my query was in exactly in that direction. Since the AppSheet deleted rows are periodically deleted by the G Script, I think @Jonathonโs suggestion is best. Or else if the Geotag was closely tied to the row number [_RowNumber], we could have thought of a [_RowNumber] based approach.
If the upper range of geotag numbers is knowable (e.g. they are unlikely to ever exceed 500 as they are being removed about as fast as they are created), then another solution may exist in line with @MultiTech_Visions thoughts:
You could create a virtual column that holds a static list of values from 1 to 500 (just a comma separated string of numbers stored in a usersetting).
You could then run two filters against the list: one to remove all values greater than the current max geotag value (getting your complete range of numbers), and then the other to remove all duplicates.
@Suvrutt_Gurjar what would the โRow Numberโ suggestion be?
Maybe that would be the easier option here.
The number is just used as a reference for me, I donโt need it to be tied to a specific row.
Hi @Mic_L_Angelo,
I have not yet worked out exact solution yet based on [_RowNumber] and may not be possible also because there could be empty rows in between till Google Script deletes them. Or else typically without empty rows, the count and max number of Gtag numbers would match the [_RowNumber]-1 count. So I believe, some expression could have been possible in that direction.
I will definitely post if something useful strikes.
@Suvrutt_Gurjar @Jonathon @MultiTech_Visions
Thanks for all the back and forth, guys! I appreciate all the ideas you guys provided.
At this point, Iโm gonna call it a wash for now. I do need it resolved, but itโs not important enough for me to make any new virtual or actual columns to solve it.
Iโll just use some kind of MAX(column) +1 formula for now. Iโll tinker with Rank() a bit and see what happens.
In any case, thanks for the input.
Maybe this?
Add a virtual column to The Pinpoints called (e.g.) Unused Geotag Label:
IFS(
ISBLANK(
FILTER(
"The Pinpoints",
(([_THISROW].[Geotag Label] - 1) = [Geotag Label])
)
),
([Geotag Label] - 1)
)
FILTER("The Pinpoints", ...)
produces a list of rows in the The Pinpoints table that match the given criteria (...
; see (2)).
(([_THISROW].[Geotag Label] - 1) = [Geotag Label])
matches those rows that have a Geotag Label column value exactly one less than this rowโs.
ISBLANK(...)
asks, are there no rows with a Geotag Label value one less than this rowโs (...
; see (1))?
IFS(..., ([Geotag Label] - 1))
identifies the unused Geotag Label value identified by (3)).
Then, to choose an available Geotag Label:
ANY(
SORT(
The Pinpoints[Unused Geotag Label]
- LIST("")
)
+ (MAX(The Pinpoints[Unused Geotag Label]) + 1)
)
Interesting solution Steve; if Iโm understanding this correctly each row is performing a check to see if there is a gap in the sequence beneath it. This is sort-of mimicking an iterative formula. Can you speak to the performance of a solution such as this?
One scenario where I think this (air-quotes) fails is if there is a gap greater than one. For example, if the sequential values go {1, 2, 3, 6, 7, 8}
, then the formula would only return 5 as a gap instead of both 4 and 5. Although, upon reusing 5, 4 would become an option. It may be best to do the reverse (e.g. each row should check if there is a gap above it instead of below), such that gaps are filled in ascending order?
Correct!
Not great. Better solution coming shortly.
True.
Hereโs another alternative thatโs much more efficient than my previous:
Create a virtual column in the The Pinpoints table named (e.g.) Geotag Label - 1 with an App formula of ([Geotag Label] - 1)
.
Then, to choose an available Geotag Label:
ANY(
SORT(
The Pinpoints[Geotag Label - 1]
- The Pinpoints[Geotag Label]
- LIST("", 0)
)
+ (MAX(The Pinpoints[Geotag Label]) + 1)
)
Essentially, each row knows its own Geotag Label value, and identifies its expected predecessor. To find an available value, start with all expected predecessors, remove those that exist and those you donโt want to use (blanks, 0), and sort so that the lowest is first. In case all expected predecessors are used, choose a new value.
You have a clever mind.
If youโd prefer to fill in the gaps from below, the alternative for that is:
Virtual column Geotag Label + 1: ([Geotag Label] + 1)
Available value:
ANY(
SORT(
LIST(1)
+ The Pinpoints[Geotag Label + 1]
- The Pinpoints[Geotag Label]
)
)
Hi @Steve,
Wow! All interesting solutions !
This is an interesting case - please consider adding this to the documentation or as a standalone tips and tricks post.
This is brilliant!!! Way to go @Steve
Thanks to everyone commenting so far. I didnโt expect such engagement.
Iโll give your second option a try. @Steve
A question tho, does your suggestion require the [Geotag Label] field to be in sequential order?
For the scenario iโm imagining, they wouldnโt be
In other words, the order would be something like (143, 287, 19, 333)
Would your formula still work with this example ?
Order would not matter for either of @Steveโs solutions!
@Jonathon Nice. Lemme give a spin right now and let yall know
Try removing - LIST("", 0)
.
Try:
ANY(
SORT(
The Pinpoints[Geotag Label - 1]
- The Pinpoints[Geotag Label]
- LIST(0)
)
+ LIST(MAX(The Pinpoints[Geotag Label]) + 1)
)
No dice, still getting the max value
User | Count |
---|---|
43 | |
28 | |
24 | |
24 | |
13 |