Change the status field in more than one rows.

AppSheet AppSheet Q&A
,
1

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
status-closed action674×500 30.3 KB

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:

run on rows.png
run on rows.png1076×522 33.1 KB

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.

2

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

1 Like
3

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.

4

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.

5

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?

6

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.