InfoPath Dev

I have a table "A" with 5 fields that correspond to keys from tables "B","C","D","E", and "F". When InfoPath SAVES data into table "A", I only want it to save the keys, yet when my user VIEWS the form, I want them to see ALL of the datafields from the corresponding tables. For example, I want to Store the values 1,2,3,4,5 into Table "A", my primary datasource. But when the user VIEWS the form, they will SEE "Text1","Text2", "Number1" from table "B" that corresponds to the key value 1, they will SEE "Text3","Text4", "Text5" from table "C" that corresponds to the key value 2, they will SEE "Number2","Number3", "Text6" from table "D" that corresponds to the key value 3, etc... The stored values from table "A" are in a repeating section. Is there an easy way to make this work?

Your explanation is somewhat confusing. Are you saying that the value 1 is sotred in B and the value 2 is stored in C, etc.? If so, where does Text1 and Number1 come from?

What it kind of sounds like you are asking is for the ability to have multiple tables bound to the same set of data nodes, and each table only showing a few of the fields. If so, this is easily done by placing the table into the view, and then removing the columns you don't want in that copy of the table.

I also don't understand what you meant by stating that you only want to store "keys" in table A... What do you mean by "store" here? If your table does not store all values within it, then those non-stored values cannot be viewed.

Perhaps I'm just confused.

Sorry for making this sound confusing. Let me try to simplify my example... The 1st table "Product" has 4 fields; productID (an integer), productDesc (text fld), unitPrice (currency), categoryID (integer). The 2nd table "Product Category" has 2 fields; categoryID (an integer), categoryDesc (text fld). The 3rd table "Customer" has 4 fields; customerID (integer), customerName (text), customerAddr (text), customerTaxRate (decimal - percent). The 4th table "Order" contains 2 fields; orderNo (integer), customerID (integer). The 5th table "OrderDetails" has 5 fields; orderNo (integer), orderDetailNo (integer), productID (integer), orderQty (integer), extendedPrice (currency). Now when the InfoPath form is opened, 1) I want to see a drop-down list for the customer and 2) when I select one, I want to see all of the related customer data on the order, then 3) I want to see a drop-down list for the products and 4) when I select one, I want to see all of the related product data on each line of the order detail. And finally 5) when I click the SUBMIT button, I want to save the order and order detail record(s) as defined by Tables 4 and 5.

When I tried to do this in the process of designing a new form, InfoPath was okay with me defining the order and order detail tables as the form structure and "saying" it was enabled for updating. But as soon as I added any of the other "reference" tables, it changed it to being disabled for updating.

Hopefully, this concrete example removes some of the confusion. The examples I have seen are so simplistic in representing an order/order detail process. They assume you just type in the customer information, the order information, and provide a free form entry of product information including price. This is not the real world. The real world requires that when someone orders a quantity of 10 "Widget A"s, they are not ordering a quantity of 10 "Widdiegets A"s because their typing skills are not quite up to speed.

Thanks again for taking another look at my question. I appreciate your patience and assistance in helping me understand this interesting product.

Thanks for the clarification Michael! It is incredibly difficult to parse the text describing your tables, please use this format next time:

Product
--producID
--productDesc
--unitPrice
--categoryID

Product Category
--categoryID
--categoryDesc

Customer
--customerID
--customerName
--customerAddr
--customerTaxRate

Order
--orderNo
--customerID

OrderDetails
--orderNo
--orderDetailNo
--productID
--orderQty
--extendedPrice

1-2) See this HowTo:http://www.infopathdev.com/howto/tutorials/default.aspx?i=9111ef580f95479890fc18fb2c5bac85
3) Just pop it onto the view. Make sure to use 'productID' as the value and 'ProductDesc' as the display name
4) It sounds like you will have a repeating table with all of the order details, and when you select a product you only want to see details where someone has bought that product. To do this you can add filtering to the repeating table that goes like this:

"ProductID" "is equal to" [Select Field] -> [Product drop-down value] AND
[Product drop-down value] "is not blank"

Although the examples given might be rather simplistic, I think all that you are trying to accomplish should be relatively simple. Let us know if you have any other questions, or if you want us to take a look at your solution.

Matt,

Thanks for tip on describing and showing schema easily.

Okay, I see how your example started with customer ID. But now in the process of working through your example, I have ended up adding 5 more fields to my "MAIN" data source definition of "myFields".

If I understand how InfoPath works (which is questionable at this point ;), and if I were writing the update(s) back out to a Database (i.e. using your example - let's say we originally assigned Customer ID 2 - Northern Lights Advertising to an order, but now we realize we made a mistake and should have assigned Customer ID 4 - Plush Designs instead). Am I not correct in assuming that an update will send the additional "non-key" values back out to the Database as well?

You have gotten to the crux of my issue. I ONLY want to send ORDERid (I know it is not part of your example) and the updated CustomerID back out to the database. I do NOT want nor need to send the additional 5 fields now sitting in "my fields" to the database.

I hope I have not made this confusing again...