How to start number at 1 each new day

TE21
Participant II

I am recording tests and need to be able to start back at 1 on each day and then automatically count for that day only. Meaning if I ran 10 tests today I need my form to auto number each test I enter for today, and then tomorrow start back at 1. Could someone help me?

0 1 92
1 REPLY 1

Darmund
Participant III

I think you could do something by setting an initial value expression on your test number column.

Basically, if the previous recordโ€™s creation date is equal to todayโ€™s date, the initial value of the test number column should be the last recordโ€™s test number +1. But if the previous recordโ€™s creation date is less than todayโ€™s date than the initial value of the test number column should be 1.

Maybe something like:
IF([_THISROW-1].[CreationDate] = TODAY(),
[_THISROW-1].[TestNumber]+1,
1)

Youโ€™d have to add the CreationDate column though and make sure it stores the correct date value.

Edit: Nvm, [_THISROW-1] doesnโ€™t work the way I thought it would. You could try comparing the latest date in the table like this:

IF(MAXROW(โ€œTestโ€, โ€œDateโ€) = TODAY(),
[_THISROW].[TestNumber]+1,
1)

This way youโ€™d be comparing to the test with the largest Date value instead of the previous. It would be hard to add tests on days other than the current day.

Alright, final edit cause I think this works:
IF(
MAX(Test[Date]) = TODAY(),
MAX(SELECT(Test[TestNumber], ([Date]=TODAY())))+1,
1)

Top Labels in this Space