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

Contains

c

Starts With

sw

Ends With

ew

In

in

Less Than

lt

Greater Than

gt

Less Than Equals

lte

Greater Than Equals

gte

Between

btw

Match

mt

All Of

all

Any Of

any

None Of

none

Exactly

exactly

Period Component Query

  • To filter the records of an object for a particular duration e.g. for days, weeks, months, years, financial years, etc.

  • Period types are as follows:

    • days: n

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

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

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

  • No labels