Increase Record ID by 1 every time specific word is found

Hi All

I have a report that I am trying to assign record id to.

New Record always starts when it contains "Hello " so from first "Hello" every row should have assigned record id 1 until second "Hello" where it should start record id 2 and so on.

There is different amount of rows between them so it's not straight forward for me. Could you please help?

I think it should be done somehow with multi-row formula but I cannot quite get it right

Here is what I tried based on research:

IF Contains([Row-1:Field_1], "Hello")
then [Row-1:Record ID]+1
Else
[Row-1:Record ID]
Endif

hello.PNG

Workflow

Datasets

Developer Tools

Accepted answers

mceleavey

Hi @dlesny ,

This is a straightforward multi-row formula is you are using only one text string for the search criteria, in this case MARS LIVERPOL.

If, however, you are using multiple search terms then you will need to wrap this process in a batch macro and overwrite the search criteria with the control parameter value.

The formula I used is as follows:

if contains([Field_1],"MARS LIVERPOL") then [Row-1:RecordID]+1 else [Row-1:RecordID] endif

Screenshot 2023-08-22 113816.jpg

Screenshot 2023-08-22 113914.jpg

In the input I have ensured the first row does not contain field names, although this might only be for the example you have provided:

Screenshot 2023-08-22 114024.jpg

Workflow attached.

I hope this helps,

mceleavey_solution.yxmd

FinnCharlton

@dlesny the same approach should work, try:

IF StartsWith([Field1],'Hello')
then [Row-1:Record ID]+1
Else
[Row-1:Record ID]
Endif

All comments

citronmelisa

If I understand your question correctly, something like this should work:

if [Row-1:FIELD_1]!=[FIELD_1] then [Row-1:rec_id]+1 else [Row-1:rec_id] endif

image.png

FinnCharlton

Hi @dlesny, your formula is nearly correct, you just need to swap the [Row-1:Field_1] in your expression for just [Field1]:

IF Contains([Field_1], "MARS LIVERPOL")
then [Row-1:Record ID]+1
Else
[Row-1:Record ID]
Endif

dlesny

Hello

Thank you for your reply. I didn't explain it right. Please see picture on what I am looking for.

mceleavey

Hi @dlesny ,

This is a straightforward multi-row formula is you are using only one text string for the search criteria, in this case MARS LIVERPOL.

If, however, you are using multiple search terms then you will need to wrap this process in a batch macro and overwrite the search criteria with the control parameter value.

The formula I used is as follows:

if contains([Field_1],"MARS LIVERPOL") then [Row-1:RecordID]+1 else [Row-1:RecordID] endif

Screenshot 2023-08-22 113816.jpg

Screenshot 2023-08-22 113914.jpg

In the input I have ensured the first row does not contain field names, although this might only be for the example you have provided:

Screenshot 2023-08-22 114024.jpg

Workflow attached.

I hope this helps,

mceleavey_solution.yxmd

dlesny

Perfect, thank you very much

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703