3

Using AMPScript, I want to pass from a URL Attribute lookup variables and use these variables in my LookupOrderRows function.

The challenge is that not all lookup variables have to be passed by the URL e.g I might lookup just 'office' or I could also lookup both 'office' and 'score.

I've tried several approaches of Concat the string etc - but can't see to make the lookup work.

Has anyone had any success performing similar?

IF not empty( Requestparameter("office") ) then 
Set @field1_name = "OFFICE"
Set @field1_value = Requestparameter("office") 
EndIf

IF not empty( Requestparameter("specialism") ) then 
Set @field2_name = "SPECIALISM"
Set @field2_value = Requestparameter("specialism") 
EndIf

IF not empty( Requestparameter("score") ) then 
Set @field3_name = "SCORE"
Set @field3_value = Requestparameter("score") 
EndIf


set @rows = LookupOrderedRows(@LeadsDE,0,"[Engagement Activity Date] desc",@Field1_Name,@Field1_Value,@Field2_Name,@Field2_Value,@Field3_Name,@Field3_Value)

My current solution is a lot If then statements and combinations i.e. :

/* OFFICE ONLY*/
IF NOT EMPTY(@input_office) AND EMPTY(@input_organisation) AND EMPTY(@input_specialism)  AND EMPTY(@input_score) then
    set @rows = LookupOrderedRows(@LeadsDE,0,"[Engagement Activity Date] desc","OFFICE",@input_office)
ENDIF

/* OFFICE + ORG*/
IF NOT EMPTY(@input_office) AND NOT EMPTY(@input_organisation) AND EMPTY(@input_specialism)  AND EMPTY(@input_score) then
    set @rows = LookupOrderedRows(@LeadsDE,0,"[Engagement Activity Date] desc","OFFICE",@input_office,"ORGANISATION_NAME",@input_organisation)
ENDIF
/* OFFICE + ORG + SPECIALISM */
IF NOT EMPTY(@input_office) AND NOT EMPTY(@input_organisation) AND NOT EMPTY(@input_specialism)  AND EMPTY(@input_score) then
    set @rows = LookupOrderedRows(@LeadsDE,0,"[Engagement Activity Date] desc","OFFICE",@input_office,"ORGANISATION_NAME",@input_organisation,"SPECIALISM",@input_specialism)
ENDIF
/* OFFICE + ORG + SCORE */
IF NOT EMPTY(@input_office) AND NOT EMPTY(@input_organisation) AND EMPTY(@input_specialism)  AND NOT EMPTY(@input_score) then
    set @rows = LookupOrderedRows(@LeadsDE,0,"[Engagement Activity Date] desc","OFFICE",@input_office,"ORGANISATION_NAME",@input_organisation,"Engagement Score",@input_score)
ENDIF
/* OFFICE + ORG + SPECIALISM + SCORE  */
IF NOT EMPTY(@input_office) AND NOT EMPTY(@input_organisation) AND NOT EMPTY(@input_specialism)  AND EMPTY(@input_score) then
    set @rows = LookupOrderedRows(@LeadsDE,0,"[Engagement Activity Date] desc","OFFICE",@input_office,"ORGANISATION_NAME",@input_organisation,"SPECIALISM",@input_specialism,"Engagement Score",@input_score)
ENDIF

IF NOT EMPTY(@input_office) AND EMPTY(@input_organisation) AND NOT EMPTY(@input_specialism)  AND EMPTY(@input_score) then
    set @rows = LookupOrderedRows(@LeadsDE,0,"[Engagement Activity Date] desc","OFFICE",@input_office,"SPECIALISM",@input_specialism)
ENDIF

IF NOT EMPTY(@input_office) AND EMPTY(@input_organisation) AND NOT EMPTY(@input_specialism)  AND NOT EMPTY(@input_score) then
    set @rows = LookupOrderedRows(@LeadsDE,0,"[Engagement Activity Date] desc","OFFICE",@input_office,"SPECIALISM",@input_specialism,"Engagement Score",@input_score)
ENDIF

IF NOT EMPTY(@input_office) AND EMPTY(@input_organisation) AND EMPTY(@input_specialism)  AND NOT EMPTY(@input_score) then
    set @rows = LookupOrderedRows(@LeadsDE,0,"[Engagement Activity Date] desc","OFFICE",@input_office,"Engagement Score",@input_score)
ENDIF
  • First note, you are not using the @ before your AMPscript variables when setting them in the conditions. I haven't checked the rest yet though so may not be the complete solution to your issue. – Gortonington May 8 '19 at 17:57
  • Hey Gortonington - apologies, the code was writtien whilst on a train - it does have @ I'm just trying to simplify the lookup logic - but so far have resorted to loads of it statements (second post) – Gravitai May 8 '19 at 18:17
3

If you'll be working with a variable combination of fields and want to build your lookup's where clause dynamically, you could try leveraging the TreatAsContent function.

I tested the following on my end and it appears to work:

%%[
/*
var @dataExt,
    @fieldSort,
    @officeName,
    etc...
*/ 

set @dataExt = 'some_de'
set @fieldSort = 'some_field DESC'


/* expected fields */
set @officeName = 'office'
set @officeVal = RequestParameter(@officeName)

set @specialismName = 'specialism'
set @specialismVal = RequestParameter(@specialismName)

set @scoreName = 'score'
set @scoreVal = RequestParameter(@scoreName)


/* use characters that won't appear in field names or values */
set @separator = '~'
set @delimiter = '`'

/* create name-value string based on expected fields */
set @nameValPairs = Concat(
  @officeName, @separator, @officeVal, @delimiter,
  @specialismName, @separator, @specialismVal, @delimiter,
  @scoreName, @separator, @scoreVal
)


/* create name-value collection */
set @fieldRowset = BuildRowsetFromString(@nameValPairs, @delimiter)


/* build where clause */
set @whereClause = ''

for @i = 1 to RowCount(@fieldRowset) do
  set @row = Row(@fieldRowset, @i)
  set @item = Field(@row, 1)
  set @fieldName = Substring(@item, 1, Subtract(IndexOf(@item, @separator), 1))
  set @fieldVal = Substring(@item, Add(IndexOf(@item, @separator), 1))

  if not Empty(@whereClause) and not Empty(@fieldVal) then
    set @whereClause = Concat(@whereClause, ',')
  endif

  if not Empty(@fieldVal) then
    set @whereClause = Concat(@whereClause, '"', @fieldName, '","', @fieldVal, '"')
  endif
next @i


if not Empty(@whereClause) then
]%%
%%=TreatAsContent(Concat("%", "%[set @rowset = LookupOrderedRows(@dataExt,0,@fieldSort,", @whereClause, ")]%", "%"))=%%
%%[
Output(Concat('Where Clause: (', @whereClause, ')<br>'))
Output(Concat('Row Count: ', RowCount(@rowset)))

else
  Output(Concat('Missing valid field name-value pairs'))
endif
]%%
|improve this answer|||||
  • Mark - you are a genius !! Thank very much for this answer - it was spot on and works a treat. Your solution has considerably reduced the amount of code in a very useful and expandable approach. Kudos to you my friend ! – Gravitai May 10 '19 at 10:54
0

I just tested a simplified version of your code and it works as expected:

%%[ Set @Field1_Name = 'NAME1'
set @Field2_Name = 'NAME2'
set @de = 'DENAME'
set @Field1_Value = 'VALUE1'
set @Field2_Value = 'VALUE2'


set @rows = LookupOrderedRows(@de,0,"FIELDNAME desc",@Field1_Name,@Field1_Value,@Field2_Name,@Field2_Value)

]%%

%%=rowcount(@rows)=%%

This returns a value of 1 which is expected

To begin troubleshooting your issue I would print out your Variables to ensure they are the values you expect. If you aren't using a an content builder to test your ampscript, where you simply set a value where you would expect a requestparameter

I also don't see where you set a value for your VAR when the requestparameter is empty

You can also review using an iif() statement to simplify your if statements

set @field1 = IIF(EMPTY(Requestparameter("office")),'office',Requestparameter("office"))=%%
|improve this answer|||||
  • Thanks EazyE - I don't think my question was clear enough - it's a difficult one to explain. What I ideally wanted to do is eliminate the need for doing lots of IF/THEN statements to set the LookupOrdered Rows. Something like this : set @rows = LookupOrderedRows(@de,0,"FIELDNAME desc",IIF (NOT EMPTY(@Field1_Name), '@Field1_Name,@Field1_Value',''),IIF (NOT EMPTY(@Field2_Name), '@Field2_Name,@Field2_Value','') ) I've tried Concat, IIF etc and can't see to get it to work. My output string formats correct, but LookupRows doesnt accept LookupRows(@MyString) – Gravitai May 9 '19 at 11:24

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.