I'm trying to automate some processes around budgets and projects but can't yet get it working in a simplified test-app.
Am I chasing an utopia or should it work and am i doing something wrong?
Scenario:
I have a projects table: [Projects] and each table has a reference [Projects-Reference]
Each project can have multiple changes [Changes] so there is a one-to-many relationship via [Related Project].
Next each change can have multiple requests [Request] so also there is a one-to-many relationship via [Related Changes] and [Related Changes - Status].
A change has a status [Changes-Status] {submitted/approved/rejected}
Each request contains fields like [Request-Description], [Request-Quantity], [Request-LocalPrice], [Request-ExchangeRate], [Request-ValueInDollar] , etc.
So there are multiple request inside a project that can have different status via the Change table in between.
The objective:
The requests made are reviewed by he Change they are related too and than as group decided -for example- APPROVED. In that case I would like to copy the relevant Request-fields to a table [Approved] so that the values that have been approved are 'frozen' in the [Approved] table and cannot be messed later on by people that are working inside the project group.
The [Change-Status] field is manually changed to {approved} in the [Change] table
- based on the change of [Related Changes - Status] field in [Requests] all applicable requests should be copied with the applicable fields to the table [Approved]
- a relationship between the relevant Change and the now approved request should be created/copied. So that in the end we can see what what has been approved in the project and at what costs.
The challenge:
This all sounds simple but in my test app I can't get it working so that the change of [Related Changes - Status] to {approved} triggers the copy of the requested details to the approved table. And when during troubleshooting I use the same rule and with a trigger on a manually changed a test field in the [Request] table it works perfect.
I used the 'When the data changes' option on the trigger and the 'Copy record(s)' option on the action and than copy the values of the request fields to the relevant approval fields.
I didn't even study on how to copy the relationship yet.The question:
Is my scenario and desire possible via 'Automation' at all? Can you use a relationship field as trigger? And can i copy (maintain) that relationship the relevant change?
Thanks Jan-Willem.
PS as suggested in one of the other related posts i also tried a solution to keep all original data in the request table and work further with filters, reports rules and forms to separate and 'freeze' the approved from submitted and rejected but that leads to an enormous and confusing workaround.
------------------------------
Jan-Willem S
------------------------------