Min value not in a list?

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?

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?

@MultiTech_Visions

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.

1 Like

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.

2 Likes

@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,

  • 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
2 Likes

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

2 Likes

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

1 Like

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

@Mic_L_Angelo,

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

1 Like

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

1 Like