InfoPath Dev

Hi I have a multiple list box which receives it's data from a SQL table. I have another multiple list box which receives fields from another SQL table filtered on whatever was selected in the first multiple list box. I want the second multiple list box not to be choices, but to have all the entries selected (can i use anything other than a multiple list box, for multiple values?). The reason being the second multiple list box is hidden from the users. I want the second filtered list box to basically be automatically checked, or more importantly their values captured. The scenario is thus: A user can work in 1 area up to 10 areas. Depending on the area worked an approver is filtered/received from the approvers SQL table. If the user works in more than one area, several approvers are required. This process of 'selecting' approvers should be hidden from users. They can only select the area they work in and the approvers are selected automatically. PLEASE NOTE: There are TWO SQL tables - AREA and APPROVER (approver is filtered on area code - the underlying SQL schema CANNOT be changed). Any help would be greatly appreciated. Many thanks

I just got done doing something very similar, except mine were SharePoint lists. First lists was a transaction type list. Inside of this list was a lookup to an approvers list. The user selects whichever approvers should be used for each transaction type in SP.

So in Infopath the transactions list and the approvers list are both data connections. I needed to do something very similar to what you are doing. I ended up using code to create a table in my schema to store the list of all the possible approvers. I had this table filled by my Form_Load whenever the form is opened, and added a field named ApproverRequired to it as a check box.

So once the form opens we have the multi-select box showing the list of transactions. They can pick however many they want. Once they are done they click a button which calls the following code:

//This method determines which approvals are required.

//Setup Transaction Types Navigator for later use.

XPathNavigator transNavRoot = DataSources["Transaction Types"].CreateNavigator();

XPathNavigator root = MainDataSource.CreateNavigator();

//This is the multi-select field 

XPathNodeIterator iterStandardCOSTran = root.Select("/my:COS/my:COSTransactionIdentification/my:StandardTransactions", NamespaceManager);

string selectedTransId = String.Empty;

//Loop through the values in the transaction multiselectforeach (XPathNavigator transNav in iterStandardCOSTran)

{

if (!String.IsNullOrEmpty(transNav.Value))

{

selectedTransId = transNav.Value;

//This looks up the specific transaction id from the Transactions data source

XPathNavigator trans = transNavRoot.SelectSingleNode("/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[d:ID = '" + selectedTransId + "']", NamespaceManager);SetRequiredApprovers(trans.Select("d:Approval_x0020_Roles/Value", NamespaceManager));

}

}

}

So as it finds the values that were selected in the multi-select it loops through them and calls the SetRequireApprovers method which gets passed the list of approval roles that are required for that transaction type.

private void SetRequiredApprovers(XPathNodeIterator approvers)

{

//Loop through provided iterator and set each approver role it contains to required for approval.

while (approvers.MoveNext())

{

//This line gets a navigator to the specific approver role we are currently looping through.

XPathNavigator approver = MainDataSource.CreateNavigator().SelectSingleNode("/my:COS/my:ApproverIdentification/my:ApprovalRole[my:ApprovalRoleID = '" + approvers.Current.Value + "']", NamespaceManager);

//Finally we set the ApprovalRequired flag to true to indicate that this approver is required. 

approver.SelectSingleNode("my:ApprovalRequired", NamespaceManager).SetValue("true");

}

}

I know this is probably confusing and may be of no use to you, but I thought I would try to help, since your problem sounds similar to mine.