Search a DB based on input / Merging Results

Hi All,

I’m a newbie so bear with me. I’m working on a pipeline that connects to a CRM system. This CRM system tracks everything by the customer’s contact ID number. However, our web site uses a customer’s email address for all processing. I’m trying to find a way to take an email address input and find the corresponding customer’s contact ID number.

Does anyone out there have any tips on how to do this?

This is the path I’m currently taking but it seems very laborious:

  • I use a REST GET to get all contacts. This only gets me high level attribute fields (with one being contact ID).
  • I use another REST GET to get the full details of each contact. This gets me a link to find the email addresses but not the actual email addresses.
  • I use another REST GET to get all of the email addresses of all of the contacts. This gets me email addresses but errors when a contact doesn’t have an email address (not sure how to skip those null values).

I’m also not sure how to merge all of these attribute values back together into a complete list of results. (ie. I have no idea how to match the email addresses of the last REST GET with the full details found in the previous REST GET. Any ideas?

So it seems to me that I need to use Routers and Joins to aggregate all of the mapped fields together from multiple REST Gets. Is this correct?

Hi mkramer,

Firstly, there must be a better way to query a customer’s data in the CRM system via the email address. Failing that, you might consider building a correlation db with the steps you describe. Then you can reference the database (maintenance will be an issue).

Secondly, if you do need to use the path described, note that there will be an “original” field on the output of each REST GET where you can access/remap the data that was present on the input.

Thirdly, as self-described “newbie”, make sure you fully understand the streaming nature of the document processing in a pipeline. I find that the realities of the streaming implementation are difficult even for seasoned/intelligent software developers to grasp initially. Getting a firm grasp on it will help you see what you need to do with that data.


Hi TK,

Thanks for the tip. This made me look deeper into the REST API of the system I’m trying connect. There is a query option and I have the syntax working in Postman but when I copy and paste the syntax into a REST GET, it errors and says it’s a bad URI. Any advice on why a query like this would error? It’s clearly something syntactically wrong but I’m not sure what it is:

“https://.com/services/rest/connect/v1.3/queryResults/?query=select * from contacts”

Failure: The provided URI is invalid: https://.com/services/rest/connect/v1.3/queryResults/?query=select * from contacts, Reason: The provided URI is invalid: https://airborn– * from contacts, Resolution: Please verify the provided URI and parameters are syntactically correct

Have you tried moving the query to the Query parameters portion of the dialog and leaving the URL to end at Results?

Never mind. I found the mistake. You actually have to put %20 in the URI to fill any spaces. Once I did that, it worked. I think I’m all set now. Thanks for the help.