This article explains the OData filters to help improve OnePlan performance.
General Guidance
When building filters, you need to use backend name of the field, not the name of the OData field.
When you are filtering on a choice field choice, you need to use the Choice Id.
When filtering on a Boolean field, you need to use lowercase 'true' or 'false'.
When filtering for a specific work item, plan, or resource, use the Work Item Id, Plan Id, or Resource Id values rather than the item/plan/resource name.
Note
When building filters, you need to use backend name of the field, not the name of the OData field.
-
Go to the OnePlan Configuration area
.
-
Go to Fields, and locate the choice field you need the choice id for. Click the Edit icon
for that field.
-
In the Edit Field form, click into the Choices field. This will open the Edit Choices form.
-
Click the carrot in a column header, then select Columns > Id from the drop-down. The Id field will be added into the view.
-
The Id field contains the ids for each choice in the field. Copy the Id for the desired choice value.
Currently, there is no way to get a security group Id value or work item type Id value directly from OnePlan. However, these values are available in the OData tables. There is a SecurityGroups table that lists out the Ids for each security group in your OnePlan environment (SecurityGroups.Id). The work type ids are available from the Tasks table (Tasks.WorkTypeID). Reach out to your OnePlan representative if you need assistance gettng these values for your OData filters.
The Tasks table can be pre-filtered in the OData query, enhancing the performance of task data loading. The supported OData filter operators include:
-
eq: Exact matches
-
ne: Not equal to
-
gt: Greater than
-
lt: Less than
-
ge: Greater than or equal to
-
le: Less than or equal to
-
and: Combines multiple filters with an "and" condition
-
or: Combines multiple filters with an "or" condition
You can filter on work item types by using the operators above with the work item type ID values.
?$filter=Fields/Status eq 'NotStarted' or Status eq 'InProgress'
In this example, I am filtering the Tasks table to display tasks that are Not Started or In Progress. To create this filter, I need the internal name for the Status field, as well as the choice ids for Not Started and In Progress.
The Plans table can be pre-filtered in the OData query, enhancing the performance of plan data loading. This includes plans in plan-type specific tables (e.g. Plan_Project, Plan_Idea). The supported OData filter operators include:
-
eq: Exact matches
-
ne: Not equal to
-
gt: Greater than
-
lt: Less than
-
ge: Greater than or equal to
-
le: Less than or equal to
-
and: Combines multiple filters with an "and" condition
-
or: Combines multiple filters with an "or" condition
You can filter on plan by using the operators above with the plan ID values.
?$filter=Fields/BusinessUnit eq 'e1f71a07-23c2-ac69-96b1-b5639b2bb5c7'
In this example, I am filtering the Plans table by the Business Unit field. To filter on this field, I need to use the field's backend name, BusinessUnit. I want to filter the table on a specific Business Unit, such as IT, so I only see plans that are associated the the IT Business Unit. Since Business Unit is a choice field, I need to use the choice id for IT.
The Resources table can be pre-filtered in the OData query, enhancing the performance of resource data loading. The supported OData filter operators include:
-
eq: Exact matches
-
ne: Not equal to
-
gt: Greater than
-
lt: Less than
-
ge: Greater than or equal to
-
le: Less than or equal to
-
and: Combines multiple filters with an "and" condition
-
or: Combines multiple filters with an "or" condition
You can filter on OnePlan security groups by using the operators above with the security group ID values (instead of the group name).
The Timesheet table can be pre-filtered in the OData query, enhancing the performance of timesheet data loading.
The supported OData filter operators include:
-
eq: Exact matches
-
gt: Greater than
-
lt: Less than
-
ge: Greater than or equal to
-
le: Less than or equal to
Note
Currently, the Timesheet table can only be filtered based on the timesheet period (not by project, resource, or other).