Is it possible to use codes and separate tables when working with lots of rules, instead of large IF statements?

Samuel_Henton · ‎Nov 21, 2021

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.

Rupert_Hoffsch1 · ‎Nov 21, 2021

Hi there! Do you have a pro account in order to use a script? In general, I’m not sure I understand 100% what you’re trying to build. I think it’ll help if you would share some screenshots.

Kamille_Parks · ‎Nov 21, 2021

If your Table A has a formula that concatenates an {Input Code}, you could have another field that is a Link to Another Record type pointing to Table B.

Then use a simple Automation to copy the value of {Input Code} into the {Link} field. That way your {Output Code} could be a Lookup-type field.

Be aware of what your plan’s allotment of Automations per month if you go with this method.

Samuel_Henton · ‎Nov 22, 2021

Thanks both! Apologies for the poor explanation.

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.

Samuel_Henton · ‎Nov 22, 2021

I’m luckily on the Enterprise plan so can use scripts. They require a user action though do they not? I would like it to trigger automatically when all input data is entered.

Rupert_Hoffsch1 · ‎Nov 22, 2021

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:

Kamille_Parks · ‎Nov 22, 2021

For my suggestion to work, the primary field of Table B needs to be {Rule Output Code}, so you would need to shuffle your fields.