1

I have following data extension: "My_DataExtension" "My_DataExtension" DE has following fields:

1. My_Text_field_1 (text 300)

I have a cloudPage named "My_CloudPage".

  • This cloudPage has 1 input text element like "Text_Input_1" .
  • Also, this cloudpage has "submit" button.

Now, when a user enters some text value in "Text_Input_1" & clicks "submit" button, I want to search in "My_DataExtension" for "My_Text_field_1" for the matching value entered by the user.

I was trying to use the "LookUpRows()" function and this function would take "WHERE" clause. But, user might not enter exact text.

How can I retrieve records from data extension by matching DE field values partially?

E.g. "My_DataExtension" has following 3 records: - My_Text_field_1 ==> "This is my test value 1" - My_Text_field_1 ==> "This is my test value 2" - My_Text_field_1 ==> "This is my test value 3"

And if the user has entered following value on the cloudpage & clicks "submit" button: i.e. Text_Input_1 ==> "This is"

I want to be able to search for the "My_DataExtension" records & it should return me 3 DE rows.

Please let me know which AMPscript function can help me achieve this?

1

Unfortunately there is no direct way to do this in AMPScript.

You have 2 possibilities that you can use. 1 in AMPScript and 1 in SSJS.

AMPSCRIPT: (if retrieved rows is fairly low quantity)

  1. First, you would have to add a row to your DE that contains a constant value (flag) inside of it. I usually name this field 'Secret' and use a value of 1.

    So for instance your DE would have 2 fields - My_Text_field_1 | Secret
  2. You would do your lookup based on this 'Secret' field - LookupRows('yourDE', 'Secret', 1)

    This will return ALL rows in this DE up to 2500 I believe.
  3. You would then utilize IndexOf() function for your returned value from the Cloudpage to do a 'Contains' inside of a For Loop

Putting it all together you get:

%%[
SET @Text = AttributeValue('Text_Input_1') 
/* or RequestParameter('Text_Input_1') if the value is being passed as a parameter */
SET @Rows = LookupRows('yourDE', 'Secret', 1)
SET @Count = 0

FOR @i=1 TO Rowcount(@Rows) DO

  SET @Row = Row(@Rows, @i)
  SET @Text_Input_1 = Field(@Row, 'Text_Input_1')

  IF IndexOf(@Text_Input_1, @Text) > 0 THEN

    SET @Count = ADD(@Count, 1)

    /* Set any other actions you want to take if the value contains the string */

  ENDIF

Next @i
]%%



SSJS: (Processes slow and has a larger resource draw)

For the SSJS solution, you would use the Rows.Retrieve() Function.

<script runat="server">

Platform.Load("core", "1.1.1");

var text_Input_1 = Platform.Request.GetFormField('Text_Input_1'); /* Get form field */
var yourDE = DataExtension.Init("yourDE");
var filter = {Property:"My_Text_field_1 ",SimpleOperator:"Like",Value: text_Input_1};
var data = yourDE.Rows.Retrieve(filter);
</script>

This will return a rowset containing all of the rows that are 'like' your text input value from the cloud page.

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.