Ok here's the thing. I've literally spent all day today working on this. In an ideal world my items table is as follows:
ItemID
ItemName
ItemDecorID (left join to decor table)
ItemSupplierID (inner join to contact table) *
ItemCurrencyID (inner join to currency table) *
ItemCostPrice *
ItemRetailPrice *
ItemProjectID (inner join to projects table)
etc etc. 35 fields in total may with keys linking to other tables
The items with an asterix should ideally pull across the data from the decor table IF the ItemDecorID does not equal 0. I've set this up already and created a detail page using if statements that generates the correct information based on the entry in the ItemDecorID field. However on my results page I ran into problems. The supplier uses an ID in both the items table and the decor table, the decor table uses a join to connect to the items table, displaying the SupplierName for items that use the decor sheet in a repeat region is causing me problems. Any suggestions to get round this greatfully received. The next problem is that I have the facility to add multiple items from my results table to an order using the eCart Get from Recordset behaviour. This will presumably take an awful lot of hand coding to get this to work if I'm pulling the data from 2 different tables dependant on the decorID entry. Again happy to give it a go if you can point me in the right direction. Otherwise I thought the only and simplest option would be to populate the relevant fields in the items table from the decor sheet. I thought I could also create a multiple update page that would allow you to update the data should the decor sheet change - again not ideal so suggestions welcome.
