Zoho Creator: Retrieve record with case-insensitive query

: Category: Zoho Creator; 21 November 2019; 21 November 2019; Hits: 28481; Rating:

( 913 Ratings )

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.

copy raw

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;
}

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:

copy raw

...
l_ProductDetails = Product[Product_Name.toLowerCase() = v_Name.toLowerCase()];
...

...
l_ProductDetails = Product[Product_Name.toLowerCase() = v_Name.toLowerCase()];
...

Returns the error: "Expression is not allowed in left side of a Criteria".

There used to be an old solution for doing this and can still be found in the community forums from a 7 year old post; but now you can apply a containsIgnoreCase function to search the records:

copy raw

...
l_ProductDetails = Product[Product_Name.containsIgnoreCase(v_Name)];
...

...
l_ProductDetails = Product[Product_Name.containsIgnoreCase(v_Name)];
...

Lastly, there is a function I find myself using more and more called equalsIgnoreCase() which could be used:

copy raw

...
l_ProductDetails = Product[Product_Name.equalsIgnoreCase(v_Name)];
...

...
l_ProductDetails = Product[Product_Name.equalsIgnoreCase(v_Name)];
...

Other Functions of Interest

contains case-sensitive search, can be used on strings or lists
containsIgnoreCase case-insensitive search
endsWith
endsWithIgnoreCase
equals
equalsIgnoreCase
matches
notContains though I tend to use !myVar.contains()
startsWith
startsWithIgnoreCase used in fuzzy logic searches to broaden the resultset
containKey Applies to maps only
containValue Applies to maps only