/
Query

Query

Owned by Products Admin
Last updated: Jul 11, 2023
12 min read

Overview

  • This API is used to query records from a particular object

  • API queries records from the standard, custom, and metadata type object only if the user has the required access

  • HTTP method: POST

  • Base URL: https://api-{warehouse}.pwrapps.com

  • Endpoint: /data/v1/{instance}/{workspace}/query

Authorization

Internal

  • GCRT

  • RCRT

External

  • SID

Exception

  • API does not allow querying records of any object if the isQueryable is set as false

Body

  • { "objType": "test__Timesheet", "fields": [ "_id", "name", "test__Project_Name", "test__Task_Name", "test__Billable", "test__Time_Spent", "test__Timesheet_Date", "test__Test_Lookup", "test__Test_Integer" ], "filters": { "conditions": [{ "leftField": { "fieldOptions": { "name": "test__Timesheet_Date" } }, "operator": "greaterThanEqualTo", "rightField": { "type": "static", "fieldOptions": { "value": "2021-05-14" } }, "isNot": false }, { "leftField": { "fieldOptions": { "name": "test__Timesheet_Date" } }, "operator": "lessThanEqualTo", "rightField": { "type": "static", "fieldOptions": { "value": "2021-06-01" } }, "isNot": false }, { "conditions": [ { "leftField": { "fieldOptions": { "name": "test__Timesheet_Date" } }, "operator": "lessThanEqualTo", "rightField": { "type": "static", "fieldOptions": { "value": "2021-06-01" } }, "isNot": false } ], "evaluationCriteria": "and", "isCriteria": true, "isNot": false } ], "evaluationCriteria": "and" }, "orderBy": { "name": "asc" }, "limit": 200, "grouping": { "groupByFields": [], "aggregationFields": [] }, "searchText": "", "searchFields": [ "name" ], "showCount": true }

Required Parameter

objType

  • Object name on which the query needs to perform

Optional Parameters

fields:

  • Fields arrays contain the field names of the object to be retrieved in the response

  • If the fields array is not provided in the body then it will return only _id, objType, and instance of the records

  • The query returns an error if an invalid field or field which is not in the object is provided in the request body

filters:

  • You can apply conditions to filter records to be queried

  • Conditions in the filters consist of leftField, rightField, operator, isNot, and conditions

  • The left field is the object that contains fieldsOptions: {name: "fieldName"} where fieldName is the field on which the condition is applied

  • The right field is the object that contains fieldsOptions: {value: fieldvalue} where fieldValue is the value for which the record will be searched

  • Operators can be equals/eq, notEquals/ne, lessThan/lt, greaterThan/gt, lessThanEqualTo/lte, greaterThanEqualTo/gte, contains/c, startsWith/sw, endsWith/ew, in, notIn, match/mt, btw, any, as per the field type selected in the left field

  • isNot accepts a boolean value

  • If isNot is true, then it will filter not of the given condition, else will filter as per the given condition

  • Conditions inside the conditions act as sub-conditions of the filter

  • Evaluation criteria are the logical operators to combine two or more conditions with “and” & “or”, conditions are combined with “and“ by default

orderBy:

  • The orderBy contains the object with the field name as key and asc or desc as value

  • If the orderBy is {fieldName: asc} then the records will be sorted in ascending order

  • If the orderBy is {fieldName: dsc} then the records will be sorted in descending order

grouping:

  • Grouping contains the groupByFields and aggregationFields fields

    • The records are grouped by the fields provided in the groupByFields array

    • The aggregationFields is an array of objects where each object contains an operator, field, and name

      • Operator value can be sum, min, max, count or avg

  • Based on the aggregation operation, this field contains the name of the field on which the aggregation operation needs to be performed

limit:

  • Number of the records to be queried at once

    • e.g. if there are 200 records of an object and you set a limit of 100 then it will query the first 100 records and show them on the List View

  • The default limit is 200 or as per the page size if the records need to be displayed on the table or listview

offset:

  • You can set the offset for the query on the object

  • If the offset is “n“ then the query will take the records “n+1” onwards

  • For e.g., if there are 200 records and you set offset 100 then it will show records from the 101st record on the List View

searchText:

  • Contains the text to search the record by searchable fields

searchFields:

  • It contains an array of searchable fields

showCount:

  • showCount accepts a boolean value

  • If showCount is true, then the query shows the count of records retrieved

  • If showCount is false, then the query does not show the count of records retrieved

operators:

Operator Label

Operator Name

Equals

eq, =

notEquals

ne, !=

Contains

c

Starts With

sw

Ends With

ew

In

in

Less Than

lt, >

Greater Than

gt, <

Less Than Equals

lte, >=

Greater Than Equals

get, <=

Between

btw

Match

mt

All Of

all

Any Of

any

None Of

none

Exactly

exactly

during

during

Period Component Query

  • The period Component query is only applicable to Date and DateTime fields

  • The value type used in the period query are as follows

    • custom

      • If we set the value type custom then we have to provide the value

      • ex. {valueType: custom, value: “2023-06-24”}

    • relative

      • If we set the value type relative then it will take the value as the current date and time

  • The operators used in a period component query are as follows

    • equals

    • greaterThan

    • greaterThanEquals

    • lessThan

    • lessThanequals

    • current

    • next

    • currentAndNext

    • previous

    • currentAndPrevious

    • during

  • Fiscal Year and Fiscal Quarter

    • You can set the fiscal year on the instance using the field FYYearStartMonth

    • For example, set FYYearStartMoth:

      • { label: “Mar”, value:3}

    • Then your fiscal year will start from March and ends in Feb

Period Units

  • The period units used in the period component query are as follows

    • milliseconds

    • seconds

    • minutes

    • hours

    • days: n

      • If conditions: { periodUnit: days, periodCount: 'n'}

        • It will return the records which are created between the current date to 'n' days

      • If conditions: { periodUnit: days, periodCount: -'n'}

        • It will return the records which are created from the 'n' days prior to the current date

    • weeks: n

      • If conditions: { createdOn: "weeks:n"}

        • It will return the records which are created between the current week to 'n' weeks

      • If conditions: { createdOn: "weeks:-n"}

        • It will return the records which are created from the 'n' weeks prior to the current week

    • months: n

      • If conditions: { createdOn: "months:n"}

        • It will return the records which are created between the current month to 'n' months

      • If conditions: { createdOn: "months:-n"}

        • It will return the records which are created from the 'n' months prior to the current month

    • calQuarters: n (calendar quarter)

      • If conditions: { createdOn: "calQuarters:n"}

        • It will return the records which are created between the current calendar quarter to 'n' calendar quarters

      • If conditions: { createdOn: "calQuarters:n"}

        • It will return the records which are created from the 'n' calendar quarter prior to the current calendar quarter till the current calendar quarter

    • fYQuarters: n (financial quatrer)

      • If conditions: { createdOn: "fYQuarters:n"}

        • It will return the records which are created between the current financial quarter to 'n' financial quarters

      • If conditions: { createdOn: "fYQuarters:n"}

        • It will return the records which are created from the 'n' financial quarter prior to the current financial quarter till the current financial quarter

    • calYears: n (current calendar year)

      • If conditions: { createdOn: "calYears:n"}

        • It will return the records which are created between the current calendar year to 'n' calendar years

      • If conditions: { createdOn: "calYears:n"}

        • It will return the records which are created from the 'n' calendar year prior to the current calendar year till the current calendar year

    • fYYears: n (current financial year)

      • If conditions: { createdOn: "fYYears:n"}

        • It will return the records which are created between the current financial year to 'n' financial years

      • If conditions: { createdOn: "fYYears:n"}

        • It will return the records which are created from the 'n' financial year prior to the current financial year till the current financial year

Equals

  • By using an equals operator we will fetch records of the provided value

  • valueType: custom

    • If conditions: {valueType: “custom”, periodUnit: days, periodCount: n}

      • It will return records of the date which comes according to the period count

    • If conditions: {valueType: “custom”, periodUnit: weeks, periodCount: n}

      • It will return records of the date which comes according to the period count and period unit

        • ex. {value: 2023-06-23, periodCount: 3 then it will return records of date 2023-07-14}

    • If conditions: {valueType: “custom”, periodUnit: months, periodCount: n}

      • It will return records of the date which comes according to the period count and period unit

        • ex. {value: 2023-06-23, periodCount: 3 then it will return records of date 2023-09-23}

    • If conditions: {valueType: “custom”, periodUnit: calQuarter, periodCount: n}

      • It will return records of the date which comes according to the period count and period unit

        • ex. {value: 2023-06-23, periodCount: 3 then it will return records of date 2024-02-23}

    • If conditions: {valueType: “custom”, periodUnit: calYear, periodCount: n}

      • It will return records of the date which comes according to the period count and period unit

        • ex. {value: 2023-06-23, periodCount: 3 then it will return records of date 2026-06-23}

  • valueType: relative

    • If conditions: {valueType: “relative”, periodUnit: calQuarter, periodCount: n}

      • It will return records of the date which comes according to the period count and period unit

        • ex. {value: “today’s date and time”, periodCount: 3 then it will return records of date “today’s date + periodCount”

Greater Than

  • By using the greater than operator we will fetch records greater than the provided value

  • valueType: custom

    • If conditions: {valueType: “custom”, periodUnit: days, periodCount: n}

      • It will return records of dates greater than the date which came according to the period count and period unit

        • ex. {value: 2023-06-23, periodCount: 3 then it will return records greater than the date 2023-06-26}

  • valueType: relative

    • If conditions: {valueType: “relative”, periodUnit: calQuarter, periodCount: n}

      • It will return records of dates greater than those that came according to the period count and period unit

        • ex. {value: “today’s date and time”, periodCount: 3 then it will return records of a date greater than “today’s date + periodCount” date

Greater Than Equal

  • By using the greater than equal operator we will fetch records greater than the provided value and the records of the date provided

  • valueType: custom

    • If conditions: {valueType: “custom”, periodUnit: days, periodCount: n}

      • It will return a record of the date which came according to the period count and period unit, and records greater than the date

        • ex. {value: 2023-06-23, periodCount: 3 then it will return records of the date 2023-06-26 and records of a date greater than 2023-06-26 }

  • valueType: relative

    • If conditions: {valueType: “relative”, periodUnit: calQuarter, periodCount: n}

      • It will return a record of the date which came according to the period count and period unit, and records greater than the date

        • ex. {value: “today’s date and time”, periodCount: 3 then it will return records of a date “today’s date + periodCount” date and greater than that date

Less Than

  • By using the less than operator we will fetch records less than the provided value

  • valueType: custom

    • If conditions: {valueType: “custom”, periodUnit: days, periodCount: n}

      • It will return records of the date less than the date which came according to the period count and period unit

        • ex. {value: 2023-06-23, periodCount: 3 then it will return records less than the date 2023-06-20}

  • valueType: relative

    • If conditions: {valueType: “relative”, periodUnit: calQuarter, periodCount: n}

      • It will return records of the date less than the date which came according to the period count and period unit

        • ex. {value: “today’s date and time”, periodCount: 3 then it will return records of the date less than “today’s date - periodCount” date

Less Than Equals

  • By using the less than equal operator we will fetch records less than the provided value and the records of the date provided

  • valueType: custom

    • If conditions: {valueType: “custom”, periodUnit: days, periodCount: n}

      • It will return a record of the date which came according to the period count and period unit, and records less than the date

        • ex. {value: 2023-06-23, periodCount: 3 then it will return records of the date 2023-06-20 and records of a date less than 2023-06-20 }

  • valueType: relative

    • If conditions: {valueType: “relative”, periodUnit: calQuarter, periodCount: n}

      • It will return a record of the date which came according to the period count and period unit, and records less than the date

        • ex. {value: “today’s date and time”, periodCount: 3 then it will return records of a date “today’s date - periodCount” date and less than that date

Current Operator

  • By using the current operator it will fetch records according to the period unit from beginning to end

  • The value type for operator current is always relative

    • If conditions: {periodUnit: days}

      • It will return all records of the current day

    • If conditions: {periodUnit: weeks}

      • It will return all records of the current week from Sunday to Saturday

    • If conditions: {periodUnit: months}

      • It will return all records of the current month from 1st day of the current month to the last day of the current month

Next Operator

  • If conditions: {periodUnit: days, periodCount: n}

    • It will return records between the next day and the “today + periodCount” day

  • If conditions: {periodUnit: weeks, periodCount: n}

    • It will return records between the next week and the “today + periodCount” week

currentAndNext Operator

  • If conditions: {periodUnit: days, periodCount: n}

    • It will return records between the current day and the “today + periodCount” day

  • If conditions: {periodUnit: weeks, periodCount: n}

    • It will return records between the current week and the “today + periodCount” week

previous Operator

  • If conditions: {periodUnit: days, periodCount: n}

    • It will return records between the prior day of the current day and the “today - periodCount” day

  • If conditions: {periodUnit: weeks, periodCount: n}

    • It will return records between the prior week of the current week and the “today - periodCount” week

currentAndPrevious Operator

  • It will return records between the current day and the “today - periodCount” day

  • If conditions: {periodUnit: days, periodCount: n}

    • It will return records between the current day and the “today - periodCount” day

  • If conditions: {periodUnit: weeks, periodCount: n}

    • It will return records between the current week and the “today’s week - periodCount” week

During Operator

  • During means some time between the beginning and the end of a period

  • The during operator of the period component query is applicable for the Date and DateTime fields

Relative Literals in During Operator

  • Yesterday

    • If condition{ period: ”yesterday”}

      • It will return the previous day's record from the current day

    • If condition{ period: ”tomorrow”}

      • It will return the next day's record from the current day

    • If condition{ period: ”today”}

      • It will return the current day record

    • If condition{ period: ”next7Days”}

      • It will return the next 7 day's records from the current day

    • If condition{ period: ”next30Days”}

      • It will return the next 30 day's records from the current day

    • If condition{ period: ”next60Days”}

      • It will return the next 60 day's records from the current day

    • If condition{ period: ”next90Days”}

      • It will return the next 90 day's records from the current day

    • If condition{ period: ”next180Days”}

      • It will return the next 180 day's records from the current day

    • If condition{ period: ”previous7Days”}

      • It will return the previous 7 day's records from the current day

    • If condition{ period: ”previous30Days”}

      • It will return the previous 30 day's records from the current day

    • If condition{ period: ”previous60Days”}

      • It will return the previous 60 day's records from the current day

    • If condition{ period: ”previous90Days”}

      • It will return the previous 90 day's records from the current day

    • If condition{ period: ”previous180Days”}

      • It will return the previous 180 day's records from the current day

    • If condition{period: “currentWeek”}

      • It will return the current week records

    • If condition {period: “nextWeek”}

      • It will return the next week records from the current week

    • If condition {period: “previousWeek}

      • It will return the previous week records from the current week

    • If condition {period: “currentAndNextWeek”}

      • It will return the next week and the current week records

    • If condition {period: “currentAndPreviousWeek}

      • It will return the previous week and the current week's’s records

    • If condition{period: “currentMonth”}

      • It will return the current month records

    • If condition {period: “nextMonth”}

      • It will return the next month records from the current month

    • If condition {period: “previousMonth}

      • It will return the previous month records from the current month

    • If condition {period: “currentAndNextMonth”}

      • It will return the next month and the current month records

    • If condition {period: “currentAndPreviousMonth}

      • It will return the previous month and the current month records

    • If condition{period: “currentCalendarQuarter”}

      • It will return the current calendar quarter records

    • If condition {period: “nextCalendarQuarter”}

      • It will return the next calendar quarter records from the current calendar quarter

    • If condition {period: “previousCalendarQuarter}

      • It will return the previous calendar quarter records from the current calendar quarter

    • If condition {period: “currentAndNextCalendarQuarter”}

      • It will return the next calendar quarter and the current calendar quarter records

    • If condition {period: “currentAndPreviousCalendarQuarter}

      • It will return the previous calendar quarter and the current calendar quarter records

    • If condition{period: “currentCalendarYear”}

      • It will return the current calendar year records

    • If condition {period: “nextCalendarYear”}

      • It will return the next calendar year records from the current calendar year

    • If condition {period: “previousCalendarYear}

      • It will return the previous calendar year records from the current calendar year

    • If condition {period: “currentAndNextCalendarYear”}

      • It will return the next calendar year and the current calendar year records

    • If condition {period: “currentAndPreviousCalendarYear}

      • It will return the previous calendar year and the current calendar year records

    • If condition{period: “currentFiscalQuarter”}

      • It will return the current fiscal quarter records

    • If condition {period: “nextFiscalQuarter”}

      • It will return the next fiscal quarter records from the current fiscal quarter

    • If condition {period: “previousFiscalQuarter}

      • It will return the previous fiscal quarter records from the current fiscal quarter

    • If condition {period: “currentAndNextFiscalQuarter”}

      • It will return the next fiscal quarter and the current fiscal quarter records

    • If condition {period: “currentAndPreviousFiscalQuarter”}

      • It will return the previous fiscal quarter and the current fiscal quarter records

    • If condition{period: “currentFiscalYear”}

      • It will return the current fiscal year records

    • If condition {period: “nextFiscalYear”}

      • It will return the next fiscal year records from the current fiscal year

    • If condition {period: “previousFiscalYear”}

      • It will return the previous fiscal year records from the current fiscal year

    • If condition {period: “currentAndNextFiscalYear”}

      • It will return the next fiscal year and the current fiscal year records

    • If condition {period: “currentAndPreviousFiscalYear”}

      • It will return the previous fiscal year and the current fiscal year records

Custom period in During Operator

  • If the period is custom, then rangeFlow, periodCount, periodUnit, and should be required

    • rangeFlow

      • You can set the rangeFlow as from and till

        • For Example: condition: {periodUnit:”days”, periodCount: 1, rangeFlow:”from”}

          • It it will return the current day and next day record

      • You can set the startDate and endDate which is optional if you are given the startDate and endDate then it will return records according to the given startDate and endDate otherwise it considers the current date as startDate or endDate

startDate and endDate in During Operator

  • You can set the startDate and endDate conditions for during operator if the valueType is custom

    • For Example: condition: startDate: "2023-06-02" and endDate: "2023-06-19"}

    • It will return the records from 2023-06-02 to 2023-06-19

  • The start date is always less than the end date

 

Related content

Rest API Workflow Action
Rest API Workflow Action
noKodr
More like this
Filters
Filters
PWR Rollups
More like this
Query Records
Query Records
noKodr - For Salesforce
More like this
API
API
noKodr
More like this
API
API
noKodr - For Salesforce
More like this
Query Records
Query Records
noKodr
More like this