Hi all,

This is probably not too tricky but I just can't get my head around how to do it. I have a database that contains stock quantities and sales data for many different products over about 25 different branches of a company. The fields are Product ID, Branch, Quantity in stock etc. The problem is its quite unwieldy to look at like this - I want an overview of the different stock levels in the different branches. So - I want a new table that only has one line per product rather than 25 lines per product (for each branch). Across the screen will be stock levels etc. in each branch.

The way I've tried it (and it didn't work!), I've created a product-code-branch key in the original table which is a text concatenation of the product code and branch (=Product Code & " " & Branch Code). I have then set up 25 relationships between this field and 25 new fields in the new table which just represent all the Product-code-branch combinations. (Jesus if you're still reading I'm impressed!). I should then be able to have lots of little related fields showing stock quantities in all the different branches horizontally across the screen. For products in branch 1 I use the relationship set up between the product code 1 keys. If I search for a product it will come up with one result and I will be able to get an overview of my empire! But... it doesn't work. (btw the fields in this table are just a full stock listing). I will pray to the angel of filemaker to canonise anyone who understands this incredibly badly worded post and can help me here...

Either your current relationships, keys, or the way you've assigned the fields on the layout is incorrect. What you did should work. Can you upload a sample or clone of your file? Look carefully at the keys on each side, to see if they are exactly the same, and the same type (text, number).