Zoho Creator: Retrieve record with case-insensitive query

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:
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.
v_Name = "Myproduct1";  // note the lowercase P
l_ProductDetails = Product[Product_Name == v_Name];
b_Exists = if(l_ProductDetails.count() > 0, true, false);
if(!b_Exists)
{
	// insert into Product table
	r_ProductCreate = insert into Product
	[
		Product_Name=v_Name
		Added_User=zoho.loginuser
	];
	v_ProductID = r_ProductCreate.ID;
}
else
{
	// DON'T insert into Product table
	v_ProductID = l_ProductDetails.ID;
}

What Happens:
  • In the example above, the count to see if "Myproduct1" exists counts as zero (0). It doesn't think the product exists because it is searching with case-sensitivity enabled.
  • It tries to insert into the product table, but returns the error "Result: Error on Execution" > "Error details: Execution Failed Due to invalid expressions insert statement is terminated Line:(99)" > "Duplicate entry 'MyProduct1' for key 'Product_Name' Line:(99)".
This indicates that the search to see if the product exists is case-sensitive, but when trying to insert the product, it is case-insensitive.

How?
Attempt 1 to lowercase both sides of the operation in the record retrieval also fails:
...
l_ProductDetails = Product[Product_Name.toLowerCase() = v_Name.toLowerCase()];
...
Returns the error: "Expression is not allowed in left side of a Criteria".

A working solution was found in the community forums from a 7 year old post where you can apply a containsIgnoreCase function to search the records:
...
l_ProductDetails = Product[Product_Name.containsIgnoreCase(v_Name)];
...

Almost there
The above doesn't account for if the new product is not exactly the same name despite being case-insensitive and containing the search string. So if I try to insert a product called "product", the system will say this product exists despite the matching record(s) being "MyProduct1", and all the records in the above example because they all contain the word "product". So we have to loop through and check if the product name matches irrespective of case in the usual manner (lower-casing both values). Here's the above function rewritten that will do what we want:
v_Name = "Myproduct1";
b_Exists = false;
for each r_ProductDetails in Product[Product_Name.containsIgnoreCase(v_Name)]
{
	if(v_Name.toLowerCase() == r_ProductDetails.Product_Name.toLowerCase())
	{
		v_ProductID = r_ProductDetails.ID;
		b_Exists = true;
		break;
	}
} 
if(!b_Exists)
{
	// insert into Product table
	r_ProductCreate = insert into Product
	[
		Product_Name=v_Name
		Added_User=zoho.loginuser
	];
	v_ProductID = r_ProductCreate.ID;
}

Source(s):

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

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 - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.