Newbie question about linking listbox and textbox to same XML table

[jeffwoiton]

I am trying to create a custom sales order sheet which calls data from
an XML table originally created in Access. What I want to do is select
a product description from a listbox in one column and have it populate
the unit price into a textbox in the next column.

My table looks kinda like this (simplified):
WidgetX, $100
WidgetY, $200
WidgetZ, $300
So if the users select WidgetY from the listbox, I want it to put $200
in the Unit Price textbox. I can't seem to figure out how to do this,
InfoPath seems to want to return the first item in the unitPrice field
($100, in this example).

On top of that, I want users to be able to have multiple listboxes in
order to put multiple items on the sales order, all of them calling
from the same XML list, but when I tried doing that all the listboxes
are linked and I get the same choice in all my listboxes. I want each
listbox to have a discrete choice, and send the corresponding unitPrice
into a textbox to its right. From there, I know how to sum it up, add
sales tax, etc., but this part baffles me.

I'm pretty smart and stuff but can't seem to figure this one out.
Anyone ever do this before?

 

[Scott L. Heim [MSFT]]

Hi Jeff,

What you are trying to accomplish is a very common scenario and many folks
have run into exactly what you are experiencing.

Basically, if I understand correctly, what you want to have is
"synchronized" drop-down boxes. For example, if I choose "Breakfast" from
the first list, I want the second list to only show breakfast foods.
However, if I choose "Dinner" then the second list should only display
dinner foods.

In order to accomplish this, you will need to use a Filter and implement
the "current()" function in the XPath filter expression. The following link
has this explained in detail along with samples:

http://blogs.msdn.com/infopath/search.aspx?q=current()&p=1

I hope this helps!

Best Regards,

Scott L. Heim
Microsoft Developer Support

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

 

[Shemp]

Thanks for your quick reply, Scott. You're close, but what I was
wanting is a dropdown for Product Description and a textbox for Unit
Price. As in my previous example, if a user selects WidgetY from the
Description dorpdown, I want WidgetY's price ($200) to appear in the
UnitPrice textbox.

Are you saying that I need to use a dropdown rather than a textbox for
Unit Price?

I followed the example in the link you mentioned and didn't get the
desired result. Thanks for any additional help you can give me.

 

[Scott L. Heim [MSFT]]

Hi,

Sorry for the mis-information. Here are some "better" steps!

1) I exported the Products table from Access to an XML file
2) Created a new, blank form
3) Added a new data connection (Receive Data) to the Products XML file
4) Added a Repeating table to my form with 2 columns
- Renamed the first column to: txtProduct
- Renamed the second column to: txtCost
- Right-clicked on txtProduct and changed this to a drop-down list

5) Displayed the Properties window for txtProduct and set the Data
Connection to Products, selected Products for the Entries box, set the
Value to ProductID and the Display Name to ProductName
6) Right-click on txtProducts, display the Properties, click Rules and the
click Add
7) Click Add Action and then select Set a Fields Value
8) In the "Field" box, click the builder button so you can select "txtCost"
from you Main data source
9) In the "Value" box, click the builder button and then click Insert Field
or Group
10) Choose the Product Secondary Data Source
11) Expand Products and select Unit Price
12) Click the Filter Data button and then click Add
13) In the first drop-down box, choose Insert Field or Group and select
ProductID from the "Secondary" (Products) data source
14) In the second drop-down box, leave this as: "is equal to"
15) In the third drop-down box, choose Insert Field or Group and then
choose txtProduct from your *Main* data source
16) Click OK 3 times to get you back to the Insert Formula window
17) Click the Edit XPath option - your Formula should look something like
this:

xdXDocument:GetDOM("Products")/dataroot/Products/UnitPrice[../ProductID =
xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:txtProduct]

18) Change this to:

xdXDocument:GetDOM("Products")/dataroot/Products/UnitPrice[../ProductID =
current()]

19) Click OK until you are back to the form and test!

Again, my apologies for initially providing the incorrect information -
hopefully this is now correct!

Best Regards,

Scott L. Heim
Microsoft Developer Support

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

 

[Shemp]

Thanks so much, Scott, this works like a champ!
I appreciate the extra effort in looking into this.

 

[Shemp]

OK, one last question. I can't figure out how to make conditional (If
.... Then) statements.

For example, here's what I have:
Subtotal $____________
[ ] Discount _____%
Discount Amount $____________
[ ] WA State Sales Tax ___%
Sales Tax Amount $____________
Grand Total $____________

Probably doesn't line up too well, but I hope you get the idea. Those
are checkboxes next to discount and sales tax. If they are checked, I
want the Grand Total to include them in the formula; if not, or if they
are hidden (they are both within separate option groups), I want Grand
Total to either ignore them or treat them as zero. The thing is, I
don't want all of my customers to know that discounts are available, so
if they're not getting one I would rather hide it, but it needs to be
factored into Grand Total. So Grand Total needs to check to see if
it's visible or checked or whatever, then act accordingly. Currently,
Grand Total returns NaN until I make the Discount Amount visible, so
even though it shows $0 it's not acceptable - I want it completely
hidden if not needed, but Grand Total needs to check if it's visible.
Clear as mud?

Can this be done, and if so how? Thanks for looking into this!