Run a data action avoiding duplicates in different table

[Espegg]

I have 2 tables:
- PAYMENTS SCHEDULED
- ALL PAYMENTS

Also I have created a bot (Run a data action) on Table PAYMENTS SCHEDULED with "Updates" Data change type with the condition:
AND([STATO] = "SALDATO TOTALE", [ODR] = "DA SINGOLO ODV")


If there is this condition the bot works good and then creates a row to the table ALL PAYMENTS

But the problem is that if, in future a user edit a row in Table PAYMENTS SCHEDULED already created in the table ALL PAYMENTS with the bot, the bot creates another time the row and so I will have a duplicate on ALL PAYMENTS table.

How to avoid this? 

[Suvrutt_Gurjar]

Are the two table related, meaning do they have a reference relationship? If so which table is parent and which table is child? Also is it correct understanding that you run the bot on the "Payments Scheduled" table and the bot adds a new row in the "All Payments" table?

[Espegg]

Hi @Suvrutt_Gurjar ,
thanks for your reply.

Actually there is no relationship between the two tableS.
But iF it could solve the problem, I could create a ref column of ALL PAYMENTS table that takes the value from a ref column that I pass with the bot from the table PAYMENT SCHEDULED

[Suvrutt_Gurjar]

Yes, since anyway there is a relation between the two tables you may want to consider  creating reference relationship between them that will make any data exchange or validation easier between them.

For example assuming ALL PAYMENTS table is a child table if PAYMENT SCHEDULED table, your bot condition could be something like 

AND([STATO] = "SALDATO TOTALE", [ODR] = "DA SINGOLO ODV", ISBLANK([Related ALL Payments]))

Where [Related ALL Payments] is the reverse reference column in the PAYMENT SCHEDULED table. 

The essence is , I believe you will need to establish some common ground such as reference column to disallow creation of duplicate rows in one table when you are firing the bot on the other table.

[Espegg]

I have created a ref row in ALL PAYMENTS table ID RATEO that take the key value from PAYMENT SCHEDULED table. With the bot I pass this key value from PAYMENT SCHEDULED table to ALL PAYMENTS table and it works good.
But then If I edit a row in PAYMENT SCHEDULED table already "botted" in ALL PAYMENTS table, it creates a duplicate...
The bot in this case must update the row already created in ALL PAYMENTS table with the update value, not create a new row...

How could I solve?

[Espegg]

[Related ALL Payments] you mean the REF column in ALL PAYMENTS Table? @Suvrutt_Gurjar 

[Suvrutt_Gurjar]

No it is a reverse reference column in the PAYMENTS SCHEDULED table.

[Espegg]

Perfect! Now it works, but in case I update a record already existing in ALL PAYMENTS and if I update the column in PAYMENTS SCHEDULED, how could I update also in ALL PAYMENTS table for that record?

[Suvrutt_Gurjar · Accepted solution]

You could try reference actions. 

Please see the sample app below

Reference Actions - AppSheet

Actions: The Essentials - AppSheet Help

Please take a look at the action type "Data: execute an action on a set of rows"