Selection issues/ scripting help

[#1]

I posted this before, but it seems like the thread has died out. I will try to explain a little better here.

I have a document that contains around 30 tables. It was originally built for users to see information regarding orders. So The flow of the document begins with the Order head table and flows down to order details, jobs, shipments, and invoices. Other selection fields are also connected to the Order head table, like the Customer table for users to select customers and see their orders. Order details also has selection tables for part classes and for product groups.

We are also Multi company so part of the link from table to table is the company field. For user selection I am using the Company field from the Order head table.

With this set up everything was working correctly. Users select a company and see everything related to order regarding that company. The same is true for products, classes, and customers.

Now I have been requested to add Quote information to this document as well.

This is where I am having problems. There can be quotes that have no Orders, and Orders that are not related to quotes. They are linked from the quote detail table to the order detail table.

Users want the ability to select a company,customer,product,class (fields that quotes details and order details both have) and see ALL orders and ALL quotes from the selection.

Currently due to my selection tables/Company field linking into the Order head and order detail tables, when the user selects something they expect to see everything, but this is creating an left join to the quote and only pulling quotes that are linked to Orders.

In the other thread someone suggested concatenating the order tables with the quote tables to allow this to work, however this removes the link between quotes and orders. (They want to see a chart also that shows total quotes compared to what was ordered)

How could I go about doing this where they only need to select one field and see both sides of the data? Linking the customer to both quote head and to order head would create a circular reference, as would doing the same with the other selection tables.

Any help would be highly appreciated.

Attached is the table view. Circled in red are the tables/fields that I am having selection issues with.

[#2]

Hi,

I've had similar situations to this and I think you'd be well served going for a link table.  without knowing exactly the data involved and how the link fields you currently use have been derrived, you could have a table with an order detail column, and a quote detail column - some will have values in both, some just the quote and some just the order - and use this to link to customer / part / prod tables. 

Would that be possible?  I know it feels a bit odd adding a manufactured table in like that, but sometimes it's the only way to avoid circular references etc.

Hope that helps.

Cheers,

Emma

[patrickanderson]

I think a Link table would be your best bet here as well, you need to pull the customer info from other sources (Quotes and Orders). Load them DISTINCT and CONCATENTATE  the two tables, this will give a linked table with all possibilties from both sources.

Many examples can be found if you search "linked table".

Good Luck

[#4]

How would I create this linking table? Would I need to do something like a resident load and concatenate the tables there? It seems a little confusing with having to keep all tables up to date.

Attached is a pain drawing of what I think you are talking about?

[#5]

Yep, resident loads would be the best way.

can you link the customer table to the order detail list?

something like

link:

load distinct

companyord,

companycust,

resident orderhed;

left join (link)

load distinct

companyord,

Companyrel,

companypart,

companyprod,

quotedtlink

resident Orderdtl;

left join(link)

quotedtllink

resident quotedtl;

however ... looking at the values in the tables, I'm not sure whether there should be parts / products represented from both the quote side and the order side (depends what information appears in your quotes).

you should essentially end up with a table that at the detail level links to quotedtl, orderdtl, customer, part and prodgroup (although ... that sounds like it should be linked to part anyway ... rather than an order ... but I don't know your data ...).  Essentially like yourd but without the order/quote table and just a few fields from the detail tables used to create the link rather than joining the whole of both tables.

[#6]

Unfortunately no, the customers only link into quotes and order through the header tables, so I will need to load 2 link tables.

For our data part classes are linked into the part table. Quotes and orders however allow you to enter product codes on the detail tables (for parts that are not in the part master) So technically there could be a quote line that has one produce code, and an order line that is linked to that quote line that lists a different product code.

I will have to think about this for a bit to get my mind wrapped around how to get it to work.

First I do have a question. If I combine the orderdtl and the quotedtl tables for the link table, using a concatenate function, it removes the link between quotes and orders.

Using a left join to combine these tables would restrict the data to whatever exists in the first table. IE quotedtl left joined with orderdtl would only pull quotes that have a link and all orders.

Wouldn't I run into the same issue that I am having now if I use this link detail table as a "middle man" between the quotedtl and orderdtl table?

[#7]

Hi,

erm ... thinking about it, possibly yes, rather than using a left join to bring the information in from the quote table a standard join might be a better idea.  Left join should still be ok for bringing in the order header and order detail info together as there must be an order header for a detail line.

Basically you need to figure out which fields will give you a link table a bit like the below.  You'll probably have to load information from at least three tables (header and detail tables), maybe four.

Does that help?  It's a bit diffiult not being able to see the application or the source data!

Cheers,

Emma

[#8]

My concern is creating this link table. The only ways I know to combine tables are with joins, or concatenate. Left/Right joins would restrict the data, and Concatenate removes the link between tables.

So say it was a link table combined using a Join. If a Customer is selected, since it goes through the link table, it would only return Quotes that exist for Orders/ Only orders that are linked to quotes.

If I do a Link table with Concatenate and I want to show a combined chart showing Quote amounts by Quoted date as a Bar, and Order amounts linked to those quotes as a line, it does not work. (Similar to what I have now concatenating the quote and order tables into 2 combined tables)

When I create a table box that contains Quote Number, Line, Amount, Order Number, Line, Amount, it shows as 2 rows if there is a link. Shows the Row for the Order information, with the quote number, and a line for the Quote info with no order info.

I will try and make a sample app, might take awhile with 30 tables of data to scramble.

Update:

I tried creating the link tables, and this is allowing the correct selection for customers products and classes. All data is being displayed when one of those is selected. So that for that purpose the link tables are working great.

However as I suspected, using the link tables removes the link between quotes and orders themselves. IE Chart by Quoted date only displays quote info, and does not display the order info that is linked with those quotes.

Attached is a image of the table view as of now, and below is the portian of the script where I created the link tables.

Headlink:
    Load Distinct
    companycustlink as companycustlink2,
    companyordlink,
    companyordlink as linklink,
    Company as Company2
    Resident Order;
TempQuote:
Concatenate (Headlink)
    Load Distinct
    quotehedlink2,
    quotehedlink2 as linklink,
    companycustlink1 as companycustlink2,
    Company1 as Company2
    Resident Quote;
Detaillink:
Load Distinct
    companyord as linklink,
    companyord,
    companyprod1 as companyprod,
    companypart1 as companypart,
    quotedtllink3 as quotedtllink
    Resident Orderdtl;
Tempquotedtl:
Concatenate (Detaillink)
Load Distinct
    quotehedlink23 as linklink,
    quotehedlink23,
    companyprod2 as companyprod,
    companypart2 as companypart,
    quotedtllink5 as quotedtllink
    Resident Quotedtl;

[#9]

Is what I am trying to accomplish not possible?

[#10]

Help?