Filter value sources

Document filter value sources allow you to specify where and how BC retrieves the filter values for each task or resource that is sent to Dime.Scheduler.

note

Tasks and resources are generic terms within Dime.Scheduler and do not reflect any specific table in BC. As such, it is possible to have multiple sources for both resources and tasks.

Default values can be set, which can be overridden when data is retrieved from tables further down in the hierarchy. This allows you to set values in the event that there's no data defined in BC, or to set different values when data is defined and when data is not defined, for instance one can choose to set "No" as the default value when no skills are found and "Yes" when skills are found for a certain task or resource.

Declare resource and task filter value sources

In Business Central, hit ALT + Q and look for the 'Dime.Scheduler Doc. Filter Value Sources' administration page. This list contains the following columns:

Field, tab or group	Description
Table No.	Specifies the table number that is the source of the task or resource.
Comment	Free text for internal use.
Entity Type	Specifies whether the table refers to a task or resource in Dime.Scheduler.
Resource Key Field	Specifies the field that identifies the resource when Entity Type = Resource. This is the field sent to Dime.Scheduler as ResourceNo.
Job Key Field	Specifies the field that identifies the job when Entity Type = Task. This is the field sent to Dime.Scheduler as JobNo.
Task Key Field	Specifies the field that identifies the task when Entity Type = Task. This is the field sent to Dime.Scheduler as TaskNo.

Below is an example of some of the setup that comes with the standard connector. In this particular setup, resources are pulled from the resources table, as well as the work and machine centers. At the other end, planning is done for service item lines, sales lines, etc.

Once the master document filter value source is declared, you need to specify the source(s) and rules for all the filter groups the entity has filter values for. This implies that you can set up links to an unlimited number of tables with an bottomless hierarchical depth. For each table link, you can specify how to retrieve records from the table and which filters and conditions apply.

Tables

In the ribbon of the 'Dime.Scheduler Doc. Filter Value Sources' list, navigate to the 'Tables' menu item. This is a hierarchical list; the top-level item in the hierarchy is the entity's base table and is automatically created in the 'Tables' page. Nested levels underneath the base entity enable you to dig deeper into the entity's graph and declare more specific filter value sources. Dime.Scheduler will explore the entire graph when it creates filters for resources and tasks.

Field, tab or group	Description
Link to Table No.	Specifies the table number of the next table in the hierarchy.
Method	Specifies how the table is to be read: Match: Retrieves the record with an exact match based on the links and filters. First: Retrieves the first record matching the criteria of the links and filters. Last: Retrieves the last record matching the criteria of the links and filters. All: Retrieves all records matching the criteria of the links and filters.

For each table you can specify:

• Links refer to the field(s) that link the selected table with the parent table. It is essentially a declarative relationship builder that defines how to navigate across the graph. For instance, you'll typically link the Document No. field of a line with the No. field of a header.
• Filters can be applied on the selected table to limit the number of records. For instance, a filter can be generated to only retrieve records that are not blocked.
• Conditions specify filters on the parent table that determine if the link is applicable, for instance only link the line table (the parent) with the item table (the child) if the line type of the line table is of type item.
• Fields: specify the fields that are the source of a filter value. You can also specify default values and override behavior.

info

Links, filters and conditions cannot be defined for base tables.

Links

On this page, hit ALT + Q and look for 'Links', or click the corresponding 'Links' item in the ribbon.

Field, tab or group	Description
Link from Field No.	Specify the field from the parent table.
Link to Field No.	Specify the field from the child table to connect it with the field from the parent table (link from field no.).

You can specify an unlimited number of links to be linked between the parent and child table.

Filter

On this page, hit ALT + Q and look for 'Filters', or click the corresponding 'Filters' item in the ribbon.

Field, tab or group	Description
Link from Field No.	Specify the field from the selected table, the child table, on which to apply the filter.
Value	Enter the filter value; this can be any value which is recognized as a filter by BC. Option values are recognized and autocompleted.

You can specify an unlimited number of filters to be applied on the child table. Be aware that all filters are applied at once.

Conditions

On this page, hit ALT + Q and look for 'Conditions', or click the corresponding 'Conditions' item in the ribbon.

Field, tab or group	Description
Link from Field No.	Specify the field from the parent table on which to apply the filter.
Value	Enter the filter value; this can be any value which is recognised as a filter by BC. Option values are recognised and auto-completed.

You can specify an unlimited number of conditions to be applied on the parent table. Be aware that all conditions are applied at once.

Fields

On this page, hit ALT + Q and look for 'Fields', or click the corresponding 'Fields' item in the ribbon. The sections below cover each column in more detail.

Filter group

Specify the filter group for which to set a filter value.

Field number

Specify the field that contains the filter value. This is an optional field, which makes it possible to use any kind of value or source. For instance, a custom dimension can be used as a field; check out this guide for more info.

Fixed value

Value

A free text field where you can enter a fixed or default value.

For example, for a sales order you can consider using a fixed value 'Sales' for the 'Department' filter group. Each sales order, and only a sales order, will then apply this filter.

Behavior

The behavior options are in relation to the field number above, and determine how - and if - the fixed value is used as the filter value:

• None: The fixed value is used as the filter value if no field number is set.
• Overrule: The fixed value always overrules the value from the field number; you can use this to set a specific value when a record exists for field number, regardless of the value of the field number
• Overrule when empty: The fixed value overrules the value from field number only when the field number is empty.

Value when not found

A free text field where you can specify a value for the filter value if no record exists for the field number; in combination with the overrule behavior of the fixed value you can set a Yes/No or true/false (or any other value pair) depending on the fact if a record exists or not.

Hierarchy behavior

If you have set a value for the same filter group on a higher level in the hierarchy, then the hierarchy behavior options determine if the current level overrides this value.

The below options apply only if the current level returns a filter value. In other words, when a field number is defined, but none of the fixed values are entered, and there is no record found for the current level. If so, the current level hasn't got any filter values and this level in the hierarchy is ignored.

• None: The current level does not override a filter value that was set at a higher level in the hierarchy. The current level does set the filter value if no filter value was set for the filter group.
• Overrule: The current level always overrules a filter value that was set at a higher level in the hierarchy.
• Overrule when empty: The current level overrules an empty filter value that was set at a higher level in the hierarchy.

Example: Skills

The standard connector comes with a few preconfigured filter groups, values and sources. Let's take the skills entity as an example. By default, the hierarchy is set up to look like this for a service item line:

.
├── Service Item Line
│    ├── Resource Skills
│        ├── Skill Code

To establish this hierarchy, we need to tell Business Central how they are linked (link), what data should be retrieved (filter), and which fields to use to compare to the filter value list of the 'Skills' filter group (field). The filter group uses table 5955 as the source of the filter values, so in the service item line graph we need to find a way to get to the same table. In this particular hierarchy, we need to go two levels deep to get there.

To navigate from the service item line to the resource skills, the Links tell Business Central to use the 'Service Item No.' field to join it with the Resource Skill's 'No.' field. The Filter indicates that this level should only yield records of type 'Service Item'. No Fields are mapped here since we're going to use the field from the next level.

To navigate from the resource skill to the skill code, the Links section tells Business Central to use the Resource Skill's 'Skill Code' and join it with the Skill Code's 'Code' field. No filters are applied here since there's already one set at the level above this one. To use the right filter value reference, we are going to add a Field entry and link it to the Skill's 'Description'.

Thus, what happens, in essence, is that the filter group generates values from a source table (table 5955 - skills), and that we navigate the path of a service item to link the skills required for that work item to a corresponding value in the filter values list.

For instance, in Business Central table 5955 could contain the following list:

.
├── Skills
│    ├── NETWORKING
│    ├── DATABASE
│    ├── PROGRAMMING
│    ├── DEVOPS

A simple filter group would simply use the entire source, without any filters, to create filter values:

.
├── Filter Group: Skill
│    ├── NETWORKING
│    ├── DATABASE
│    ├── PROGRAMMING
│    ├── DEVOPS

Service item lines allow you to specify required skills:

.
├── Service Item Line: "Upgrade SQL Server"
│    ├── Resource Skills
│        ├── Skill Code: DATABASE
│        ├── Skill Code: NETWORKING

Without the setup as mentioned above, Dime.Scheduler wouldn't be able to connect the dots. But with this config, it knows how to map and compare these different tables and values. So in the end, Dime.Scheduler will append two requirements for service order item "Upgrade SQL Server" with the two items in the filter group required to carry out that work.

This is the easiest example imaginable. But one might consider to splitting up the skills table in several filter groups. For example, you could have a filter group called "System Admin Skills" and another named "Developer Skills", and allocate the skills to the right group accordingly. There is no right or wrong here - it is up to the customer to decide how the filter groups functionality should be set up.

Read more

• Using dimensions for filter groups