Filtering Guidelines

General Filters

The following are general examples that can be applied to all filterable fields.

To find records with a specific value, use ?filter={fieldname} eq '{value}' or ?filter={fieldname} == '{value}':

• objectDefns?filter=name eq 'CA7'

To find all records with an array of values for a field, use ?filter={fieldname} eq {'value', 'value', 'value'}:

• objectData/task?filter=code eq {'TPS_001', 'TPS_002', 'TPS_003'}

  You can also use the or/|| operator:

• objectData/task?filter=code eq 'TPS_001' or code eq 'TPS_002' or code eq 'TPS_003'

To find all records that don't contain a specific value, use ?filter={fieldname) ne or ?filter={fieldname} != {value}:

• timeTemplates?filter=name ne 'ECTP_2'

Soft Deleted Records

Any record that has the activeFlag field uses a "soft delete" feature. When a DELETE is performed on a soft delectable record, the record isn't removed from the database. Instead, the activeFlag for the record is set to "false". When a GET is performed for one or more records, any records that have the activeFlag set to false aren't returned. To access these records, you can filter on the activeFlag field and return any that are set to "false". For example:

• masterDataDefns?filter=activeFlag eq false

Endpoints that natively have the soft delete feature include the following:

• attributeDefns

• eventTypes

• masterDataDefns

• subscriptions

• timeTemplates

Filtering for DateTime

In addition to the equal and not equal operators, timestamps can use the greater than, less than, greater than or equal, and less than or equal operators to filter on any DateTime values. For example:

• timeEvents?filter=timestamp eq '2020-05-07 08:25:20.000'

• timeEvents?filter=timestamp ne '2020-05-07 08:25:20.000'

• timeEvents?filter=timestamp gt '2020-05-07 00:00:00.000'

• timeEvents?filter=timestamp lt '2020-05-12 00:00:00.000'

• timeEvents?filter=timestamp ge '2020-05-07 00:00:00.000'

• timeEvents?filter=timestamp le '2020-05-12 00:00:00.000'

The following endpoints can be filtered for timestamps:

• AttributeDefn

• EventType

• MasterDataDefn

• TimeEvent

• TimeRecord

• TimeTemplate

• Person

Filtering for Partial Values

To find records that match the start of a string, use ?filter=startsWith(field, 'value'):

• objectData/Person?filter=startsWith(displayName, 'Julie')

To find records that match the end of a string, use ?filter=endsWith(field, 'value'):

• objectData/Person?filter=endsWith(displayName, 'Smith')

To find records that match the middle of a string, use ?filter=contains(field, 'value'):

• apiConnectionDefns?filter=contains(description, 'Sap Business Rule')

Compound Filtering

To find records based on multiple values, use the "and" or the "or" operators to combine several criteria. You can also use the "not" filter to find all records that don't match another query result. For example:

• objectData/Person?filter=lastName eq 'Smith' and locale eq 'San Jose'

• objectData/Person?filter=lastName eq 'Lopez' or locale eq 'San Jose'

• timeRecords?filter=personId eq {'z20050522365915710965ecf', 'z200218175313339336716e7'} and (startTimestamp gt ‘2019-11-04 00:00:00.000’ and startTimestamp lt ‘2019-11-06 00:00:00.000’)

• objectData/Person?filter=(endsWith(displayName,'Smith') and locale eq 'Chicago') or (lastName eq 'Lopez' and locale eq 'San Jose') or locale eq 'San Francisco'

• objectData/Person?filter=not (locale eq 'San Francisco')

• objectData/Person?filter=not (locale eq {'San Francisco','Philidelphia'})

Filtering for Tags

Tags are used with time templates to flag whether or not they appear in the UI. To find specific records based on their tags, use the following construct:

tags.tagValue eq {'<tag1name>','<tag2name>'}

For example:

timeTemplates?filter=tags.tagValue eq {'TimeRecordProcessing','ManualAllocation'}

Segmented Value Filtering

When filtering on the segmented picklist values, the final segment is the result. To return the final segment, you must include every segment above it in the filter query. For example, to filter on an employee where the picklist has the following segmentation:

• Organization

  • Department

    • Manager

      • Employee

The filter must contain the Organization, Department, and Manager segments. For example:

picklists/z200910201211958359421c8/values?filter=org eq 'PI' and dept eq 'ITI' and manager eq 'Monique Chevalier'

Segmented Key Filtering

To filter for the keys of a segmented picklist, you need to include the segments above the one you're filtering for in the query. For example, to return the Manager level keys, use the following:

picklists/z200910201211958359421c8/keys/2?filter=org eq 'PI' and dept eq 'ITI'

apiParam Filtering

First, you need an apiConnectionDefn to point to the external system with the values for the picklist. For example:

{
    "name": "fieldglass_server",
    "label": "Fieldglass Server",
    "description": "Connection to fieldglass_server",
    "url": "https://xxxx.fieldglass.us.cloud.sap/api/v1/documents/v1",
    "authenticationType": "OAUTH2",
    "headers": {
        "X-ApplicationKey": "9LT2u7t8gXGgG8JqZYQ9qtxDKu8Z9vz5",
        "content-type": "application/json"
    },
    "tokenRequestDefn": {
        "grantType": "CLIENT_CREDENTIALS",
        "clientId": "maxjones",
        "clientSecret": "insite_webservies_:fg",
        "tokenRequestUrl": "https://xxxx.fieldglass.us.cloud.sap/api/oauth2/v2.0/token?X-ApplicationKey=9LT2u7t8gXGgG8JqZYQ9qtxDKu8Z9vz5"
    }
}

Next, you need a picklist definition that points to the values. For example:

{
    "name": "FieldGlassPickListAPI_Resources-Assignments",
    "description": "Picklist by API of FieldGlass company currencies",
    "referenceType": "API",
    "reference": {
        "apiConnectionDefnId": "z2011131740018045220078f",
        "apiPath": "/resources/{resourceId}/assignments",
        "apiFormat": "Fieldglass"
    },
    "fields": {
        "key": [
            "assignmentId"
        ],
        "value": [
            "assignmentTitle",
            "assignmentRef"
        ]
    }
}

Finally, create a query for the picklist value. In this example, we're querying the picklist with the zId of z2011131740065625276578f and using the API parameter of resourceId to find which assignments are valid for the person or asset.

picklists/z2011131740065625276578f/values?apiParams=resourceId eq 'z20100718090014737645988'

The output for both segmented picklists and API picklists includes a base64 key value to identify the segment across systems.