Skip to main content
  • Home
  • Get Help
  • How Do I
  • How to loop from regexp extracted output list (to create individual new records in Airtable)?
Question

How to loop from regexp extracted output list (to create individual new records in Airtable)?

  • 12 July 2024
  • 8 replies
  • 35 views

Hi,

I’m attempting to automate accounts payables reconciliation based on mail statements. The statements can contain n instances of a line (in Finnish) “NN has paid the invoice”.

I get the raw text portion of the mail out, and get text strings extracted with Formatter’s Extract Pattern (regexp). The result is a “list” with Output 0...n as shown in the next image.

 

 

In the next step I attempt to use Loop these to then create a new record for each Output 0...n in Airtable. I have tried both Create Loop from Text and .. From Line Items, but I can’t get either to make sense.

Is there something else I need to do to the outputs I have, before looping them?

 

Rgds,

 

Björn

 

 

 

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

8 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @BjörnE 

For us to have more context, post screenshots with how your Zap steps are outlined and configured in EDIT mode.

The data returned from the Formatter step is structured as an array, so use this Zap action: Looping - Create Loop from Line Items

Map the data into the Looping step, then from the Looping step to the Airtable step.

Help info for Looping: https://zapier.com/apps/looping/help

 

If you are trying to create 10 or less Airtable records, then use this Zap action: Airtable - Create Records (with Line Item Support)

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
B

Hi @Troy Tessalone,

 

Thank you for your answer. Here are the screenshots.

 

  1. exctracting the pattern

 

  1. getting output list of n results

 

  1. choosing Create Loop from Line Items

 

***********************************

  1. not understanding which data to use as input for the loop

 

That is as far as I gotten so far. The choices visible under “Insert Data” don’t seem logical. Tried “ID” but there’s no data. Tried “Output 0” which resulted in 1 output set but not processing “Output 2”.

 

I would have expected to here be able to select an array/line item object which would hold all the strings.

Still thinking I’m missing a step here?

 

Rgds,

 

Björn

 

 

Troy Tessalone
Userlevel 7
Badge +14

@BjörnE 

Issue is that the Formatter step is outputting variables within each array that have different labels.

 

A different approach would be to use a Code step: https://zapier.com/apps/code/help

const text = inputData.text;
const pattern = /(\w+ \w+ on maksanut laskun \d+)/g;
const matches = text.match(pattern);

output = {matches: matches};

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
B

Thank you @Troy Tessalone. 

 

Stepping out on even more unfamiliar territory here with coding steps :). Would have a preferred a way to deal with just regex, but if that’s not possible, this is the solution. 

 

Your code worked like a charm otherwise, but my input data is more complex than this. In the meantime I had with the help of a friend solved my other question you replied to, the one regarding \d+ in the regex (but had not gotten back to replying to you there).

...

uudet kulukorvaukset

A A on maksanut laskun

490,00 €

/ 490,00 €

B B on maksanut laskun

490,00 €

/ 490,00 €

...

 

The input data has CR/LF as viewed in Notepad++, which were somehow resolved when copy/pasted in regex101, but then were showstoppers in reality.

 

Hence we arrived at this as the regex solution, in Python: 

(?sm)\w+ \w+ on maksanut laskun..\d+,\d+

 

This of course didn’t work in a Javascript setting, the (?sm) was not allowed :). Tried to tinkle with it in regex101 but couldn’t find the right syntax. Here is what I came up with in your code setting:

const pattern = /(\w+ \w+ on maksanut laskun..\d+,\d+)/gs;

 

This however only fetches one instance, and not the first.

 

Would you be able to still help, and see how this Python version would be translated into a correct Javascript version?

Rgds,

 

Björn
 

Troy Tessalone
Userlevel 7
Badge +14

@BjörnE 

When I tested the updated regex it returned multiple matches.

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
B

@Troy Tessalone, thank you again.

That was a surprise!

It seems there’s something in the content of the variable “Body Plain” that responds differently to the regex, than to plain text in the input data field. I just copied the text and anonymised it before pasting it here, but it was not from within Zapier, I think it was from the actual mail via Notepad++.

Placing a Formatter step before the Javascript regex to convert the text to ascii removes whatever characters in the content that makes the regex not work correctly. Problem is, my languages are Finnish and Swedish, so would need UTF-8 for all the å, ä, and ö. With the ascii conversion I get names that will not match anything further down the line.

Since I didn’t find any conversion to UTF-8, I tried a Formatter step to replace those CR/LF characters Notepad++ shows. Replacing [:newline:] with [:space:] gives a more compact looking content, but it still produces only one match, and not the first one. So same result as without any replace action.

Does this make any sense to you? How to find the “offending” characters? Or how to convert into UTF-8?

Sorry this has gotten so complicated and longwinded…

Cheers,

Björn

Troy Tessalone
Userlevel 7
Badge +14

@BjörnE 

One option may be to use AI to translate the language to english to then use that data in the Code step.

Options

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
B

@Troy Tessalone 

 

Thank you again. At this point, however, I feel it would be the wrong way to translating. I can already achieve a ASCI version match with ASCII replace, and I don’t see any benefit from translating over that. 

I’ll look at how to get the ASCII text working in Airtable. If that works, it could be just fine.

At this point, many thanks to you @Troy Tessalone!

Rgds,

Björn

Terms & Conditions