Best answer

Remap mySQL JSON Fields into Zoho CRM?

  • 7 March 2020
  • 1 reply
  • 49 views

Userlevel 1

I have a mySQL to Zoho CRM Zap. Everything works great except some of my fields in the mySQL database are JSON formatted. What I need to do is to reformat the JSON data into separate fields for import into Zoho CRM.

This is the json in one of the fields:

{

   "title":"Lifetime",

   "expirationtype":"forever",

   "expiration":"000000000000",

   "recurrence_count":"0",

   "price":"699.00",

   "trial_price_1":"0.00",

   "trial_time_1":"000000000000",

   "trial_price_2":"0.00",

   "trial_time_2":"000000000000"

}

So I need to pull out just the price and remap it to another field in Zoho. Here is what the field looks like:

Createupdate module entry in Zoho CRM when new or updated row in MySQL  Zapier 2020-03-06 15-57-02.png

Any ideas? Thanks!


icon

Best answer by Danvers 9 March 2020, 18:28

Hi @Garrett It looks like this is coming as a line item array, is that right?

If so, you can use the Formatter app to turn the line items into text. Then you can use another Formatter step to split the text and get the data that you need.

For the Line item to text step, select the Formatter app then choose 'Utilities' as the Action Event. For the Transform, choose Line item to text. That will turn the json array into a text string separated by commas.

You can then use another Formatter step (or probably more than one) to split the text and get the Price from the text.


View original

This topic has been closed for comments

1 reply

Userlevel 7
Badge +11

Hi @Garrett It looks like this is coming as a line item array, is that right?

If so, you can use the Formatter app to turn the line items into text. Then you can use another Formatter step to split the text and get the data that you need.

For the Line item to text step, select the Formatter app then choose 'Utilities' as the Action Event. For the Transform, choose Line item to text. That will turn the json array into a text string separated by commas.

You can then use another Formatter step (or probably more than one) to split the text and get the Price from the text.