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")
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

]%%
  • Okay I see what I have to do now. – user48743 Oct 26 '17 at 16:19

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.