Skip to main content
Solved

Integromat x Airtable - Filtering Records / Populating a Multi-Select Field with Many Results

  • July 12, 2020
  • 6 replies
  • 45 views
T

Hi Airtable fam,

I’ve tried everything at this point… After banging my head against the wall for a full two days, I decided to stop being a dummy and ask for help.

Full disclosure… I am a complete novice on Airtable, having just started to interact with it about a week ago.

In essence, what I’m attempting to do is:

  1. Retrieve all the records of one table (exercises)
  2. Filter by two metrics (match difficulty of the exercise with a “difficulty selector”, + or - 1 on a different table, AND make sure the required equipment for the exercise matches what the user has, again, on a different table.)
  3. Paste the RECORD IDs of the exercises that pass the filter into a field in another table (there are usually about 20-30)

I’ve been able to get pretty far down the road on my own using Integromat. The only step giving me grief is step 3…

Here’s what my Integromat scenario looks like:

  1. Airtable - Watch Records
  2. Airtable - Search Records
  3. Array Aggregator
  4. Iterator
  5. Filter (for difficulty and equipment)
  6. Array Aggregator
    … from there I have no idea how it should play out.
  • I’ve tried pasting the resultant array from the second aggregator directly into the field in Airtable and, because it’s an array of collections, it’s trying to paste “[Collection]” into the field.
  • I’ve tried iterating back through all the exercises and adding them one-by-one to the table, but Integromat simply updates the value 20-30 times and leaves me with the last exercise in the target field.
  • I noticed that if I, as an Airtable user, just paste a series of record IDs separated by commas into the field, it works, so I tried iterating through, converting the record IDs to text, and appending a ", " but Integromat will either add all of them as one new record, for example “rec…, rec…, rec…, rec…” if I have “Smart Links” enabled. If I have them disabled, it throws an error that it’s unable to create a new option in the multi-select field.

I’ve seen some questions on here that are similar, but deal with JSON. I haven’t converted anything to JSON in Integromat… do I need to?? I tried many times to duplicate the JSON workflows that are listed here to no avail.

I’m at a loss. I’m probably over-complicating this, but I’d appreciate an assist if anyone has experience trying to automate a workflow with Integromat. And, as someone who’s not a programmer and a newbie to Airtable, please explain like I’m five. :laughing:

Thanks.

  • Travis

Best answer by Mohamed_Swella1

Hi @Mohamed_Swellam,

Thanks again for the help.

I followed your steps until I got to the part with using the formula… I couldn’t figure out if you meant to create a formula field in Airtable or to apply that formula with an Integromat operation, so I tried both.

If I try to apply that formula to a new column in Airtable, I get “NaN” returned in all the cells.
If I try to apply that formula in Integromat, it doesn’t work. I presume because of formatting.

I tried probably 20 other ways to force this to work. Every time I came back to the same problem… how to update a field in Airtable with multiple options using Integromat.

I’d even settle for something less elegant if it worked. I tried placing the {Cardio Selector} value in the cell, which works fine. The problem is adding {Cardio Selector} -1 and {Cardio Selector} +1 to that after without erasing the first value. I tried the add function suggested here and Integromat refuses to execute the code… Add Value in Multiselect Fields

And we haven’t yet addressed the equipment matching part of the problem.

Any suggestions?

Thanks again for your patience…

  • Travis

Hi @Travis_Schirner,

Add brackets and this should solve it

({Cardio Selector}+1)&", "&{Cardio Selector}&", "&({Cardio Selector}-1)

In this part, are you trying to match whether or not the client has the required equipment and then get the relevant exercise for example?

I think your best bet would be script in this case.

BR,
Mo

6 replies

Mohamed_Swella1
Forum|alt.badge.img+17

Hi @Travis_Schirner,

Welcome to Airtable Community! :slightly_smiling_face:

I think there are at least 2 steps you should do in Airtable first to simplify things.

First, why are you using integromat to match the difficulty and search for the equipment in another table? This should be done with Rollups or Lookups in Airtable in a view for the records you want to send .

As for the pasting part, it would replace the data in the field because you did not choose the existing data to be also pasted. What you should do is add the existing data as well in the field then separate it with a comma for the new data.

If you can add screenshots of the base I might be able to help you more.

BR,
Mo

T
  • Travis_Schirner
  • Author
  • New Participant
  • 3 replies
  • July 12, 2020

Hi @Travis_Schirner,

Welcome to Airtable Community! :slightly_smiling_face:

I think there are at least 2 steps you should do in Airtable first to simplify things.

First, why are you using integromat to match the difficulty and search for the equipment in another table? This should be done with Rollups or Lookups in Airtable in a view for the records you want to send .

As for the pasting part, it would replace the data in the field because you did not choose the existing data to be also pasted. What you should do is add the existing data as well in the field then separate it with a comma for the new data.

If you can add screenshots of the base I might be able to help you more.

BR,
Mo


Hey Mo,

Thanks so much for your willingness to help. I’m not sure if rollups or lookups have the right functionality because I’m trying to cross reference records in different tables. Again, being a complete novice at this, I have no idea… but I did attempt it and couldn’t figure out how to do what I want with them.

Here’s a screenshot of the table I’m trying to add the values of the exercises to (in the “Cardio Options” field):

Here’s a screenshot of the table that has the records I’m trying to filter:

The filter I want to apply has to do a few things:

  • Cross reference exercise difficulty (star rating) in the “Cardio Database” table with a numeric integer (1-10) “Cardio Selector” value in the “User Plans” table.
  • Cross reference equipment in the “Cardio Database” table with the equipment of the user (which comes from a linked field of a previous table, but is also listed on the “User Plans” table).

When I’ve tried to configure filters on Rollup or Lookup fields, they’re quite limited. I have to set the values of the star ratings or specify exact text for a “contains” filter. I’m trying to fully automate the workflow, which is why I turned to Integromat.

Thank you!

Mohamed_Swella1
Forum|alt.badge.img+17

Hey Mo,

Thanks so much for your willingness to help. I’m not sure if rollups or lookups have the right functionality because I’m trying to cross reference records in different tables. Again, being a complete novice at this, I have no idea… but I did attempt it and couldn’t figure out how to do what I want with them.

Here’s a screenshot of the table I’m trying to add the values of the exercises to (in the “Cardio Options” field):

Here’s a screenshot of the table that has the records I’m trying to filter:

The filter I want to apply has to do a few things:

  • Cross reference exercise difficulty (star rating) in the “Cardio Database” table with a numeric integer (1-10) “Cardio Selector” value in the “User Plans” table.
  • Cross reference equipment in the “Cardio Database” table with the equipment of the user (which comes from a linked field of a previous table, but is also listed on the “User Plans” table).

When I’ve tried to configure filters on Rollup or Lookup fields, they’re quite limited. I have to set the values of the star ratings or specify exact text for a “contains” filter. I’m trying to fully automate the workflow, which is why I turned to Integromat.

Thank you!


Hey @Travis_Schirner,

You’re welcome, its my pleasure.

I do have a few ideas that might help you. Here is one:

First, make a formula in the Cardio Exercise Database that is equal to Cardio Difficulty to change the stars to a number. Then, in this new field, make a Link To Another Record, Create New Table, Cardio Difficulty. This will group all the Cardio difficulty and link it to one record with the difficulty.

In this new table, make a Rollup Field that is ArrayJoin of the Exercise Name.

If I understand correctly, you want the options in the User Plans table to include the cardio difficulty +/- 1? You can create a formula there that gives the number in the cardio selector +/- 1 separated by a comma. So it would be
{Cardio Selector}-1&","&{Cardio Selector}&","&{Cardio Selector}+1

Using Integromat (or Zapier, which ever works better for you), You will copy paste this new formula in the User Plan table to a linked field that is linked to the Cardio Difficulty table you did in step 1. Then using Lookup field you would lookup the relevant exercises.

Try this and let me know what you think.

BR,
Mo

T
  • Travis_Schirner
  • Author
  • New Participant
  • 3 replies
  • July 12, 2020

Hey @Travis_Schirner,

You’re welcome, its my pleasure.

I do have a few ideas that might help you. Here is one:

First, make a formula in the Cardio Exercise Database that is equal to Cardio Difficulty to change the stars to a number. Then, in this new field, make a Link To Another Record, Create New Table, Cardio Difficulty. This will group all the Cardio difficulty and link it to one record with the difficulty.

In this new table, make a Rollup Field that is ArrayJoin of the Exercise Name.

If I understand correctly, you want the options in the User Plans table to include the cardio difficulty +/- 1? You can create a formula there that gives the number in the cardio selector +/- 1 separated by a comma. So it would be
{Cardio Selector}-1&","&{Cardio Selector}&","&{Cardio Selector}+1

Using Integromat (or Zapier, which ever works better for you), You will copy paste this new formula in the User Plan table to a linked field that is linked to the Cardio Difficulty table you did in step 1. Then using Lookup field you would lookup the relevant exercises.

Try this and let me know what you think.

BR,
Mo


Hi @Mohamed_Swellam,

Thanks again for the help.

I followed your steps until I got to the part with using the formula… I couldn’t figure out if you meant to create a formula field in Airtable or to apply that formula with an Integromat operation, so I tried both.

If I try to apply that formula to a new column in Airtable, I get “NaN” returned in all the cells.
If I try to apply that formula in Integromat, it doesn’t work. I presume because of formatting.

I tried probably 20 other ways to force this to work. Every time I came back to the same problem… how to update a field in Airtable with multiple options using Integromat.

I’d even settle for something less elegant if it worked. I tried placing the {Cardio Selector} value in the cell, which works fine. The problem is adding {Cardio Selector} -1 and {Cardio Selector} +1 to that after without erasing the first value. I tried the add function suggested here and Integromat refuses to execute the code… Add Value in Multiselect Fields

And we haven’t yet addressed the equipment matching part of the problem.

Any suggestions?

Thanks again for your patience…

  • Travis
Mohamed_Swella1
Forum|alt.badge.img+17
  • Mohamed_Swella1
  • Inspiring
  • 1030 replies
  • Answer
  • July 13, 2020

Hi @Mohamed_Swellam,

Thanks again for the help.

I followed your steps until I got to the part with using the formula… I couldn’t figure out if you meant to create a formula field in Airtable or to apply that formula with an Integromat operation, so I tried both.

If I try to apply that formula to a new column in Airtable, I get “NaN” returned in all the cells.
If I try to apply that formula in Integromat, it doesn’t work. I presume because of formatting.

I tried probably 20 other ways to force this to work. Every time I came back to the same problem… how to update a field in Airtable with multiple options using Integromat.

I’d even settle for something less elegant if it worked. I tried placing the {Cardio Selector} value in the cell, which works fine. The problem is adding {Cardio Selector} -1 and {Cardio Selector} +1 to that after without erasing the first value. I tried the add function suggested here and Integromat refuses to execute the code… Add Value in Multiselect Fields

And we haven’t yet addressed the equipment matching part of the problem.

Any suggestions?

Thanks again for your patience…

  • Travis

Hi @Travis_Schirner,

Add brackets and this should solve it

({Cardio Selector}+1)&", "&{Cardio Selector}&", "&({Cardio Selector}-1)

In this part, are you trying to match whether or not the client has the required equipment and then get the relevant exercise for example?

I think your best bet would be script in this case.

BR,
Mo

T
  • Travis_Schirner
  • Author
  • New Participant
  • 3 replies
  • July 16, 2020

Hi @Travis_Schirner,

Add brackets and this should solve it

({Cardio Selector}+1)&", "&{Cardio Selector}&", "&({Cardio Selector}-1)

In this part, are you trying to match whether or not the client has the required equipment and then get the relevant exercise for example?

I think your best bet would be script in this case.

BR,
Mo


Thanks, Mo… this was a key part of the solution for me, and the other was switching to Zapier. Not sure why everything with Integromat is so complex.

Thank you for the help!


Terms & ConditionsAccessibility statement