Help with email loop workflow

Workflows
, , ,
1

Hi there, I'm quite the noob here, so please bare with me!

Objective: To create a workflow in Retool that runs daily, fetches infraction data from a retool database, generates CSV files for each unique partner_id without including email addresses, and sends these CSV files to the respective email addresses associated with each partner_id.

Steps Involved:

  1. Data Fetching:
  • Query Name: fetchData
  • SQL Query:
SELECT infractions.*, emails_med.email
FROM infractions
LEFT JOIN emails_med ON infractions.partner_id = emails_med.partner_id
WHERE status IS NULL;
  • This query retrieves all infractions and their associated emails where the status is null.

2.Data Processing:

  • JavaScript Query Name: processData
  • Functionality:
    • Group the fetched data by partner_id.
    • Create a separate CSV file for each partner_id, excluding the email field.
    • Convert the grouped data into CSV format using PapaParse.
  • Output: A map of partner_id to corresponding CSV data.
const results = fetchData.data; // Your SQL query result
const groupedData = results.reduce((acc, row) => {
  if (!acc[row.partner_id]) {
    acc[row.partner_id] = [];
  }
  acc[row.partner_id].push(row);
  return acc;
}, {});

let csvDataMap = {};

Object.keys(groupedData).forEach(partner_id => {
  const csvData = groupedData[partner_id].map(row => {
    const { email, ...rest } = row; // Exclude the email field
    return rest;
  });

  const csv = Papa.unparse(csvData);
  csvDataMap[partner_id] = csv; // Store CSV in the map
});

return csvDataMap; // Returning the CSV data map

After this part, how do I create a loop that will take all the emails (it can be more than 1) from each partner_id and send the corresponding csv data to then? Also how do I choose the email that Im going to send this through?

1 Like
2

Hi @Stefany_Monteiro,

You're close! I think what you need to do is create a code block that will generate an array of objects with the info needed for each email. What you want to end up with should look like the following:

[
  {
    to: 'name@domain.com,name2@domain.com',
    attachment: [
      {
        data: {{ dataFromPapaParse }}, 
        name: 'filename.csv', 
        contentType: 'text/csv'
     }
   ]
  },
  {
    to: 'name@domain.com',
    attachment: [
     {
       data: {{ dataFromPapaParse }}, 
       name: 'filename2.csv', 
       contentType: 'text/csv'
     }
   ]
  }
]

Attachment is an array just to make it a bit easier to use in the email component.

Looking at your code, you should be able to tweak it a bit like this (bottom half)

let csvDataMap = []; // An array now

Object.keys(groupedData).forEach(partner_id => {
  let partnerEmail = null
  const csvData = groupedData[partner_id].map(row => {
    const { email, ...rest } = row; // Exclude the email field
    
    //You might need to handle joining multiple emails into a string here depdending on your data
    partnerEmail = email 
    return rest;
  });

  const csv = Papa.unparse(csvData);

  const assembledData = {
    to: partnerEmail,
    attachment: [
      {
         data: csv, 
         name: `${partner_id}.csv`, 
         contentType: 'text/csv'
      }
    ]
  csvDataMap.push(assembledData)
});
return csvDataMap

Then you can add a Retool Loop component, make it an email resource, and set it up like this:

image
image809×584 23 KB

Be sure to click that part circled in green so you can use code for the attachment.

1 Like