Hi,
Hopefully some Retool Javascript experts can help me with the following as ChatGPT was not able to provide the correct solution.
-
Goal: The goal is to supplement API data with SQL database data.
-
Steps: I have a Javascript query that retrieves data from an API. For each output row I want to look up a corresponding value (OH_Code) in my SQL database. The JS query contains a reference to three SQL queries. Based on a fallback logic (Invoice number query, Bank Account query, Name query) the OH_Code should be looked up. Unfortunately the OH_Code is not found, while there should be matching OH_Code. I suspect that the problem lies in the processing of the parameters in the sql queries WHERE clause.
-
Details: The Javascript (called Total) I currently have:
async function fetchAll() {
let nextPageKey = undefined;
let allRecords = [];
while (true) {
const response = await getTransactions.trigger({ additionalScope: { nextPageKey } });
if (Array.isArray(response.transactions)) {
allRecords = allRecords.concat(response.transactions);
} else {
console.warn("Unexpected response format:", response);
break;
}
if (!response.nextPageKey) break;
nextPageKey = response.nextPageKey;
}
const invoiceNumbersSet = new Set();
const accountNumbersSet = new Set();
const partyNamesSet = new Set();
const transactionsWithExtras = allRecords.map(tx => {
const kenmerkLine = tx.descriptionLines.find(line => line.includes("Kenmerk:"));
const kenmerk = kenmerkLine ? kenmerkLine.split("Kenmerk:")[1].trim() : "";
const omschrijvingLine = tx.descriptionLines.find(line => line.includes("Omschrijving:"));
const omschrijvingText = omschrijvingLine ? omschrijvingLine.split("Omschrijving:")[1].trim() : "";
const regeloverloopLines = tx.descriptionLines.filter(line => line.includes("regeloverloop"));
const regeloverloopText = regeloverloopLines.length ? regeloverloopLines.join(' ') : "";
const omschrijving = omschrijvingText + (regeloverloopText ? " " + regeloverloopText : "");
const invoiceMatches = omschrijving.match(/\b[MDU]0\d{6}\b/g) || [];
invoiceMatches.forEach(inv => invoiceNumbersSet.add(inv.trim().toLowerCase()));
if (tx.counterPartyAccountNumber) accountNumbersSet.add(tx.counterPartyAccountNumber.trim().toLowerCase());
if (tx.counterPartyName) partyNamesSet.add(tx.counterPartyName.trim().toLowerCase());
return {
...tx,
kenmerk,
omschrijving,
invoiceNumbers: invoiceMatches.join(','),
typeDescription: tx.descriptionLines[0]?.trim() || ""
};
});
const invoiceList = Array.from(invoiceNumbersSet).map(i => i.trim().toLowerCase()).join(',');
const accountList = Array.from(accountNumbersSet).map(i => i.trim().toLowerCase()).join(',');
const partyList = Array.from(partyNamesSet).map(i => i.trim().toLowerCase()).join(',');
console.log({ invoiceList, accountList, partyList });
const [invoiceSQLResult, accountSQLResult, nameSQLResult] = await Promise.all([
invoiceList ? sqlQueryInvoiceNumbers.trigger({ additionalScope: { invoiceNumbers: invoiceList } }) : { data: [] },
accountList ? sqlQueryAccountNumbers.trigger({ additionalScope: { accountNumbers: accountList } }) : { data: [] },
partyList ? sqlQueryPartyNames.trigger({ additionalScope: { partyNames: partyList } }) : { data: [] },
]);
console.log({ invoiceSQLResult, accountSQLResult, nameSQLResult });
const invoiceLookup = {};
invoiceSQLResult?.data?.forEach(row => {
if (row?.AH_TransactionNum) {
invoiceLookup[row.AH_TransactionNum.trim().toLowerCase()] = row.OH_Code;
}
});
const accountLookup = {};
accountSQLResult?.data?.forEach(row => {
if (row?.A1_BankAccount) {
accountLookup[row.A1_BankAccount.trim().toLowerCase()] = row.OH_Code;
}
});
const nameLookup = {};
nameSQLResult?.data?.forEach(row => {
if (row?.OH_Fullname) {
nameLookup[row.OH_Fullname.trim().toLowerCase()] = row.OH_Code;
}
});
const enrichedRecords = transactionsWithExtras.map(tx => {
const invoiceMatches = tx.invoiceNumbers.split(',').filter(Boolean);
const OH_Code = invoiceMatches.map(inv => invoiceLookup[inv.trim().toLowerCase()]).find(Boolean)
|| accountLookup[tx.counterPartyAccountNumber?.trim().toLowerCase()]
|| nameLookup[tx.counterPartyName?.trim().toLowerCase()]
|| null;
return {
...tx,
OH_Code
};
});
return enrichedRecords;
}
return await fetchAll();
The SQL queries:
sqlQueryInvoiceNumbers
SELECT
AH_TransactionNum,
OH_Code
FROM
AccTransactionHeader
JOIN OrgHeader ON OH_PK = AccTransactionHeader.AH_OH
JOIN GlbCompany ON GC_PK = AH_GC
WHERE
AccTransactionHeader.AH_Ledger = 'AR'
AND AH_TransactionType IN ('CRD','INV')
AND GC_Code = 'RID'
AND AH_OutstandingAmount <> 0
AND LOWER(LTRIM(RTRIM(AH_TransactionNum))) IN (
SELECT LTRIM(RTRIM(LOWER(value))) FROM STRING_SPLIT({{ invoiceNumbers }}, ',')
)
sqlQueryAccountNumbers
SELECT
A1_BankAccount,
OH_Code
FROM
OrgHeader
LEFT JOIN OrgCompanyData ON OB_OH = OH_PK
LEFT JOIN AccAPAccountDetails ON A1_OB = OB_PK
LEFT JOIN GlbCompany ON GC_PK = OB_GC
WHERE
GC_Code = 'RID'
--AND A1_IBANNumber <> ''
AND LOWER(LTRIM(RTRIM(A1_BankAccount))) IN (
SELECT LTRIM(RTRIM(LOWER(value))) FROM STRING_SPLIT({{ accountNumbers }}, ',')
)
sqlQueryPartyNames
SELECT
OH_Fullname,
OH_Code
FROM
(
SELECT
OH_Code,
OH_Fullname,
SUM(AH_OutstandingAmount)OutstandingAmount
FROM
AccTransactionHeader
JOIN OrgHeader ON OH_PK = AccTransactionHeader.AH_OH
JOIN GlbCompany ON GC_PK = AH_GC
WHERE
AccTransactionHeader.AH_Ledger = 'AR'
AND AH_TransactionType IN ('CRD','INV')
AND GC_Code = 'RID'
GROUP BY
OH_Code, OH_Fullname
)MT
WHERE
MT.OutstandingAmount <> 0
AND EXISTS (
SELECT 1
FROM STRING_SPLIT({{ partyNames }}, ',') s
WHERE LTRIM(RTRIM(LOWER(MT.OH_Fullname))) = LTRIM(RTRIM(LOWER(s.value)))
)
- Screenshots:
(the data in the screenshots is fictitious data)
The consol log showing the parameter data is collected correctly.
(No invoices are found, that is correct in this case)
But no output from the sql queries.
I expect a match via the sqlQueryPartyNames query.
I have tried to find the answer via ChatGPT but after countless adjustments I still dont get this to work. I hope someone can help point me in the right direction.
Thanks.
