OData Filters

  • Updated

This article explains the OData filters to help improve OnePlan performance.

Tips for Building OData Filters

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.

Get the Internal Name for a Field

Note

When building filters, you need to use backend name of the field, not the name of the OData field.

  1. Go to the OnePlan Configuration area Settings.png.
  2. Go to Fields, and locate the field you need the internal name for. Click the Edit icon Edit.png for that field.

  3. In the Edit Field form, locate the Internal Name field. This contains the internal name for the field. Copy this value.

    get_field_internal_name.png

Get the Plan Id Value

  1. In OnePlan, navigate to the plan you would like to the id for.

  2. Click on the User Preferences menu, then select Share With.

    Get_Plan_Id_Share_with.png

  3. In the Shareable URL, locate the value following PlanId=####. This is the plan id for the current plan - copy the everything after PlanId=.

    share_url_get_plan_id.png

Get the Choice Id Value 

  1. Go to the OnePlan Configuration area Settings.png.
  2. Go to Fields, and locate the choice field you need the choice id for. Click the Edit icon Edit.png for that field.

  3. In the Edit Field form, click into the Choices field. This will open the Edit Choices form.

    Get_choice_id_edit_field_form.png

  4. Click the carrot in a column header, then select Columns > Id from the drop-down. The Id field will be added into the view.

    Get_choice_id_value_add_column_to_view.png

  5. The Id field contains the ids for each choice in the field. Copy the Id for the desired choice value.

    get_choice_id_copy_value.png

Get the Security Group Id and Work Item Type Id

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.

Tasks Table 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.

Tasks Table Filter Example

?$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.

Plans Table Filters

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.

Plans Table Filter Example

?$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.

Resources Table Filters

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).

Resources Table Filter Example

?$filter=Fields/Rate gt 100

In this example, I am filtering the Resources table to only display resources with rates greater than $100. To filter on this field, I just need the field backend name.

You can filter on the Date field, and apply greater than, less than, and inclusive date filters.

Dates need to be formatted as yyyy-mm-dd. For example:

?$filter=Date gt 2025-01-01 and lt 2025-02-25

Resource Plan Table (ResPlan) Filters

The ResPlan 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
  • gt: Greater than
  • lt: Less than
  • ge: Greater than or equal to
  • le: Less than or equal to

ResPlan Table Filter Examples

You can filter of the Date field. 

Date filters for the ResPlan table need to be formatted as Date [operator] datetime'[yyyy-mm-ddThh:mm:ss]'. For example, the following filter will bring in resource data after 01 April 2025. 

?$filter=Date gt datetime'2025-04-01T00:00:00'

You can also combine 2 ifs using an and. For example, the following filter will bring in resource data after 01 April 2025, and before 30 June 2025.

?$filter=Date gt datetime'2025-04-01T00:00:00' and Date lt datetime'2025-06-30T00:00:00'

Financial Plan Table (Costs) Filters

The Costs 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
  • gt: Greater than
  • lt: Less than
  • ge: Greater than or equal to
  • le: Less than or equal to

Costs Table Filter Examples

You can filter the Costs table on the Date field. 

Date filters for the Costs table need to be formatted as Date [operator] datetime'[yyyy-mm-ddThh:mm:ss]'. For example, the following will filter the Costs table to bring in costs for 02 Feb 2025. 

?$filter=Date eq datetime'2020-02-02T00:00:00'

You can also combine two ifs with an and. For example, the following will filter the Costs table to bring in costs after 02 Feb 2025 and on or before 02 April 2025.

?$filter=Date gt datetime'2020-02-02T00:00:00' and Date le datetime'2025-04-02T00:00:00'

Timesheet Table Filters

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).

Timesheet Table Filter Example

You can filter on the PeriodStart and PeriodEnd fields.

Dates need to be formatted as yyyy-mm-dd. For example:

?$filter=PeriodStart gt 2023-01-01

You can also combine 2 ifs using an and. For example:

?$filter=PeriodStart gt 2023-01-01 and PeriodStart lt 2023-03-01

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.