using Current() and repeating sections

[Tim Philomeno]

ok...I have read a bunch of stuff about this but I am so new at using xpath
queries that I am not able to figure out the syntax...and maybe I an not even
close but...

I am trying to populate a ddown list using the value saved in another in a
repeating section....Customers/Contacts....I have read and implemented this
scenario where it is not in a repeating section getting a ref to the
Dataobject for the contacts and setting the query text (parameter) to the
current clientno...and then Query() to refresh...see below.....

begin code
***************************************
Dim theDataObject
theDataObject =
thisXDocument.DataObjects.Item("GetContactsForClientNoCONTACTS")

theDataObject.DOM.setProperty("SelectionNamespaces", _

"xmlns:dfs=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution""" & _
"
xmlns:ns8=""http://tempuri.org/SparCRMService/SparCRMService""")

Dim queryValue
queryValue =
thisXDocument.DOM.selectSingleNode("/my:NewProj/my:Contacts/my:Contact/my:ConName")

queryValue.text = e.NewValue

theDataObject.Query()
***************************************
end of code

So now I want to use Current() in the selectSingleNode call (i assume) but I
can't get the syntax right....

Any help?

Tim Philomeno, Sparling, Inc.

 

[Scott L. Heim [MSFT]]

Hi Tim,

The "current()" function is an XSLT XPath function so you are unable to use
this in code. However, if you are attempting to create "cascading"
drop-down lists, then you can do this with "Rules" and "Filtering" through
the UI.

Can you describe a bit more about what you actually want to accomplish and
I should be able to provide you with sample steps.

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,

Correction: although you may be able to use the "current()" function in
code, you can accomplish what you need through the UI.

Sorry for the confusion.

Scott L. Heim
Microsoft Developer Support

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

 

[Tim Philomeno]

Scott,

If I understand all that I have been reading about rules and filters, I am
not sure that they will work in this case.

I have a Customers dropdown that stores CustID...then I have a Contacts
dropdown that is dependant on CustID....select * from contacts where
CustID='Blah'...

If I were using filters, wouldn't I need to have all Contacts?...unusable
with 25000 contacts coming back to filter out just those for a Customer...

I was hoping to use Current() and selectSingleNode to get the CustID param
of the Dataobject for Contacts and set it to the
Current()/my:fields/blah/blah/custID...

The issue is getting a refernce to the DataObject in the Repeating Table.

Am I making sense?

Thanks!

Tim Philomeno

 

[Scott L. Heim [MSFT]]

Hi Tim,

Maybe this will help:

- When you create your first secondary data connection (i.e. Customers),
insure the option on the last screen (Automatically retrieve data when form
is opened) is *enabled.*
- When you create your Contacts secondary data connection, insure this
option is *NOT* checked
- Specify a filter on your Contacts drop-down list that is based on the
customer selected in the first drop-down list
- Add a Rule to your customers drop-down where the Action is "Query using a
data connection" and then you will want to choose your Contacts data
connection

When I test this using the Customers and Orders tables from Northwind, it
works as expected...I hope you find the same!

Best Regards,

Scott L. Heim
Microsoft Developer Support

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

 

[Tim Philomeno]

Scott,

This all makes perfect sense now but....for some reason it does not work.
When the query fires to fill the contacts drop down, the CustID is
blank.....the field I am pointing to does have a value but it is not being
assigned by the filter and sent on to the database.....my filter says where
myQuery CustID is equal to the CustID being saved by the Customer dropdown...

NOTE: this is all in a repeating table!!!

 

[Scott L. Heim [MSFT]]

Aha! The Repeating Table is the "monkey wrench" here...let me provide some
sample steps for you...please do try these as documented so we can make
sure they work as you need!

- Create a new, blank InfoPath form
- Add a Repeating Table control to the form with just 2 columns
- Change each field to a drop-down list and set the first field name to:
ddlCustomers and the second to: ddlOrders
- Add a data connection to the Customers table from the Northwind sample
database. You will only need the CustomerID and CompanyName fields but do
make sure this data is loaded as the form loads
- Add a data connection to the Orders table from the Northwind sample
database. You will only need the OrderID and CustomerID fields but do make
sure this data is *NOTE* loaded as the form loads
- Right-click on ddlCustomers and choose Properties
- Set the Data Connection to Customers, click the button next to Entries,
highlight d:Customers, click OK, leave the value field as @CustomerID and
click the button next to Display Name to set this to CompanyName.
- Right-click on ddlOrders and choose Properties
- Set the Data Connection to Orders
- Click the button next to Entries and highlight d:Orders

** Important Steps! **

- Click the Filter Data button
- Click Add
- In the first box choose CustomerID
- In the second box choose "is equal to"
- In the third box choose Select a field or group
- From the Data Source box choose Main
- Drill down, select ddlCustomer and click OK
- Click OK again - you should now be at the "Filter Data" window
- Click the Modify button - this should display the 3 boxes where you just
made selections. From the first box choose "The Expression" - you should
now see the XPATH result of the choices you made:

@CustomerID =
xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:ddlCustomer

- Modify this expression to this:

@CustomerID = current()/my:ddlCustomer

- Click OK 4 times to get back to your form

** NOTE: the expression above is obviously based on your naming the
controls as I described.

- Right-click on ddlCustomers and choose Properties
- Click the Rules button
- Click Add
- Click Add Action
- From the Action box choose Query using a data connection
- From the Data Connections box choose Orders
- Click OK 4 times to get back to your form
- Preview the form - you should be able to choose a Customer in the first
row and see just a few related orders.
- Insert a new row and select a different customer - you should now see a
different subset of orders!

I hope this helps!!

Scott L. Heim
Microsoft Developer Support

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

 

[Tim Philomeno]

Scott,

I got that to work....so if I am seeing my sql trace correctly, when the
Customers DD is changed, a query is sent to the SQL Server selecting ALL
orders and they are filtered on the Infopath side?

Of course what I want to do is only select from the SQL Server the ones for
the current customer....send a parameter....i cant handle all 25000 of them

 

[Scott L. Heim [MSFT]]

Hi Tim,

I understand - ok...let's try this:

- Leave the "Rule" on ddlCustomers - this is the one that simply clears the
previous data from ddlOrders.
- Remove the "Filter" from ddlOrders - you will still need a data
connection to Orders and still do *NOT* retrieve the data upon load but you
can remove the filter.
- Right-click on ddlCustomers and choose Properties
- Click Data Validation
- From the Events list choose OnAfterChange and click the Edit button
- You should see something like this:

Sub msoxd_my_ddlCustomer_OnAfterChange(eventObj)

' Write code here to restore the global state.

If eventObj.IsUndoRedo Then
' An undo or redo operation has occurred and the DOM is read-only.
Exit Sub
End If

' A field change has occurred and the DOM is writable. Write code here to
respond to the changes.
End Sub

- Add this code just before the End Sub:

If eventObj.Operation = "Insert" Then
Dim objOrders
Dim strSQL

'This assumes these were the only fields chosen when you setup the
data connection
strSQL = "SELECT CustomerID, OrderID FROM Orders "

Set objOrders = XDocument.DataObjects("Orders")
objOrders.queryAdapter.Command = strSQL & "WHERE CustomerID = '" &
eventObj.Site.text & "'"
XDocument.DataAdapters("Orders").Query
objOrders.queryAdapter.Command = strSQL
End If

** NOTE: The eventObj argument holds a reference to the current row you are
on!

- Test and let me know!!

Scott L. Heim
Microsoft Developer Support

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

 

[Tim Philomeno]

Scott,

I have seen this code somewhere....when I try it in our test form I get the
error:

"Public member 'Command' on type 'DataSourceObjectWrapper' not found."

 

[Scott L. Heim [MSFT]]

Tim,

What is your backend data source? SQL Server, Access, Web Service?

Scott L. Heim
Microsoft Developer Support

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

 

[Tim Philomeno]

SQL Server

 

[Tim Philomeno]

my production form uses a WebService though that serves up a dataset from sQL
Server

 

[Scott L. Heim [MSFT]]

Tim,

That's really odd - I am not sure yet what would be causing this error as I
have not seen this before. Are you using the exact code I provided or have
you modified it slightly? If you have modified it, can you post back what
you are now using?

Scott L. Heim
Microsoft Developer Support

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

 

[Tim Philomeno]

Actually...I converted it to managed code...


If e.Operation = "Insert" Then
Dim objOrders
Dim strSQL

'This assumes these were the only fields chosen when you
setup the data(connection)
strSQL = "SELECT CustomerID, OrderID FROM Orders "

objOrders = thisXDocument.DataObjects("Orders")
objOrders.Command = strSQL & "WHERE CustomerID = '" &
e.Site.text & "'"
thisXDocument.DataAdapters("Orders").Query()
objOrders.Command = strSQL

 

[Scott L. Heim [MSFT]]

Tim,

I have not tried this against a web service but this code now works
directly against SQL Server so it should work in our test form:

If e.Operation = "Insert" Then
Dim objOrders As ADOAdapter
Dim objOrdersDO As DataObject
Dim strSQL As String

'This assumes these were the only fields chosen when you
'setup the data(connection)
strSQL = "SELECT CustomerID, OrderID FROM Orders "

objOrders = thisXDocument.DataAdapters("Orders")
objOrders.Command = strSQL & "WHERE CustomerID = '" &
e.Site.text & "'"
objOrdersDO = thisXDocument.DataObjects("Orders")
objOrdersDO.Query()
End If

Scott L. Heim
Microsoft Developer Support

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

 

[Tim Philomeno]

Scott,

That works on our test form.......

I see what is going on now...when I try to adapt this to my production form
using a webservice I now get "Specified cast is not valid"....error

I assume that the what is being returned from thisXDocument.DataAdapters is
not of type ADOAdapter...???

 

[Scott L. Heim [MSFT]]

Hi Tim,

It took me a little bit but I have this working now against a web service!

Here is the code I am using in the "OnAfterChange" event of my ddlCustomers
drop-down list:

If e.Operation = "Insert" Then
Dim objOrdersDO As DataObject
Dim objDOM As IXMLDOMDocument3
Dim myQueryField As IXMLDOMNode

'Get a reference to the data object
objOrdersDO = thisXDocument.DataObjects("GetOrders")
'Load the data objects DOM into a new DOMDocument variable
so we can
'set the SelectionNamespaces to be able to navigate the
nodes
objDOM = objOrdersDO.DOM

'Set the SelectionNamespaces property
objDOM.setProperty("SelectionNamespaces", _

"xmlns:dfs=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolut
ion""" _
& " xmlns:tns=""http://tempuri.org/NWTables/Service1""")

'Get a reference to the query field from the web method
myQueryField =
objOrdersDO.DOM.selectSingleNode("//dfs:myFields/dfs:queryFields/tns:GetOrde
rs/tns:strCustID")

'Set the text of this query field to the selection made
from the ddlCustomers box
myQueryField.text = e.Site.text

'Execute the query for the GetOrders data connection to
retrieve the records
objOrdersDO.Query()

'Clean up
objOrdersDO = Nothing
objDOM = Nothing
myQueryField = Nothing
End If

** NOTE: There are some changes you will need to make:

- Where I set the "objOrdersDO" variable - you will need to reference the
correct name of your data connection
- Where I use "SetProperty" to set the SelectionNamespaces - the "dfs"
namespace is probably fine but you will need to modify the URL for the
"tns" namespace (you can get this from your web service.)
- Where I am doing a "selectSingleNode" to get to the query field from my
web service - here again, you will need to reference the correct
"queryField" (you can look at your Data Source Task Pane for this)

I think that is it - let me know how this works!

Scott L. Heim
Microsoft Developer Support

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

 

[Tim Philomeno]

Scott...that did the trick....but......

Now when I go to the next section and choose a different customer, the
contact dropdown looses its list and so the value shows up in the dropdown
instead of the description and the dropdown list is changed to the new
customer's list....

So, my conclusion is that the dropdown list data does not stay with the drop
down of a repeating section so the ability of the dropdown list to display
the correct description is lost when the data is re-quereied.... that
doesn't seem right...shouldn't there be a mechanism to allow the dropdown
data to stay with a control in a repeating section to allow it to support
that control throughout the life of the form?

By the way, I really appreciate you hanging in there with me...I have
learned a great deal in the last few days ....

Tim P

 

[Scott L. Heim [MSFT]]

Hi Tim,

I do not experience that behavior...so I can confirm my test: are you using
a Repeating Table or Repeating Section?

Thanks,

Scott L. Heim
Microsoft Developer Support

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