Auto-filtering relation data

Auto-filtering relation data

This one’s an advanced feature that will only be used in certain circumstances but is very beneficial where there is a good use. It’s most easily explained by an example.

Here, we’re adding a product to a customer order. There’s a relation from order line to product that lets you select from the full range of products. However, what if the product list depends on the customer? Different customers may have different products that they’re allowed to order, i.e. an agreed product list. Using this feature we can set that up.

The idea is that the product list is filtered by a value from the order. To specify the filter value, add a cross-referenced field to the main order record. In this case, a cross referenced field is added to show an ‘order list’ name. After adding the cross referenced field, edit it’s options and tick ‘use as a filter for relations’.

Each product is assigned to one or more order lists and the order list field added to the view used for selecting products.

Then whenever an order is added, the value supplied by the cross-referenced field is used as a filter to the product list, to narrow down the products.

This type of filtering can be used whenever there is a parent-child type relationship where the child options need to be narrowed down depending on criteria from the parent.

Note: the view used for choosing items will be filtered on any field with the same name as one used in the cross reference. That means it doesn’t need to be exactly the same field, the cross reference can be e.g. a calculation.