Use form fields in SQL query, iterate through that query and more

How to
#1

First, let me ask if this will even be possible… or if I should just have the backend devs make an endpoint to handle the logic.

The use case is a Partner wants to enroll a Client, but we must check first if the Client is already owned by some other partner.

The form has an email field and a phone field (and others, but the email/phone are what I need to validate if it is OK for this partner to add this client)…

In our database is an Identities table which relates emails and phones to the GlobalAccountID table.

So first I need to see if there are any rows returned from the identities table that have a matching identity (from the email or phone field).

If any rows are found, I now need to get the globalAccount ID from each row.

Next I need to iterate through these results:

For each result found from the first SQL statement, I must lookup a result in a Fields table to check if a record exists. If a record exists, the partner can NOT enroll this client. If no record exists the partner CAN enroll the client.

^ looking at the overall setup of automations… this looks possible? Maybe even just within a single (complex) SQL statement?

If generally, what I’m trying to do is do-able… then here is where I’m stuck just trying to make things work step by step.

I’m in the form component, and set a workflow to run when the button is clicked.

I just have an email field only for now, and in the automation builder, I set a ‘value for testing’ on the trigger.

The first step in the automation I have to set a variable I have labelled email, and I used the function to set this value to elements.Form.value.email

However when I go to the test step for the variable - and check the result it shows:

object {0}

…so of course when I then run the SQL test, it returns no results.

Here is the SQL:

SELECT
  identities.global_account_id
FROM
  identities
WHERE
  identities.key LIKE '%'{{params.email}}'%'

Here is a video walkthrough:

1 Like