Constrained Dynamic Linked Items & Picklists
Basic Dynamic Linked Items and picklists make every row in the Report that they are based on, available in the picklist. However, in many situations you may find that the picklist will return results which are not relevant to the record you are working on. Adding constraint mappings to the Dynamic Linked Item will limit the picklist results by applying a filter to the underlying Report based on values found on the form fields of the record you are working on.
When using constraint mappings on Dynamic Linked Items, you will need to be conscious of the mapping between form field and Report column when setting up the Report. For the constraint to work, your report will need to include a column which will contain the values you would expect to see on the form field.
NOTE: The mappings need to be of the same data type, so when using a form field which relates to another record - Customer, Prospective Customer or Product for example - the report column being constrained needs to be the Id of the corresponding record.
Below are two working examples using Constrained Dynamic Linked Items & Picklists.
NOTE: When changing the value in a constraining Field it can have an affect on the constrained field/s. In most Cases information will not be deleted or modified in any way but a warning message will appear at the top of the Record to inform you that there may be invalid information present. In the case of a Product Picker the action will remove the data from the constrained fields, so the product will need to be re-selected fully.
Using a Constrained Dynamic Linked Item to select a Finance Contact
So for example, on Invoices we would like to add a Finance Contact field as a Dynamic Linked Item. Without setting a constraint mapping, when we use the picklist to select the Finance Contact, we will get a list of all contacts found on our database, irrespective of their relevance to the invoice.
Setting a constraint mapping will make it possible to only show people in the picklist where their employer is the customer of the invoice. Thus our list of available contacts in the picklist will contain fewer options which are directly relevant to the invoice, making it much easier and quicker to select the right contact.
Creating the Report
Our Finance Contact will be a Person record in Workbooks and we would like to constrain our results to only display employees of the Organisation selected as the Customer of the Invoice. Therefore it would be sensible to create our Report on People, then add columns for Person Name (the column we wish to display), the Employer and the Employer Id (Id is necessary here as this is what the Customer field is stored as on the database).
Setting up the Dynamic Linked Item Custom Field
As mentioned previously, the Customer form field is actually using the object Id on the database, therefore we need to map this form field to the Employer Id Report column. You can of course add further auto-populate mappings if required.
Now if we go to the Invoice record and select Atlantic Computer Services as our customer, the Finance Contact field will only display results for People whose Employer is Atlantic Computer Services.
To confirm this is correct, we can compare the rows on our Report used in the Dynamic Linked Item where the Employer is Atlantic Computer Services to those given in the picklist and see that they correspond.
Using a Dynamic Picklist and Constrained Dynamic Linked Item to Create a Product Picker
In this example we will create a product picker on Opportunity line items. The product picker consists of two custom fields on the line item; the first is called Select Category, which will offer you a list of product categories once selected, using a constrained Dynamic Linked Item, the second Select Product field will list just the products available which reflect that of the chosen product category.
Using an auto-populate field mapping from the Product Id of the Report to the Product field on the line item, the remaining fields on the line item will be automatically populated as if we had selected the product normally.
Creating the Report
The report which will form basis of the Dynamic Picklist and Dynamic Linked Item will need to include columns for Product Code, Category and Id at a minimum.
To build the Dynamic Picklist for Product Category as part of our picker, we add a summary view and group by Category.
Adding the 'Select Category' Dynamic Picklist
We create our Select Category custom field on the Opportunity line item. This field is of type Dynamic Picklist, the Report and view we select correspond to the Product Categories view in the Report just created.
This will create a picklist listing our product categories on the Opportunity line item.
Adding the Select Product Constrained Dynamic Linked Item
To complete our product picker we add another Opportunity line item custom field, this time of type Dynamic Linked Item. We choose the same Report as before but select the Details view which contains all our Products. We add a constraint mapping where the Form Field is Select Category (Line items). The field we have just created as the first stage in the product picker is mapped to the Category Report Column.
To ensure that fields on the line item are populated in the same way as if we had selected the Product normally, we must also add an auto-populate field mapping. The required mapping is Id to Product (Line items).
NOTE: Adjust the Order Line items on any Opportunity Form Layouts where you wish to use the product picker, otherwise users may not see the custom fields on the line item by default.
With the Select Category and Select Product columns in place we now have our functioning product picker. So if we select the category Expenses, the Select Product column only gives us the option for Products which are of the category Expenses, rather than the entire list. Likewise, when we select the category as Merchandise, the products available correspond to our Products of Category Merchandise.
We can correlate this to our Product Report.
Lastly, when we select a Product, the auto-populate field mapping populates the underlying Product field with the Product Id, which then populates other line item fields.