GET Requests – Searchstrings

Overview

This article provides technical details on how to filter the data records returned when making a GET call to the CIMcloud API. The API has a very powerful search mechanism that allows you to perform simple or complex SQL-where-style searches when pulling data from the API. This provides tremendous flexibility when getting data from the API. We call the filtering commands that the API supports “search strings” because they perform a “search”-like action on the data returned from the API by passing commands (when you call the API to perform a GET) via query”string” variables that are appended to the API’s endpoint URL.

Explanation / Examples

Endpoint URL Format for API Calls

This is the overall format of the API endpoint.

https://api.cimcloud.com/{sitename}/{resource}/{searchstring}/{page_number}
The {searchstring} portion is optional and is the topic of this article.

Reference the API Framework/Technical Overview article for technical details on the CIMcloud API.

Examples of How the Searchstring Is Used

These are two quick examples showing how a searchstring can be passed to the customer accounts endpoint to filter the results returned to only those that records match the criteria passed.

Simple Example

Endpoint Call
- https://api.cimcloud.com/{sitename}/accounts/searchlike~nm~manufacturing
Result
- This will return all customer accounts with “manufacturing” in the company name.

Complex Example

Note
- This uses “and“, “or“, parenthesis “( commands in here )” for order of operations, the date function, and the dateadd function.
Endpoint Call
- https://api.cimcloud.com/{sitename}/accounts/searchlike~nm~manufacturing|and|(searchbetween~create_date~<<dateadd:days:-10>>~<<date>>|or|searchgreatherthanequal~last_modify_date~1/1/2021)
Result
- This will return all customer accounts that meet both of these conditions:
  - have “manufacturing” in the company name,
  - and meet one (or both) of these two date-related criteria
    - was created in the past 10 days, or
    - was modified on or after 1/1/2021

Searchstring (Filtering) Commands

The following “searchstring” commands can be passed in the URL when calling API GETs to filter the records returned.

searchexact finds rows that = the value
- Pattern: searchexact~fieldname~value
- Example: searchexact~nm~bob
- SQL: nm = ‘bob’
searchlike finds rows that match the pattern, % is a wild card field
- Pattern: searchlike~<field>~<value>
- Example: searchlike~nm~cimcloud
- SQL: nm like ‘%cimcloud%
searchlist – finds rows that match list of values
- Pattern: searchlist~fieldname~value1~value2~value3~etc…
- Example: searchlist~nm~a~b~c~d~e
- SQL: nm in (‘a’,’b’,’c’,’d’,’e’)
  - Note: You can specify an unlimited number of values
searchleft finds rows where the first x fields = the value
- Pattern: searchleft~<field>~<length>~<value>
- Example: searchleft~nm~1~w
- SQL: left(nm,1) = ‘w’
searchright finds rows where the last x fields = the value
- Pattern: searchright~<field>~<length>~<value>
- Example: searchright~nm~1~w
- SQL: right(nm,1) = ‘w’
searchgreatherthanequal finds rows >= the value
- Pattern: Searchgreatherthanequal~fieldname~value
- Example: Searchgreatherthanequal ~price~10
- SQL: price >= 10
searchlessthanequal finds rows <= the value
- Pattern: Searchlessthanequal~fieldname~value
- Example: Searchlessthan~price~10
- SQL: price <= 10
searchlessthan finds rows < the value
- Pattern: Searchlessthan~fieldname~value
- Example: Searchlessthan~price~10
- SQL: price < 10
searchflagon is the bit position on
- Pattern: searchflagon~fieldname~position
- Example: searchflagon~flags~20
- SQL: isnull(flags,0) = isnull(flags,0) & power(2,20)
searhflagoff is the bit position off
- Pattern: searchflagoff~fieldname~position
- Example: searchflagoff~flags~20
- SQL: isnull(flags,0) & Power(2, 20) = POWER(2, 20)
isdate Is the value in the field a valid date (regardless of the field database type)
- Pattern: isdate~fieldname
- Example: isdate~nm
- SQL: isdate(nm) = 1
isnumeric Is the value in the field a numeric value (regardless of the field database type)
- Pattern: isnumeric~fieldname
- Example: isnumeric~nm
- SQL: isnumeric(nm) = 1
isnull is the value NULL
- Pattern: isnull~fieldname
- Example: isnull~nm
- SQL: nm is null
searchbetween finds rows where the field is between 2 values (can be date or numeric)
- Pattern: searchbetween~fieldname~startdate~enddate
  - You can also use db fields as the start & end date.
  - Example: searchbetween~date~1/01/2007~10/02/2007
- SQL: date between ‘1/01/2007′ to ’10/02/2007’
  - Note a date without a time is considered in SQL as midnight. You will need to add 1 to the date to include the “to date”
  - For example: between ‘1/01/2007′ to ’10/02/2007′. Searches between 1/01/2007 and end of day ’10/1/2007’. If you want to include the entire day of ’10/2/2007′ you have to set it to ’10/3/2007′ or ’10/2/2007 11:59:59′

Searching with Number Values

By default the search string assumes the value you are passing is text. You can override this by putting a @ before the value. The only exception is the position for ‘searchflagon’ and ‘searchflagoff’ it’s assumed to be a numeric value.

For example if you want to pass the value 1000 into a searchexact
- searchexact~price~@1000

Using ‘Not’

The word ‘not’ can be appended to any of the search commands to perform a “not” search.

For example, the searchexact command can be replaced with searchexactnot
Example
- Find all products where the SKU field does not contain “FF”
- Assuming the table contains item numbers
  - FF3198
  - GA50998
  - EE543238
Command
- searchlikenot~sku~FF

Explicit Grouping

Allows you to explicitly group search commands together, including using “add”, “or”, & parenthesis (for order of operations).

When using “and” and “or”, they need to start and end with a pipe = “|”.

Example format
- command1|and|command2
- command1|or|command2
Example using parenthesis for order of operations
- (command1|or|command2)|and|command3

Examples of the entire command passed in the API call:

(searchlike~p.c_l_nm~ward|and|searchlike~p.c_f_nm~eric)
((searchlike~p.c_l_nm~aaa|and|searchlike~p.c_f_nm~jj)|or|(searchexact~p.username~333|and|searchexact~p.username~ee))|and|searchexact~p.nm~pppp

Functions Commands

Function commands go into the value element of any command, starting with “<<” and ending with “>>”.

Format (replacing the value in any command): <<FunctionName>>
Format (if the function requires parameters to be passed in): <<FunctionName:Paramaters>>

The following functions are supported (the function name is in bold):

dateadd – Adds to (or subtracts from) the current date
- Function name = dateadd
- Parameters format = interval type:interval number
  - interval types
    - Minutes – min
    - Hours – hours
    - Days – days
    - Weeks – weeks
    - Months – months
    - Quarters – quarters
    - Year – year
  - interval number = the qty to add or subtract (can be a negative or positive number)
- Examples that would replace the value in any command
  - <<dateadd:days:1>>
  - <<dateadd:min:-10>>
  - <<dateadd:years:10>>
- Examples of the entire command using this function
  - searchexact~date~<<dateadd:days:10>>
  - searchbetween~date~<<date>>~<<dateadd:days:10>>
  - searchbetween~date~9/10/2007~<<dateadd:days:10>>
now – Is replaced with the current date and time
- Function name = now
- Example that would replace the value in any command
  - <<now>>
- Example of the entire command using this function
  - searchexact~date~<<now>>
date – Is replaced with the current date
- Function name = date
- Example that would replace the value in any command
  - <<date>>
- Example of the entire command using this function
  - searchexact~date~<<date>>

Case Sensitivity

The API search is not case sensitive. These two example API calls will return the same results.

https://api.cimcloud.com/{sitename}/accounts/searchlike~nm~manufacturing
https://api.cimcloud.com/{sitename}/accounts/searchlike~nm~Manufacturing

Was this article helpful

Yes No