Print

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:
copyraw
Product_Name   Product_SKU   
-------------- -------------
MyProduct1     TEST01
Myproduct2     TEST02
myproduct3     TEST03
  1.  Product_Name   Product_SKU 
  2.  -------------- ------------- 
  3.  MyProduct1     TEST01 
  4.  Myproduct2     TEST02 
  5.  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.
copyraw
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;
}
  1.  v_Name = "Myproduct1";  // note the lowercase P 
  2.  l_ProductDetails = Product[Product_Name == v_Name]
  3.  b_Exists = if(l_ProductDetails.count() > 0, true, false)
  4.  if(!b_Exists) 
  5.  { 
  6.      // insert into Product table 
  7.      r_ProductCreate = insert into Product 
  8.      [ 
  9.          Product_Name=v_Name 
  10.          Added_User=zoho.loginuser 
  11.      ]
  12.      v_ProductID = r_ProductCreate.ID; 
  13.  } 
  14.  else 
  15.  { 
  16.      // DON'T insert into Product table 
  17.      v_ProductID = l_ProductDetails.ID; 
  18.  } 

What Happens: 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:
copyraw
...
l_ProductDetails = Product[Product_Name.toLowerCase() = v_Name.toLowerCase()];
...
  1.  ... 
  2.  l_ProductDetails = Product[Product_Name.toLowerCase() = v_Name.toLowerCase()]
  3.  ... 
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:
copyraw
...
l_ProductDetails = Product[Product_Name.containsIgnoreCase(v_Name)];
...
  1.  ... 
  2.  l_ProductDetails = Product[Product_Name.containsIgnoreCase(v_Name)]
  3.  ... 

Lastly, there is a function I find myself using more and more called equalsIgnoreCase() which could be used:
copyraw
...
l_ProductDetails = Product[Product_Name.equalsIgnoreCase(v_Name)];
...
  1.  ... 
  2.  l_ProductDetails = Product[Product_Name.equalsIgnoreCase(v_Name)]
  3.  ... 

Other Functions of Interest
Source(s):
Category: Zoho :: Article: 696