How to restart a sequence of numbers?

Hi everyone!

Would greatly appreciate guidance for the date/time type and sequence numbering.

I’m aware that the sequential numbers are an issue on appsheets but in another post someone shared this expression that actually works:

(MAX(RESOURCES[WATERING]) + 1)

This will return sub-sequential numbers of the maximum value in the column.

What i would like to do is to sequence the waterings of the day, then refresh the count on the next day.
I tried this formula, but the watering numbers wont even change:

If(MAXROW(“RESOURCES”, “Date4”)=DATE([Date4]),(MAX(RESOURCES[WATERING]) + 1),1)

Thanks!

Hi @Oldsoul welcome to AppSheet!!

First, just to clarify. They ONLY way to ensure 100% true sequential numbering is if there is only EVER a single person adding the rows where the number is assigned AND they are always doing it from the exact same device.

Now having said that, there are many instances where applying a sequential numbering may never run into an issue only because the app never hits the use case where the issue would occur.

The more users and more frequently the app is used - the more likely sequential numbering will be interfered with.

WHATEVER YOU DO…do NOT use the sequential number as the row key.


Assuming your app is in the low risk category, I would suggest an expression like this to restart your numbering daily:

IF (COUNT(SELECT(Resources[ID], [Date4] = TODAY()) = 0,
     1,
      MAX(Resrouces[Watering]) + 1
)

Basically, if no rows exist for the day assign 1, otherwise grab the max value and add 1.

Someone might have some condensed way to do the same thing.

3 Likes

Thank you for the warm welcome @WillowMobileSystems !

Yes, in this case only one person is required to capture this information in the same device, which will contain the app.

I tried the formula but it runs into this error:
Cannot compare List with Number in (SELECT(Resorces [Watering], ([Date4] = TODAY())) = 0)

Date4 is date type and Watering is a Numeric type.

Sorry… the above expression had a parenthesis missing.

The expression was trying to compare the SELECT() = 0. Can’t do that. Need one more “)” after the TODAY() function. So the complete expression should be:

IF (COUNT(SELECT(Resources[ID], [Date4] = TODAY())) = 0,
     1,
      MAX(Resources[Watering]) + 1
)
1 Like

Thank you!

That expression does keep counting the watering!

So iit’s NOT working as you expect?

thanks for the good question and answer !!
@Oldsoul @WillowMobileSystems

But let me ask a few questions.
How to restart a sequence of numbers adds a new row?

As an answer to the original question. I would use this expression:

MAX( SELECT(
  Table[Serial] ,
  [Date] = TODAY()
) ) + 1

There’s no reason to count it first, blank-value+ 1 will equal 1.

This question doesn’t make any sense.

3 Likes

sorry my English is bad
I want to restart a sequence of numbers when adding a new row.

if this formula when adding new row Serial will continue number Serial past row

(MAX(MyTable[Serial]) + 1)

I’m still not understanding. You want to restart the series for every record? That would mean every record would get the value of 1. Maybe you can explain what you want with a screenshot of some actual data?

1 Like

im use this formula

(MAX(MyTable[Serial]) + 1)

is perfect

but when I add other forms
Screen Shot 2021-08-26 at 13.32.21

Its value is continuous but i want to start over

Ok. I think what you’re showing in the screenshots are child records within a parent, and you want the sequence to restart for every parent record. In which case, you simply slightly adapt the expression that I posted above.

Change it to:

MAX( SELECT(
  Table[Serial] ,
  [parent-ref] = [_THISROW].[parent-ref]
) ) + 1
2 Likes

Thanks so much !!
Really appreciate everything you’ve done.
That’s very kind of you.

1 Like

Awesome!

1 Like