Hi i have 2 different queries that pull data from 2 different resources:
GetAllClients_EU:
SELECT uri_identity
, first_name
, last_name
FROM mem_client_card
WHERE business_id = {{GetAccDetails_EU.data.business_id[0]}};
and GetAllForms_EU:
select id, client_id, business_id
from consultation_form
where business_id = {{GetAccDetails_EU.data.Console_business_id[0]}}
how do I pull them together to construct what I need which is to generate a URL of data included in the GetAllForms_EU query and pull the client name associated with the form from GetAllClients_EU query?
I was trying something like a js script but keep getting errors of no data when I run it - even though if I run the 2 queries on their own data is there.
Is there perhaps a simpler way to join data from 2 tables from 2 different resources so I can do everything I need from within a simple SQL query?
My js script so far is:
async function fetchData() {
try {
// Execute both queries
const [clientsResponse, formsResponse] = await Promise.all([
GetAllClients_EU.trigger(),
GetAllForms_EU.trigger()
]);
// Check if both queries returned data
if (!clientsResponse || !formsResponse) {
console.error('One or both queries did not return data');
return [];
}
// Extract data from the query results
const clientsData = clientsResponse;
const formsData = formsResponse;
// Initialize an array to store the constructed URLs
const urlData = [];
// Iterate over each form record to construct URLs
formsData.forEach(form => {
// Find the corresponding client information
const client = clientsData.find(client => client.uri_identity === form.client_id);
if (client) {
// Extract required data for URL construction
const business_id = form.business_id;
const client_id = form.client_id;
const form_id = form.id;
const client_name = `${client.first_name}_${client.last_name}`;
// Construct the URL using the extracted data
const constructedURL = `https://app.company.com/businesses/${business_id}/clients/${client_id}/consultations/forms/${form_id},${client_name}`;
// Add the constructed URL to the array
urlData.push({ URL: constructedURL });
}
});
// Return the array containing the constructed URLs
return urlData;
} catch (error) {
console.error('An error occurred:', error);
return [];
}
}
// Execute the fetchData function
const urlData = await fetchData();
What I basically need to achieve - if I was doing it outside of retool is simply a query like this that uses a simple join:
SELECT ( 'https://app.company.com/businesses/'
|| forms.business_id
|| '/clients/'
|| forms.client_id
|| '/consultations/forms/'
|| forms.id ) AS formUrl
, ( mcc.first_name
|| '_'
|| mcc.last_name ) AS ClientName
, forms.id
FROM forms_eu.consultation_form AS forms
JOIN memento_master_eu.mem_client_card AS mcc
ON forms.client_id = mcc.uri_identity
WHERE forms.business_id = 'blablablablablabla'
AND forms.status = 'COMPLETED'
ORDER BY clientname
