Skip to main content

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!

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.)

Thank you @kuovonne ! It works very perfectly.

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.)


@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!

 

@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.

Reply


Terms & Conditions