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 14 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"
					}
				}
			},
			{
				"leftField": {
					"fieldOptions": {
						"name": "test__Timesheet_Date"
					}
				},
				"operator": "lessThanEqualTo",
				"rightField": {
					"type": "static",
					"fieldOptions": {
						"value": "2021-06-01"
					}
				}
			}
		],
		"evaluationCriteria": "and"
	},
	"orderBy": {
		"name": "asc"
	},
	"limit": 10000,
	"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

  • Conditions in the filters consist of leftfield, rightfield, and operator

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

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

  • Operators can be equals, notEquals, lessThan, greaterThan, lessThanEqualTo, greaterThanEqualTo, contains, startsWith, endsWith, in, notIn

  • Evaluation criteria work with two operators “and” & “or”, evaluation criteria mainly use with multiple condition

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 we want to perform, 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

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 101st record on the List View

searchText:

  • Contains the search text to search by searchable fields

searchFields:

  • It contains an array of searchable fields

showCount:

  • showCount accepts 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

Period Component Query

  • If we want to filter the records of 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 till 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 till 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 till 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