Creating conditional new records based on a previous record

[nic_hamley]

Hi everyone,

I'm building out a workflow that will be used over multiple years. One thing I want to do is have a text input to determine the year-end or half year and the year in question, then create three previous period comparative fields based on this. For example, my current text input is:

What I want to do is create 3 new records, which basically says, if the HY/YE column record 1 is HY, then record 2 should be YE, record 3 should be HY, record 4 should be YE. And for the Year (YY) column, if record 1 is HY and 20, then record 2 should be 19, record 3 should be 19, record 4 should be 18. So it reads like this in a table:

Record	HY/YE	Year (YY)
1	HY	20
2	YE	19
3	HY	19
4	YE	18

I'm thinking a multi-row formula would work but not sure how to structure it. Any tips would be greatly appreciated.

Cheers!

[nic_hamley]

I should say that record 1 should be the anchor here (i.e. the user inputs what record 1 should be using text input, then the workflow updates to add in the correct extra rows)

Thanks

[DavidP · Accepted solution]

Here's a way to do it

[nic_hamley]

Legend @DavidP ! Thanks very much!