
Dynamically Insert Subform Rows and Add Picklist Values in Zoho Creator
This set up allows you to insert subform rows and add to picklist dynamically with values unique to each row on a Zoho Creator subform.
SELECT
/* --- This first instance is for all the new leads in the system --- */
"Full Name" 'Name',
"Created Time" 'Date',
"Lead Source",
/* --- If you do not already have it as a field in the Leads module, create the "Link to CRM" here using the CONCAT function --- */
"Link to CRM",
"Id" 'Record ID',
'New Leads' 'Category'
FROM "Leads"
UNION ALL
SELECT
/* --- This second instance is for CONVERTED leads, essentially, new contacts --- */
"Full Name" 'Name',
"Created Time" 'Date',
"Lead Source",
concat('YOUR_CONTACTS_BASE_URL', "Id"),
"Id" 'Record ID',
'Converted Leads' 'Category'
FROM "Contacts"
UNION ALL
SELECT
c."Full Name" 'Name',
d."Created Time" 'Date',
c."Lead Source",
concat('YOUR_DEALS_BASE_URL', d."Id"),
d."Id" 'Record ID',
'New Deals' 'Category'
FROM "Deals" d
JOIN "Contacts" c ON d."Contact Name" = c."Id"
UNION ALL
SELECT
c."Full Name" 'Name',
d."Closing Date" 'Date',
c."Lead Source",
concat('YOUR_DEALS_BASE_URL', d."Id"),
d."Id" 'Record ID',
'Closed Deals' 'Category'
FROM "Deals" d
JOIN "Contacts" c ON d."Contact Name" = c."Id"
WHERE d."Stage" LIKE 'Closed Won'
Click here to copy these scripts. For more information on Analytics, read through our blog post on apps that integrate with Zoho Analytics, as well as our Tableau and Analytics comparison. And, for more Zoho-wizardry, check out our GitHub page.
Build a SQL table which makes for easy business analysis in Zoho Analytics. Create a Sales Funnel chart with this data.
SELECT
/* --- This first instance is for all the new leads in the system --- */
"Full Name" 'Name',
"Created Time" 'Date',
"Lead Source",
/* --- If you do not already have it as a field in the Leads module, create the "Link to CRM" here using the CONCAT function --- */
"Link to CRM",
"Id" 'Record ID',
'New Leads' 'Category'
FROM "Leads"
UNION ALL
SELECT
/* --- This second instance is for CONVERTED leads, essentially, new contacts --- */
"Full Name" 'Name',
"Created Time" 'Date',
"Lead Source",
concat('YOUR_CONTACTS_BASE_URL', "Id"),
"Id" 'Record ID',
'Converted Leads' 'Category'
FROM "Contacts"
UNION ALL
SELECT
c."Full Name" 'Name',
d."Created Time" 'Date',
c."Lead Source",
concat('YOUR_DEALS_BASE_URL', d."Id"),
d."Id" 'Record ID',
'New Deals' 'Category'
FROM "Deals" d
JOIN "Contacts" c ON d."Contact Name" = c."Id"
UNION ALL
SELECT
c."Full Name" 'Name',
d."Closing Date" 'Date',
c."Lead Source",
concat('YOUR_DEALS_BASE_URL', d."Id"),
d."Id" 'Record ID',
'Closed Deals' 'Category'
FROM "Deals" d
JOIN "Contacts" c ON d."Contact Name" = c."Id"
WHERE d."Stage" LIKE 'Closed Won'
Click here to copy these scripts. For more information on Analytics, read through our blog post on apps that integrate with Zoho Analytics, as well as our Tableau and Analytics comparison. And, for more Zoho-wizardry, check out our GitHub page.
This set up allows you to insert subform rows and add to picklist dynamically with values unique to each row on a Zoho Creator subform.
Learn to create a customized inventory report with Zoho Analytics. This tutorial contains a link to our GitHub page for SQL code that will help with your table creations.
How nice would it be if you could, at the press of a button, send clients an email w/a Zoho Books invoice? Replete with “Pay Now” buttons that link to PayPal/Stripe/other payment gateways?
Whether you work primarily out of CRM or the Zoho Finance Suite, you can use Analytics to build commissions dashboards. This involves some fairly simple SQL code.
If you are an inventory manager, this blog post could change your life. If you are not, it will at least teach you how to build some wicked inventory tracking for your business.
Convert fields, related activities, attachments, notes and more from one record to another across modules via custom function.