I am writing a web-api to query our employee table. To that end, I want to be able to query an employee based on a case-insensitive search of the email address.
Here's my queryFilter that I am using...
filter: a!queryFilter( field: "Email", operator: "=", value: upper(http!request.queryParameters.emailAddress) )
Note that at the moment, though I am converting the incoming parameter to upper, what I was wanting to do is [somehow] produce a match between the Email field and the http!request.queryParameters.emailAddress parameter regardless of any differences in case.
So, if the EMPLOYEE.Email field has "Joe.Smith@myorg.com.au", then I want this to match against http!request.queryParameters.emailAddress values of:
"joe.smith@myorg.com.au" or "JOE.SMITH@myorg.com.au" or "JOE.SMITH@MYORG.COM.AU" and so on.
Is there a way to do this in Appian? What I'm looking for is either a comparison operator that does a case-insensitive comparison, or some way to [also] convert the Email field to upper to match the upper'd value of http!request.queryParameters.emailAddress.
Any help with this is much appreciated. This *should* be quite simple, but I've been struggling to find a way to do it.
thanks heaps,
David.
Discussion posts and replies are publicly visible
AFAIK queryFilter() does a case-insensitive filtering. Have you tried value as just http!request.queryParameters.emailAddress? without any function like upper() etc. The filter should work properly.
Dear David,
a!query( filter: a!queryFilter( field: "email", operator: "=", value: http!request.queryParameters.email ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: 50 ) )
I tried the above syntax where it was not giving the results, hence I changed the operator to have "contains" rather "=". This time I got the results, then I switched back to "=" operator then it worked.
Ideally the above mentioned behavior is not expected rather it should work with the "=" operator. Also I would suggest if this does not work, you could return the email as Upper case in the record and search with upper(http!request.queryParameters.emailAddress)