Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

« Previous Version 35 Next »

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 for 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 give 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, you set FYYearStartMoth:

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

    • Then your fiscal year will start in March to 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”

GreaterThan

  • By using greaterThan 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 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 a date greater than “today’s date + periodCount” date

GreaterThanEqual

  • By using the gte operator we will fetch records greater than the provided value and the records of 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

LessThan

  • By using the lt 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

LessThanEquals

  • By using the lte 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

Period Custom in During Operator

  • If the period is custom, then rangeFlow, startDate, and endDate should be required

    • rangeFlow

      • You can set rangFlow in two ways start and end

      • If you can set rangeFlow as a start then the start date should be required

        • For Example: {periodUnit: days, periodCount: 1, rangeFlow: “start”, startDate: 2023-06-22}

          • It will return the record which starts from 2023-06-22 and ends after one day

      • If you can set rangeFlow as a end then the end date should be required

        • For Example: {periodUnit: days, periodCount: 1, rangeFlow: “end”, endDate: 2023-06-22}

          • It will return the record which starts from 2023-06-22 and ends before one day

        • Since the end will return previous records and the start will return the next records according to the given periodUnit and periodCount

startDate and endDate in During Operator

  • You can set the startDate and endDate conditions in during operator

    • 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

  • No labels