using more than one AMPScript function to set a variable

Question

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

Answer 1

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

]%%