Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Find text string in Long text field

Topic Labels: Formulas
Solved
Jump to Solution
398 4
cancel
Showing results for 
Search instead for 
Did you mean: 
PeterJNCK
6 - Interface Innovator
6 - Interface Innovator

Hello,
I have a table that is synced with transactions from my bank account, but I need help with extracting data from a JSON string stored in the Notes (Long text) field.
An example of the JSON looks like this:

 

{
"transactionId": "2022142460021",
"endToEndId": "\/VS2501000545\/SS\/KS308",
"bookingDate": "2025-01-10",
"valueDate": "2025-01-10",
"transactionAmount": {
"amount": "-390.00",
"currency": "EUR"
},
"currencyExchange": [
{
"exchangeRate": "1.00000000",
"quotationDate": "2025-01-10"
}
],
"creditorAccount": {
"iban": "SK9111110000002303283006"
},
"debtorName": "COMPANY s.r.o.",
"debtorAccount": {
"iban": "SK8511000000002622224000",
"currency": "EUR"
},
"remittanceInformationUnstructured": "Platba faktury 2501000995",
"remittanceInformationStructured": "[{\"reference\": \"2501000995\", \"referenceType\": \"VariableSymbol\"}, {\"reference\": \"308\", \"referenceType\": \"ConstantSymbol\"}]",
"additionalInformation": "Platba 1111\/000000-2303283006",
"bankTransactionCode": "",
"creditorAgent": "UNCRSKBXXXX",
"debtorAgent": "TATRSKBXXXX",
"internalTransactionId": "",
"ext_nordigen_acc_id": "",
"pending": false
}

 

To identify payments in my Partner table using automation, I need to write formulas for two fields:

1.IBAN (but exclude mine, as for income, I am the creditor, and for outcome, I am the debtor).
2.Variable symbol.

I was using ChatGPT to help with the formulas:

For IBAN:

 

IF(
  AND(
    FIND("SK", {Notes}),  // Ensure "SK" exists in the Notes field
    NOT(FIND("SK8511000000002622224000", {Notes})) // Exclude your IBAN
  ),
  REGEX_EXTRACT({Notes}, "(SK\\d{2}\\d{4}\\d{4}\\d{4}\\d{4}\\d{4})"),  // Extract the first IBAN
  BLANK()  // Return blank if no IBAN is found or if your IBAN is found
)

 

For Variable symbol:

 

REGEX_EXTRACT({Notes}, "\"reference\": \"(\\d+)\".*\"referenceType\": \"VariableSymbol\"")

 

But neither of these formulas is working. 😐
Thank you!

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

ChatGPT isn't very good at writing Airtable formulas. I'm not surprised that the formulas it produces don't work. The first formula that you posted isn't even a legal Airtable formula.

I would go about this with a series of formula fields.

First a formula to extract the creditorAccount to a {creditorAccount} field

IF(
  REGEX_MATCH({Notes}, '"creditorAccount": {[^}]+}'),
  REGEX_EXTRACT({Notes}, '"creditorAccount": {[^}]+}')
)

Then a formula to extract the IBAN from the creditorAccount info

IF(
  REGEX_MATCH({creditorAccount}, '"iban": "\\w+"'),
  REGEX_REPLACE(
    REGEX_EXTRACT({creditorAccount}, '"iban": "\\w+"'),
    '("iban": ")(\\w+)(")', 
    '$2'
  )
)

To get the info for the debtorAccount, use the same formulas, but use "debtorAccount" instead of "creditorAccount".


Here are some other possible methods to get the data

- Use an AI Field to extract the data you want.

- Write a script that parses the JSON. (But if you use ChatGPT to write the script, you might have issues with the code not working either.)

See Solution in Thread

4 Replies 4
kuovonne
18 - Pluto
18 - Pluto

ChatGPT isn't very good at writing Airtable formulas. I'm not surprised that the formulas it produces don't work. The first formula that you posted isn't even a legal Airtable formula.

I would go about this with a series of formula fields.

First a formula to extract the creditorAccount to a {creditorAccount} field

IF(
  REGEX_MATCH({Notes}, '"creditorAccount": {[^}]+}'),
  REGEX_EXTRACT({Notes}, '"creditorAccount": {[^}]+}')
)

Then a formula to extract the IBAN from the creditorAccount info

IF(
  REGEX_MATCH({creditorAccount}, '"iban": "\\w+"'),
  REGEX_REPLACE(
    REGEX_EXTRACT({creditorAccount}, '"iban": "\\w+"'),
    '("iban": ")(\\w+)(")', 
    '$2'
  )
)

To get the info for the debtorAccount, use the same formulas, but use "debtorAccount" instead of "creditorAccount".


Here are some other possible methods to get the data

- Use an AI Field to extract the data you want.

- Write a script that parses the JSON. (But if you use ChatGPT to write the script, you might have issues with the code not working either.)

PeterJNCK
6 - Interface Innovator
6 - Interface Innovator

Thank you @kuovonne ! It works very perfectly.

@kuovonne could you please be so kind and help me with the Variable Symbol as well?
This should probably require another regex prompt. 😬

"remittanceInformationStructured": "[{\"reference\": \"20240001\", \"referenceType\": \"VariableSymbol\"}, {\"reference\": \"308\", \"referenceType\": \"ConstantSymbol\"}]",

It should return 2024001.
Thank you!

 

You can get the variable symbol using a variation of the technique I showed in my previous formula. Use a REGEX functions to narrow down the portion of text you want, using multiple fields as appropriate to make things easier to understand. The REGEX functions are documented in the formula field reference, which links to the syntax needed to create a regular expression. You can also use a REGEX tester when building out your expressions.