Hi, I am designing an Infopath 2010 form based on an access 2010 database.
I have to populate a cascading drop down based on two tables [Customers] & [Sites] 1st drop down "Customers" filtered 2nd drop down "Sites"
Problem: Cusomers can have many Sites & Sites can have many Customers - classic many to many relationship - so drop downs will only show one customer per site.
ie Site_A has two customers Cust_1 & Cust_2 For the above to work Site_A will need to be entrered into the [Site] table twice (duplicated) - one for each Customer.
I have therfore created a third table [Multi_Site] with fields Multi_Site ID, Site ID(FK) & Customer ID
So when a new record for site is created a drop down based on [Multi_Site] table shows a list of all customers and of course several can be selected aginst the SITE entry in the [Multi_Site] table.
If I use the [Multi_Site] table for "Customers" drop down I get the list with multiple instances of the same customer against each individual site and so use show unique entries only on the drop down list (this is slow as there are more than 5,000 sites against 600 customers so its pulling all the sites and each customer duplicated and matched to that site).
1. I understand that Access 2010 has a "multivalue" field function - can this be used in infopath 2010 and if so what seperators can be used (mutliple site ID's against each customer in one field would be perfect).
2. If i can't use the above, how can i use the [Multi_Site] in the "Customer" drop down and have that populate 5 fields (Customer Name, Address 1, Address 2 etc) from the [Customers] secondary data source either with code or rules into the main table during query or when adding a new record when i will have to use the [Customers] secondary data source?
3. Is there a way to mimic a many to many relationship using several one to many tables or by using rules/code in the infopath form?
Your help will greatly appreciated