Multi-Row Formula "Row+1" Reference Only Works for One Row

[wht822]

Hi all, I'm in a situation where I have a field that is empty in each row except for one cell that contains the word "Cash." The field name is "Identify Cash" and I am trying to use the multi-row formula tool to make every cell in the "Identify Cash" field above the cell that contains the word "Cash" to also contain the word "Cash." Right now, the formula I'm using is

if IsEmpty([Identify Cash]) then [Row+1:Identify Cash] else [Identify Cash] endif

I think the issue is that the tool works from the top to the bottom. So when it sees one cell is blank, it references the next cell. And if that next cell does not contain the word "Cash," then it remains empty. However, once the tool finally reaches a cell that is empty where the next cell has "Cash" listed, the tool only updates that one cell and doesn't back track to update the rows above. Therefore, the tool can only update one row at a time. Does anyone have any suggestions around this?

Thanks!

[fmvizcaino · Accepted solution]

Hi @wht822 ,

You are absolutely right, since the multi-row tool goes from top to bottom, you will only update the cell right above the cell containing the 'Cash' value.

One thing you could do is to order your data in order for your idea to work.

Best,

Fernando Vizcaino

[TonyA]

The Multi-Row Formula tool does work from top to bottom. The easiest way I can see to do what you want is use a RecordID tool to create an index column and use that to determine how the Identify Cash column gets filled.

For example, if you want all rows above the row that has "Cash" in that column to be set to "Cash," then sort Record ID in descending order and use 

IF IsEmpty([Identify Cash]) THEN [Row-1:Identify Cash] ELSE [Identify Cash] ENDIF

Then resort RecorID in ascending order.

[DQAUDIT]

This is my understanding of the "ask".  You have one field called "Identify Cash".  Let's assume there are 500 rows of data.  If on row 241 "Cash" is populated then do you want the preceding 240 rows need to be populated?

If so this will work:

Step 1: Input Tool to source in data (If you don't have one already add a Record ID type field)

Step 2: Connect Input Tool To filter and search [Identify Cash] = "Cash"

Step 3: Add Append Fields Tool

Step 4: Connect the Input Tool from Step 1 to the T achor of the Append Fields Tool

Step 5: Connect the Step 2 Filter to the S anchor of the Append Fields Tool

Step 6: Configure the Append Fields Tool (I de-selected the [Identify Cash] data element from the Source input and renamed the [Record ID] from the source input to be [CASH_POINT]

Step 7: Add a multi-row formula tool to update existing field: [Identify Cash]

Expression: IIF([Record ID] < [CASH_POINT],"Cash",[Identify Cash])