InfoPath Dev

We have a personal reimbursement application where they enter some basic information about the user, then can enter 1 or more expense items into a repeating table in a repeating section.  Two of the fields in the repeating table are Purpose and Category.  Purpose is a drop down list box where the choices have been entered manually.  Category is a drop down list box which uses a data source to query a SQL table based on the Purpose chosen.  The last bit of code for the Purpose.OnChange event populates the Category drop down box:

'PopulateCategory
If e.Site.text <> "" Or e.Site.text <> "Select" Then
Dim adapter As ADOAdapterObject
adapter = CType(thisXDocument.DataAdapters("PurposeCategory"), ADOAdapterObject)
adapter.Command = String.Format("SELECT Category from bpiPersonalReimbursementPurposeCategory where Purpose = '" + e.Site.text + "' order by Purpose,Category")
adapter.Query()
Dim adap As New SqlDataAdapter("SELECT Category from bpiPersonalReimbursementPurposeCategory where Purpose = '" + e.Site.text + "' order by Purpose,Category", strConnString2)
Dim dsDat As New DataSet
adap.Fill(dsDat)

If the uses stays on that records they can switch between different Categories.  They can also switch to a different Purpose and then a new list of Categories will display in the drop down box.

When the user enters another expense item, if they go back to one they entered previously and choose the Category drop down, only the selected Category displays, not the entire list related to the Purpose.

I have tried adding the above code to the Category field, modified a bit since we're not on Purpose, but now, after they've entered a second expense item and go back to the first, the Category choices for the second expense item appear in the first one.  So it seems to be using the Purpose from the current expense item to populate Category fields on other expense items.

It seems like this should be simple.  I'm dimming relevant variables as follows in Category.onChange

Dim Category As IXMLDOMNode = e.Site

Dim Purpose As IXMLDOMNode = Category.selectSingleNode("../ns1:Purpose")

Should Purpose reflect the purpose of the current expense item?

Any help would be appreciated.  We've struggled with this problem for several years but I've finally been told that it's time to fix it.  Thanks in advance. 

Mary Mc

 

Well - the root of your issue is that you are dealing with a repeating table.

You have a data source - PurposeCategory. You query it for a given purpose, and get your categories for the first row. The user enters a second row. She selects a different purpose, which returns a different list of categories. If you go click on the category drop down in the first row, what would you anticipate seeing? The data source currently holds values for the categories based on the query you made for the second purpose - does that make sense? And there is no action to requery for the first purpose - since that is running on the change of the purpose field, and the purpose field in the first row has not changed....

 Does that make sense? Temporarily put the data fields from the PurposeCategory on your form and preview and try adding some different rows - I think that will help you see what I mean.

So, on to some work arounds - is this a browser form? Or do you fill it out in InfoPath? If it is a browser form, if you are using SharePoint / InfoPath 2010, you can use filters on your drop downs and you could change your query to return data for all purposes that have been selected in the form, include the purpose with the category in the data source and add a filter to the category list.

However, I'm guessing that the reason this code was implemented was because browser forms in IPFS 2007 didn't allow filters. If so, there are some other options that may be acceptable for you - just let me know the details and I can make a few suggestions. :-)

Hilary,

Thanks for your reply.  Our form is a browser form accessed through SharePoint.  We use Infopath 2007.  I looked up filters in Help but as you mentioned they aren't available in 2007.  I would love to hear your suggestions :)  Thanks!!

 

Mary

So, I'd take a UI approach in this case. Since we don't have filters available for IPFS 2007, you need to make sure your query re-executes for the correct product when the user goes to a different row and wishes to change categories.

One approach I've seen that I like is in the attached sample form  - I'm not using any code or secondary data connections here because I want you to be able to try the form in preview. Just save it locally, right click and select design and then preview the form - after a user selects a category, the category is displayed in an expression box & they have to click a button to change it. You'd add your code to the button to requery for that row's product so that when the drop down is displayed again, the correct data has been loaded.

Hilary,

I have a couple of questions re your soluton. 

1.  If I use an expression box, will the data in the box be saved when the form is submitted?  The form goes through any number of approvers before the data is written out to our accounting system.  I thought I remembered that expression boxes didn't save the data.

2.  If I use a button to requery, how can I get the Purpose for that particular table row. 

3.  I wonder if I could put code on Category.OnBeforeChange to run the query right when they click the down arrow button but here too I don't know how to get the Purpose for that row as it currently uses the Purpose from the row where I last selected a Purpose.  Is there a way to determine which row I'm in, and then get the Purpose for that row,  Using things like : '../preceding-sibling::my:item[1]/ - that type of syntax.

Your help is greatly appreciated.

Mary

 

The expression box is bound to the category field. The category field is storing the data. So, the field saves the data, and the expression box just displays it.

The button knows what row it is in. The code you posted in your original post used e.Site to get the value of Purpose. You can add code to your button and use e.Source to get that instance of the repeating group. Probably something like e.Source.selectSingleNode("ns1:Purpose") to get the value for that specific row....

Thanks Hilary.  I'm on vacation now for a couple of weeks but will look at it again when I get back.

 Hilary,

I have the same problem. I have a drop-down list box inside a repeating group that is being populated by a SharePoint list. When there is only one group it works fine, as soon as you add another group, it no longer works. What can I do or is this not possible in InfoPath? 

Actually, it sounds like you might have a different problem - how is your form set up? Do you query for your SharePoint list items on load? Or are you querying for just the SharePoint items you need in each group (which can cause an issue like I described). Are you using filters on your drop down? Is your form a browser form or do you use InfoPath to fill it out?

 I have a repeating group that has fields 1, 2 and 3. Field 2 is a dropdown box which pulls information from SharePoint site items to populate the box. We are using a web service to fill this out.  

Hi Hilary,

I'm back from vacation and added your idea to my per reim form.  I am getting an error and am wondering if it is because my drop down list uses a data connection called PurposeCategory to populate it.  Here is the error message after I select an item from the Category field (which never populates).

 System.Runtime.InteropServices.COMException
The query cannot be run for the following DataObject: PurposeCategory

InfoPath cannot run the specified query.
InfoPath cannot update the data, because the form is currently read-only.

   at Microsoft.Office.Interop.InfoPath.SemiTrust.ADOAdapter2.Query()
   at Microsoft.Office.Interop.InfoPath.SemiTrust.ADOAdapterObjectWrapper.Query()
   at PersonalReimbursements.PersonalReimbursements.Category_OnBeforeChange(DataDOMEvent e)
   at Microsoft.Office.Interop.InfoPath.SemiTrust._DataDOMEventSink_SinkHelper.OnBeforeChange(DataDOMEvent pDataDOMEvent)

What do you think?  THANKS!

Mary

So - to aaronthompson528 - I'm not clear what "using a web service to fill this out" means - you may want to start a new thread w/ the details on your form, and maybe under the Options tab attach a sample so we can get a better idea of the issue you are having.

Hey, Mary - it is kind of hard to track down what may be going wrong with just that error info - can you run your code in debug? That would give you a better idea of what is happening. Here's a blog post to help you run in debug if you need it.

Hi Hilary,

I ran my code in debug and saw where the error was occurring.  I was occurring on code I had on the Category_OnBeforeChange.  I had added that code there awhile back in an attempt to repopulate the Category drop down based on the Purpose  which is my issue here.  The code is below.  The error occurred in the commented out code at 'adapter2.Query(). 

Regarding your suggestion, I have changed my form so it contains the expression box and button and the rules and conditional formatting.  I also added the CategorySelected field to my schema.  The CategorySelected field is changing from true to false where appropriate and fields are displaying based on that value.  When I create my first expense item I am able to select my Category as usual.  When I create my second expense item, I can select my Category.  But the problem still remains when I go back to the first expense item and click the drop down I see the categories for the second expense item, the one I just inserted,  and not the current expense item.  I'm unclear how the drop down on the Category field on the first expense item will be re-populated.  You mentioned using e.source.selectsinglenode("ns1:Purpose") but I wasn't clear about that.  Thanks again for your help!!  Mary

<InfoPathEventHandler(MatchPath:="/ns1:ProcessDocument/ns1:Document/ns1:PersonalReimbursement/ns1:ExpenseItems/ns1:item/ns1:Category", EventType:=InfoPathEventType.OnBeforeChange)> _
Public Sub Category_OnBeforeChange(ByVal e As DataDOMEvent)
Dim Category As IXMLDOMNode = e.Site
Dim Purpose As IXMLDOMNode = Category.selectSingleNode("../ns1:Purpose")
If Purpose.text <> "" Or Purpose.text <> "Select" Then
'Dim adapter2 As ADOAdapterObject
'adapter2 = CType(thisXDocument.DataAdapters("PurposeCategory"), ADOAdapterObject)
'adapter2.Command = String.Format("SELECT Category from bpiPersonalReimbursementPurposeCategory where Purpose = '" + Purpose.text + "' order by Purpose,Category")
'adapter2.Query()  *****ERROR OCCURRED HERE
adap8 As New SqlDataAdapter("SELECT Category from bpiPersonalReimbursementPurposeCategory where Purpose = '" + Purpose.text + "' order by Purpose,Category", strConnString2)
Dim dsDat8 As New DataSet
adap8.Fill(dsDat8)
End If
End Sub

In an on before change, the DOM is read only - you need to run your code in a changed event.

I feel like I'm not explaining well - and I am kind of at a loss as to how to explain better - I'd hoped the sample form would help. The thing is that your PurposeCategory data source only has the items for the second purpose when you query it for the specific purpose. Your button for redisplaying the drop down needs to run the same query again for the purpose for that row. You need to add code to the button to run the filtered query again.