I have this challenge maybe is somenthing simple but I’m really stuck now ,
The thing I want to accomplish here is that some users are entering purchase requisitions in a table named the same.
In order to avoid mistakes with the account selection & the approver (manually speaking), I want to create a Formula that based on multiple validations can show the exact account # and the approver.
Things to be validated to get the right account #;
1.- Validate if the “Site” column is the same,
2.-Then Validate if the Department is the same,
3.-After those validations then validate the Item Category.
4.- Show automatically the right account.
Ex. In Yellow I put the right accounts manually just to let you guys know the correct answers.
The other issue I have is to have the right approver.
1.- Validate if the “Site” column is the same,
2.-Then Validate if the Department is the same,
3.-After that validate if the Limit is <= 1000 then select the approver, If the limit is higher than 1000 but less than 1500 then select the right approver, and if the ammount is over 1500 Get the name Julio as he has a limit of 5000.
Ex. In Blue I put the right approvers manually just to let you guys know the correct answers.
Finally it turns around to compare (lists with) conditions and have an outcome of “true()” or “false()”.
Very good possible with Coda and the skill set required is more in the area of being able to structure the logic then the actual coding (One of my biggest learning)
Approver formula uses multiple ‘Contains’ and IF statements
The down side of this is that is it very strict in it’s results and if you add a new Approver with a limit of say $3000, then both that approver and Julio will show in results as they both now meet the requirement of being greater than $1500. You will need to adjust the formula each time you want to add restrictions.
Alternative formula column - This uses ‘Contains’ filtering to get a list of valid approvers, sorts by Approval Limit (ascending) and then chooses the first row which would be the approver with the nearest limit.
This overcomes the issue above
As these are calculated by formula, the value will always update if something changes to the Approvers. For example if you add a new approver with a closer limit or if you adjust someone’s department so they are no longer eligible.
This being an issue depends on what you do with these values. If you take the Purchase Requisitions row information and place it in another table as static data then it will be fine but if you keep the rows as they are they will not present accurate information in the future. E.g. In two months time you want to see who was the valid approver on a Purchase Requisition it may not show the same approver now as it did then.
Site and Department can also suffer from this issue. Each is based on the Selected Person. E.g. If Daniel is moved to Site 1 then all existing and historic Purchase Requisitions will now show Site 1. The only fix for this would again be manually setting the info in another table or hidden columns.
Having it use a formula to display the Approver/Account like this may slow down the doc over time as the number of rows increase. An alternative would be to apply the same filtering via automation and have it set the value once so it is just a manual entry. Note that if this method is used and anything changes for the approver (limit, site, dept) it will not auto-update any existing purchase requisitions.
Lastly, if you note on row 2 I replicated row 1 and added multiple Item Categories. By doing this it is possible to return more than one Account number. You will need to disable Multiple Select on the Item Categories column or at least be conscious of it going forwards.
Here is the doc I created:
You can access the full published version for copying here.
Let me know if you need any help understanding the formula or what it is doing. I encourage you to look into Table Filters and Contains as Formula.
First thank you very much for the solution, this gave me headache but you understood very well the problem.
The other thing I want to say it’s that I learned a lot with your comments/suggestions, I will definately take those in consideration, becasue I didn’t knew I was to going to have the formula update issue until you mentioned it.
,