I have Table A where a user inputs some information to make a record. This information gets concatenated to create a code (Input Code). This code I want to use to search in Table B for a matching code. Table B is my ‘Rules’ table. Next to each input code, is an output code. I want that Output code from Table B to be sent back to Table A which is then used to populate some further fields based on what text is contained in the code.
So Table A is my main table. Table B is my ‘Rules’ table which is comprised of many ‘Input Codes’ and in the next field, an ‘Output Code’. I want to be able take my concatenated code from Table A (a.k.a. my Input Code - which is generated by concatenating info the user has just entered into a few fields in Table A) and search through Table B for a matching ‘Input Code’. This matching code has a correlating ‘Output Code’ next to them which I want to bring back into Table A. This output code is used to populate some further fields in Table A.
I am essentially trying to avoid many, horribly long, IF statements by have a ‘Rules Table’ (Table B).
I’m sure this is possible, I’m just struggling with the process of linking or searching.
My overall aim is to avoid very lengthy IF statements as I have about 100 rules to work with.
Here is my Table A. Fields up to ‘Classification’ are filled by the user and that information is concatenated into a string called ‘Input Rule Code’.
I want this ‘Input Rule Code’ to search Table B (my table full of rules) for a matching code. This is because this table is my list of rules and next to the input codes are output codes such as below. This output code I want to be copied back to Table A.
This ‘Output Code’ string is then used to fill in the next fields in Table A. So dependant on what is inputted, that information will match a rule, of which has output information which I want to use to fill in the next few fields shown below.
I’m struggling to get my head around how I can search Table B for a matching code then take the Output Code from the matched row/rule and copy it back to Table A so it can be used to fill in the next fields.
Thank you so much for your help!
If this is possible, it seems to me a better solution than having a complicated IF statement with 100 rules inside of it.
The solution proposed by @Kamille_Parks is a great way of not using a script, I built the base quickly and recorded a video to demonstrate:
Let me know if you have questions re. that! Please find two screenshots in the following showing the automation trigger and how the record is updated:
W.r.t. scripts: Scripts can be triggered like automations and would therefore be triggered just like the automation in the video. If the solution above doesn’t work seamless, let me know and I’ll write a small script when I find the time :slightly_smiling_face: