Hi All,
I have been building a solution that can make an API call to shorten URLs from a workflow.
I want to be able to:
* Query Data
* Define Message Content for each record including long URL
* Call API using javascript
* Grab response to API calls (which is message content with the long URL replaced with Short URL)
* Output data to SFTP as .csv file AND write the URL shortening that has gone on (the API responses) to custom schemas.
I see 2 possible approaches from what I've been testing, both with challenges to get me what I want.
Approach 1
- Use Query to define what recipient data is to be exported (adding data to temp schema as needed, e.g. mobile number).
- Use Data Extraction (file) activity to define columns to export... including:
- Calculated field 1 (for user to define SMS content for all records, with long URLs defined)
- Calculated field 2 (javascript expression) that:
- Uses Calculated field 1 as a variable
- Extracts any URLs and stores in Array Variable
- Makes API call for each URL in Array and stores in new array
- creates final message variable that is written for each record.
- (This is working as expected).
- File Transfer Activity to push to SFTP.
What I feel like I am missing from this piece is being able to write the "final message" variable to custom schemas.
- I want to be able to log each URL that has been shortened in a "URL Shortening" schema
- I want to be able to log all the records that are being exported in a "SMS delivery logs schema" along with other temp schema data from my original query activity.
So my questions for this approach are:
- Am I able to use xtk.session.write() jsapi method in a javascript calculated field on a data extraction activity (it doesn't seem to work, xtk not defined)?
- Can I add the output of the calculated field to my temp schema following the data extraction activity? (and really it must be a long text data type as it could exceed 255 characters). This would be my preference as I feel like I could do the subsequent set-up needed from that point.
Approach 2.
The other way I thought I could achieve this (pretty similar to be honest) would be:
- Use Query to define what recipient data is to be exported (adding data to temp schema as needed, e.g. mobile number).
- Use Javascript activity to select the temp data from the previous query activity with QueryDef. Define the message content as a variable and then use the QueryDef result to create a for loop for the API calls and retrieve the responses.
- What I'd then need to be able to do is add the API response variable in my JS activity as a column to my temp schema (using enrichment?) - again important it's a long text data type. I could then do my subsequent activities if I have these API call results in my temp schema (e.g. data export and then subsequent JS activities to update custom schemas with xtk.session.write() method).
So my question for this approach is:
- Can I use the the API response variable from my JS activity and add as a column to my temp schema. It must be long text field. How would I do this?
Thanks
David
