1

I would like to be able to do the following in a single AMPscript variable:

  1. set the format (FormatNumber)
  2. set a value for blanks (IF EMPTY)
  3. pull it in from a synchronized Data Extension(LookupOrderedRowsCS and Rows)

When compiling the values I have used the following code, but this doesn't appear to be working.

What is the best way to achieve this? Going forward, how would I use more than one function in conjunction?

 Var  @accountId, @Number, @row, @accountRows
  Set @accountId = AccountId
  Set @accountRows = LookupOrderedRowsCS('ent.Accounttable', 1, 'Id', 'Id', @accountId)  
  Set @row = row(@accountRows,1)
  Set @Number = (FormatNumber (field(@row,"Number__c")"F0")
If EMPTY(@row) Then
Set @Number = "NA"
Else
  Set @Number = (FormatNumber (field(@row,"Number__c")"F0")

1 Answer 1

6

You are almost there. Other then a few validation issues, you had it right.

  • Added AttributeValue instead of a direct call, better null value handling
  • Corrected FormatNumber function layout
  • added another variable to provide less potential for syntax errors on IF statement
  • Added ENDIF to IF statement

Below should give you the result you need:

%%[

    Var @accountId, @num, @Number, @row, @accountRows

    Set @accountId = AttributeValue("AccountId")

    Set @accountRows = LookupOrderedRowsCS('ent.Accounttable', 1, 'Id asc', 'Id', @accountId) 

    Set @row = row(@accountRows,1)

    Set @num = field(@row,"Number__c")

    If EMPTY(@num) OR @num == "" Then

      Set @Number = "NA"

    Else

      Set @Number = FormatNumber(@Number,"F0")

    ENDIF

]%%

If you only ever need 1 row, you can skip the lookuporderedrows() and just use Lookup().

See below:

%%[

    Var @accountId, @num, @Number

    Set @accountId = AttributeValue("AccountId")

    Set @num = Lookup("ent.Accounttable", "Number_c", "Id", @accountId) 

    If EMPTY(@num) OR @num == "" Then

      Set @Number = "NA"

    Else

      Set @Number = FormatNumber(@Number,"F0")

    ENDIF

]%%
1
  • Okay I see what I have to do now.
    – user48743
    Commented Oct 26, 2017 at 16:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .