Change the status field in more than one rows.

[lec]

Hi, 

I have a table BIC as below:

Datetime      Bank    Dated          Account        Amount    Status
2/22/2023      UBA     2/9/2023      1321742231       100           Opened        
2/22/2023      UBS     1/27/2023     7475174212      200           Opened
2/22/2023      UBA     1/17/2023     2369904112     300           Opened

I have created a bot/animation which generates a pdf file with rows of table, based on the condition that [Status] = Opened AND Today()-[Dated] > 1 

for simplicity. 

After the pdf has executed I wish to change all the [Status]=Opened values to Closed so that they don't interfere with the generation of the next PDF later at some point of time. 

For this I created an action 

status-closed action

Behavior has the formula :  AND([Status] = "Opened", TOTALHOURS(TODAY() - [Dated]) / 24 > 12)

This I tested and it changes successfully the values of the Status in the column that I select. So it works on one column. 

Then after the PDF is generated I created another task as below:

 

but this does not work. I know the value showing in Referenced rows is wrong and should be, i think,  

selecting the rows governed by the formula:

AND([Status] = "Opened", TOTALHOURS(TODAY() - [Dated]) / 24 > 12) but this gives an error

and I am unable to find the right way of referencing the rows that I wish to target. 

I'll be grateful for any help. Thanks. 

[dbaum · Accepted solution]

The expression in the Referenced rows property must return values from the table's key column.

[lec]

Hi Dbaum, 

Thanks. So I have a Key field named BIC ID and I changed the reference field to BICs[BIC ID], but that doesn't do anything either.  

Do I need to generate a list [BIC ID] values governed by the formula 

AND([Status] = "Opened", TOTALHOURS(TODAY() - [Dated]) / 24 > 12 

that targets the rows I want the Status values changed in? If so, how can I do this ? 

Thanks.

[lec]

Hi, 

Here is what I tried and thought it should work. I wrote an expression for the referenced rows as below:

SELECT(BICs[BIC ID], AND([Status] = “Opened”, TOTALHOURS(TODAY() - [Dated]) / 24 > 12))

  this returns a list of [BIC ID] which are correct.

Shouldn't then the reference action "Set the status to closed" then change the Status field of the corresponding rows ( calculated list of BIC ID keys ) from open to closed? Somehow the  Status field is still not getting updated to Opened to Closed.

Please suggest what may be going wrong and where? Appreciate any help. Thanks.  

[dbaum]

Review the automation monitor. Is the "Trigger the Status Change Action" step occurring? Does it indeed yield the intended list of rows? Is it somehow revising the status as intended, but then the status is changed again for some separate reason?

[lec]

Hi, Thanks for the help. It worked. Yes the list was being generated and I really did not change anything. It seems like it was a caching issue. I think after a couple of tries and refreshes, it worked.