I apologise for this long and detailed explanation. I was hoping I might see the light while typing it, but that hasn¹t happened, so I cast it before you in the hope someone knows what to do with all this. I have a feeling there won¹t be an easy answer!
I'm building a database of food products. I have all the data in a flat format (supplied in Excel and imported to FMPro). Each product has the following format: Temperature Group SubRange SkuRange(optional) SKU CODE Name etc. (the rest are product attributes and post no big problems) For example: Frozen Meat & Poultry Sausages Cooked Sausages F2233 Frozen Delicatessen Rice - [no sku range] - F1234 Chilled Cheese & Dairy Yogurt Mixed Packs C9999 Chilled Fresh Produce Potatoes - [no sku range] - C8888 The names of Groups, SubRanges etc are longish text strings and prone to typographical errors. So I want the customer to be able to click on a field and see a drop down menu of the appropriate available headings. Or add a new one. (I know I should probably give them all numeric IDs... this is supposed to be a short-term solution, I don¹t want to get too bogged down...) I built tables for each, set up various relations, and created value lists based on the relations. At first it seemed to work in the Product table, creating a new record, you could click on Temperature and see the 4 choices, then click on Group and see only the choices within the chosen Temperature etc. But when I created a new record in the Group table, I was unable to make that option appear. I ended up making all sorts of value lists based on all sorts of relationship and got into an unhappy mess. That¹s when I realised I should have drawn it all up as a diagram before I started. But I found I couldn¹t do that in a meaningful way either! Temperature has a one to many relationship to Group Group has a one to many relationship with SubRange SubRange has an optional one to many relationship with SkuRange ... but how do these relate to Product? Any ideas? Thanks Emma - - Fourninety Limited, a member company of the Media Square plc group. Registered in England No. 3737606. Registered address Clarence Mill, Clarence Road, Bollington, Cheshire. SK10 5JZ Confidentiality Notice: The information in this document and any attachments are confidential. It is intended only for the use of the named recipient. If you are not the intended recipient please notify us immediately and delete this document. The contents on this document must not be disclosed to any other person nor may any copies be taken. The views of the author may not represent the views of the Company. Security Warning: This e-mail has been created in the knowledge that Internet e-mail is not a 100% secure communications medium. All liability for any claims arising as a result of using this medium to transmit information by us or to us is excluded to the extent permitted by law.
