2

I have 3 Fields showing 'number of rewards' available that are decimal inputs.

Is there a way I can display a sum of the 3 columns in a field in a data extension so that number can be used in email personalisation?

EG:

Field a - 2
Field b - 6
Field c - 1

Sum
Total - 8

Alternatively, is this possible with AMP script?

also, is it possible to combine with a Lookup if data is stored in a separate table and linked through a contact id?

EG - %%[set @contactId =[ContactId]]%% %%=Lookup('othertable','field1','ContactID',@contactId)=%%

Something like:

%%[set @contactId =[ContactId]]%% %%[set @field1 =[%%=Lookup('othertable','field1','ContactID',@contactId)=%%] %%[set @field2 =[%%=Lookup('othertable','field1','ContactID',@contactId)=%%] %%[ SET @total = Add(field1,field2) ]%%

disclaimer - this doesn't work, but an example of what I'm trying to pull off!

5

Yep, this is possible via ampscript and SQL. For SQL you would need to run a query as a update. This should work:

SELECT (field1+field2+field3) AS TotalSum 
FROM TableName

Ampscript solution using the Add function (assuming field1 and field 2 are in sendable DE)

%%[
SET @total = Add(field1,field2)
SET @total = Add(@total,field3)
]%%

Rounding to the nearest whole number can be achieved using the FormatNumber function in Ampscript

%%=FormatNumber(@total,"N")=%%
  • Excellent, thanks! Can you think of a way this can combined with a Lookup in the case data is updated separately to contact data? – Ben Owen Oct 31 '19 at 10:50
  • Have added info in the original question – Ben Owen Oct 31 '19 at 10:59
  • as long as you have a a value to look up against in your other table i dont see why a lookup followed by ampscript addition wont work, you should probably use contact key/subscriber key rather than contactID which is a system identifier. When you say it doesn't work what do you mean? are you getting a error. Are you getting results returned from your lookup? – Noor Alam Nov 17 '19 at 22:42
  • Hi Noor, yes when trying the snippet of AMP I added as an edit to the original question I got an error message when attempting to preview a test email, I'll revisit again with a fresh set of eyes but I went through every possible variant of field names I could to ensure it wasn't a simple syntax error. – Ben Owen Nov 19 '19 at 10:23
  • help.salesforce.com/… contactID is not there as a valid personalisation string, as mentioned previously I'd recommend subscriberKey – Noor Alam Nov 19 '19 at 12:06
0

You can use the Add function in AMPscript and Subtract function.

    %%[ 
         set @Avalue = Subtract(@a,2) 
         set @Bvalue = Subtract(@b,6)
         set @Cvalue = Subtract(@c,1)
         set @sum = Add(@Avalue, @Bvalue)
         set @sum2 = Add(@sum, @Cvalue)
    ]%%
  • Add only takes 2 params – Noor Alam Apr 10 '19 at 13:18

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.