Mapping secondary data to main source

[BFSmith]

I have a Returns Authorization form that has Customer info in it
(CustNumber,CustName,Address1, etc) in the main data source. I have a
secondary data source (CustomerMaster) that I would like to use a dropdown
list box to select the proper Customer Number (It's be nice to display the
Number & Name in the dropdown) and map those fields into the Main data
source. I think I've read that I need to use code attached to the drop down
field on the OnAfterChange event. Any examples?

 

[Adam Harding]

I don't think you need code. Just add your main datasource back in as a
secondary datasource and populate the drop-down list with customer number and
use filtering to also show the correct name.

Job done.

Cheeres Adam

 

[BFSmith]

That doesnt make much sense to me...notice my <= pointer to a problem...I
think I have other problems also...here is the codse so far:

function msoxd__tblRAHeader_CustomerNum_attr::OnAfterChange(eventObj)
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}

// A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

// Retrieve the vwCustomerInfo_basic secondary data source
// (the DOM of the vwCustomerInfo_basic Data Object)
//var producPriceAuxDom = XDocument.DataObjects("vwCustomerInfo_basic").DOM;
var vwCustomerInfo_basicAuxDom =
XDocument.DataObjects("vwCustomerInfo_basic").DOM;

// Retrieve the SQL statement of the data source
var strSQL =
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command;
XDocument.UI.Alert(strSQL);

// Retrieve the field in the InfoPath form whose value will be used in the
WHERE clause
var node = XDocument.DOM.selectSingleNode("/my:myFields/my:CustomerNum");
XDocument.UI.Alert(node == null); //<= returns true..field name is
CustomerNum???????
XDocument.UI.Alert(node.text);

// Generate the new SQL statement with WHERE clause
strSQL += " where tblRAHeader.CustomerNum = ' " + node.text + " '";

// Populate the command with the new SQL statement
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects["vwCustomerInfo_basic"].Query();

// Find the price in the secondary data source and the main data source
var CustomerNameAuxNode =
vwCustomerInfo_basicAuxDom.selectSingleNode("vwCustomerInfo_basic:Name");
var CustomerNameFormNode = eventObj.Site.selectSingleNode("../my:CustName");

// Update the main data source with the queried value;
CustomerNameFormNode.text = CustomerNameAuxNode.text;

}

 

[BFSmith]

Got past the FormDataRetrieval problem by using:
var strCustomerId = eventObj.NewValue; ??? Why didnt

var node = XDocument.DOM.selectSingleNode("/my:myFields/my:CustomerNum");

work?

Also, now I am getting a secondary data source namespace problem with

var vwCustomerInfo_basicAuxDom =
XDocument.DataObjects("vwCustomerInfo_basic").DOM;

The query blows out because the namespace isnt vaild.

That doesnt make much sense to me...notice my <= pointer to a problem...I
think I have other problems also...here is the codse so far:

function msoxd__tblRAHeader_CustomerNum_attr::OnAfterChange(eventObj)
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}

// A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

// Retrieve the vwCustomerInfo_basic secondary data source
// (the DOM of the vwCustomerInfo_basic Data Object)
//var producPriceAuxDom = XDocument.DataObjects("vwCustomerInfo_basic").DOM;
var vwCustomerInfo_basicAuxDom =
XDocument.DataObjects("vwCustomerInfo_basic").DOM;

// Retrieve the SQL statement of the data source
var strSQL =
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command;
XDocument.UI.Alert(strSQL);

// Retrieve the field in the InfoPath form whose value will be used in the
WHERE clause
var node = XDocument.DOM.selectSingleNode("/my:myFields/my:CustomerNum");
XDocument.UI.Alert(node == null); //<= returns true..field name is
CustomerNum???????
XDocument.UI.Alert(node.text);

// Generate the new SQL statement with WHERE clause
strSQL += " where tblRAHeader.CustomerNum = ' " + node.text + " '";

// Populate the command with the new SQL statement
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects["vwCustomerInfo_basic"].Query();

// Find the price in the secondary data source and the main data source
var CustomerNameAuxNode =
vwCustomerInfo_basicAuxDom.selectSingleNode("vwCustomerInfo_basic:Name");
var CustomerNameFormNode = eventObj.Site.selectSingleNode("../my:CustName");

// Update the main data source with the queried value;
CustomerNameFormNode.text = CustomerNameAuxNode.text;

}

I don't think you need code. Just add your main datasource back in as a
secondary datasource and populate the drop-down list with customer number and
use filtering to also show the correct name.

Job done.

Cheeres Adam

 

[Adam Harding]

BFSmith

I currently DONT use code to do this in upwards of 4 or 5 forms that are
currently in use. Which is why i mentioned it

That doesnt make much sense to me...notice my <= pointer to a problem...I
think I have other problems also...here is the codse so far:

function msoxd__tblRAHeader_CustomerNum_attr::OnAfterChange(eventObj)
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}

// A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

// Retrieve the vwCustomerInfo_basic secondary data source
// (the DOM of the vwCustomerInfo_basic Data Object)
//var producPriceAuxDom = XDocument.DataObjects("vwCustomerInfo_basic").DOM;
var vwCustomerInfo_basicAuxDom =
XDocument.DataObjects("vwCustomerInfo_basic").DOM;

// Retrieve the SQL statement of the data source
var strSQL =
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command;
XDocument.UI.Alert(strSQL);

// Retrieve the field in the InfoPath form whose value will be used in the
WHERE clause
var node = XDocument.DOM.selectSingleNode("/my:myFields/my:CustomerNum");
XDocument.UI.Alert(node == null); //<= returns true..field name is
CustomerNum???????
XDocument.UI.Alert(node.text);

// Generate the new SQL statement with WHERE clause
strSQL += " where tblRAHeader.CustomerNum = ' " + node.text + " '";

// Populate the command with the new SQL statement
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects["vwCustomerInfo_basic"].Query();

// Find the price in the secondary data source and the main data source
var CustomerNameAuxNode =
vwCustomerInfo_basicAuxDom.selectSingleNode("vwCustomerInfo_basic:Name");
var CustomerNameFormNode = eventObj.Site.selectSingleNode("../my:CustName");

// Update the main data source with the queried value;
CustomerNameFormNode.text = CustomerNameAuxNode.text;

}

I don't think you need code. Just add your main datasource back in as a
secondary datasource and populate the drop-down list with customer number and
use filtering to also show the correct name.

Job done.

Cheeres Adam

 

[BFSmith]

So, how do you do it then? I'll need an example probably.

BFSmith

I currently DONT use code to do this in upwards of 4 or 5 forms that are
currently in use. Which is why i mentioned it

That doesnt make much sense to me...notice my <= pointer to a problem...I
think I have other problems also...here is the codse so far:

function msoxd__tblRAHeader_CustomerNum_attr::OnAfterChange(eventObj)
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}

// A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

// Retrieve the vwCustomerInfo_basic secondary data source
// (the DOM of the vwCustomerInfo_basic Data Object)
//var producPriceAuxDom = XDocument.DataObjects("vwCustomerInfo_basic").DOM;
var vwCustomerInfo_basicAuxDom =
XDocument.DataObjects("vwCustomerInfo_basic").DOM;

// Retrieve the SQL statement of the data source
var strSQL =
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command;
XDocument.UI.Alert(strSQL);

// Retrieve the field in the InfoPath form whose value will be used in the
WHERE clause
var node = XDocument.DOM.selectSingleNode("/my:myFields/my:CustomerNum");
XDocument.UI.Alert(node == null); //<= returns true..field name is
CustomerNum???????
XDocument.UI.Alert(node.text);

// Generate the new SQL statement with WHERE clause
strSQL += " where tblRAHeader.CustomerNum = ' " + node.text + " '";

// Populate the command with the new SQL statement
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects["vwCustomerInfo_basic"].Query();

// Find the price in the secondary data source and the main data source
var CustomerNameAuxNode =
vwCustomerInfo_basicAuxDom.selectSingleNode("vwCustomerInfo_basic:Name");
var CustomerNameFormNode = eventObj.Site.selectSingleNode("../my:CustName");

// Update the main data source with the queried value;
CustomerNameFormNode.text = CustomerNameAuxNode.text;

}

I don't think you need code. Just add your main datasource back in as a
secondary datasource and populate the drop-down list with customer number and
use filtering to also show the correct name.

Job done.

Cheeres Adam

:

I have a Returns Authorization form that has Customer info in it
(CustNumber,CustName,Address1, etc) in the main data source. I have a
secondary data source (CustomerMaster) that I would like to use a dropdown
list box to select the proper Customer Number (It's be nice to display the
Number & Name in the dropdown) and map those fields into the Main data
source. I think I've read that I need to use code attached to the drop down
field on the OnAfterChange event. Any examples?

 

[Adam Harding]

With CustMaster and CustData tables

HAve a drop down list (on field1)which gets its data from Custmaster and
displays cust name.

Field2 is customer Number is set to show the customer number where the cust
name from on the form equals the cust name in CustMaster.

Field3 is the address that show the address where the cust name on the form
is equal to the cust name in CustMaster. Job Done

Every time you select a different name in cust name the customer number and
customer address change automatically by cross checking against your dbase.

To perform these steps go into the properties of Field2. Edit default value
using the fx button. Click insert field or group, now choose the customer
number field from your secondary data source called CustMaster. Now the
important bit, before OK'ing out of there click Filter. Then in the filter
condition specify where Field1(the Customer name on the form) is equal to
customer name (from the secondary datasource called CustMaster) Now OK out of
there.

You now have Field2 showing the correct customer number dependant on what
name you choose on the form, isn't that all you are after?

Furthermore, if you wanted to search on cutomers already in the main
datasource you can add the main datasource back in as a secondray datasource
and use that to set the conditions described above.

I hope this makes sense but would happily expand on this further.

Cheers Adam

So, how do you do it then? I'll need an example probably.

BFSmith

I currently DONT use code to do this in upwards of 4 or 5 forms that are
currently in use. Which is why i mentioned it

That doesnt make much sense to me...notice my <= pointer to a problem...I
think I have other problems also...here is the codse so far:

function msoxd__tblRAHeader_CustomerNum_attr::OnAfterChange(eventObj)
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}

// A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

// Retrieve the vwCustomerInfo_basic secondary data source
// (the DOM of the vwCustomerInfo_basic Data Object)
//var producPriceAuxDom = XDocument.DataObjects("vwCustomerInfo_basic").DOM;
var vwCustomerInfo_basicAuxDom =
XDocument.DataObjects("vwCustomerInfo_basic").DOM;

// Retrieve the SQL statement of the data source
var strSQL =
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command;
XDocument.UI.Alert(strSQL);

// Retrieve the field in the InfoPath form whose value will be used in the
WHERE clause
var node = XDocument.DOM.selectSingleNode("/my:myFields/my:CustomerNum");
XDocument.UI.Alert(node == null); //<= returns true..field name is
CustomerNum???????
XDocument.UI.Alert(node.text);

// Generate the new SQL statement with WHERE clause
strSQL += " where tblRAHeader.CustomerNum = ' " + node.text + " '";

// Populate the command with the new SQL statement
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects["vwCustomerInfo_basic"].Query();

// Find the price in the secondary data source and the main data source
var CustomerNameAuxNode =
vwCustomerInfo_basicAuxDom.selectSingleNode("vwCustomerInfo_basic:Name");
var CustomerNameFormNode = eventObj.Site.selectSingleNode("../my:CustName");

// Update the main data source with the queried value;
CustomerNameFormNode.text = CustomerNameAuxNode.text;

}


:

I don't think you need code. Just add your main datasource back in as a
secondary datasource and populate the drop-down list with customer number and
use filtering to also show the correct name.

Job done.

Cheeres Adam

:

I have a Returns Authorization form that has Customer info in it
(CustNumber,CustName,Address1, etc) in the main data source. I have a
secondary data source (CustomerMaster) that I would like to use a dropdown
list box to select the proper Customer Number (It's be nice to display the
Number & Name in the dropdown) and map those fields into the Main data
source. I think I've read that I need to use code attached to the drop down
field on the OnAfterChange event. Any examples?

 

[BFSmith]

OK...got that to work...thanks...it is pretty slow though...takes 5 seconds
to return after selecting an item from the CustomerName DDLB...seems pretty
slow??? Only 8000 rows in the CustomerMast table..all other SQL ops run very
fast...I even dis-associated the fields that pull data from the secondary
source from the secondary source...leaving only the DDLB for
CustomerName...and it still takes 5+ seconds to return from the DDLB
selection...so-I changed the CustomerName (Char(30)) back to just a text box
and made the CustomerNumb the DDLB (with the proper association back to the
CustomerMast table (BTW-CustomerNumB is the Primary Key) and noticed the
selection from the DDLB took "only' 2 seconds...still pretty slow but a
noticeable difference between CustomerName & CustomerNumb performance on the
DDLB????

With CustMaster and CustData tables

HAve a drop down list (on field1)which gets its data from Custmaster and
displays cust name.

Field2 is customer Number is set to show the customer number where the cust
name from on the form equals the cust name in CustMaster.

Field3 is the address that show the address where the cust name on the form
is equal to the cust name in CustMaster. Job Done

Every time you select a different name in cust name the customer number and
customer address change automatically by cross checking against your dbase.

To perform these steps go into the properties of Field2. Edit default value
using the fx button. Click insert field or group, now choose the customer
number field from your secondary data source called CustMaster. Now the
important bit, before OK'ing out of there click Filter. Then in the filter
condition specify where Field1(the Customer name on the form) is equal to
customer name (from the secondary datasource called CustMaster) Now OK out of
there.

You now have Field2 showing the correct customer number dependant on what
name you choose on the form, isn't that all you are after?

Furthermore, if you wanted to search on cutomers already in the main
datasource you can add the main datasource back in as a secondray datasource
and use that to set the conditions described above.

I hope this makes sense but would happily expand on this further.

Cheers Adam

So, how do you do it then? I'll need an example probably.

BFSmith

I currently DONT use code to do this in upwards of 4 or 5 forms that are
currently in use. Which is why i mentioned it

:

That doesnt make much sense to me...notice my <= pointer to a problem...I
think I have other problems also...here is the codse so far:

function msoxd__tblRAHeader_CustomerNum_attr::OnAfterChange(eventObj)
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}

// A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

// Retrieve the vwCustomerInfo_basic secondary data source
// (the DOM of the vwCustomerInfo_basic Data Object)
//var producPriceAuxDom = XDocument.DataObjects("vwCustomerInfo_basic").DOM;
var vwCustomerInfo_basicAuxDom =
XDocument.DataObjects("vwCustomerInfo_basic").DOM;

// Retrieve the SQL statement of the data source
var strSQL =
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command;
XDocument.UI.Alert(strSQL);

// Retrieve the field in the InfoPath form whose value will be used in the
WHERE clause
var node = XDocument.DOM.selectSingleNode("/my:myFields/my:CustomerNum");
XDocument.UI.Alert(node == null); //<= returns true..field name is
CustomerNum???????
XDocument.UI.Alert(node.text);

// Generate the new SQL statement with WHERE clause
strSQL += " where tblRAHeader.CustomerNum = ' " + node.text + " '";

// Populate the command with the new SQL statement
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects["vwCustomerInfo_basic"].Query();

// Find the price in the secondary data source and the main data source
var CustomerNameAuxNode =
vwCustomerInfo_basicAuxDom.selectSingleNode("vwCustomerInfo_basic:Name");
var CustomerNameFormNode = eventObj.Site.selectSingleNode("../my:CustName");

// Update the main data source with the queried value;
CustomerNameFormNode.text = CustomerNameAuxNode.text;

}


:

I don't think you need code. Just add your main datasource back in as a
secondary datasource and populate the drop-down list with customer number and
use filtering to also show the correct name.

Job done.

Cheeres Adam

:

I have a Returns Authorization form that has Customer info in it
(CustNumber,CustName,Address1, etc) in the main data source. I have a
secondary data source (CustomerMaster) that I would like to use a dropdown
list box to select the proper Customer Number (It's be nice to display the
Number & Name in the dropdown) and map those fields into the Main data
source. I think I've read that I need to use code attached to the drop down
field on the OnAfterChange event. Any examples?

 

[BFSmith]

Also-if I use the default value option to populate those fields from the
secondary data source-when I re-open the form-it no longer displays the data
from the initial submit to the database. Those fields I used from the
secondary database are filling the main data sources' fields-I need them to
show the db values when opened/reopened.

OK...got that to work...thanks...it is pretty slow though...takes 5 seconds
to return after selecting an item from the CustomerName DDLB...seems pretty
slow??? Only 8000 rows in the CustomerMast table..all other SQL ops run very
fast...I even dis-associated the fields that pull data from the secondary
source from the secondary source...leaving only the DDLB for
CustomerName...and it still takes 5+ seconds to return from the DDLB
selection...so-I changed the CustomerName (Char(30)) back to just a text box
and made the CustomerNumb the DDLB (with the proper association back to the
CustomerMast table (BTW-CustomerNumB is the Primary Key) and noticed the
selection from the DDLB took "only' 2 seconds...still pretty slow but a
noticeable difference between CustomerName & CustomerNumb performance on the
DDLB????

With CustMaster and CustData tables

HAve a drop down list (on field1)which gets its data from Custmaster and
displays cust name.

Field2 is customer Number is set to show the customer number where the cust
name from on the form equals the cust name in CustMaster.

Field3 is the address that show the address where the cust name on the form
is equal to the cust name in CustMaster. Job Done

Every time you select a different name in cust name the customer number and
customer address change automatically by cross checking against your dbase.

To perform these steps go into the properties of Field2. Edit default value
using the fx button. Click insert field or group, now choose the customer
number field from your secondary data source called CustMaster. Now the
important bit, before OK'ing out of there click Filter. Then in the filter
condition specify where Field1(the Customer name on the form) is equal to
customer name (from the secondary datasource called CustMaster) Now OK out of
there.

You now have Field2 showing the correct customer number dependant on what
name you choose on the form, isn't that all you are after?

Furthermore, if you wanted to search on cutomers already in the main
datasource you can add the main datasource back in as a secondray datasource
and use that to set the conditions described above.

I hope this makes sense but would happily expand on this further.

Cheers Adam

So, how do you do it then? I'll need an example probably.

:

BFSmith

I currently DONT use code to do this in upwards of 4 or 5 forms that are
currently in use. Which is why i mentioned it

:

That doesnt make much sense to me...notice my <= pointer to a problem...I
think I have other problems also...here is the codse so far:

function msoxd__tblRAHeader_CustomerNum_attr::OnAfterChange(eventObj)
{
// Write code here to restore the global state.

if (eventObj.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is read-only.
return;
}

// A field change has occurred and the DOM is writable. Write code here to
respond to the changes.

// Retrieve the vwCustomerInfo_basic secondary data source
// (the DOM of the vwCustomerInfo_basic Data Object)
//var producPriceAuxDom = XDocument.DataObjects("vwCustomerInfo_basic").DOM;
var vwCustomerInfo_basicAuxDom =
XDocument.DataObjects("vwCustomerInfo_basic").DOM;

// Retrieve the SQL statement of the data source
var strSQL =
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command;
XDocument.UI.Alert(strSQL);

// Retrieve the field in the InfoPath form whose value will be used in the
WHERE clause
var node = XDocument.DOM.selectSingleNode("/my:myFields/my:CustomerNum");
XDocument.UI.Alert(node == null); //<= returns true..field name is
CustomerNum???????
XDocument.UI.Alert(node.text);

// Generate the new SQL statement with WHERE clause
strSQL += " where tblRAHeader.CustomerNum = ' " + node.text + " '";

// Populate the command with the new SQL statement
XDocument.DataObjects["vwCustomerInfo_basic"].QueryAdapter.Command = strSQL;

// Run the query
XDocument.DataObjects["vwCustomerInfo_basic"].Query();

// Find the price in the secondary data source and the main data source
var CustomerNameAuxNode =
vwCustomerInfo_basicAuxDom.selectSingleNode("vwCustomerInfo_basic:Name");
var CustomerNameFormNode = eventObj.Site.selectSingleNode("../my:CustName");

// Update the main data source with the queried value;
CustomerNameFormNode.text = CustomerNameAuxNode.text;

}


:

I don't think you need code. Just add your main datasource back in as a
secondary datasource and populate the drop-down list with customer number and
use filtering to also show the correct name.

Job done.

Cheeres Adam

:

I have a Returns Authorization form that has Customer info in it
(CustNumber,CustName,Address1, etc) in the main data source. I have a
secondary data source (CustomerMaster) that I would like to use a dropdown
list box to select the proper Customer Number (It's be nice to display the
Number & Name in the dropdown) and map those fields into the Main data
source. I think I've read that I need to use code attached to the drop down
field on the OnAfterChange event. Any examples?