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?

Solved Solved
0 40 1,531
1 ACCEPTED 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]
  )
)

View solution in original post

40 REPLIES 40

MultiTech
Participant V

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.

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

Jonathon
Participant V

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

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

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

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

Steve
Participant V

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)
)
  1. FILTER("The Pinpoints", ...) produces a list of rows in the The Pinpoints table that match the given criteria (...; see (2)).

  2. (([_THISROW].[Geotag Label] - 1) = [Geotag Label]) matches those rows that have a Geotag Label column value exactly one less than this row’s.

  3. ISBLANK(...) asks, are there no rows with a Geotag Label value one less than this row’s (...; see (1))?

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

Steve
Participant V

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.

MultiTech
Participant V

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

@Steve

The second suggestion just gives me the max value

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

Top Labels in this Space