Using Case in Zoho Analytics

PHOTO

Thu Dec 19 2024 16:22:05 GMT+0000 (Coordinated Universal Time)

Saved by @RehmatAli2024 #deluge

SELECT
    "v"."Id" AS "ePOS_Variant_Id",
    "v"."ePOS Variant Name" AS "ePOS_Variant_Name",
    "vv"."CRM Record Id" AS "Verification_Record_Id",
    "vv"."Name" AS "Verification_Name",

    CASE
        WHEN "vv"."CRM Record Id" IS NULL THEN 'Create'
        WHEN "v"."Id" IS NULL THEN 'Delete'
        WHEN "v"."ePOS Variant Name" <> "vv"."Name" THEN 'Update'
    END AS "Action"
FROM  "ePOS Variants" AS "v"
FULL OUTER JOIN "ePOS Variant (Zoho Creator-Trees Verification Process)" AS "vv" 
    ON "v"."Id" = "vv"."CRM Record Id"
WHERE
    "vv"."CRM Record Id" IS NULL
    OR "v"."Id" IS NULL
    OR "v"."ePOS Variant Name" <> "vv"."Name"

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

Deluge Code

@RehmatAli2024

Add Contact Roles to specific Deal in Deluge Send Mail Merge Document with auto merge field from record Get file from Zoho Form and attach in Zoho CRM contact Get Contact Role name with id in Zoho CRM Dynamic signers in Zoho Sign using Zoho writer Mail Merge Template from Zoho CRM Deluge: Update Blueprint Stage in CRM with deluge Searching account in Chart of account in Books with account code Deluge: Search Zoho CRM user with Email Deluge: Trigger workflow rules optional map Deluge: Delete record in Zoho CRM Deluge: Create task in Zoho CRM Deluge: Send email with template id in Zoho CRM Deluge: add record in Zoho Creator subform Deluge: Insert / Create record in Zoho Creator Deluge: Update record in Zoho Creator Deluge: Delete record from zoho creator Search in zoho desk with criteria SQL sub query Inserting in Zoho Analytics table Custom Related List Code Upload image on item in zoho inventory Upload Bulk image to Zoho Inventory Item Api Searching Quote/estimate in books quote number Update values in subform of zoho creator Create Retainer on Quote Accept in Zoho Books Download Attachment in CRM Attaching a file to bill in Zoho books Send Record Template as PDF in Send Mail (Zoho Creator Only) Using Case in Zoho Analytics Create Customer , Item and Invoice if null one line Meeting/Event created from Zoho Bookings

Deluge | addMinutes() function

<variable> = <dateTimeValue>.addMinutes(<numberOfMins>);

Deluge Functions WorkDrive Embed Code

<iframe src="https://workdrive.zohoexternal.com/embed/bjw0e76ce7549f555496596b428147038b51d?toolbar=false&appearance=light&themecolor=green" scrolling="no" frameborder="0" allowfullscreen=true width="800" height="520" ></iframe>

#deluge #zoho #delugescript #creator #workflow

Start-End Date Time

if(input.Start_Date_Time != null && input.Service_Lookup != null)
{
	getServiceDetails = Add_Services[ID == input.Service_Lookup];
	serviceMinutes = getServiceDetails.Service_Time;
	input.End_Date_Time = input.Start_Date_Time.addMinutes(serviceMinutes);
}

Add Contact Roles to specific Deal in Deluge

		contact_map = Map();
		contact_map.put("First_Name	",rep.get("Name1_First"));
		contact_map.put("Last_Name",rep.get("Name1_Last"));
		contact_map.put("Mailing_City",rep.get("Address3_City"));
		contact_map.put("Phone",rep.get("PhoneNumber2"));
		contact_map.put("Mailing_State",rep.get("Address3_Region"));
		contact_map.put("Mailing_Zip",rep.get("Address3_ZipCode"));
		contact_map.put("Mailing_Street",rep.get("Address3_AddressLine1"));
		contact_map.put("Mailing_Street_Line_2",rep.get("Address3_AddressLine2"));
		contact_map.put("Email",rep.get("Email2"));
		contact_map.put("Mailing_Country",rep.get("Address3_Country"));
		contact_map.put("Social_Security_Number",rep.get("SingleLine4"));
		contact_map.put("Passport_Number",rep.get("SingleLine5"));
		contact_map.put("Is_the_legal_representative_an_American_citizen",rep.get("Dropdown3"));
		contact_map.put("Title",rep.get("SingleLine7"));
		contact_map.put("Occupation",rep.get("SingleLine8"));
		contact_map.put("Account_Name",account_id);
		// 		info contact_map;
		create_contact = zoho.crm.createRecord("Contacts",contact_map);
		info create_contact;
		if(create_contact.containKey("id"))
		{
			contact_id = create_contact.get("id");
			//////// Get Roles Ids /////
			// 		get_contact_roles_ids = invokeurl
			// 		[
			// 			url :"https://www.zohoapis.com/crm/v4/Contacts/roles"
			// 			type :GET
			// 			connection:"zoho_crm"
			// 		];
			// 		info get_contact_roles_ids;
			////////////////////////////////////////////////////
			//////////////// Add Contact role to a specific deal ////////
			role_map = Map();
			role_map.put("name","Legal Representative");
			role_map.put("id","6051205000000006871");
			Contact_Role = Map();
			Contact_Role.put("Contact_Role",role_map);
			data = Map();
			data.put("data",Contact_Role.toList());
          //////// 6051205000001977001 is Contact Id /////////
			add_contact_role_to_deal = invokeurl
			[
				url :"https://www.zohoapis.com/crm/v4/Deals/6051205000001947026/Contact_Roles/6051205000001977001"
				type :PUT
				parameters:data.toString()
				connection:"zoho_crm"
			];
			info add_contact_role_to_deal;
        }

Send Mail Merge Document with auto merge field from record

recipient = Map();
recipient.put("type", "email");
recipient.put("value", "roy@gmail.com");
/////////////////////////
signer = Map();
signer.put("recipient_name", "Rehmat");
signer.put("action_type", "sign");
signer.put("recipient", recipient);
//////////////////
signers = List();
signers.add(signer);
///////////////////
mail_merge_template = Map();
mail_merge_template.put("name", "Copy Shareholder Agreement");
//////////////////
sign_mail_merge = Map();
sign_mail_merge.put("file_name", "Agreement");
sign_mail_merge.put("sign_in_order", false);
sign_mail_merge.put("mail_merge_template", mail_merge_template);
sign_mail_merge.put("signers", signers);
///////////
param = Map();
param.put("sign_mail_merge", sign_mail_merge.toList());
///////////
info param;

send_doc = invokeurl
[
	url: "https://www.zohoapis.com/crm/v6/Deals/"+DealID+"/actions/sign_mail_merge"
	type: POST
	parameters: param.toString()
	connection: "zoho_crm"
];

info send_doc;

Download Mail Merge Document and Create Request in Zoho Sign

mail_merge_template = Map();
mail_merge_template.put("name","Copy Shareholder Agreement");
download_mail_merge = Map();
download_mail_merge.put("mail_merge_template", mail_merge_template);
download_mail_merge.put("output_format", "pdf");
download_mail_merge.put("file_name", "Test Shareholder Agreement");

param = Map();
param.put("download_mail_merge", download_mail_merge.toList());

download_pdf = invokeurl
[
	url: "https://www.zohoapis.com/crm/v6/Deals/"+DealID+"/actions/download_mail_merge"
	type: POST
	parameters: param.toString()
	connection:"zoho_crm"
];

info download_pdf;
download_pdf.setParamName("file");

/////////// Send Using Zoho Sign ////////


action_list = List();
action = Map();
action.put("action_type", "SIGN");
action.put("recipient_email", "roy@gmail.com");
action.put("recipient_name", "Roy");
action.put("verify_recipient", true);
action.put("verification_type", "EMAIL");
action_list.add(action);
request = Map();
request.put("request_name", "Test Shareholer Agreement");
request.put("is_sequential", false);
request.put("actions", action_list);
request.put("email_reminders", true);
request.put("reminder_period", 2);
requests = Map();
requests.put("requests", request);
data = Map();
data.put("data", requests);
info data;
/////
send_doc = invokeurl
[
	url: "https://sign.zoho.com/api/v1/requests"
	type: POST
	parameters: data
	files: download_pdf
	connection: "zoho_sign"
];

info send_doc;

Get file from Zoho Form and attach in Zoho CRM contact

// path = "https://forms.zoho.com/api/form/OnboardingForm/formperma/RcHkbHGBh8_5yjVX6sSfhb2OfIHid-3SyJAZVPOuZns/resthookdownload?filepath=/OnboardingForm/FileUpload11/1715686347747_Legal_Rep_Rep1_first_Proof_of_address.png";

passport_files = rep.get("FileUpload10");
			for each  file in passport_files
			{
				path = file.get("path");
				getFile = invokeurl
				[
					url :path
					type :GET
					connection:"zoho_form"
				];
				info "Getting file = " + getFile;
				getFile.setParamName("file");
				attach_file = invokeurl
				[
					url :"https://www.zohoapis.com/crm/v3/Contacts/" + contact_id + "/Attachments"
					type :POST
					files:getFile
					connection:"zoho_crm"
				];
				info "Attaching files = " + attach_file;
			}

Get Contact Role name with id in Zoho CRM

getRoles = invokeurl
	[
	url: "https://www.zohoapis.com/crm/v3/Contacts/roles/"+contact.get("Contact_Role")
	type: GET
	connection:"zoho_crm"
	];
	info getRoles;
	contact_role_name = getRoles.get("contact_roles").get(0).get("name");

Dynamic signers in Zoho Sign using Zoho writer Mail Merge Template from Zoho CRM

// 1. You need to use tags in the Template
/// Here is link for tags: https://help.zoho.com/portal/en/kb/zoho-sign/user-guide/sending-a-document/articles/automatic-field-addition-in-zoho-sign

dealLDetails = zoho.crm.getRecordById("Deals",DealID);
// info dealLDetails;
// getAllFieldsResponse = invokeurl
// [
// 	url :"https://writer.zoho.com/api/v1/documents/flo7ld4ba09726a694424994054acbb636e81/fields"
// 	type :GET
// 	connection:"zoho_crm"
// ];
// info getAllFieldsResponse;
fieldTextData = Map();
fieldTextData.put("Deal_Name",dealLDetails.get("Deal_Name"));
fieldTextData.put("Stage",dealLDetails.get("Stage"));
fieldTextData.put("Owner",dealLDetails.get("Owner").get("name"));
contact_roles = zoho.crm.getRelatedRecords("Contact_Roles","Deals",DealID);
// info contact_roles;
contact_List = List();
count = 0;
signerList = List();
for each  contact in contact_roles
{
	count = count +1;
	name = contact.get("Full_Name");
	contact_map = Map();
	contact_map.put("Contact_Roles.Full_Name",name);
	contact_map.put("Contact_Roles.First_Name", count);
	signerMap = Map();
	signerMap.put("recipient_"+count, contact.get("Email"));
	signerMap.put("action_type", "sign");
	signerMap.put("language","en");
	contact_List.add(contact_map);
	signerList.add(signerMap);
}
fieldTextData.put("Contact_Roles",contact_List);
data_mp = Map();
data_mp.put("data",fieldTextData);
params = Map();
params.put("merge_data",data_mp);
params.put("service_name","zohosign");
params.put("signer_data",signerList);
params.put("sign_in_order","true");
// optional
params.put("message","Please find the document to be signed here");
//optional
params.put("reminder_period","15");
//optional
params.put("set_expire","30");
//optional
documentid = "flo7ld4ba09726a694424994054acbb636e81";
templatename = "Test Shareholder Agreement";
params.put("filename",templatename);
url = "https://zohoapis.com/writer/api/v1/documents/" + documentid + "/merge/sign";
info params;
/////////////////////////////
documentObj = invokeurl
[
	url :url
	type :POST
	parameters:params
	connection:"zoho_crm"
];
info documentObj;

Deluge: Update Blueprint Stage in CRM with deluge

		blueprint_details = invokeurl
		[
			url: "https://www.zohoapis.com/crm/v5/Deals/"+DealID+"/actions/blueprint"
			type: GET
			connection:"zoho_crm"
		];
	// 	info blueprint_details;
		blueprint = Map();
		blueprint.put("transition_id","6051205000002261178");
	// 	blueprint.put("data",dataMap);
		blueprintList = List();
		blueprintList.add(blueprint);
		param = Map();
		param.put("blueprint",blueprintList);
		update_blueprint_stage = invokeurl
		[
		url :"https://www.zohoapis.com/crm/v5/Deals/" + DealID + "/actions/blueprint"
		type :PUT
		parameters:param.toString()
		connection:"zoho_crm"
		];
		info update_blueprint_stage;

Searching account in Chart of account in Books with account code

	accountCode = 202;
	searchMap = Map();
	searchMap.put("account_code",accountCode);
	///
	fetch = zoho.books.getRecords("chartofaccounts","20091181966",searchMap,"zoho_books");
	info fetch;

Deluge: Search Zoho CRM user with Email

criteria = "email:equals:"+vendor_email;
response = zoho.crm.searchRecords("users", criteria);
info response;

Deluge: Trigger workflow rules optional map

options = map();
optionlist = list();
optionlist.add("workflow");
options.put("trigger",optionlist);
updateRec= zoho.crm.updateRecord(module_name, updatemap, options, connection);

Deluge: Delete record in Zoho CRM

deleteRecordMap = Map();
deleteRecordMap = {"module":"Leads","id":leadId};
deleteResp = zoho.crm.invokeConnector("crm.delete",deleteRecordMap);
info deleteResp;

Deluge: Create task in Zoho CRM

task_map = Map();
task_map.put("Subject","Contact nick name is missing.");
task_map.put("Description","Please add the nickname of the selected contact for this task, so that the system can send a notification about the unchanged deal within 90 days to this contact.");
task_map.put("Deal_Priority",true);
task_map.put("Who_Id",contact_Id);
task_map.put("Due_Date",zoho.currentdate.addDay(5));
task_map.put("Priority","? Normal (THIS WEEK)");
task_map.put("What_Id",deal_Id);
task_map.put("Owner",deal_owner);
task_map.put("$se_module","Deals");
create_name_task = zoho.crm.createRecord("Tasks",task_map);
info "Creating nickname missing task = " + create_name_task;

Browse more snippets >>