I have three tables, table 1 has fields on Merchant and their info, table 2 has fields on Orders (Order Number, Date) those are visible in table 1 in a portal, table 3 has the ordered products that are in table 2's portal.

my question, how would i link and add them together to work and how can I make table 1s portal display the supplier related orders?.

Please see file.

Full Guest Account!

As usual thanks to you guys!

Clone8gv3.fp7.zip

I have had a quick look at the file - as you probably know you need to sort out the data structure and everything else will follow.

I think you want to be able to see a list of merchants (suppliers), click on one of the suppliers in your navigation list and see a list of the (outstanding) orders from customers for products (materials) supplied by the selected supplier?

I think you need to do some work on the data structure:

1) You need a products file that simply stores details of products;

2) You need to make a decision as to whether a product can come from more than one supplier

3) If their is no chance of needing more than one supplier per product then you need to store the supplier id in the products table

3) You need to have a fairly straight forward sales order structure:

order header => order detail (stores detail lines) much as you have

order detail => products

[4) You may need to add some data and process in for shipping/part shipping orders]

5) Going to the requirement - the portal needs to show (outstanding - in brackets because you don't state this as a requirement) sales order information for that supplier.

You can't go to a table occurrence of the order header table because one customer order may have products coming from more than one supplier

So as a quick and dirty solution you need to create a table occurrence of the order details/lines linked to the supplier table by what you would have called merch_id - ie you need a copy of the merch id in the order details table

I emphasise this is quick and not terribly purist - but you could simply put a calc field in the order details table that grabs the supplier id from the products table when the order detail line is created

It is slightly fragile and you need to be aware of the overhead you are creating by denormalising the database for this function

HTH

I have had a quick look at the file - as you probably know you need to sort out the data structure and everything else will follow.

I think you want to be able to see a list of merchants (suppliers), click on one of the suppliers in your navigation list and see a list of the (outstanding) orders from customers for products (materials) supplied by the selected supplier?

I think you need to do some work on the data structure:

1) You need a products file that simply stores details of products;

2) You need to make a decision as to whether a product can come from more than one supplier

3) If their is no chance of needing more than one supplier per product then you need to store the supplier id in the products table

3) You need to have a fairly straight forward sales order structure:

order header => order detail (stores detail lines) much as you have

order detail => products

[4) You may need to add some data and process in for shipping/part shipping orders]

5) Going to the requirement - the portal needs to show (outstanding - in brackets because you don't state this as a requirement) sales order information for that supplier.

You can't go to a table occurrence of the order header table because one customer order may have products coming from more than one supplier

So as a quick and dirty solution you need to create a table occurrence of the order details/lines linked to the supplier table by what you would have called merch_id - ie you need a copy of the merch id in the order details table

I emphasise this is quick and not terribly purist - but you could simply put a calc field in the order details table that grabs the supplier id from the products table when the order detail line is created

It is slightly fragile and you need to be aware of the overhead you are creating by denormalising the database for this function

HTH

Ok so I slimed the project down for me to understand the relations. It seems to make more sense but it still wont work. If you could have another look. Thanks

Clone8gv3_2.fp7.zip

Sorry - going to be away for the next 10 days so hopefully somebody else can pick up...

Simon