Rules Applying to Dropdowns sometimes...

[Michael Sainz]

Hey all,
Have an interesting question here. I have a drop down box that gets its
values from a table called Products that is linked to by a secondary data
source. Its Display is "model" and it's value is "productid". I also have a
text box that is bound to a field called "UnitPrice". All of this is in a
repeating table.

I have a rule that is bound to the drop-down box named Price Lookup. First
action is querying the database table called Products. The second action is
setting a fields value. It takes the UnitPrice field in the Products table
(which it just queried) and sets the UnitPrice field in the primary data
connection. But this has a filter on it, and the filter checks to make sure
that the ProductID from both tables are equal to eachother so that it gets
the correct unit price.

This solution works for the first transaction line. When I try and add a
second one though, it's like it doesn't query the database again or
something and makes the newly selected product on the second like have the
same price as the price on the first line item.

If you guys need any more information, just ask and any help is much
appreciated!

Michael

 

[Scott L. Heim [MSFT]]

Hi Michael,

Based on your description, it sounds like you will need to implement the
"Current()" function in your filter so you get the appropriate row.

Take a look at:

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.

 

[Michael Sainz]

Thanks Scott for the link.

/Root/Data/Cities/City[ @state = current()/Selection/SelectedState ]

When I follow this and put that into my XPATH, it looks like this...

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitPrice[../@ProductID
=
current()xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:Orders/d:OrderDetails/@ProductID]

But I get an error. I have tried multiple locations for it too but doesn't
want to work. This seems like the solution I want after reading the blog,
but just can't get it to validate the XPATH.

Michael

 

[Scott L. Heim [MSFT]]

Hi Mike,

Using the "current()" function can be a bit confusing!

Let me describe how my sample looks and then I'll include my original and
modified XPath expression.

1) I created a new, blank form
2) Added a data connection to the Products table from the Northwind sample
database
3) Added a Repeating Table, with just 2 columns, to my form
4) Changed the text box in the first column to a drop-down list and changed
the name to: fldProduct
5) Changed the name of the text box in the second column to: fldPrice
6) Set the properties of fldProduct so that the "Value" is ProductID and
Display Name is ProductName
7) Added a Rule to fldProduct to set the field value of fldPrice

So basically this is basically the scenario you describe. However, it
appears as though your initial XPath is not correct. What you want to occur
is: "set the value of fldPrice to the UnitPrice field from my secondary
data source where the ProductID field in my secondary data source is equal
to the ProductID from fldProduct on my form."

So, here is what you would need to initially do for the filter:

- Choose to Add a Rule
- Add Action (Set a field's value)
- Click the builder button for the "Field" option
- Select your Main data source and drill down to select the appropriate
field where you want the price to be displayed
- Click the builder button for the "Value" option
- Click Insert Field or Group
- Choose your Secondary Data Source and drill down to the Price data field
- Click Filter Data and then click Add
- In the first drop-down box, choose Select a Field or Group
- Select the ProductID field from your Secondary data source
- In the second drop-down box, leave this as "is equal to"
- In the third drop-down box, choose Select a Field or Group
- Select your Main Data Source and drill down so you can select the
drop-down box that displays your Products (for me, it is: fldProduct)
- Click OK until you get back to the Insert Formula window and click the
Edit XPath option

At this point, my XPath is as follows:

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitP
rice[../@ProductID =
xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:fldProduct]

What you need to do now is change this so the XPath is:

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitP
rice[../@ProductID = current()/../my:fldProduct]

** NOTE: You interpret this as: "Give me the UnitPrice (@UnitPrice) Where
the ProductID (@ProductID) is equal to the product I selected from
fldProduct on the current row I am editing."

When I test this now and add rows to my Repeating Table each price is
correct for the item selected.

I hope this makes sense and I did not confuse you!

Best Regards,

Scott L. Heim
Microsoft Developer Support

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

 

[Michael Sainz]

Hey Scott,
Thanks for the great help. I actually understand it (I think...) and
attacked it the same way you did. I understand that what i'm trying to do is
this.

Get me the @UnitPrice (from the secondary source) when ProductID(from
secondary source) is equal to the ProductID (in the main datasource, and
that is defined by my drop-down box) and place it to @UnitPrice in the main
data connection so the text-box can pick it up and display.

If I am wrong on this, then we may have found the issue

But if i'm not, then your help would be much appreciated again. I have tried
that exact approach as can be confirmed by the following:

.../@UnitPrice[xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@ProductID
= ../@ProductID]

The textbox does not update. So I think it's either Infopath isn't grabbing
the data because the expression is wrong, or it is grabbing the data and the
text-box is not displaying it. Below is what I put in in response to your
helpful post yesterday.

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitPrice[../@ProductID
=
current()/../dfs:myFields/dfs:dataFields/d:Orders/d:OrderDetails/@ProductID]

Thanks so much!
Michael

Hi Mike,

Using the "current()" function can be a bit confusing!

Let me describe how my sample looks and then I'll include my original and
modified XPath expression.

1) I created a new, blank form
2) Added a data connection to the Products table from the Northwind sample
database
3) Added a Repeating Table, with just 2 columns, to my form
4) Changed the text box in the first column to a drop-down list and
changed
the name to: fldProduct
5) Changed the name of the text box in the second column to: fldPrice
6) Set the properties of fldProduct so that the "Value" is ProductID and
Display Name is ProductName
7) Added a Rule to fldProduct to set the field value of fldPrice

So basically this is basically the scenario you describe. However, it
appears as though your initial XPath is not correct. What you want to
occur
is: "set the value of fldPrice to the UnitPrice field from my secondary
data source where the ProductID field in my secondary data source is equal
to the ProductID from fldProduct on my form."

So, here is what you would need to initially do for the filter:

- Choose to Add a Rule
- Add Action (Set a field's value)
- Click the builder button for the "Field" option
- Select your Main data source and drill down to select the appropriate
field where you want the price to be displayed
- Click the builder button for the "Value" option
- Click Insert Field or Group
- Choose your Secondary Data Source and drill down to the Price data field
- Click Filter Data and then click Add
- In the first drop-down box, choose Select a Field or Group
- Select the ProductID field from your Secondary data source
- In the second drop-down box, leave this as "is equal to"
- In the third drop-down box, choose Select a Field or Group
- Select your Main Data Source and drill down so you can select the
drop-down box that displays your Products (for me, it is: fldProduct)
- Click OK until you get back to the Insert Formula window and click the
Edit XPath option

At this point, my XPath is as follows:

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitP
rice[../@ProductID =
xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:fldProduct]

What you need to do now is change this so the XPath is:

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitP
rice[../@ProductID = current()/../my:fldProduct]

** NOTE: You interpret this as: "Give me the UnitPrice (@UnitPrice) Where
the ProductID (@ProductID) is equal to the product I selected from
fldProduct on the current row I am editing."

When I test this now and add rows to my Repeating Table each price is
correct for the item selected.

I hope this makes sense and I did not confuse you!

Best Regards,

Scott L. Heim
Microsoft Developer Support

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

 

[Scott L. Heim [MSFT]]

Hi Mike,

You are close! Here is what I have in my filter expression:

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitP
rice[../@ProductID = current()/../@ProductID]

** NOTE: This is modified slightly as my previous sample was not bound to a
database.

However, you do not want to change this:

current()/../@ProductID

Here is what you indicated you had entered:

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitP
rice[../@ProductID =
current()/../dfs:myFields/dfs:dataFields/d:Orders/d:OrderDetails/@ProductID]

I believe if you just modify this to:

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitP
rice[../@ProductID = current()/../@ProductID]

It should work (obviously this assumes the structure of your datasource is
the same! <G>)

Let me know how it goes!!

Best Regards,

Scott L. Heim
Microsoft Developer Support

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

 

[Michael Sainz]

Scott,
It worked like a charm! But one thing I do like to do is find out why it
works so I know in the future. It seems to me that the difference between
the one that I entered originally and the one that you recommended is that
mine was fully defining the XPATH
"current()/../dfs:myFields/dfs:dataFields/d:Orders/d:OrderDetails/@ProductID"
while yours was using a relative or shortened down syntax
"current()/../@ProductID". Why wouldn't it worked both ways?

Anyways, I truely do appreciate the help Scott!

Michael Sainz

 

[Scott L. Heim [MSFT]]

Hi Michael,

The reason the full path does not work is XPath is relative after the first
node step. Thus, in your case, it's trying to look for a child node under
the node returned by the current function.

Actually to make this even easier, since we are adding a rule to the
"ProductID" field, "current()" will return the ProductID so all we really
need is this:

xdXDocument:GetDOM("Products")/dfs:myFields/dfs:dataFields/droducts/@UnitP
rice[../@ProductID = current()]

There is a bit more explanation on this at the following URL:

http://blogs.msdn.com/infopath/archive/2004/09/13/228881.aspx

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.