Get Records with a query condition of "like" not returning all available results in the response

1

When I send an API JSON request for get records of the following:

 

{“app”:“7”,“query”:“Text_EmailAddress like " sshadler “”,“fields”:[”$id",“Text_EmailAddress”,“Text_LastName”,“Text_FirstName”]} 

 

I get the following respond from the API, two successful finds:

 

{“records”:[{“Text_EmailAddress”:{“type”:“SINGLE_LINE_TEXT”,“value”:“sshadler@yahoo.com”},“Text_FirstName”:{“type”:“SINGLE_LINE_TEXT”,“value”:“Scott”},“Text_LastName”:{“type”:“SINGLE_LINE_TEXT”,“value”:“Shadler”},"$id":{“type”:"__ID__",“value”:“30”}},{“Text_EmailAddress”:{“type”:“SINGLE_LINE_TEXT”,“value”:“sshadler@rplaw.com”},“Text_FirstName”:{“type”:“SINGLE_LINE_TEXT”,“value”:“Scott”},“Text_LastName”:{“type”:“SINGLE_LINE_TEXT”,“value”:“Shadler”},"$id":{“type”:"__ID__",“value”:“21”}}],“totalCount”:null}

 

However, if I just remove one letter at the end of the search string qualifier from  sshadler  to  shadle , such as:

 

{“app”:“7”,“query”:“Text_EmailAddress like " sshadle “”,“fields”:[”$id",“Text_EmailAddress”,“Text_LastName”,“Text_FirstName”]}

 

I get no results:

 

{“records”:[],“totalCount”:null}

 

Why are the results of a “like” condition in text search with a get records request not consistently returned expected response?

2

Hello Scott!

This is because of the search limitation in kintone that you can only search by full word, not partial word, even when you are using the “like” operation.

For example, when you are searching for a record that has a word “kintone”, you need to type the whole word “kintone” in order to get the expected results. You cannot get the same result by such as “kint”, “kinton”, etc…

In your case, I think the “@” symbol became the delimiter so “sshadler” turns out be a one word. So that’s why when you search by “sshadler” the result turns out fine, but when you search by “sshadle” you won’t get any result because there is no record with email address that has the word “sshadle”.

I hope this helps.

3

Since there is a limitation on returning up to 500 records in a GET records action;

How would you recommend searching a filtering on a partial word search to reduce the number of records?

4

Hello Scott!

As of now, a method that I can suggest is the following:

  1. Using the REST API, get all records information (may be easier if you can specify the field or filter at this point)

  2. Put the specific field information from the obtained data into array.

  3. Use the JS function (such as “match” or “indexOf”) to search

  4. Obtain the key item from the search result and put into array.

(Repeat 3 and 4 until the records are gone)

  1. Using the array information created from the step 4., create an URL query

I hope this helps!