For Zoho Services only:


I'm actually part of something bigger at Ascent Business Solutions recognized as the top Zoho Premium Solutions Partner in the United Kingdom.

Ascent Business Solutions offer support for smaller technical fixes and projects for larger developments, such as migrating to a ZohoCRM.  A team rather than a one-man-band is always available to ensure seamless progress and address any concerns. You'll find our competitive support rates with flexible, no-expiration bundles at https://ascentbusiness.co.uk/zoho-services/uk-zoho-support.  For larger projects, talk to our experts and receive dedicated support from our hands-on project consultants at https://ascentbusiness.co.uk/zoho-services/zoho-crm-implementation.

The team I manage specializes in coding API integrations between Zoho and third-party finance/commerce suites such as Xero, Shopify, WooCommerce, and eBay; to name but a few.  Our passion lies in creating innovative solutions where others have fallen short as well as working with new businesses, new sectors, and new ideas.  Our success is measured by the growth and ROI we deliver for clients, such as transforming a garden shed hobby into a 250k monthly turnover operation or generating a +60% return in just three days after launch through online payments and a streamlined e-commerce solution, replacing a paper-based system.

If you're looking for a partner who can help you drive growth and success, we'd love to work with you.  You can reach out to us on 0121 392 8140 (UK) or info@ascentbusiness.co.uk.  You can also visit our website at https://ascentbusiness.co.uk.
Zoho Survey & Zoho Analytics: Query to generate individual responses and grouped pages

Zoho Survey & Zoho Analytics: Query to generate individual responses and grouped pages

What?
An article on some Zoho ANSI-SQL (in other words, queries that work in Zoho Analytics) as I couldn't find any examples online on how to do this.

Why?
I've been tasked with generating a radar/spider/web chart based on the results from a survey. Apparently this will be done in Zoho Analytics.
The Brief:
We would like the survey data to be generated as a chart and embedded in a PDF brochure we send out to our customers...

How?
Well fulfilling the brief (whether it's the right tool for the job is a different issue), let us synchronize Zoho Surveys into Zoho Analytics. I found I need to edit the setup of the sync every time a new survey is added... but at least it lets me synchronize every hour.

Then I'll write the SQL queries to get the individual responses into a table with labels and columns that can help me group them (and identify which responses belong to which survey attempts).

Then I'll write the SQL to sum up the responses per page per survey attempt.

Finally, I'll see if analytics can be used to generate the radar chart.

ZohoSurveys - Individual Responses Query
copyraw
SELECT
		 concat('REF-', left_pad(to_integer(substring(r2."ID", 8)), 6, '0')) AS "Attempt ID",
		 s."Title" AS "Survey",
		 r2."End Date" AS "Response Date",
		 
			CASE p."Title" 
				 WHEN 'DEMOGRAPHICS'  THEN 2
				 WHEN 'AWARENESS'  THEN 3
				 WHEN 'ENGAGEMENT'  THEN 4
				 WHEN 'SATISFACTION & FEEDBACK'  THEN 5
				 WHEN 'USABILITY'  THEN 6
				 WHEN 'FEATURES'  THEN 7
				 WHEN 'PRICING'  THEN 8
				 WHEN 'SUPPORT'  THEN 9
				 WHEN 'TRUST'  THEN 10
				 WHEN 'PERFORMANCE'  THEN 11
			 END AS "Page Number",
		 p."Title" AS "Page",
		 p."Description" AS "Description",
		 to_integer(substring_before(q."Text", '.')) AS "Question Number",
		 q."Text" AS "Question",
		 r."Text" AS "Answer",
		 r2."Status" AS "Status",
		 r3."Variable Value" AS "Respondent Email"
FROM  "Responses (Zoho Survey)" r
LEFT JOIN "Surveys (Zoho Survey)" s ON r."Survey ID"  = s."ID" 
LEFT JOIN "Questions (Zoho Survey)" q ON r."Question ID"  = q."ID" 
LEFT JOIN "Respondents (Zoho Survey)" r2 ON r."Respondent ID"  = r2."ID" 
LEFT JOIN "Response Variables (Zoho Survey)" r3 ON r2."ID"  = r3."Respondent ID" 
LEFT JOIN "Pages (Zoho Survey)" p ON r."Page ID"  = p."ID" 
LEFT JOIN "Rows (Zoho Survey)" r4 ON q."ID"  = r4."Question ID"  
WHERE	 p."Title"   'GENERAL'
ORDER BY "Response Date" DESC,
	 "Attempt ID",
	 "Page Number",
	 "Question Number"
  1.  SELECT 
  2.           concat('REF-', left_pad(to_integer(substring(r2."ID", 8)), 6, '0')) AS "Attempt ID", 
  3.           s."Title" AS "Survey", 
  4.           r2."End Date" AS "Response Date", 
  5.   
  6.              CASE p."Title" 
  7.                   WHEN 'DEMOGRAPHICS'  THEN 2 
  8.                   WHEN 'AWARENESS'  THEN 3 
  9.                   WHEN 'ENGAGEMENT'  THEN 4 
  10.                   WHEN 'SATISFACTION & FEEDBACK'  THEN 5 
  11.                   WHEN 'USABILITY'  THEN 6 
  12.                   WHEN 'FEATURES'  THEN 7 
  13.                   WHEN 'PRICING'  THEN 8 
  14.                   WHEN 'SUPPORT'  THEN 9 
  15.                   WHEN 'TRUST'  THEN 10 
  16.                   WHEN 'PERFORMANCE'  THEN 11 
  17.               END AS "Page Number", 
  18.           p."Title" AS "Page", 
  19.           p."Description" AS "Description", 
  20.           to_integer(substring_before(q."Text", '.')) AS "Question Number", 
  21.           q."Text" AS "Question", 
  22.           r."Text" AS "Answer", 
  23.           r2."Status" AS "Status", 
  24.           r3."Variable Value" AS "Respondent Email" 
  25.  FROM  "Responses (Zoho Survey)" r 
  26.  LEFT JOIN "Surveys (Zoho Survey)" s ON r."Survey ID"  = s."ID" 
  27.  LEFT JOIN "Questions (Zoho Survey)" q ON r."Question ID"  = q."ID" 
  28.  LEFT JOIN "Respondents (Zoho Survey)" r2 ON r."Respondent ID"  = r2."ID" 
  29.  LEFT JOIN "Response Variables (Zoho Survey)" r3 ON r2."ID"  = r3."Respondent ID" 
  30.  LEFT JOIN "Pages (Zoho Survey)" p ON r."Page ID"  = p."ID" 
  31.  LEFT JOIN "Rows (Zoho Survey)" r4 ON q."ID"  = r4."Question ID" 
  32.  WHERE     p."Title"   'GENERAL' 
  33.  ORDER BY "Response Date" DESC, 
  34.       "Attempt ID", 
  35.       "Page Number", 
  36.       "Question Number" 
You can take out the order by clause. I just have it here to preserve my sanity when viewing the output. Note that "Page Number" is my custom sorting mechanism so you would have to refer to the output without this column before modifying the query to include the page names of your survey. GENERAL was the first page asking for lead/contact name when I have this prepopulated from a ZohoCRM integration. Also note that the question number comes from it prefixing the question per page that the client did when setting up her survey (eg. 1. question 1, 2. next question, 3. ...).

ZohoSurveys - Page Responses Query
This is a second query which queries the first query... Well it's for the radar chart and what it does is group all the responses per page (per survey attempt) so that given a survey reference, it will give me the sums per page.
copyraw
SELECT
		 i."Attempt ID" AS "Survey Response",
		 max(i."Survey") AS "Survey",
         i."Page Number",
		 max(i."Page") AS "Page",
		 max(i."Respondent Email") AS "Respondent",
		 sum(to_integer(i."Answer")) AS "Answer Sum"
FROM  "ZohoSurveys - Individual Responses Query" i 
GROUP BY i."Attempt ID",
	  i."Page Number"
  1.  SELECT 
  2.           i."Attempt ID" AS "Survey Response", 
  3.           max(i."Survey") AS "Survey", 
  4.           i."Page Number", 
  5.           max(i."Page") AS "Page", 
  6.           max(i."Respondent Email") AS "Respondent", 
  7.           sum(to_integer(i."Answer")) AS "Answer Sum" 
  8.  FROM  "ZohoSurveys - Individual Responses Query" i 
  9.  GROUP BY i."Attempt ID", 
  10.        i."Page Number" 

The Analytics bit to set up a Web Chart
Zoho Survey & Zoho Analytics: Query to generate individual responses and grouped pages - X Y Axis

Zoho Survey & Zoho Analytics: Query to generate individual responses and grouped pages - User Filters

The final bit: Embed the radar chart in a Zoho CRM merge template
This is the easy bit... not really... not a clue how I'm going to do this nor if writer will just allow us to embed it... but let's give it a go:

The page responses query showing only the last survey attempt per respondent
I'm going to modify the analytics query above to only return the latest responses that a respondent has completed; this means that if they have completed the same survey twice, this only returns the latest one they completed.
copyraw
SELECT
		 i."Attempt ID" AS "Survey Response",
		 max(i."Survey") AS "Survey",
		 i."Page Number",
		 max(i."Page") AS "Page",
		 max(i."Respondent Email") AS "Respondent",
		 sum(to_integer(i."Answer")) AS "Answer Sum"
FROM  "ZohoSurveys - Individual Responses Query" i
-- join to only return only the latest survey attempt answered per respondent
LEFT JOIN "ZohoSurveys - Individual Responses Query" j ON LOWER(j."Respondent Email")  = LOWER(i."Respondent Email")
	 AND	(j."Response Date"  > i."Response Date"
	 OR	(j."Response Date"  = i."Response Date"
	 AND	j."Attempt ID"  > i."Attempt ID"))  
WHERE	 j."Attempt ID"  IS NULL
GROUP BY i."Attempt ID",
	  i."Page Number"
  1.  SELECT 
  2.           i."Attempt ID" AS "Survey Response", 
  3.           max(i."Survey") AS "Survey", 
  4.           i."Page Number", 
  5.           max(i."Page") AS "Page", 
  6.           max(i."Respondent Email") AS "Respondent", 
  7.           sum(to_integer(i."Answer")) AS "Answer Sum" 
  8.  FROM  "ZohoSurveys - Individual Responses Query" i 
  9.  -- join to only return only the latest survey attempt answered per respondent 
  10.  LEFT JOIN "ZohoSurveys - Individual Responses Query" j ON LOWER(j."Respondent Email")  = LOWER(i."Respondent Email") 
  11.       AND    (j."Response Date"  > i."Response Date" 
  12.       OR    (j."Response Date"  = i."Response Date" 
  13.       AND    j."Attempt ID"  > i."Attempt ID")) 
  14.  WHERE     j."Attempt ID"  IS NULL 
  15.  GROUP BY i."Attempt ID", 
  16.        i."Page Number" 

Export the web chart from Analytics and attach as an image on the CRM record
So the survey is sent via the contact module but we actually only ask Analytics about the survey and the contact email. Here's a standalone function given a CRM contact record ID that will take the survey they completed, generate a radar/spider/web chart, and attaches it as a PNG file on the CRM record.
copyraw
string standalone.fn_Analytics_ExportChart(int p_ContactID)
{
/* *******************************************************************************
	Function:       string standalone.fn_Analytics_ExportChart(int p_ContactID)
	Label:          Fn - Analytics - Export Chart
	Trigger:        Standalone / On-demand
	Purpose:		Extracting a chart from Zoho Analytics
	Inputs:         The CRM contact record
	Outputs:        -

	Date Created:   2025-09-25 (Joel Lipman)
					- Initial release
	Date Modified:	???
					- ???

	More Information:
					https://help.zoho.com/portal/en/community/topic/export-zoho-analytics-chart-with-api-in-deluge
					https://www.zoho.com/analytics/api/v1/data-api/export-data.html
					https://www.zoho.com/analytics/api/v2/bulk-api/export-data.html
					https://www.zoho.com/crm/developer/docs/api/v8/upload-files-to-zfs.html
					https://www.zoho.com/crm/developer/docs/api/v8/update-records.html
					https://help.zoho.com/portal/en/kb/writer/automation-guide/zoho-crm/articles/how-to-merge-subform-fields-and-image-field-via-zoho-writer-s-merge-apis#Step_5_Pass_Image_Field_Values_in_Merge_API

	******************************************************************************* */
	//
	// --------------------------------------------------------------------
	// using Analytics API v1.0 (suprisingly still works in September 2025)
	/*
	v_OwnerEmail = "<the email of the app owner in analytics>";
	v_WorkspaceName = "Zoho CRM Reports";
	v_ViewName = "ZohoSurveys - Spider Chart";
	// invalid oauth is check your TLD (eg. .com or .eu or ...)
	v_Endpoint = "https://analyticsapi.zoho.com/api/" + v_OwnerEmail + "/" + zoho.encryption.urlEncode(v_WorkspaceName) + "/" + zoho.encryption.urlEncode(v_ViewName);
	m_Params = Map();
	m_Params.put("ZOHO_ACTION","EXPORT");
	m_Params.put("ZOHO_OUTPUT_FORMAT","IMAGE");
	m_Params.put("ZOHO_ERROR_FORMAT","JSON");
	m_Params.put("ZOHO_API_VERSION","1.0");
	f_ChartExport = invokeurl
	[
		url :v_Endpoint
		type :POST
		parameters:m_Params
		connection:"zanalytics"
	];
	*/
	//
	// --------------------------------------------------------------------
	// using Analytics API v2.0 (this time with criteria)
	//
	// get contact email and related surveys
	r_ContactDetails = zoho.crm.getRecordById("Contacts", p_ContactID);
	v_ContactEmail = r_ContactDetails.get("Email");
	info v_ContactEmail;
	//
	// the following related list couldn't be found hence the change to the summary query in analytics
	// l_ContactSurveys = zoho.crm.getRelatedRecords("Zoho_Survey", "Contacts", p_ContactID);
	// info l_ContactSurveys;
	//
	// get the org id by going to the cog in the top right for org settings in Analytics and look at the last number in the URL
	v_AnalyticsOrgID = "123456879"; 
	//
	// go to the web chart we made in analytics and look at the URL:
	// the workspace ID follows the /workspace/
	// the view ID follows the /view/ part
	v_WorkspaceID = "1234567000008912345"; 
	v_TableID = "9876543000002198765"; 
	//
	// put the survey name that the web chart applies to
	v_SurveyName = "My Own Survey";
	//
	// build the header for interacting with the API v2.0
	m_Header = Map();
	m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID);
	//
	// build up the config to send (we want an image back)
	m_Config = Map();
	m_Config.put("responseFormat","image");
	//
	// for the criteria, we need to get the exact survey we want to chart on
	// we also want the respondent to match the email on the contact record
	// note the labels correspond to the user filters in our web chart
	v_Criteria = "\"Survey\"='"+v_SurveyName+"' and \"Respondent\"='" + v_ContactEmail + "'";
	//
	// build up the config and send it to the API
	m_Config.put("criteria",v_Criteria);
	m_Params = Map();
	m_Params.put("CONFIG",m_Config.toString());
	// 
	// in analytics, browse to the target table and note the URL IDs after workspace and view 
	v_Endpoint2 = "https://analyticsapi.zoho.com/restapi/v2/workspaces/" + v_WorkspaceID + "/views/" + v_ViewID + "/data";
	info v_Endpoint2;
	// 
	// do the deed
	f_ChartExport = invokeurl
	[
		url :v_Endpoint2
		type :GET
		parameters:m_Params
		headers:m_Header
		connection:"zanalytics"
	];
	//
	// change the filename
	f_ChartExport.setFileName(v_SurveyName + " " + zoho.currentdate.toString("dd-MMM-yyyy") + ".png");
	f_ChartExport.setParamName("file");
	info f_ChartExport;
	//
	// --------------------------------------------------------------------
	//
	// declare apis domain
	v_ZohoApisDomain = "https://www.zohoapis.com";
	//
	// upload the image to the file system or ZFS
	v_FileID = "";
	r_ZfsUpload = invokeurl
	[
		url :v_ZohoApisDomain + "/crm/v8/files"
		type :POST
		files:f_ChartExport
		connection:"zcrm"
	];
	l_ZfsData = ifnull(r_ZfsUpload.get("data"),List());
	for each  m_ZfsData in l_ZfsData
	{
		if(!isNull(m_ZfsData.get("code")))
		{
			if(m_ZfsData.get("code").equalsIgnoreCase("SUCCESS"))
			{
				v_FileID = m_ZfsData.get("details").get("id");
			}
		}
	}
	info v_FileID;
	// 
	// upload to the image field on the contact record (using invokeurl and v8 cost short code wasn't working)
	m_ImageUpload = Map();
	m_ImageUpload.put("File_Id__s",v_FileID);
	l_ImageUploads = List();
	l_ImageUploads.add(m_ImageUpload);
	m_UpdateContact = Map();
	m_UpdateContact.put("My_Image_Upload_Field",l_ImageUploads);
	l_RecordsToSend = List();
	l_RecordsToSend.add(m_UpdateContact);
	m_Data = Map();
	m_Data.put("data",l_RecordsToSend);
	m_Data.put("trigger",List());
	info m_Data;
	v_Endpoint3 = v_ZohoApisDomain + "/crm/v8/Contacts/" + p_ContactID;
	r_UpdateContact = invokeurl
	[
		url :v_Endpoint3
		type :PUT
		parameters:m_Data.toString()
		connection:"zcrm"
	];
	info r_UpdateContact;
	//
	// now need to query to get the preview URLs needed to merge images into writer documents
	v_PreviewID = "";
	r_ContactDetailsAgain = invokeurl
	[
		url :v_Endpoint3
		type :GET
		connection:"zcrm"
	];
	l_ResponseData = ifnull(r_ContactDetailsAgain.get("data"),List());
	for each  m_ResponseData in l_ResponseData
	{
		if(!isNull(m_ResponseData.get("id")))
		{
			for each  m_ReportImage in m_ResponseData.get("My_Image_Upload_Field")
			{
				if(m_ReportImage.get("File_Name__s").startsWith(v_SurveyName))
				{
					v_PreviewID = m_ReportImage.get("Preview_Id__s");
					break;
				}
			}
		}
	}
	//
	// build up URL
	if(v_PreviewID != "")
	{
		v_ImageURL = v_ZohoApisDomain + "/crm/v8/__attachment_preview/" + v_PreviewID;
		info v_ImageURL;
		//
		// specify template id of mail merge document (browse to it and check the URL)
		v_Template3 = "aaaa1111bbbb2222cccc3333dddd4444eeee5";
		//
		// embed in a mail merge brochure PDF
		//get merge field names (for reference - not required later in this fuction) 
		//r_Fields = zoho.writer.getMergeFields(v_Template3,"zwriter"); 
		//info r_Fields;
		// 
		// map data to these fields 
		m_Data = Map();
		m_Data.put("Radar_Chart",v_ImageURL);
		// 
		m_MergedData = Map();
		m_MergedData.put("merge_data",{"data":m_Data});
		// 
		// to generate and attach 
		r_DownloadResponse = zoho.writer.mergeAndDownload(v_Template3,"pdf",m_MergedData,"zwriter");
		r_AttachResponse = zoho.crm.attachFile("Contacts",p_ContactID,r_DownloadResponse);
		if(r_AttachResponse.get("code") == "SUCCESS")
		{
			info "Generated and attached file to CRM record.  ";
		}
	}
	//
	// send to an email for testing
	/*
	sendmail
	[
		from :zoho.adminuserid
		to :v_ContactEmail
		subject :"Radar Chart for " + v_ViewName
		message :"See attached"
		Attachments :file:f_ChartExport
	]
	*/
	//
	return "The function executed.  Doesn't mean it did what we wanted to do.  Just that it executed.";
}
  1.  string standalone.fn_Analytics_ExportChart(int p_ContactID) 
  2.  { 
  3.  /* ******************************************************************************* 
  4.      Function:       string standalone.fn_Analytics_ExportChart(int p_ContactID) 
  5.      Label:          Fn - Analytics - Export Chart 
  6.      Trigger:        Standalone / On-demand 
  7.      Purpose:        Extracting a chart from Zoho Analytics 
  8.      Inputs:         The CRM contact record 
  9.      Outputs:        - 
  10.   
  11.      Date Created:   2025-09-25 (Joel Lipman) 
  12.                      - Initial release 
  13.      Date Modified:    ??? 
  14.                      - ??? 
  15.   
  16.      More Information: 
  17.                      https://help.zoho.com/portal/en/community/topic/export-zoho-analytics-chart-with-api-in-deluge 
  18.                      https://www.zoho.com/analytics/api/v1/data-api/export-data.html 
  19.                      https://www.zoho.com/analytics/api/v2/bulk-api/export-data.html 
  20.                      https://www.zoho.com/crm/developer/docs/api/v8/upload-files-to-zfs.html 
  21.                      https://www.zoho.com/crm/developer/docs/api/v8/update-records.html 
  22.                      https://help.zoho.com/portal/en/kb/writer/automation-guide/zoho-crm/articles/how-to-merge-subform-fields-and-image-field-via-zoho-writer-s-merge-apis#Step_5_Pass_Image_Field_Values_in_Merge_API 
  23.   
  24.      ******************************************************************************* */ 
  25.      // 
  26.      // -------------------------------------------------------------------- 
  27.      // using Analytics API v1.0 (suprisingly still works in September 2025) 
  28.      /* 
  29.      v_OwnerEmail = "<the email of the app owner in analytics>"; 
  30.      v_WorkspaceName = "Zoho CRM Reports"; 
  31.      v_ViewName = "ZohoSurveys - Spider Chart"; 
  32.      // invalid oauth is check your TLD (eg. .com or .eu or ...) 
  33.      v_Endpoint = "https://analyticsapi.zoho.com/api/" + v_OwnerEmail + "/" + zoho.encryption.urlEncode(v_WorkspaceName) + "/" + zoho.encryption.urlEncode(v_ViewName)
  34.      m_Params = Map()
  35.      m_Params.put("ZOHO_ACTION","EXPORT")
  36.      m_Params.put("ZOHO_OUTPUT_FORMAT","IMAGE")
  37.      m_Params.put("ZOHO_ERROR_FORMAT","JSON")
  38.      m_Params.put("ZOHO_API_VERSION","1.0")
  39.      f_ChartExport = invokeUrl 
  40.      [ 
  41.          url :v_Endpoint 
  42.          type :POST 
  43.          parameters:m_Params 
  44.          connection:"zanalytics" 
  45.      ]
  46.      */ 
  47.      // 
  48.      // -------------------------------------------------------------------- 
  49.      // using Analytics API v2.0 (this time with criteria) 
  50.      // 
  51.      // get contact email and related surveys 
  52.      r_ContactDetails = zoho.crm.getRecordById("Contacts", p_ContactID)
  53.      v_ContactEmail = r_ContactDetails.get("Email")
  54.      info v_ContactEmail; 
  55.      // 
  56.      // the following related list couldn't be found hence the change to the summary query in analytics 
  57.      // l_ContactSurveys = zoho.crm.getRelatedRecords("Zoho_Survey", "Contacts", p_ContactID)
  58.      // info l_ContactSurveys; 
  59.      // 
  60.      // get the org id by going to the cog in the top right for org settings in Analytics and look at the last number in the URL 
  61.      v_AnalyticsOrgID = "123456879"
  62.      // 
  63.      // go to the web chart we made in analytics and look at the url: 
  64.      // the workspace ID follows the /workspace/ 
  65.      // the view ID follows the /view/ part 
  66.      v_WorkspaceID = "1234567000008912345"
  67.      v_TableID = "9876543000002198765"
  68.      // 
  69.      // put the survey name that the web chart applies to 
  70.      v_SurveyName = "My Own Survey"
  71.      // 
  72.      // build the header for interacting with the API v2.0 
  73.      m_Header = Map()
  74.      m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID)
  75.      // 
  76.      // build up the config to send (we want an image back) 
  77.      m_Config = Map()
  78.      m_Config.put("responseFormat","image")
  79.      // 
  80.      // for the criteria, we need to get the exact survey we want to chart on 
  81.      // we also want the respondent to match the email on the contact record 
  82.      // note the labels correspond to the user filters in our web chart 
  83.      v_Criteria = "\"Survey\"='"+v_SurveyName+"' and \"Respondent\"='" + v_ContactEmail + "'"
  84.      // 
  85.      // build up the config and send it to the API 
  86.      m_Config.put("criteria",v_Criteria)
  87.      m_Params = Map()
  88.      m_Params.put("CONFIG",m_Config.toString())
  89.      // 
  90.      // in analytics, browse to the target table and note the URL IDs after workspace and view 
  91.      v_Endpoint2 = "https://analyticsapi.zoho.com/restapi/v2/workspaces/" + v_WorkspaceID + "/views/" + v_ViewID + "/data"
  92.      info v_Endpoint2; 
  93.      // 
  94.      // do the deed 
  95.      f_ChartExport = invokeUrl 
  96.      [ 
  97.          url :v_Endpoint2 
  98.          type :GET 
  99.          parameters:m_Params 
  100.          headers:m_Header 
  101.          connection:"zanalytics" 
  102.      ]
  103.      // 
  104.      // change the filename 
  105.      f_ChartExport.setFileName(v_SurveyName + " " + zoho.currentdate.toString("dd-MMM-yyyy") + ".png")
  106.      f_ChartExport.setParamName("file")
  107.      info f_ChartExport; 
  108.      // 
  109.      // -------------------------------------------------------------------- 
  110.      // 
  111.      // declare apis domain 
  112.      v_ZohoApisDomain = "https://www.zohoapis.com"
  113.      // 
  114.      // upload the image to the file system or ZFS 
  115.      v_FileID = ""
  116.      r_ZfsUpload = invokeUrl 
  117.      [ 
  118.          url :v_ZohoApisDomain + "/crm/v8/files" 
  119.          type :POST 
  120.          files:f_ChartExport 
  121.          connection:"zcrm" 
  122.      ]
  123.      l_ZfsData = ifnull(r_ZfsUpload.get("data"),List())
  124.      for each  m_ZfsData in l_ZfsData 
  125.      { 
  126.          if(!isNull(m_ZfsData.get("code"))) 
  127.          { 
  128.              if(m_ZfsData.get("code").equalsIgnoreCase("SUCCESS")) 
  129.              { 
  130.                  v_FileID = m_ZfsData.get("details").get("id")
  131.              } 
  132.          } 
  133.      } 
  134.      info v_FileID; 
  135.      // 
  136.      // upload to the image field on the contact record (using invokeUrl and v8 cost short code wasn't working) 
  137.      m_ImageUpload = Map()
  138.      m_ImageUpload.put("File_Id__s",v_FileID)
  139.      l_ImageUploads = List()
  140.      l_ImageUploads.add(m_ImageUpload)
  141.      m_UpdateContact = Map()
  142.      m_UpdateContact.put("My_Image_Upload_Field",l_ImageUploads)
  143.      l_RecordsToSend = List()
  144.      l_RecordsToSend.add(m_UpdateContact)
  145.      m_Data = Map()
  146.      m_Data.put("data",l_RecordsToSend)
  147.      m_Data.put("trigger",List())
  148.      info m_Data; 
  149.      v_Endpoint3 = v_ZohoApisDomain + "/crm/v8/Contacts/" + p_ContactID; 
  150.      r_UpdateContact = invokeUrl 
  151.      [ 
  152.          url :v_Endpoint3 
  153.          type :PUT 
  154.          parameters:m_Data.toString() 
  155.          connection:"zcrm" 
  156.      ]
  157.      info r_UpdateContact; 
  158.      // 
  159.      // now need to query to get the preview URLs needed to merge images into writer documents 
  160.      v_PreviewID = ""
  161.      r_ContactDetailsAgain = invokeUrl 
  162.      [ 
  163.          url :v_Endpoint3 
  164.          type :GET 
  165.          connection:"zcrm" 
  166.      ]
  167.      l_ResponseData = ifnull(r_ContactDetailsAgain.get("data"),List())
  168.      for each  m_ResponseData in l_ResponseData 
  169.      { 
  170.          if(!isNull(m_ResponseData.get("id"))) 
  171.          { 
  172.              for each  m_ReportImage in m_ResponseData.get("My_Image_Upload_Field") 
  173.              { 
  174.                  if(m_ReportImage.get("File_Name__s").startsWith(v_SurveyName)) 
  175.                  { 
  176.                      v_PreviewID = m_ReportImage.get("Preview_Id__s")
  177.                      break
  178.                  } 
  179.              } 
  180.          } 
  181.      } 
  182.      // 
  183.      // build up URL 
  184.      if(v_PreviewID != "") 
  185.      { 
  186.          v_ImageURL = v_ZohoApisDomain + "/crm/v8/__attachment_preview/" + v_PreviewID; 
  187.          info v_ImageURL; 
  188.          // 
  189.          // specify template id of mail merge document (browse to it and check the URL) 
  190.          v_Template3 = "aaaa1111bbbb2222cccc3333dddd4444eeee5"
  191.          // 
  192.          // embed in a mail merge brochure PDF 
  193.          //get merge field names (for reference - not required later in this fuction) 
  194.          //r_Fields = zoho.writer.getMergeFields(v_Template3,"zwriter")
  195.          //info r_Fields; 
  196.          // 
  197.          // map data to these fields 
  198.          m_Data = Map()
  199.          m_Data.put("Radar_Chart",v_ImageURL)
  200.          // 
  201.          m_MergedData = Map()
  202.          m_MergedData.put("merge_data",{"data":m_Data})
  203.          // 
  204.          // to generate and attach 
  205.          r_DownloadResponse = zoho.writer.mergeAndDownload(v_Template3,"pdf",m_MergedData,"zwriter")
  206.          r_AttachResponse = zoho.crm.attachFile("Contacts",p_ContactID,r_DownloadResponse)
  207.          if(r_AttachResponse.get("code") == "SUCCESS") 
  208.          { 
  209.              info "Generated and attached file to CRM record.  "
  210.          } 
  211.      } 
  212.      // 
  213.      // send to an email for testing 
  214.      /* 
  215.      sendmail 
  216.      [ 
  217.          from :zoho.adminuserid 
  218.          to :v_ContactEmail 
  219.          subject :"Radar Chart for " + v_ViewName 
  220.          message :"See attached" 
  221.          Attachments :file:f_ChartExport 
  222.      ] 
  223.      */ 
  224.      // 
  225.      return "The function executed.  Doesn't mean it did what we wanted to do.  Just that it executed."
  226.  } 

Category: Zoho :: Article: 910

Add comment

Your rating:

Submit

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Accreditation

Badge - Zoho Creator Certified Developer Associate
Badge - Zoho Deluge Certified Developer
Badge - Certified Zoho CRM Developer

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF

Please publish modules in offcanvas position.