This post displayed so much better when I was writing it...
I hope someone can help point me in the right direction with this, because I cannot get my head around it at the moment! I struggle to even try to articulate what I am trying to achieve, as you will probably soon find out...
Basically, I have used InfoPath 2010 to create a form linked to various lists on a SharePoint (also 2010) site.
SharePoint Lists:
CLIENTS - All 4,735 customers, with Channel, Group, and Customer Number of each of their outlets.
PRODUCTS - 80 different products, their product number and tax rate.
TAXES - All 22 different states and their individual tax rates.
The first field is a drop-down in which they will select the "Channel". This drop-down is populated from CLIENTS.
The second field is also a drop-down, filtered on the "Channel" they chose, and shows a list of "Groups", also from CLIENTS.
When they have made these choices, a repeating table brings up a list of all of the specific Customers related to the Channel and Group, and more information about them (again, this comes from the CLIENTS list.) This can be any number up to around 10 Customers.
Finally, in another repeating table, customers can select products from a drop-down list (from PRODUCTS) and provide information about their projected sales against each. There's no limit to the number of products they can choose, but it isn't usually more than 5 or 6.
So far, all of this works absolutely fine.
BUT, what I then want to appear, further down the table, is each Customer which appeared in the first repeating table, listed with information about each product from the second table displayed. Just to complicate it even more, it needs to be combined with information from the TAXES list.
So the business want to see something like this:
CHANNEL: X
GROUP: ACME INDUSTRIES
CUSTOMER A - STATE X
CUSTOMER B - STATE X
CUSTOMER C - STATE Y
---------------------------------------------------------------------------
Product A
Product B
---------------------------------------------------------------------------
Customer A - State A - Tax Code (for State X) - Customer Number
Product A - Cost A - Calculation: Tax Due (Cost A / Tax Code for State X)
Product B - Cost B - Calculation: Tax Due (Cost B / Tax Code for State X)
Customer B - State B - Tax Code (for State X) - Customer Number
Product A - Cost A - Calculation: Tax Due (Cost A / Tax Code for State X)
Product B - Cost B - Calculation: Tax Due (Cost B / Tax Code for State X)
Customer C - State C - Tax Code (for State Y) - Customer Number
Product A - Cost A - Calculation: Tax Due (Cost A / Tax Code for State Y)
Product B - Cost B - Calculation: Tax Due (Cost B / Tax Code for State Y)
If anyone has *any* idea how I can get something like this working, I will be so grateful. Ideally without any coding too, please... If you're still with me, then thank you for reading!