Connection Question?

[#1]

I am having an Issue trying to visualize this.

I currently have a dashboard that I created that contains around 30 tables. I have the linking all set correctly.

Out system can set up to handle multi company, so each table has the field 'Company' that I use in all links. Now I want to display this field on the dashboard.

I do not know where to take it from because of the current linking. I currently am taking the company field from the Orders table. I also have the quote table, which is linked to the Order table. I want the user to be able to see all quotes depending on the company selected. Since the Company field is coming from the Order table, if forces the link so that it only pulls the quotes that have orders attached to them.

How could I do this so it does not limit the data due to the location of this field?

Note:

There can be orders with out quotes, and with.

There can be quotes with out orders and with.

[mazacini]

Hi

You said that each table has the field 'Company'.

It looks like you have a Quotes table?

If so, is there not a 'Company' field on the Quotes table?

Joe

[jonathandienst · Accepted solution]

Hi

It sounds like you need to either one or a combination

• create some composite keys (eg ordernumber/company composite)
• create a link table (with all common attributes, including company)

• concatenate the fact tables (eg orders/quotes in a single table)

Not having seen your data model, I cannot be specific, but I would start with the last option above.

Perhaps you could post a sample of your application for more specific suggestions.

Regards

Jonathan

[#4]

The Quote table does have a Company field. I am trying to avoid having multiple selections for company.

[#5]

A lot of the data is confidential, so it would be difficult to scramble the correct fields to post a sample.

After looking at the table view (attached), I found that the issue goes beyond the company selection.

Quotes and Orders are linked via the Orderdtl table and the quotedtl table.

Currently if I select anything from the quote side, it natualy pulls in only the Order lines that have a link to the possible results from Quotes.

Same thing goes for if I select anything on the Order side.

This becomes an issue when users want to select, say a certain customer. I would want it to work as when they click a Customer record, they see all orders and all quotes. The current set up though would select all orders, and only quotes linked to those orders.The same issue goes with part class, or Product Group.

Would concatenating the orderdtl and quotedtl allow the return for all data based on say a customer selection, while still maintaining selection linking between Quotes and Orders? (IE Quote selected would only return orders from that quote)

These are the current fields linking the orderdtl and quotedtl tables,

Orderdtl:

company & '-' & quotenum & '-' & quoteline as quotedtllink ,

Quotedtl:

company & '-' & quotenum & '-' & quoteline as quotedtllink ,

To concatenate, would I remove this link, and just combine company, quotenum, and quoteline into one master table?

Edit:

I concatenated the order table and the quote table to try and fix the customer link issue. I combined the quotedtl and the orderdtl to try and solve the part and prodcode issue. I have now run into a problem that they are not combining quite right.

Attached image of a table. I need it to show everything as one row, but since quote amount does not exist on the order, it shows as a different line.

NEW EDIT:

It seems that with the tables concatenating they are just combining the fields. This is removing the link that is needed to link the quotes to the orders. How can I do a full outer join concatenation to preserve this link, but still have the ability to have the selection flow correctly?

[#6]

Trying this in the Development Section. Anyone have any ideas?