CRM - Joel Lipman .Com

Zoho Creator: Retrieve record with case-insensitive query

: Joel Lipman; Zoho; 21 November 2019; Hits: 4492

What?
Thought I'd put an article here to remind me how to make the retrieval of a record case-insensitive.

Why?
Consider that I have the following creator table:

copy raw

Product_Name   Product_SKU   
-------------- -------------
MyProduct1     TEST01
Myproduct2     TEST02
myproduct3     TEST03

Product_Name Product_SKU
-------------- -------------
MyProduct1 TEST01
Myproduct2 TEST02
myproduct3 TEST03

I'm trying to insert a record for a new product if it doesn't exist in the table but if it does exist then to simply skip adding the product.

Category: Zoho :: Article: 696

: Joel Lipman; Zoho; 27 September 2019; Hits: 6694

What?
An article on setting a date field to either the first Monday of the next month or to the last Tuesday of the current/next month. What I mean by the last Tuesday is if the last Tuesday of the month is before the current date, then set it to the last Tuesday of next month.

Why?
This was a request by a client who instead of specifying the 1st of every month, or 15th of each month, to say the first Monday of the month (ignoring bank holidays) or to say the last Tuesday of the month.

How?
Quite easily. For the first Monday of the month, it is likely that even if today was the first day of the month and coincidentally a Monday, then we would want to specify the field date to be the first Monday of the next month. If however we want the last Tuesday of the month and that Tuesday happens before today, then we want the last Tuesday of the next month.

: Joel Lipman; Zoho; 02 September 2019; Hits: 4228

What?
A quick article on how to count within a map. I didn't have too much difficulty getting this to work and I'm not sure if there are better ways of counting so I'm documenting it to see if I can refine the code or find a some short codes that will do the same.

Why?
I'm aiming to automate a process which counts the number of products allocated to an account or in total for a purchase order. Let us assume we have the following in a form/report called "Stock_Upload":

copy raw

RowID	Name	            SKU	        Account Email	
0001	Test Product 1	    TEST001	    This email address is being protected from spambots. You need JavaScript enabled to view it.	
0002	Test Product 1	    TEST001	    This email address is being protected from spambots. You need JavaScript enabled to view it.	
0003	Test Product 1	    TEST001	    This email address is being protected from spambots. You need JavaScript enabled to view it.	
0004	Test Product 1	    TEST001	    This email address is being protected from spambots. You need JavaScript enabled to view it.	
0005	Test Product 2	    TEST002	    This email address is being protected from spambots. You need JavaScript enabled to view it.	
0006	Test Product 1	    TEST001	    This email address is being protected from spambots. You need JavaScript enabled to view it.	
0007	Test Product 1	    TEST001	    This email address is being protected from spambots. You need JavaScript enabled to view it.

RowID Name SKU Account Email
0001 Test Product 1 TEST001 This email address is being protected from spambots. You need JavaScript enabled to view it.
0002 Test Product 1 TEST001 This email address is being protected from spambots. You need JavaScript enabled to view it.
0003 Test Product 1 TEST001 This email address is being protected from spambots. You need JavaScript enabled to view it.
0004 Test Product 1 TEST001 This email address is being protected from spambots. You need JavaScript enabled to view it.
0005 Test Product 2 TEST002 This email address is being protected from spambots. You need JavaScript enabled to view it.
0006 Test Product 1 TEST001 This email address is being protected from spambots. You need JavaScript enabled to view it.
0007 Test Product 1 TEST001 This email address is being protected from spambots. You need JavaScript enabled to view it.

The code is a for loop which iterates through each row, assigning an entry for a new product or account, and specifying the quantity for each.

How?
So depending on the number of levels, the count will be with a series if then else statements. See the results/yield section to determine which best fits your scenario:

Category: Zoho :: Article: 693

: Joel Lipman; Zoho; 22 August 2019; Hits: 7160

What?
This is a reminder on how to swap out a product in an invoice.

Why?
A client wanted one of their products to be swapped out with another as they had a 3rd-party feed that kept creating a product that they didn't stock.

How?

: Joel Lipman; Zoho; 20 August 2019; Hits: 5300

What?
A quick reminder on when I want to apply overall Tax or remove Tax from a quotes product line items.

Why?
I would apply the standard update function and although the response would say it modified the record, the tax wouldn't apply both visually and programmatically.

How?
A little undocumented this "feature" but the gist is if you want to add tax, it has to be built up in the tax options, if you want this to be zero, you need to pass the option of tax as zero and remove all other options.

: Joel Lipman; Zoho; 17 July 2019; Hits: 9768

What?
A sorta quick article to note how I can generate refresh tokens and access tokens using Zoho Deluge code (so within Zoho Creator, CRM or Books) without XML calls.

Why?
I find myself using this more and more now that API v1 is on the way out and Zoho Deluge by itself is limited somewhat with regards to functionality and record editing when compared to API v2.

How?
Note that this is using Zoho Deluge and not another server-side script such as PHP to send the requests via API.

: Joel Lipman; Zoho; 28 June 2019; Hits: 3644

What?
A quick reminder on how I got a working snippet of code to store the entries in the lookup already selected, check against another table/form for valid options, repopulate the list with the selected item and filtering the options available for selection.

Why?
This was for a multi lookup with staff as the list entries. The lookup needed to filter against various forms such as Staff Rotas, Leave Requests, Qualifications and some others.

How?
The aim/objective is the following:

Store what was already selected in the lookups
Clear the lookups list
Restore what was originally selected as selected.
Repopulate with a filter list of options (not selected but available)

: Joel Lipman; Zoho; 11 June 2019; Hits: 4240

What?
A quick article to document how to calculate the distance "as the crow files" between two coordinates given the latitude and longitude of each.

Why?
Well, you can pay for a good Google solution that will distance following roads and the such but.. the following is free albeit as-the-crow flies.

This makes use of an undocumented function in Zoho which is acos or inverse cosine. Just lifted from the JavaScript library figuring if it supports cos() then try this standard function. Note this has only been tested in Zoho Creator rather than Zoho CRM.

How?
This function returns the distance as the crow flies.

: Joel Lipman; Zoho; 03 June 2019; Hits: 5370

What?
A quick article just as a note to get the users country information from a third-party API based on the given IP address.

How?
Well this is just a list of free 3rd-party APIs and the below code is dependent on these being online and working...

: Joel Lipman; Zoho; 31 May 2019; Hits: 17326

What
A more comprehensive post on some other regex (regular expressions) to format values in Zoho.

How?

: Joel Lipman; Zoho; 09 May 2019; Hits: 4909

What?
This is a quick article on how to generate a 5 letter code from several functions: one which returns 5 randomly selected characters and another two which convert a number to 5 letters.

Why?
Zoho doesn't have a function to generate random numbers or strings. There are workarounds however.

How?

: Joel Lipman; Zoho; 08 April 2019; Hits: 8424

What?
This is a quick article on different ways to get the current user ID in Zoho through code.

How?
I'm not a fan of method #1 but I only started developing in Zoho recently when API v1 was already being deprecated (end of life at the end of this year). So I'm familiar with the methods in API v2 or just using newer functions.