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 22 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

Positive 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 operators used in period component query are as follows

    • equals

    • current

    • next

    • currentAndNext

    • during

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

    • 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: "weeks:n"}

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

      • If conditions: { createdOn: "weeks:-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

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 period component query

  • 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

  • No labels