Repeating table in secondary data source

[aturner]

Hi all,

I'm having a problem with matching/filtering the correct data from a
secondary data source. I've already read and tried several remedies from
this forum; several were helpful in other ways, but none have solved this
particular problem. Any assistance would be greatly appreciated. I don't
think it's very complicated, but the explanation may be!

I have an order form with a repeating table, where the user selects a
Product from a drop-down list. I want other fields, such as the product's
Price, to be automatically populated with the correct info associated with
the selected Product. I have used a repeating table so that the user can add
multiple products as needed.

Rather than setting the Price field solely with rules, I have created an XML
file (based on an InfoPath form) to be used as a secondary data source, so
that when prices change, I only have to update them in one place, rather than
in multiple field rules. This secondary file also contains a repeating
table, with one row per product.

Main Form:
Row 1:
-Product1 (drop-down - set to look up values from the Product2 field in
the secondary data source, which works correctly)
-Price1 (text field - value is a formula that gets the price from the
secondary data source, filtered by whether the main form and secondary data
source Product fields match: Price2[Product1 = Product2])

On the first row in the main form, when a Product is selected, the Price
field sets correctly for the selected Product. However, when a subsequent
rows are inserted, the Price fields in the additional rows default to that of
the preceding row's Product, before a Product is selected. Once a Product is
selected on the second row, the Price only updates to match the correct
Product if the selected Product appears in the drop-down list above the
Product selected for the previous row. For example, if the Product drop-down
choices are Product A, B, C:

Row 1
-Product B
-Price B (updates automatically)

Row 2
-Product A
-Price A (defaults to Price B from above, but updates to the correct Price
A after Product A is selected)

Row 3
-Product C
-Price A (defaults to Price A from above and does not update to the correct
Price C, apparently since Product C is listed below Product A in the
drop-down list)

I have tried using rules, filters, and rules with filters, on the Product
and Price fields, all to no avail. I also tried setting the Price field to a
listbox as seen in examples for how to populate a Cascading List, but was
unable to get it to filter and display only the Price (it contained all field
entries from the associated row) -- and a text field is preferable anyway,
since there will only be one line of text and no need to select from a list.

Any ideas on why this is happening and how to fix it? Since I'm not a
programmer, I'd prefer to avoid altering or creating code directly, but could
try that with an example to follow.

Any help will be greatly appreciated!

 

[em]

hi,
I don't know if this can be solved without using any code. I did a try with
some vb-script code: After selecting a product (in the drop-down list) the
script gets the appropriate product in the secondary data source and
populates the element "Price" in the main data source with the price in the
secondary data source. The steps are:

1) Create an "OnAfterChange" event-handler for the element "Product" in the
main data source (which is displayed in the repeating table and is associated
with the drop-down list). You do that in the properties window of the field.

2) Use the following vb-script code (your first line will look a bit
different):
------------------------------------------------------------
Sub msoxd_my_Product_OnAfterChange(eventObj)
If eventObj.IsUndoRedo Then
Exit Sub
End If

If eventObj.Site.text <> "" Then
eventObj.Site.parentNode.selectSingleNode("my:OrderPrice").text =
XDocument.DataObjects("products").DOM.selectSingleNode
("//products/product[ProductName='" & eventObj.Site.text &
"']").selectSingleNode("ProductPrice").text
End If
End Sub
-------------------------------------------------------------------------
Remarks:
1) Adapt the XPath-expression "my:OrderPrice" with the name of the element
"Price" in your main data source.
2) Adapt the name of the secondary data source "products" with the name of
your XML-File.
3) Adapt the XPath-expression "//products/product[ProductName='" to reflect
the structure of your secondary data source (XML-File). "ProductName" is the
element name.
4) Adapt the XPath-expression "ProductPrice" with the name of the element
"Price" in your XML-File.

Good luck!

 

[Nilesh Kelkar]

Hi,

Were you able to get a solution; if so can you share it.

I have the EXACT same situation for updating unitprice on the form from
a secondary database source (depending on what product was selected in
the dropdown). The dropdown lists gets the price correctly for the
first row, but subsequent inserts get the wrong price depending on
their position (in the dropdown selection).

Would really appreciate!!!

Regards,
Nilesh Kelkar
Calgary, Canada

Hi all,

I'm having a problem with matching/filtering the correct data from a
secondary data source. I've already read and tried several remedies from
this forum; several were helpful in other ways, but none have solved this
particular problem. Any assistance would be greatly appreciated. I don't
think it's very complicated, but the explanation may be!

I have an order form with a repeating table, where the user selects a

Product from a drop-down list. I want other fields, such as the product's
Price, to be automatically populated with the correct info associated with
the selected Product. I have used a repeating table so that the user can add
multiple products as needed.

Rather than setting the Price field solely with rules, I have created an XML
file (based on an InfoPath form) to be used as a secondary data source, so
that when prices change, I only have to update them in one place, rather than
in multiple field rules. This secondary file also contains a repeating
table, with one row per product.

Main Form:
Row 1:
-Product1 (drop-down - set to look up values from the Product2 field in
the secondary data source, which works correctly)
-Price1 (text field - value is a formula that gets the price from the
secondary data source, filtered by whether the main form and secondary data
source Product fields match: Price2[Product1 = Product2])

On the first row in the main form, when a Product is selected, the Price
field sets correctly for the selected Product. However, when a subsequent
rows are inserted, the Price fields in the additional rows default to that of
the preceding row's Product, before a Product is selected. Once a Product is
selected on the second row, the Price only updates to match the correct
Product if the selected Product appears in the drop-down list above the
Product selected for the previous row. For example, if the Product drop-down
choices are Product A, B, C:

Row 1
-Product B
-Price B (updates automatically)

Row 2
-Product A
-Price A (defaults to Price B from above, but updates to the correct Price
A after Product A is selected)

Row 3
-Product C
-Price A (defaults to Price A from above and does not update to the correct
Price C, apparently since Product C is listed below Product A in the
drop-down list)

I have tried using rules, filters, and rules with filters, on the Product
and Price fields, all to no avail. I also tried setting the Price field to a
listbox as seen in examples for how to populate a Cascading List, but was
unable to get it to filter and display only the Price (it contained all field
entries from the associated row) -- and a text field is preferable anyway,
since there will only be one line of text and no need to select from a list.

Any ideas on why this is happening and how to fix it? Since I'm not a
programmer, I'd prefer to avoid altering or creating code directly, but could
try that with an example to follow.

Any help will be greatly appreciated!

 

[aturner]

Thanks! My script editor for InfoPath is for Java script, however. Could I
get some help in converting the vb script below into Java?

 

[aturner]

Update: A friend helped me convert it to Jscript. However, now I get the
following error message when I test the form in preview mode:

Expression must evaluate to a node-set
-->0<--

The problem is in the line that begins 'eventObj.Site.parentNode'

Here's the updated code:

function msoxd_my_hwProduct::OnAfterChange(eventObj)
if (eventObj.Site.text != null )
{
eventObj.Site.parentNode.selectSingleNode("my:hwUnitPrice").text =
XDocument.DataObjects("FS Prices").DOM.selectSingleNode("//FS
Prices/hwItems/hwProduct[ProductName='" & eventObj.Site.text &
"']").selectSingleNode("hwUnitPrice").text
}

Suggestions?

 

[NK]

SOLUTION:

For a repeating table, we have a problem for getting the exact node or
parent node. This is solved by using
var oRow = eventObj.Site.selectSingleNode("..");
The oRow now contains reference to the current node in repeating table
details

To set an attribute we use the code below:
oRow.setAttribute("UnitPrice",objCurrentProduct.getAttribute("UnitPrice"));
objCurrentProduct is obtained by querying the secondary db source.

(for details see the entire function below)

function
msoxd__OrderPE_Equipment_ServiceMasterExtendedID_attr::OnAfterChange(eventObj)
{
if(eventObj.IsUndoRedo || "Insert" != eventObj.Operation)
return;
var sID = eventObj.NewValue;

if (null == sID)
{
return;
}
else
{
var objCurrentNode = null;
var oRow = eventObj.Site.selectSingleNode("..");

var objDOM = XDocument.GetDOM("ServiceMasterExtended");
objDOM.setProperty("SelectionNamespaces",'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
' +
'xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"
' +
'xmlns:q="http://schemas.microsoft.com/office/infopath/2003/ado/queryFields"
');

var objCurrentProduct =
XDocument.GetDOM("ServiceMasterExtended").selectSingleNode("//d:ServiceMasterExtended[@ID='"
+ sID + "']");

if (objCurrentProduct != null)
{
var objUnitPrice = objCurrentProduct.getAttribute("UnitPrice");
oRow.setAttribute("UnitPrice",objCurrentProduct.getAttribute("UnitPrice"));
}
else
{
//XDocument.UI.Alert ("Product Not Found");
}

}
}

Even now if you have problems, email to the post and I will try to
answer.

NK
Calgary, Canada

 

[NK]

ANOTHER QUICK SOLUTION WITHOUT SCRIPTING

hey guys, there is a problem to fetch the current node in a repeating
table, so lets just do this.

For your dropdown box that contains product list
1. double click the dropdown.
2. click on "Rules"
3. click "Add"
4. Set a Fields value.
5. In Field- select the field (text box) that you need to update when
user changes dropdown selection
6. In Value- select the value (probably coming in from a secondary
source), do so by clicking on FX button. (then insert field, filter the
data appropriatly)
7. Click OK

Now the XPATH value needs to be changed.

8. I will just show my example.

CHANGE THE XPATH FROM:
xdXDocument:GetDOM("ServiceMasterExtended")/dfs:myFields/dfs:dataFields/d:ServiceMasterExtended/@UnitPrice[../@ID
=
xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:OrderMaster/d:OrderPE_Equipment/@ServiceMasterExtendedID]

TO:
xdXDocument:GetDOM("ServiceMasterExtended")/dfs:myFields/dfs:dataFields/d:ServiceMasterExtended/@UnitPrice[../@ID
= current()/../@ServiceMasterExtendedID]

I changed the part after "=" to get
current()/../@ServiceMasterExtendedID

Hope this is all understood.

CREDITS
I got this information from
http://www.wssdemo.com/Pages/infopath_lookup_of_list_data.aspx?menu=Articles

Thought my explaination would point out correctly as to what needs to
be changed.

NK

 

[aturner]

Many thanks for all of your efforts on this! This is similar to what I had
done before. Without the extra step of changing the XPath, the original
problem exists. However, when I change the XPath as instructed below, the
Price doesn't update at all (it stays at the placeholder text of '0').

This is maddening, because it seems like your solution should work. I'm
sure that it would with only a minor change ... if only I knew what that
minor change should be!

Here's my XPath formula:
xdXDocument:GetDOM("FS
Prices")/my:myFields/my:hwItems/my:hwUnitPrice2[../my:hwProduct2 =
current()/my:myFields/my:hwItems/my:hwProduct]

I don't know why mine doesn't have the '@' symbol in front of the field
names. Could that be an issue?

 

[aturner]

Thank you. I tried this, but kept getting an error message saying that white
space was missing. Perhaps I'll have more luck with your alternative that
doesn't involve scripting.