Increase Record ID by 1 every time specific word is found

[dlesny]

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

[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

[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 · Accepted solution]

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

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:

Workflow attached.

I hope this helps,

M.

[FinnCharlton · Accepted solution]

@dlesny the same approach should work, try:

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

[dlesny]

Perfect, thank you very much :)

[mceleavey]