Skip to main content

Create ruleset based on column contents

  • July 8, 2023
  • 4 replies
  • 55 views
C
Forum|alt.badge.img+7

Hi,

I have the following challenge where I would ask for your support:

  • I have a table that contains account transactions
  • I have a (growing) set of "rules", according to which I could assign a specific type to each transaction.
  • My main question is how to define these rules in airtable and how to check if they apply and according to the result to set the type for each transaction.

To give an example - see screenshot of such a transaction:

  • My rules typically must consists of a "contains" -matching within the column "TAC-Source" and a "contains" -matching within the column "TAC-Description".
  • In the case shown in the screenshot, the rule to apply would be "If TAC-Source contains "EG Schottenfeldgasse" and if TAC-Description contains "0704"" then set column Ruleset_MAtch_Type to Value "A"

Defining these rules would work within an automation. Unfortunately they are limited to 50 per base; and I will have more than 50 rules...

I also tried to make a separate rules_table, where each line is a rule and has 3 columns; 1) Stringpart to search for in "TAC-Source" 2) Stringpart to search for in "TAC-Description" 3) the corresponding type value.
Defining the rules would work; but I have no idea how to get them applied, i.e. how to get airtable to check the matching criteria then from the ruleset table to the TACS table.

Anyone with an idea to help? Thank you!!

br Chris

4 replies

Manuel_Tanco
Forum|alt.badge.img+8
  • Manuel_Tanco
  • Participating Frequently
  • July 10, 2023

Hi there!
I don't know if this is the most straight forward way to deal with your problem here, but from the info of the post and what I can be bold to assume, I'd suggest the following (without using Scripts)

Idea 1.

I'm not sure if you process the transaction information before it's inputted into your airtable base, however the TAG Description structure looks like it could be split/processed into different columns. By the looks of it what composes the Description is maybe "Item (prescription according to G Translate)" | "MM/YYYY" | Id | Other Items. If possible I would try and process the information previous to entering into Airtable.

Another aspect would be doing the alternative rule table you mentioned with the growing combination of Source & Description and given Ruleset_Match_Type. If you one require the TAC Source and the ID of the Description then it could look something like this. Whenever you would look to add rules, this could be done through a form or directly in the table

If you were able to split the data into separate fields, specially the id value (0704), in theory you would be able to do one simple automation to get the Ruleset_Match_Type from a separate table. The automation would consist on a 

  • Record created in Transaction table
  • Find Record - using the Source and Description ID values in rules table
  • Update Ruleset in Transaction table using the found value

Idea 2. (less tidy and scalable)

You could potentially reduce the amount Automations you need for this, without making any changes to the originally data. This would only work would if you have a small/medium amount of TAC Source & TAC Description possible combinations.

You could create views for either category. I'll use Description ID as an example. In each view, you could filter out the TAC Source contains "0704" or whatever Id you've designated that View to. 

 

Once you've done the x amount of View for all your Id's. You can head to Automations and create an Automation that would look something like this per Id. (Shown in pictures below)

This all has assumptions from my side, but hopefully it is somewhat useful. If you need any clarifications on my response I've got no problem following up! Best of luck

C
Forum|alt.badge.img+7
  • Christian_L
  • Author
  • Participating Frequently
  • July 10, 2023

Hi there!
I don't know if this is the most straight forward way to deal with your problem here, but from the info of the post and what I can be bold to assume, I'd suggest the following (without using Scripts)

Idea 1.

I'm not sure if you process the transaction information before it's inputted into your airtable base, however the TAG Description structure looks like it could be split/processed into different columns. By the looks of it what composes the Description is maybe "Item (prescription according to G Translate)" | "MM/YYYY" | Id | Other Items. If possible I would try and process the information previous to entering into Airtable.

Another aspect would be doing the alternative rule table you mentioned with the growing combination of Source & Description and given Ruleset_Match_Type. If you one require the TAC Source and the ID of the Description then it could look something like this. Whenever you would look to add rules, this could be done through a form or directly in the table

If you were able to split the data into separate fields, specially the id value (0704), in theory you would be able to do one simple automation to get the Ruleset_Match_Type from a separate table. The automation would consist on a 

  • Record created in Transaction table
  • Find Record - using the Source and Description ID values in rules table
  • Update Ruleset in Transaction table using the found value

Idea 2. (less tidy and scalable)

You could potentially reduce the amount Automations you need for this, without making any changes to the originally data. This would only work would if you have a small/medium amount of TAC Source & TAC Description possible combinations.

You could create views for either category. I'll use Description ID as an example. In each view, you could filter out the TAC Source contains "0704" or whatever Id you've designated that View to. 

 

Once you've done the x amount of View for all your Id's. You can head to Automations and create an Automation that would look something like this per Id. (Shown in pictures below)

This all has assumptions from my side, but hopefully it is somewhat useful. If you need any clarifications on my response I've got no problem following up! Best of luck


Hi @Manuel_Tanco Wow - thank you so much for this extensive answer and ideas! Especially the idea with the  view is something I absolutely did not have in mind. What I now did, based on your inputs:

  • I created views, where each of the views contains one filterset (rule) and only displays the results matching this rule (eg TAC-Source contains... AND TAC-Description contains ...) -> this works perfectly! & I am not limited with the views (1000 views can be created acc to airtable, so should be enough)
  • Then I started with the automations, find records in each view and setting the update record for this view. 

  • This means for each view I started with 1x find and 1x update.

  • This worked perfect for all views, that just contained one result line. But I have some views, where the filter/rule lead to multiple result lines.
  • The challenge is now how to update multiple lines, especially as I do not know before if a view will contain in future more than one entry.  This means, to make it right; I would have to prepare each find/update set to work for multiple result lines. (Remark for clarification: TACs are account transactions, that are regularly fed into airtable, so the amount grows over time).
  • As you can see in the screenshot; I then tried the new "repeat for each in...List of records" function. This would solve the problem, as it updates then all found entries. The Issue now is, that it seems, that I can only add 1 "repeat for each in..." section in one automation, but I would need it for every find/update set. Creating now for each ruleset/view one automation leads me to the original problem, of being limited to 50 automation, which is not enough...

Any further ideas/help on this? thank you! best regards, Chris

 

Manuel_Tanco
Forum|alt.badge.img+8
  • Manuel_Tanco
  • Participating Frequently
  • July 10, 2023

Hi @Manuel_Tanco Wow - thank you so much for this extensive answer and ideas! Especially the idea with the  view is something I absolutely did not have in mind. What I now did, based on your inputs:

  • I created views, where each of the views contains one filterset (rule) and only displays the results matching this rule (eg TAC-Source contains... AND TAC-Description contains ...) -> this works perfectly! & I am not limited with the views (1000 views can be created acc to airtable, so should be enough)
  • Then I started with the automations, find records in each view and setting the update record for this view. 

  • This means for each view I started with 1x find and 1x update.

  • This worked perfect for all views, that just contained one result line. But I have some views, where the filter/rule lead to multiple result lines.
  • The challenge is now how to update multiple lines, especially as I do not know before if a view will contain in future more than one entry.  This means, to make it right; I would have to prepare each find/update set to work for multiple result lines. (Remark for clarification: TACs are account transactions, that are regularly fed into airtable, so the amount grows over time).
  • As you can see in the screenshot; I then tried the new "repeat for each in...List of records" function. This would solve the problem, as it updates then all found entries. The Issue now is, that it seems, that I can only add 1 "repeat for each in..." section in one automation, but I would need it for every find/update set. Creating now for each ruleset/view one automation leads me to the original problem, of being limited to 50 automation, which is not enough...

Any further ideas/help on this? thank you! best regards, Chris

 


Always happy to provide possible input!
I've gone through what you mentioned and I don't see a direct scalable way of setting this up due to the trigger option selected, that is without using Scripts for which I can be of little help for this scenario😓. However, maybe the following questions might help

  • I'm guessing that the When a button is clicked trigger is meant for an interace a client can directly interact with - with that said
    • When the button is supposed to be clicked, does that generate a transaction in a single specific table? If so, why not opt for a When a record is created trigger that can be combinated with the Find Records node. That way each automation runs for a single record without the need of including a Repeat for each in...
C
C
Forum|alt.badge.img+7
  • Christian_L
  • Author
  • Participating Frequently
  • July 11, 2023

Always happy to provide possible input!
I've gone through what you mentioned and I don't see a direct scalable way of setting this up due to the trigger option selected, that is without using Scripts for which I can be of little help for this scenario😓. However, maybe the following questions might help

  • I'm guessing that the When a button is clicked trigger is meant for an interace a client can directly interact with - with that said
    • When the button is supposed to be clicked, does that generate a transaction in a single specific table? If so, why not opt for a When a record is created trigger that can be combinated with the Find Records node. That way each automation runs for a single record without the need of including a Repeat for each in...

Hi @Manuel_Tanco finally I used your views proposal and combined it with a script. Took a while, as my scripting knowledge is very basic, but it works now. Thanks again for your inputs! 

Manuel_Tanco
Terms & ConditionsAccessibility statement