Each proposal can have multiple member linked with the proposal number. For example, proposal number 2022058510 has two members.
I want write a logic to update the status of the proposal in the Proposal table when status of all the members is updated to "Tele-mer completed" in the member table. I have a form which updates the status of member in Member table after Submit button is clicked.
I do understand that we need to created an automation to update the status of the proposal on Submit button click, but I am unable to write the formula for this.
After reviewing with our team we have come up with the below proposed solution:
While it might be possible to write a single automation and update both tables when clicking the button, the formula would be very complex. Instead we recommend splitting the problem into a second automation (and a helper column) which should make it much easier.
In the Proposal table, add an additional column that finds how many rows have the desired status. The name does not matter, but for this example we used [MembersWithUpdatedStatus] and substituted "UPDATED_STATUS" as the string value for the desired status, so the customer should change this accordingly.
Set this column to the following formula:
=ROWS(Filter(Members,"ThisRow()[ProposalNumber]=Members[ProposalNumber] AND Members[Status]=%","UPDATED_STATUS"))
This formula filters the number of rows in the member table which meet 2 criteria:
The member rows have the same ProposalNumber
Each of those rows has the correct Members[Status]
Create a table automation (not an automation associated with the app) that triggers on that column changing with a single action block (update):
When:
Column Changes
in table: Proposal
=[MemberMembersWithUpdatedStatussWith]
Run automation when this formula is TRUE:
=AND(ROWS(ThisRow()[Members])>0, ROWS(ThisRow()[Members])=ThisRow()[MembersWithUpdatedStatus])
That formula uses this formula fragment ROWS(ThisRow()[Members]) twice to determine a numerical count of matching members (note the similarity to the alternative form of the column filter in step 1).
This formula does the following:
It only updates the row if there is at least one associated member (this might not be necessary for the user depending on their specific use case)
Checks that the total number of members matches the total number of members that are updated.
Update Action:
Update Rows in or add row to:
Proposal
Match Rows from data source to rows in Proposal if:
[Column]
If this data:
= [ProposalNumber]
Is in proposal column
= Proposal[ProposalNumber]
Take Data From
=FINDROW(Proposal_Status,"Proposal_Status[Status]=%","UPDATED")
and write to
=[Status]
Note, that the From expression uses a =FINDROW(...) to get the correct status row (="UPDATED" is a mistake as it would write the string but not the row to the status column).
We hope this answers your questions. Please let us know if you have any other questions or comments.
Thanks for the answer. There could be lot of different member statuses and their respective conditions present, I believe doing that by adding new columns would be difficult.
We need to create single automation for the Proposal_Status[Status] column automation. Let me try combining both of the expressions:
=AND(ROWS(ThisRow()[Members])>0, ROWS(ThisRow()[Members])==ROWS(Filter(Members,"ThisRow()[ProposalNumber]=Members[ProposalNumber] AND Members[Status]=%","UPDATED_STATUS")))
We can have automation step for different conditions.
Question: How are we handle transections in automation if automation has multiple steps?
A single automation is one transaction, and all of the steps run or if an error is encountered all roll back. The order of steps does matter (i.e deleting the context row will prevent subsequent actions from using that row).
