Code

Re: Dropdown List Box in Repeating Table

Hilary Stoupa — Wed, 02 Jan 2013 19:16:10 GMT

Yeah - it is being stubborn about previewing.

What I usually do is put my cursor to the left of the expression box and backspace to ensure that there is no carriage return between the controls. I also usually set row height to auto for the row - generally that prevents these issues.

Re: Dropdown List Box in Repeating Table

marymc — Wed, 02 Jan 2013 18:55:36 GMT

Hilary,

I've attached my xsn file. It does use a schema file which I didn't attach and also has a number of data connections to databases on our servers. That is how the Category drop down is populated. Not sure if that will cause you problems.

Also, and this may seem odd but it saved massive changes at the time, the Category field has a label of Purpose. We used to have Purpose > Category but then they decided to switch them around. So if you click on the field following the Purpose label, you will see it is the Category field. In that section I have the button and the expression box. I put the CategorySelected field over to the right as I was trying to reformat things. I made the changes on the Initiator (default) view only.

Mary

Re: Dropdown List Box in Repeating Table

Hilary Stoupa — Wed, 02 Jan 2013 18:29:40 GMT

Feel free to attach your form under the Options tab if you'd like me to take a look.

Re: Dropdown List Box in Repeating Table

marymc — Wed, 02 Jan 2013 18:19:47 GMT

I don't have a carriage return between them. If I make the width smaller on both objects, there is only one line. But I have it set at a width of 336 px to accommodate some long items in the drop down list. If I take the expression box out or take the drop down box out, there is only one line. But with both on the form, even when one is hidden, it is two lines. Thanks for your help Hilary!

Re: Dropdown List Box in Repeating Table

Hilary Stoupa — Wed, 02 Jan 2013 16:34:47 GMT

Do you have a carriage return between the two controls? If you go back to the original sample I posted, you'll see that my row is only one line, regardless which control is displayed.

Re: Dropdown List Box in Repeating Table

marymc — Wed, 02 Jan 2013 16:24:20 GMT

Hilary,

That did it. The button works great. Thanks for your help. I have one final question. This field is contained in a cell of a repeating table. With the addition of the expression box it now takes up two lines instead of one, even though the expression box and drop down don't display at the same time. Is there a way when I hide either one for it not to be taken into account in the display screen so I can keep it all on one line?

Thanks again.

Mary

Re: Dropdown List Box in Repeating Table

Hilary Stoupa — Thu, 20 Dec 2012 20:53:19 GMT

If you add code to your button, you should be able to use something like this to get the purpose:

e.Source.selectSingleNode("ns1:Purpose")

Run in debug on the event on the button so you can look at e.Source. :-)

Re: Dropdown List Box in Repeating Table

marymc — Thu, 20 Dec 2012 20:34:14 GMT

Hilary,

Thanks for your patience :) I run a query for our Purpose drop down below:

adapter.Command = String.Format("SELECT Category from bpiPersonalReimbursementPurposeCategory where Purpose = '" + e.Site.text + "' order by Purpose,Category"

Since I can't use e.site.text because I won't be on the Purpose.OnAfterChange event, I'm not sure how to access the Purpose for the expense item in a repeating table that I have moved to. I think that's the last thing missing. Makes sense to re-query on the button. Sorry I didn't realize that. Helps to know that during the onBeforeChange event the DOM is read only.

Mary

Re: Dropdown List Box in Repeating Table

Hilary Stoupa — Thu, 20 Dec 2012 17:56:28 GMT

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.

Re: Dropdown List Box in Repeating Table

marymc — Wed, 19 Dec 2012 21:27:45 GMT

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

Re: Dropdown List Box in Repeating Table

Hilary Stoupa — Wed, 19 Dec 2012 19:45:02 GMT

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.

Re: Dropdown List Box in Repeating Table

Hilary Stoupa — Wed, 19 Dec 2012 19:42:22 GMT

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.

Re: Dropdown List Box in Repeating Table

marymc — Wed, 19 Dec 2012 19:27:59 GMT

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

Re: Dropdown List Box in Repeating Table

aaronthompson528 — Wed, 19 Dec 2012 17:31:05 GMT

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.

Re: Dropdown List Box in Repeating Table

Hilary Stoupa — Wed, 19 Dec 2012 17:06:19 GMT

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?