...
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
...
The period Component query is only applicable for 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 give 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, you set FYYearStartMoth:
{ label: “Mar”, value:3}
Then your fiscal year will start from March and ends 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
...
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 greaterThan 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 the date which 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 gte 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 lt 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 lte 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
...
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
...
If the period is custom, then rangeFlow, periodCount, startDateperiodUnit, and endDate should be required
rangeFlow
You can set rangFlow in two ways start and end
If rangeFlow: start then the start date is required
For Examplethe rangeFlow as from and till
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 prior one day
For Example: condition: {periodUnit: days”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 rangeFlow: end then the end date is required
End will return previous records and the start will return the next records according to the given periodUnit and periodCount”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
...