using Current() and repeating sections

[Tim Philomeno]

Repeating Section....you would need to add another piece of data...to the
orders dropdown....to simulate a value/description scenario...

 

[Scott L. Heim [MSFT]]

Hi Tim,

Thanks for the clarification - I do indeed see that behavior. I need to
work on this more - as soon as I have an update I will post it.

Thanks,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Scott L. Heim [MSFT]]

Hi Tim,

OK - in thinking about this more, it makes sense...let me see if I can
explain:

- We are binding the ddlOrders list to the results of a web service call
where the results are dynamic
- The first time we make a selection (i.e. a CustomerID of ALFKI) the
results are returned and all is well

** At this point, the *only* data returned are those Orders for ALFKI.

- We then add a new section/row and select a different CustomerID. At this
point, the ALFKI data is now gone. As such, the only option for the first
row is to display the "bound" value since it cannot find the actual
customer to show the "display" information.

I didn't notice this because I typically use just the OrderID field when I
do these samples -- my apologies. So this is expected behavior but there
may be a couple of options:

- Revert to using Filters (I realize this could be a hit when loading that
much data)
- Create an XML file that matches the data structure returned by the Orders
web method and after querying for the data you would append the results to
this XML file. The XML file would then be used as the data source for the
ddlOrders drop-down box.

The down-side that I see to the XML file solution is this: if you include
it as a Resource file, the data will only persist as long as that instance
of InfoPath is open. Once it is closed, the results you have written to
that file are lost. If you use a persisted XML file (say on a share) then
if you keep appending the data when do you stop? At some point, this would
get as big as the data coming from SQL Server.

Let me know if this makes sense...

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Kevin McNeill]

Scott I have a similar problem that I have run up against and was wondering
if you could help. I have a repeating section that that has a drop down list
that is filtered by a drop down list outside the repeating section. It works
fine for the first section however when I insert a new section the drop down
list does not always update the other text boxes that are linked to it. I am
using filters to limit the vaules of the drop down box inside the repeating
section. The text boxes inside the repeating section also are filtered based
upon the value of the drop down box inside the repeating seciton. All of the
data was added to the form.

 

[Scott L. Heim [MSFT]]

Hi Kevin,

Could you provide some additional detail as to your setup? For instance, is
your form bound to a data source (i.e. database or web service?)

Also - does the process work as you desire if you move the drop-down box
inside the repeating section? I realize this may not be the ultimate way
you want to design the form but it is merely a test to see if you get the
results you desire.

Lastly, I would like to confirm what you are trying to achieve:

Let's say your drop-down list that is outside of your repeating section
simply displays customer names. My understanding is that you would like to
have this work as follows:

- Add a Repeating Section of data
- A drop-down list in this section is filtered based on the customer
selected (i.e. it shows only those orders for the selected customer)
- You select a specific order from the drop-down list and the other fields
(i.e. order date, shipper, freight amount, etc.) are automatically filled in
- Add another repeating section and complete the above steps but the
problem is the "other fields" are not filled in the 2nd time

Is my understanding correct?

Thanks,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Kevin McNeill]

Additional Details: No the form is not bound to a database...not at least in
my mind. What I did, using your analogy of customers, was build a list of
customers and thier orders in an excel file and saved that file as xml data.
I then tied a dropdown listbox to the customers names (that has duplicates in
the dropdown) in the excel data. I then created a dropdown list box inside a
repeating section that lists purchace order numbers that is also linked to
the excel data file and filtered based upon the selection of the customer
name. Once the purchase order is selected other details about the purchase
order are filled in on the repeating section. This process repeats itself
with all future repeating sections based upon the customer name selected
orginally. I think you understand exactly what I am tring to do. I am going
to test putting the customer name dropdown list box inside the repeating
section and see what happens. I would like to add that as it currently
stands the form works when it wants to. When I select the customer name the
purchase order numbers are not filtered even the though the repeating seciton
filter is set to filter the data by the name. I can select a purchase order
number and it does fill in the other fields in the repeating seciton however
subsequent repeating sections behave sporadically when different purchase
order numbers are selected, sometimes updating the other fields in the
repeating seciton and other times just changing the dropdown list box value.

 

[Kevin McNeill]

Hi Scott,
I tried moving the customer names drop downlist box into the repeating
section and got the same results.

 

[Kevin McNeill]

Hello Again,
Just wanted to provide you with some addtional detail. I customized an
existing template called project plan. As part of the customization in the
Data Source I created several new fields? Not sure what they are called. I
created a non-repeating group called customers (to stay with our theme, mine
is actually contact info) with two fields customer lastName and customer
firstName. I then created a child repeating group under Customers that has
the purchaceOrder field and various other purchaseOrder details. The excel
data is a secondary data source that customers field and purchaseOrder fields
are populated with.

Thanks, Kevin
Kevin McNeill

 

[Scott L. Heim [MSFT]]

Hi Kevin,

I think we need to talk about design a bit. What you are trying to achieve
is hierarchical data: one customer can have many purchase orders and one
purchase order can have many line items.

-> Customer1
----> PurchaseOrder1
----------> Line 1
----------> Line 2
----> PuchaseOrder2
----------> Line1
----------> Line2
----------> Line3
-> Customer2
----> PuchaseOrder3
----------> Line1

As you have seen, Excel is not conducive to this design as the data needs
to be "flattened" so it can be shown in Excel (i.e. Customer Name is
repeated multiple times.) So my question is this: what is the end goal you
need to achieve? Does your company have a list of customers somewhere that
you can use as a secondary data source? Where will the data in these
purchase orders be stored? Will you want to do reporting on the data itself
or do you just need to keep a record of each order? If you wanted to
actually do reporting on the data itself (i.e. we sold x number of WidgetA)
then storing the data from the purchase orders themselves in a database
makes that reporting easy. In this scenario, you would have a form created
from a database but you could also publish the form to, say, Sharepoint
where you would have a central repository for all created purchase orders
(you could achieve the same result if you just published your form to a
shared location that all users could access.)

I don't mean to get on a soap box but what you are asking is quite common
but I want to be sure of the design you are using and where you want this
project to end up.

If you would like some sample steps using the Northwind sample database I
would be happy to generate those for you so you can see how this would work
when the data design is proper.

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[MarAms]

Hi there,

a colleague pointed me to this blog and the suggestion about the filtering
of cascading drop-downs with data from a secondary connection.
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

 

[Cristina]

Hello,

I am trying the same thing. The data is displayed correctly on the infopath
form, but it is not stored correctly in the xml.
Is there a way to fix this?

Thank you,
Cristina