topic Re: Regular Expression with Workato Lookup Table in Workato Pros Discussion Board

Regular Expression with Workato Lookup Table

hendrykurnia — Tue, 04 Jun 2024 23:38:16 GMT

Hi guys,

I have a use case where I use regular expression to validate the input of postal code.

E.g. Formula = "95000-2707".match?(/(^\d{5}$)|(^\d{5}-\d{4}$)/) for matching the US postal codes.

However, this method won't work if the regular expression is stored in a lookup able.

E.g. "regular_expression" lookup table

country	regex
United States	/(^\d{5}$)\|(^\d{5}-\d{4}$)/

Formula = "95000-2707".match?(lookup("regular_expression", "country": "United States")["regex"])

I have also tried the following formula but no luck.

- "95000-2707".match?(lookup("regular_expression", "country": "United States")["regex"].to_s)

- "95000-2707".match?(/lookup("regular_expression", "country": "United States")["regex"]/) and "95000-2707".match?(/lookup("regular_expression", "country": "United States")["regex"].to_s/) with the following lookup table

country	regex
United States	(^\d{5}$)\|(^\d{5}-\d{4}$)

Thank you

Re: Regular Expression with Workato Lookup Table

gary1 — Wed, 05 Jun 2024 02:25:47 GMT

You would be able to do this with the Ruby "send" method, something like this:

[postal_code].send("match", regex)

But alas, Workato doesn't support "send".

However, you can still do this with a JavaScript action.

Set it up like this:

And use this code:

exports.main = async ( {term, inputRegex}) => { var reg = new RegExp(inputRegex); result = reg.test(term); console.log(reg); return { result }; }

Hope this helps!

Re: Regular Expression with Workato Lookup Table

hendrykurnia — Wed, 05 Jun 2024 15:48:46 GMT

Thank you Gary.

I tried this out and worked. Quick question, I'm trying to map and update batch of 100+ records in this use case. With this method, is there a way I can map the batch record? Or do I need to loop each record to with the Javascript action?

Re: Regular Expression with Workato Lookup Table

gary1 — Wed, 05 Jun 2024 16:49:03 GMT

Glad this worked.

It's possible to do this in batches. Here's the updated code.

The postal_codes input is an array of postal codes and countries. You'll need to match this format from your source data.

The lookup_table input is the entire lookup table (use the "Get all entries from..." action)

And here's the updated code:

exports.main = async ( {postal_codes, lookup_table} ) => { console.log(postal_codes); const lut = lookup_table; const response = []; postal_codes.forEach(function (i) { console.log(i); let pc = i.postal_code; let c = i.country; let match = lut.filter(function(l) { return l.entry.col1 == c; }); let reg = new RegExp(match[0].entry.col2); let result = reg.test(pc); let resultObj = { postal_code: pc, country: c, regex: reg, result: result }; response.push(resultObj); }); console.log(response); return { response }; }

FYI, there's no error handling in this, so if the lookup fails, the whole thing might crash. JavaScript is not my native tongue 😛

Re: Regular Expression with Workato Lookup Table

hendrykurnia — Wed, 05 Jun 2024 17:23:05 GMT

Thanks Gary.

I would like to share what I have found. Actually, removing the '/' at the beginning and end of the regex in formula syntax and lookup table works.

country	regex
United States	(^\d{5}$)\|(^\d{5}-\d{4}$)

"95000-2707".match?(lookup("regular_expression", "country": "United States")["regex"])

Give it a shot

Re: Regular Expression with Workato Lookup Table

gary1 — Wed, 05 Jun 2024 17:26:30 GMT

Well, that's a whole lot easier! You know, when I was working on the script, I also had to remove the '/' at the beginning and end to get it to work, but I didn't think to double back and check if that would work with Ruby! Glad it's all working now.