Hello,
We are running a query on Synced Record data using a!queryRecordType where we need to filter based on a list of Text. This is the query filter we are using:
a!queryFilter( field: 'recordType!recordName.fields.email', operator: "in", value: ri!email )
The record field is type Text and contains users' emails, which vary in how they are capitalized. The rule input ri!email is a list of Text String. When we pass in only a few items to that rule input we are able to return the expected results and we do see the records being returned with the email field matched in a case-insensitive manner. However, in our implementation we need to search on a large number of emails where the capitalization is not consistent across data sources. When we pass in 173 items to the ri!email rule input, we do not see the results being returned as expected with the email field matched in a case-insensitive manner. Then, if we update that 173-item list to make the email input match the capitalization of the values stored in the record, we do see those records returned from the query.
It seems that this must be because of some limitation, as the only change we make between the query that does return the expected case-insensitive result and the query that does not is changing the capitalization of the input value to match the record field's value. Is there some upper limit of list size that the queryRecordType function's queryFilter on a synced record is able to handle in a case-insensitive manner when using the IN operator on a Text field? If so, what is it and is there a workaround?
Discussion posts and replies are publicly visible
It’s unexpected to see this behaviour, but a reliable workaround is to maintain an additional column in record(Custom Field) with lowercase email values and perform the comparison by converting the input list to lowercase as well.This ensures consistent and case-insensitive matching, even with large input lists, while maintaining optimal query performance.
May be create a custom record field which applies function like upper () and stores email ids in upper case. Then use that custom record field in the filter condition. In the value also do an upper(ri!email) so that entire data is in uppercase to filter the data. I mentioned upper() as example same can be achieved by changing the email in custom record field in record and value in lowercase also.
Hi nicholasf4543 Try creating a Custom Record Field that stores the lowercase version of the email. For example: lower(email)
Then, in your a!queryFilter(), also convert the incoming values to lowercase using: a!queryFilter( field: 'recordType!recordName.fields.email', operator: "in", value: a!forEach(items: ri!email, expression: lower(fv!item)))
a!queryFilter()
Custom record fields are evaluated and stored at sync time, so the lowercase email will be consistently available for filtering.Since both the record data and input list are converted to lowercase, the "in" operator will behave case-insensitively, even for large lists.This ensures consistent case-insensitive matching even for large lists and avoids relying on database behavior.
"in"
Thanks all, we did create a custom record field for lowercase emails and input lower() values to the query and that did appear to resolve our issue. We were still wondering about the reason for this behavior so we can better know when to implement a similar workaround in the future.