Custom shape returns first row

[Carl]

First, I'm a rookie at InfoPath. Have read one book and made only a couple of
forms. I'm accessing Small Business Accounting Views to populate a drop-down
and then using rules to set several values in my form.

My Data Connection is based on this code:

shape
{SELECT a.AccountID, a.PurchasePrice, c.Name as Name, b.Name as ItemGroup,
a.Name as PartNumber, a.PurchaseDescription, a.VendorItemNumber, (b.Name + ';
' + a.Name + '; ' + a.PurchaseDescription) as LongDropDownDesc
FROM NonInventoryItemAccountEntityView a
left outer join ItemGroupEntityView b
ON a.ItemGroupID = b.SupportingTableID
left outer join VendorAccountView c
ON a.PreferredVendorAccountID = c.AccountID
ORDER BY ItemGroup, PartNumber}

Which does populate the drop down successfully.

I then want to put some of the returned values, @Name (for example), into a
TextBox control. To do this I wrote a Rule on the DropDown control which sets
the TextBox VendorName to @Name. The xpath I'm using is:

xdXDocument:GetDOM("ItemDropDown_WithExtraData")/dfs:myFields/dfs:dataFields/d:AccountID/@Name

I can scroll the DropDown and select different items from the list, but I
always get the returned values for the first row in the shape/dataset
returned.

What am I missing??

Thanks very much for the help.
Carl

 

[S.Y.M. Wong-A-Ton]

I'm missing a filter in the XPath expression. Did you use the "Filter
Data..." button on the "Select a Field or Group" dialog box when setting up
the rule? You need to set up a filter that links the selected value from your
drop-down list box to a field in your shape data source.

 

[Carl]

Thanks for the reply.

No, I did not use the "Filter Data.." button on the "Select a Field or
Group" dialog box when setting up the rule.

I created a rule, "Set a field's value", selected the "Field" which is
assigned to the TextBox where I want to direct the data from the DropDown,
and then for "Value" I clicked the expression editor to get to the "Insert
Formula" dialog, next I selected "Insert Field or Group...", then select the
shape from my sql statment as the Data Source and finally the field from the
shape which was Name.

I see the "Filter Data" button at this point, but what do I filter? I
already have the row selected in the DropDown.

Thanks for the ongoing discussion.
Carl

 

[Carl]

Hello again,

Okay, I figured out the filter thing. I am now using one of the fields I
populated in the Main datasource as the filter criteria for the balance of
the selections against the shape datasource I wrote.

Now the repeating section works for three rows and then new rows all return
the same values regardless of selected value in the DropDown.

Any hints on where to look?

Thanks,
Carl

 

[S.Y.M. Wong-A-Ton]

Are all of the values you are using in the drop-down list box unique? That
is, are you using an ID of some sort as the "Value" (not the "Display Name")
of the drop-down list box? If the filter you applied does not filter your
data source down to 1 record, you might experience what you are currently
experiencing.

 

[Carl]

Thanks for the reply.

I am using the AccountID from the SBA view NonInventoryItemAccountEntityView
which is by design a unique ID as the Value for the DropDown. To confirm
this I ran the SQL query in SQL Query Analyzer as is (returned 112 rows),
then with "select distinct" added to the query (also returned 112 rows). I am
reasonably certain the Value field is unique.

Thanks for the ongoing help.

Carl

 

[S.Y.M. Wong-A-Ton]

Is the drop-down list box located in repeating sections? If so, you need to
use current() in the filter to be able to identify the current row (=section)
the drop-down list box is contained in. Search this newsgroup on current() to
find samples on how to do this.

 

[Carl]

Thanks for the reply.

The DropDown was is a repeating section. Current() was the key.

For one of the formulas, this was it:

xdXDocument:GetDOM("ItemDropDown_WithExtraData")/dfs:myFields/dfs:dataFields/d:VendorItemNumber/@PreferredVendorAccountID[../@VendorItemNumber = current()]

And all worked perfectly.

Thanks for dealing with this rookie.

You rock girl!

Carl

 

[S.Y.M. Wong-A-Ton]

Thanks, Carl. Glad you got it working now.
---
S.Y.M. Wong-A-Ton

Thanks for the reply.

The DropDown was is a repeating section. Current() was the key.

For one of the formulas, this was it:

xdXDocument:GetDOM("ItemDropDown_WithExtraData")/dfs:myFields/dfs:dataFields/d:VendorItemNumber/@PreferredVendorAccountID[../@VendorItemNumber = current()]

And all worked perfectly.

Thanks for dealing with this rookie.

You rock girl!

Carl

Is the drop-down list box located in repeating sections? If so, you need to
use current() in the filter to be able to identify the current row (=section)
the drop-down list box is contained in. Search this newsgroup on current() to
find samples on how to do this.