Getting Just Unique Values in a Dropdown via XPATH
Something that has dogged me for a long time is the fact that when I'm doing cascading filtered dropdowns I've had to set up more than one data connection in order to accommodate the right behavior. I generally have a two column datasource coming from SQL; one column that contains values that will repeat, and then the second column that contains values that are sometimes unique, but can repeat too. Most solutions involve having an initial dropdown that contains the *unique* values of the first column, and what gets picked in that column drives a filtered pick list in a second dropdown. What has sucked in the past is that in order to get a list of just the distinct values, I've had to create a separate data connection and use the Select Distinct nomenclature in the SQL statement of the query. I always found this to be wasteful and slows down the solution, especially if that datasource is large. I finally got around to asking the question on whether the filter to reduce the list to just distinct values can be done with an XPATH filter. The answer is a resounding yes. The trick is that when you are specifying the fields that should be shown in the dropdown, in the Entries field you should not specify the actual attribute, but rather you should specify the level of the tree that is one level higher (i.e. the name of the repeating row, not the value). Then in the Value and Display Name field you can specify the actual attribute name (i.e. @yourvalue). The trick is to specify a filter on the Entries field, and instead of comparing values in a typical filter you will select an Expression. That expression follows the syntax of:
not(@yourvalue = preceding-sibling::dfs:YourRepeatingRowName/@yourvalue)
There will be times when your datasource structure is deeper than one level, especially when you have parent/child relationships. In those cases, you'll find that your repeating row is preceded by a d: instead of a dfs:. Other than that it works great and reduces the total number of data calls you need to make to the database, which is always a good thing.
Comments
Anonymous
October 01, 2010
Can you send me a sample of this rahzelous@gmail.comAnonymous
April 30, 2013
I also need to filter my dropdown list in my infoPath form, however I need to apply the filter based on nested values of my SECONDARY data source. Additionally the filter must be applied on an attribute of the nested secondary datasource (using the '@') symbol. Have you had any experience in this endeavor? My Secondary Datasource is similar to the following: ItemLevel1 |---(irrelevant field here) |---ItemLevel2 | |----@id |----value field here I need to filter the entries on "@id" above Thanks in advance.