I have the following codesnippet which I reference as content block by key in my SMS Asset. Unfortunately I am not able to get the price from the API. How do I make the payload dynamic such that it takes in values from my Data Extension to post to the API and fetch the price for the combination

Var @callStatus, @callStatusrowset, @payload, @headername, @headervalue, @request, @rows, @priceRow, @price, @drug_id, @drg_qty, @pharmacy_id

SET @drug_id = Lookup("TableName", "drug_id", "SubscriberKey", _subscriberkey)
SET @drg_qty = Lookup("TableName", "drug_quantity", "SubscriberKey", _subscriberkey)
SET @pharmacy_id = Lookup("TableName", "pharmacy_id", "SubscriberKey", _subscriberkey)

SET @payload = Concat('{"drug_id":"',@drug_id,

SET @headername = "Header Name"
SET @headervalue = "HeaderValue"
SET @request = HTTPPost2("Endpoint.com/v4", "application/json", @payload, false, @callStatus, @callStatusrowset, @headername, @headervalue)

IF (@request == 200) THEN
  SET @rows = BuildRowsetFromJSON(@callStatus, "$.['prices'][*]", 1)
  SET @priceRow = Row(@rows,1)
  SET @price = Field(@priceRow,'price')

  The discounted price of %%=V(@price )=%%. 

ELSEIF (@request == 404) THEN
  SET @price = "Coupon not found"

  Sorry, the coupon is not available


1 Answer 1


[UPDATE] I was able to get this working, here is the breakdown of each step and how i got it to work:


Var @callStatus, @response, @payload, @headerName, @headerValue, @rows, @priceRow, @price, @drug_id, @drg_qty, @pharmacy_id, @drug_display_name, @pharmacy_chain_name, @jsonPath, @parsedPrice

SET @drug_id = AttributeValue("drug_id") -- Unique identifier for the drug
SET @drg_qty = AttributeValue("drug_quantity") -- Quantity of the drug
SET @pharmacy_id = AttributeValue("pharmacy_id") -- Unique identifier for the pharmacy
SET @drug_display_name = AttributeValue("drug_display_name") -- Display name of the drug
SET @pharmacy_chain_name = AttributeValue("pharmacy_chain_name") -- Name of the pharmacy chain

SET @payload = Concat('{"drug_id": "', @drug_id, '", "quantity": ', FormatNumber(@drg_qty, "N2"), ', "pharmacy_id": "', @pharmacy_id, '"}')
SET @headerName = "API-Client-ID"
SET @headerValue = "Your_API_Client_ID"
SET @response = HTTPPost2("https://www.example.com/api/v4/coupons", "application/json", @payload, false, @callStatus, @response, @headerName, @headerValue)

IF @response == "200" THEN
    SET @jsonPath = "prices[0].price"
    SET @rows = BuildRowsetFromJSON(@callStatus, "$.['prices'][*]", 1)
    IF RowCount(@rows) > 0 THEN
        SET @priceRow = Row(@rows, 1)
        SET @parsedPrice = Field(@priceRow, "price")
        IF NOT EMPTY(@parsedPrice) THEN
            SET @price = FormatNumber(@parsedPrice, "N2", "en-US")
    Add message here
ELSEIF (@response == 404) THEN
    SET @price = "Coupon not found"
    RaiseError("No Coupon Found!", true)

Variable Initialization:

The script starts by declaring and setting several variables to store data that will be used in the API request and message construction.

@drug_id, @drg_qty, @pharmacy_id, @drug_display_name, @pharmacy_chain_name are retrieved using AttributeValue function calls, which fetch these values from the context (likely email or SMS send context in a marketing automation platform).

Building the API Request:

The @payload variable is constructed as a JSON string containing the drug ID, quantity, and pharmacy ID. These are dynamically inserted into the payload using concatenation. @headerName and @headerValue are set to include necessary authentication for the API request, with the actual API Client ID replaced with a placeholder for security reasons. HTTPPost2 function is used to make the POST request to the API endpoint, with the URL anonymized here for privacy. The function parameters include the URL, content type (application/json), the payload, and headers for authentication.

Processing the API Response:

The script checks the HTTP status code of the response. If it's 200, indicating success, it proceeds to parse the JSON response. It uses BuildRowsetFromJSON and a JSONPath expression to extract the price information from the response. The price is then formatted to a two-decimal number and stored in @price.

Constructing the Notification Message:

If the price was successfully retrieved, a personalized message is constructed using the drug name and pharmacy name, informing the recipient of the discounted price available. This message includes a link (anonymized here) to obtain a coupon. In case of a 404 response, indicating that the coupon was not found, the script sets @price to "Coupon not found" and raises an error.

Error Handling:

The script uses conditional logic to handle different responses from the API and to construct appropriate user messages based on whether the coupon information was successfully retrieved.

You must log in to answer this question.

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