Changing query result with a transformer

Queries and Resources
,
1

Newbee here... I am trying to change part of a SQL query result before writing it to a google sheet.

I have a an MySQL query that return about 290 rows with 6 columns. I want to replace the content of the second columns with a value calculated from the first columns.

I am trying to use a transformer attach to the query. I would like to change the content of a columns in MySQLQuery.data so I could write it to a Google sheet. The whole process as been tested and working good except the part to modify part of the data.

Here is the line of javascript I am trying now to make it work

{{ CiviCRM_Extract_Data.data.Id }} = {{ CiviCRM_Extract_Data.data.Id }}.map(strEncrypt);
return {{ CiviCRM_Extract_Data.data }}

But it is not working. CiviCRM_Extract_Data is the name of the query the transformer is attached to. The data.Id field is the one I would like to update and leave all others unchanged. Is the dataset from a query readonly?

Any hint would be appreciate! Thank you!

2

Hello,

I found my solution. Effectively, the dataset from a query seem readonly, anyway that is my deduction.

const qryData = {{ CiviCRM_Extract_Data.data }};

qryData.Id = qryData.Id.map(strEncrypt);

return qryData

This the code that worked for me. Just assigned the dataset to a local variable, made the change to this variable and return it as the result.

Might be a way to do it without duplicating the data but it works.

Thanks

3

Hey @Syxys!

Good to hear you were able to find a solution here :slightly_smiling_face:

One important thing to note is that if you're using the transformer that's part of the query editor panel it's often best to reference data instead of {{ yourQuery.data }}. The former returns data from the current call while the latter will return your data from the previous call after it was passed through the query and returned, as it does when you reference it elsewhere. The variable data is specifically for query transformers.

Using data you should be able to do something like:

data.Id = data.Id.map(strEncrypt);
return data;

If you're using a standalone query then no need to change anything!

4

Hello @Kabirdas, your advice come at the very right moment, cause I was having problem with the first run. Fields were not defined. I had to disable the transformer, run the query, and than renable it for the thing to run smoothly. Now I understand why.

Thank you!!