SQL Database Integration

Re: Cascading list issue

Greg Collins — Fri, 27 Jan 2006 22:04:35 GMT

DrValium... here's an update that should get you rolling:

The solution required two things:

1) current() represented the NODE d:Registering, and not the ATTRIBUTE @Arbetsuppgift, therefore the correct filter is expressed as "../@Element = current()/@Element"

2) Once you update the filter, the Value and Display Name fields of the drop-down list get reset to ".", which if not updated back to @Value and @id respectively, leaves the list looking blank.

With both of these adjustments in place, the repeating cascading lists work as expected.

Greg Collins [InfoPath MVP]
InfoPath Dev

Re: Cascading list issue

gbgeek — Fri, 27 Jan 2006 13:37:22 GMT

Thanks for your help!

Re: Cascading list issue

Greg Collins — Fri, 27 Jan 2006 00:22:56 GMT

Hi gbgeek,

There's no right answer as to whether to use code or filters. Filters are less work so long as you understand how to set them up correctly. If you are not inside a repeating table, it is a lot easier. Inside a repeating table, having a correct understanding of how to use current() makes all the difference.

Greg Collins [InfoPath MVP]
InfoPath Dev

Re: Cascading list issue

gbgeek — Thu, 26 Jan 2006 20:29:06 GMT

Hello,

I have a question that is similar in nature to this question. I have two drop-down lists (one is facility and the other is department). I found code online to change the department drop-down to show only departments for the selected facility. That code was here:

http://radio.weblogs.com/0131777/stories/2004/03/09/howToDynamicallyPopulatingADropDownControl.html

However, I now get an error message when I attempt to submit the form data to the database. The data goes, but I receive an error message on the WHERE clause of the script code.

As I was reviewing this post, I noticed that a filter was being used to create the cascasding between the drop-down lists. I am not sure how to filter the drop-down lists to make this work. Both drop-down lists that I have are from secondary data sources (Access tables). The primary data source also pull from the Access table.

Is the code-based way correct or should I be using a filter to achieve this cascade effect?

Re: Cascading list issue

Greg Collins — Thu, 26 Jan 2006 17:49:27 GMT

Okay, as best as I can figure out, from what you've provided me (assuming I understand correctly), you have:

DD1 is bound to [AccessDB]/myFields/dataFields/d:Register/Element
DD2 is bound to [AccessDB]/myFields/dataFields/d:Register/Task

where d:Register is a repeating node.

Then you populate DD1 from the XML file using the XPath dataFields/Register/@Element
And you populate DD2 from the same XML file using the XPath dataFields/Register/Task (using @id and @Value as the value and display name)

You want DD2 to only show those Tasks for which the ../Register/@Element is the same as the selected Element (namely [AccessDB]/myFields/dataFields/d:Register/Element)

Because you are in a repeating structure, you WILL need to use the current() function. BUT!! You must know what node current() refers to in order for this to work properly. In this case, it appears that current() is equivelant to the same node that DD2 is bound to (namely [AccessDB]/myFields/dataFields/d:Register/Task)

So your XPath, including the filter (in '[]') for DD2 would look something like:

dataFields/Register/Task[../@Element = current()/../Element]

So then, you are comparing the attribute @Element on the parent node to Task, which is Register, to the selected value in node Element in the Main DOM, on the current row, which is (starting from the 'current()' node of Task) ../Element. Note the ".." gets you back to the current Register node, and then from there, go to the Element node.

You would select the left-side comparison (../@Element) as you normally do in your filter dialog, and then use a formula to type in the right-side comparison (current()/../Element).

Greg Collins [InfoPath MVP]
InfoPath Dev

Re: Cascading list issue

DrValium — Wed, 25 Jan 2006 07:19:08 GMT

Hi Greg,

I have created the form from an access database (primary data connection) and the data source layout looks like this:

>myFields
>> dataFields
>>> d:Register
>>> Register no
>>> Employee
>>> Date
>>> Element
>>> Task
>>> Time

I have creating a repeating cascading drop-down list based on the 'Element' and 'Task' fields above.

The data in the drop-down list is gathered from a secondary data connection (an xml source) which looks something similar to this (this give you an idea of the structure anyway):

<dataFields>
<Register Element="CompanyA">
<Task id="Batching files" Value="01H" />
</Register>
<Register Element="CompanyB">
<Task id="Reconciliation" Value="03H" />
</Register>
</dataFields>

Now in the first drop down list I can choose either 'CompanyA' or 'CompanyB' which works without any problems. In the second drop box I generate the data by choosing the field 'Element', and specify the 'ID' and 'Value' in order to see one value in the drop down list box but register another value in the database.

In order to avoid data cross over effects, I create a filter with the following structure in the second drop down list box:

"Element is equal to current()/my:Element"

However, this generates no values in the drop down box which lets me assume that I am doing something wrong, but I am not sure where I go wrong or if I am trying to do something that can't be done. According to your tutorial on repeating cascading lists this should work, no?

Thank you for all the help you can provide

Regards,
Johan

Re: Cascading list issue

Greg Collins — Tue, 24 Jan 2006 17:57:01 GMT

Will you provide more technical information of what you are doing, such as actual code snippets. It is difficult to ascertain the problem at this point.

Greg Collins [InfoPath MVP]
InfoPath Dev

Re: Cascading list issue

Matt Faus — Tue, 24 Jan 2006 15:29:26 GMT

Also, you might have already seen these, but there are several HowTos that Greg has written concerning cascading lists:

http://infopathdev.com/howto/tutorials/default.aspx?s=Lists

---------------
Matt Faus
InfoPath Dev Texas

Re: Cascading list issue

DrValium — Tue, 24 Jan 2006 11:31:08 GMT

Hi Patrick,

Thank you for the welcome and I greatly appreciate any help either you or Greg can give me=)

Johan

Re: Cascading list issue

Patrick Halstead — Tue, 24 Jan 2006 05:43:11 GMT

Hi Johan and welcome to our forum,
I think it's an issue with your xpath. I'll ask Greg to chime in here.

Patrick Halstead [InfoPath MVP]
InfoPathDev

Cascading list issue

DrValium — Mon, 23 Jan 2006 14:54:59 GMT

Hello,

I have created a form based on a database in Access (primary connection). I have in this form a repeating cascading drop-down list.

When I use the filter "'First drop-down'='First drop-down'" the cascading list works, however as would be expected with data cross over effects (which gets a bit annoying after few selections).

I got the understand after reading articles that the 'current()' function in a filter would solve this. I write "'First drop-down'=current()/my:'First drop-down'" but this generates no options in the second drop-down list.

Is it not possible to eliminate the data cross over effects if you are using a database as your primary connection? Or are I perhaps referencing the wrong field by using 'my:'?

Any suggestions would be appreciated.

Thanks
Johan