Hi there,
a colleague pointed me to this blog and the questiom about the filtering of
cascading drop-downs.
We experienced the same problem. We have a solution for dropdowns that have
to filter their data from a secondary source and to fill corresponding fields
with the corresponding data from the external records.
We did this in a repeating section with products where we wanted to show the
suppliers for the products and where the suppliers were in another
data-source where they were regularly updated.
It can be done in Infopath:
- take care you and your users have the secondary datasource ready somewhere
or the form will probably choke when you try to use it
- for practical reasons at our client's we had to use an exported
Outlooktable converted to an .mdb at the client's users local drive. But it
doesn't matter very much as long as it is a data-source Infopath can use
- go to the drop down-field (for us it was 'Supplier'), select, click right
and choose the properties,
- go to 'Items in the dropdown' and
- choose the third option whether the data are a webservice, datatable,
sharepoint-library or whatever
- then choose Connections and
- set up the secondary data-connection
- then choose 'Items' below
- and to the right of Items choose 'X-Path selection'
- you will then see only the data-fields in the secondary connection
- choose the field you want to connect to your own dropdownfield - for us it
was 'Supplier' in the Infopath-form and it was 'Surname' in the mdb-table
- then choose ok to come back to the properties-menu and close it.
So for us the dropdownfield was 'Supplier'
Now we had to find the right adress, phonenumber and e-mailadress in the
secondary connection.
- we made the field 'Supplier-adress' in the Infopath-datastructure
- we made an expression-field on the form
- and connected it to the field 'Supplier-adress'
- we then went back to the field ‘Supplier’
- selected and right-clicked it and
- chose ‘properties’ again
- then we chose ‘Rules’
- and then ‘Add’
- in the resulting Rulebox we gave a name – Rule1
- then we chose ‘Action’
- and then ‘Give the value for a field’
- in the resulting box we chose ‘Field’ and then gave ‘Supplieradress’ from
the main-connection
- in the choice Value comes the tricky part:
- choose ‘Fx’ to the right for the X-path
- then choose ‘Insert Field’ at the left below
- then choose at the top of the resulting box your secondary connection
- then choose form the resulting datafields the wanted field – for us it was
‘Workadress’ in the access-table
- Click ok
- in the resulting box you get the formula @Workadress
- now tick the box under it to choose ‘Edit X-Path’
- you will get a long X-path statement which will be something like this:
xdXDocument:GetDOM("Contactpersonen")/dfs:myFields/dfs:dataFields/d:Contactpersonen/@Workadress
This will not do because it will return the value of Workadress from all the
records in the table – you will only want one probably
- change it by hand to:
xdXDocument:GetDOM("Contactpersonen")/dfs:myFields/dfs:dataFields/d:Contactpersonen/@Workadress[Current()=../@Surname]
- The resulting formula will look like @Workadress=[current()=@Surname]
- Then ok, ok as may times as it takes to get all the way back.
You can make more rules for the supplier-field to direct values to
corresponding fields – we made 6 actions in a rule for every time we used
‘Supplier’ : adress, postalcode, town, email, phone and website.
!! If you think that in the next rule it will be enough to put in something
like @Phonenumber[Current()=@Surname] you will be wrong: you need to put in
the full X-Path statement with current() and the ../ because the full
statement is vital.
So I do hope I did not make any typing-errors in this little exposé.
I do not think that this is made very easy in Infopath - the fact that you
have to put in the full statement with the ../ as well is rather hard to find.
But it works.
Marieke Nelissen, JSR Multidesign – Netherlands
Craig Fox said:
I have been struggling with this for about a week now.
I have a form with a repeating sections. This section contains several
fields that all "link" to a database. The primary field is a drop down list
that populates from a database connection. Based on the user's selection in
the drop down, I populate the other fields, text boxes and rich text boxes.
(something like Description[@ID=field1]).
This works fine for the first section. Works most of the time in the second
(repeating) section. After that, the "linked" fields get assigned values from
other sections.
I saw a Q-Article about cascading list boxes and the current() function.
This work fine if I nest List Boxes or Drop Down Lists. If I use the same
filter for a rule or default value the field never populates.
Any help would be greatly appreciated.
