For Zoho Services only:


I'm actually part of a bigger team at Ascent Business Solutions where we have support technicians and project consultants. Support is for smaller technical fixes but this can include developments, reports or integrations; depending on the size of the task. Projects are for more time-consuming developments such as revamps of the Zoho Suite of apps or on-site training. The advantage of a team is that if I am out-of-office for a day or so, there is always someone at Ascent Business Solutions who can deal with any queries/issues you may have.

Our support rates can be found and purchased at http://ascentbusiness.co.uk/zoho-support-2. A support bundle doesn't have an expiry date. So whether we can do what you want within the bundle and a year later need further support, if there are minutes left on the bundle then there is no additional charge.

Our project rates for bigger developments can be found at http://ascentbusiness.co.uk/crm-solutions/zoho-crm-packages-prices and will involve a dedicated project consultant along with developers who will hold your hand through the development process.

If you want help building a solution for one of the Zoho Apps in the Zoho Suite, contact us on 0121 293 8140 (UK) or by email at info@ascentbusiness.co.uk. You can also visit our website at http://ascentbusiness.co.uk.

I regularly build and specialize in 2-way API integrations for Xero, Shopify and eBay.

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:
  • 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:
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
  • 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

Source(s):
Category: Zoho :: Article: 696

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

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.