0

I have a simple CloudPage using the Smart capture form. The form collects a number and writes that number into a column called 'input_number' in a data extension.

Is it possible to fetch the 'input_number' and use that number in a SQL query, like a dynamic field?

Example:

The 'input_number' is 200.

create this SQL query:

SELECT TOP 200
customer_ID

FROM
CUSTOMER_INFO 

The 'input_number' is 1000.

create this SQL query:

SELECT TOP 1000
customer_ID

FROM
CUSTOMER_INFO 

The SQL query created will write data to a data extension, but only contain the rows specified by the 'input_number'

I have tried to look at: https://developer.salesforce.com/docs/atlas.en-us.mc-programmatic-content.meta/mc-programmatic-content/ssjs_queryDefinitionFunctions.htm

but I don't know SSJS, so I am not even sure that 'Query Definition Functions' can help me regarding this.

  • 1
    Hi @Martin, Hope this helps - narendraprasadblog.wordpress.com/2019/11/10/… – Naveen VM May 29 at 3:19
  • Hi @NaveenVM - Thank you for the link. I have actually already tried this. But since I don't know SSJS it is hard for me to follow the instructions. Thank you for taking your time to help though – Martin Ho May 29 at 8:55
3

Option 1 is the API and SSJS.

You can inject SSJS variables e.g. into a CreateRequest / UpdateRequest in the SOAP API. Naveen's link should help with this option.

Option 2 is to populate a data extension field with the input ("1000") and then use a subselect:

SELECT TOP (
    SELECT 
    CAST("Input_number" AS INT) AS 'intValue' 
    FROM "settings data Extension")
"customer_ID"
FROM "CUSTOMER_INFO"
| improve this answer | |
  • Option 2 worked perfectly. – Martin Ho May 29 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.