Build a database query based on multiple exported values

The first step is to create a function that loops through the data, or “payload,” and builds the two lists: listOfConcessionNumbers and listOfBranchNumbers. Once those lists are built, they are placed back into the payload, to be referenced later when querying the database.

function blockQuery (options) { 
  var listOfConcessionNumbers = "" 
  var listOfBranchNumbers = "" 

  options.data[0].FileDetailRecord.forEach(function(item){
  if(listOfConcessionNumbers.length !=0){ listOfConcessionNumbers +="," }
  listOfConcessionNumbers = listOfConcessionNumbers +"'"+ item.ConcessionNumber +"'"

  if(listOfBranchNumbers.length !=0){ listOfBranchNumbers +=","}
  listOfBranchNumbers = listOfBranchNumbers +"'"+ item.BranchNumber +"'"})

  options.data[0].listOfConcessionNumbers = listOfConcessionNumbers 
  options.data[0].listOfBranchNumbers = listOfBranchNumbers

  return{ data: options.data, errors: options.errors, abort:false} 
}

The next step is to create the SQL query, keeping in mind that the lists (as comma-separated string variables) will be readily available at this point to be used during the lookup step:

SELECT LOCATION_ID 
  FROM LOCATIONS
  WHERE CONCESSION_NUMBER in ({{{data.listOfConcessionNumbers}}}) 
    AND RIGHT (LOCATION_ID,4) in ({{{data.listOfBranchNumbers}}}) 
    AND LEFT (LOCATION_ID,2) = STORE_PREFIX;

The important aspect to note in this query is the correct handlebars syntax to reference the lists built in the JavaScript function, in particular the usage of {{{three curly braces}}}.

Finally, we have to trigger the execution of the JavaScript function within our flow. Remember that the lists have to be available in the payload, prior to executing the lookup. This means we have to execute the list at an optimal place, where we have all the data we need and, of course, prior to the database query.

In our case, this is at the step just before the lookup step (the FTP transfer, above). Here we select the hook ( ) to call blockQuery() and process the records returned by the FTP step, before they are passed downstream.

In addition, it’s important to apply the correct response mapping ( ) after the query has executed, to ensure the lookup data is available in subsequent steps.

Here are some important things to keep in mind when building a flow like the one described above: