Part Adviser -> Crystal Report

Accomplished my report by using the part number and adding subreports for each of the tables desired, i.e., QuoteDtl,JobProd, and OrderDtl.

It seems the major disadvantages is the amount of time the report takes to generate, but I'm not aware of any other way to display all the data without taking this approach.

--- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@...> wrote:
>
> Okay, I've tested the links and it proves you're correct. So, it doesn't seem possible to display the part orderrel.ordernum jobprod.jobnum for both jobs without sales order links and orders without jobs links at the same time?
>
> --sf
>
> --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> >
> > Which way to link depends on what you want to see, all jobs or all orders.
> >
> > To see Job activity the first link should go between JobProd as the first table and Order Release, from there Orderdtl to OrderRelease and OorderHed to OrderDtl. If you make this an inner join it will only show jobs that have a demand link to an order.
> >
> > An outer join will show you all jobs and any orders linked to them. If there are no orders linked the order information will be blank.
> >
> > Both of these will leave out any sales order that did not have a demand link to a job. i.e. Order was filled from stock.
> >
> > To see the order activity you would reverse the link above with OrderRelease being the first table and JobProd being the second table. An outer join will show all orders for that part and if a job was used to fill it. If there is no job for an order you can assume it was filled from stock.
> >
> > This will leave out jobs that were used to fill stock.
> >
> > Jim Kinneman
> > Encompass Solutions.
> >
> > --- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@> wrote:
> > >
> > >
> > > Thanks for the responses. I'd like to be able to pull in a range of parts and display all the orders and jobs this part are on. I'm having trouble getting by the job and sales order a particular part is on.
> > >
> > > I'm using the JobProd,OrderHed,OrderDtl,and OrderRel tables.
> > >
> > > --sf
> > > --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> > > >
> > > > Forgot to mention, if there is a link it will be via the JobProd Table.
> > > >
> > > > Jim Kinneman
> > > > Encompass Solutions, Inc.
> > > >
> > > > --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> > > > >
> > > > > Can you expand a bit on what you are trying to accomplish?
> > > > >
> > > > > Unless the part was made for that sales order there not being a link is normal. Especially for anything that is considered a stock part, MRP or a manual job could have been made it for inventory. Sales order would ship from stock/inventory and thus have no demand link back to a job.
> > > > >
> > > > > Not sure what the exact purpose of the report but you can link just on the part number but that would result in all jobs for that part being linked to all sales orders for that part very likely overkill.
> > > > >
> > > > > Have you looked at the part tracker? That has just about everything you want to know about a part. If the tracker doesn't meet your needs a dashboard that shows jobs on one tab and sales orders on another tab might be an option. Both would be synced on part number.
> > > > >
> > > > > Jim Kinneman
> > > > > Encompass Solutions, Inc
> > > > >
> > > > > --- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@> wrote:
> > > > > >
> > > > > > I'm working on a Crystal Report that allows a estimator to enter a part number or range that would reproduce Part Adviser.
> > > > > >
> > > > > > I'm running into some problems with the linking of the tables.
> > > > > >
> > > > > > example:
> > > > > >
> > > > > > widget-a was produced for 3-jobs and 4-sales order, but the sales order does not have a link to a job. In this case the part is displayed on the 4-jobs, but it's missing the sales order that doesn't have a link to a job.
> > > > > >
> > > > > > --sf
> > > > > >
> > > > >
> > > >
> > >
> >
>
I'm working on a Crystal Report that allows a estimator to enter a part number or range that would reproduce Part Adviser.

I'm running into some problems with the linking of the tables.

example:

widget-a was produced for 3-jobs and 4-sales order, but the sales order does not have a link to a job. In this case the part is displayed on the 4-jobs, but it's missing the sales order that doesn't have a link to a job.

--sf
Can you expand a bit on what you are trying to accomplish?

Unless the part was made for that sales order there not being a link is normal. Especially for anything that is considered a stock part, MRP or a manual job could have been made it for inventory. Sales order would ship from stock/inventory and thus have no demand link back to a job.

Not sure what the exact purpose of the report but you can link just on the part number but that would result in all jobs for that part being linked to all sales orders for that part very likely overkill.

Have you looked at the part tracker? That has just about everything you want to know about a part. If the tracker doesn't meet your needs a dashboard that shows jobs on one tab and sales orders on another tab might be an option. Both would be synced on part number.

Jim Kinneman
Encompass Solutions, Inc

--- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@...> wrote:
>
> I'm working on a Crystal Report that allows a estimator to enter a part number or range that would reproduce Part Adviser.
>
> I'm running into some problems with the linking of the tables.
>
> example:
>
> widget-a was produced for 3-jobs and 4-sales order, but the sales order does not have a link to a job. In this case the part is displayed on the 4-jobs, but it's missing the sales order that doesn't have a link to a job.
>
> --sf
>
Forgot to mention, if there is a link it will be via the JobProd Table.

Jim Kinneman
Encompass Solutions, Inc.

--- In vantage@yahoogroups.com, "jckinneman" <jckinneman@...> wrote:
>
> Can you expand a bit on what you are trying to accomplish?
>
> Unless the part was made for that sales order there not being a link is normal. Especially for anything that is considered a stock part, MRP or a manual job could have been made it for inventory. Sales order would ship from stock/inventory and thus have no demand link back to a job.
>
> Not sure what the exact purpose of the report but you can link just on the part number but that would result in all jobs for that part being linked to all sales orders for that part very likely overkill.
>
> Have you looked at the part tracker? That has just about everything you want to know about a part. If the tracker doesn't meet your needs a dashboard that shows jobs on one tab and sales orders on another tab might be an option. Both would be synced on part number.
>
> Jim Kinneman
> Encompass Solutions, Inc
>
> --- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@> wrote:
> >
> > I'm working on a Crystal Report that allows a estimator to enter a part number or range that would reproduce Part Adviser.
> >
> > I'm running into some problems with the linking of the tables.
> >
> > example:
> >
> > widget-a was produced for 3-jobs and 4-sales order, but the sales order does not have a link to a job. In this case the part is displayed on the 4-jobs, but it's missing the sales order that doesn't have a link to a job.
> >
> > --sf
> >
>
Thanks for the responses. I'd like to be able to pull in a range of parts and display all the orders and jobs this part are on. I'm having trouble getting by the job and sales order a particular part is on.

I'm using the JobProd,OrderHed,OrderDtl,and OrderRel tables.

--sf
--- In vantage@yahoogroups.com, "jckinneman" <jckinneman@...> wrote:
>
> Forgot to mention, if there is a link it will be via the JobProd Table.
>
> Jim Kinneman
> Encompass Solutions, Inc.
>
> --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> >
> > Can you expand a bit on what you are trying to accomplish?
> >
> > Unless the part was made for that sales order there not being a link is normal. Especially for anything that is considered a stock part, MRP or a manual job could have been made it for inventory. Sales order would ship from stock/inventory and thus have no demand link back to a job.
> >
> > Not sure what the exact purpose of the report but you can link just on the part number but that would result in all jobs for that part being linked to all sales orders for that part very likely overkill.
> >
> > Have you looked at the part tracker? That has just about everything you want to know about a part. If the tracker doesn't meet your needs a dashboard that shows jobs on one tab and sales orders on another tab might be an option. Both would be synced on part number.
> >
> > Jim Kinneman
> > Encompass Solutions, Inc
> >
> > --- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@> wrote:
> > >
> > > I'm working on a Crystal Report that allows a estimator to enter a part number or range that would reproduce Part Adviser.
> > >
> > > I'm running into some problems with the linking of the tables.
> > >
> > > example:
> > >
> > > widget-a was produced for 3-jobs and 4-sales order, but the sales order does not have a link to a job. In this case the part is displayed on the 4-jobs, but it's missing the sales order that doesn't have a link to a job.
> > >
> > > --sf
> > >
> >
>
Which way to link depends on what you want to see, all jobs or all orders.

To see Job activity the first link should go between JobProd as the first table and Order Release, from there Orderdtl to OrderRelease and OorderHed to OrderDtl. If you make this an inner join it will only show jobs that have a demand link to an order.

An outer join will show you all jobs and any orders linked to them. If there are no orders linked the order information will be blank.

Both of these will leave out any sales order that did not have a demand link to a job. i.e. Order was filled from stock.

To see the order activity you would reverse the link above with OrderRelease being the first table and JobProd being the second table. An outer join will show all orders for that part and if a job was used to fill it. If there is no job for an order you can assume it was filled from stock.

This will leave out jobs that were used to fill stock.

Jim Kinneman
Encompass Solutions.

--- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@...> wrote:
>
>
> Thanks for the responses. I'd like to be able to pull in a range of parts and display all the orders and jobs this part are on. I'm having trouble getting by the job and sales order a particular part is on.
>
> I'm using the JobProd,OrderHed,OrderDtl,and OrderRel tables.
>
> --sf
> --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> >
> > Forgot to mention, if there is a link it will be via the JobProd Table.
> >
> > Jim Kinneman
> > Encompass Solutions, Inc.
> >
> > --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> > >
> > > Can you expand a bit on what you are trying to accomplish?
> > >
> > > Unless the part was made for that sales order there not being a link is normal. Especially for anything that is considered a stock part, MRP or a manual job could have been made it for inventory. Sales order would ship from stock/inventory and thus have no demand link back to a job.
> > >
> > > Not sure what the exact purpose of the report but you can link just on the part number but that would result in all jobs for that part being linked to all sales orders for that part very likely overkill.
> > >
> > > Have you looked at the part tracker? That has just about everything you want to know about a part. If the tracker doesn't meet your needs a dashboard that shows jobs on one tab and sales orders on another tab might be an option. Both would be synced on part number.
> > >
> > > Jim Kinneman
> > > Encompass Solutions, Inc
> > >
> > > --- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@> wrote:
> > > >
> > > > I'm working on a Crystal Report that allows a estimator to enter a part number or range that would reproduce Part Adviser.
> > > >
> > > > I'm running into some problems with the linking of the tables.
> > > >
> > > > example:
> > > >
> > > > widget-a was produced for 3-jobs and 4-sales order, but the sales order does not have a link to a job. In this case the part is displayed on the 4-jobs, but it's missing the sales order that doesn't have a link to a job.
> > > >
> > > > --sf
> > > >
> > >
> >
>
Okay, I've tested the links and it proves you're correct. So, it doesn't seem possible to display the part orderrel.ordernum jobprod.jobnum for both jobs without sales order links and orders without jobs links at the same time?

--sf

--- In vantage@yahoogroups.com, "jckinneman" <jckinneman@...> wrote:
>
> Which way to link depends on what you want to see, all jobs or all orders.
>
> To see Job activity the first link should go between JobProd as the first table and Order Release, from there Orderdtl to OrderRelease and OorderHed to OrderDtl. If you make this an inner join it will only show jobs that have a demand link to an order.
>
> An outer join will show you all jobs and any orders linked to them. If there are no orders linked the order information will be blank.
>
> Both of these will leave out any sales order that did not have a demand link to a job. i.e. Order was filled from stock.
>
> To see the order activity you would reverse the link above with OrderRelease being the first table and JobProd being the second table. An outer join will show all orders for that part and if a job was used to fill it. If there is no job for an order you can assume it was filled from stock.
>
> This will leave out jobs that were used to fill stock.
>
> Jim Kinneman
> Encompass Solutions.
>
> --- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@> wrote:
> >
> >
> > Thanks for the responses. I'd like to be able to pull in a range of parts and display all the orders and jobs this part are on. I'm having trouble getting by the job and sales order a particular part is on.
> >
> > I'm using the JobProd,OrderHed,OrderDtl,and OrderRel tables.
> >
> > --sf
> > --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> > >
> > > Forgot to mention, if there is a link it will be via the JobProd Table.
> > >
> > > Jim Kinneman
> > > Encompass Solutions, Inc.
> > >
> > > --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> > > >
> > > > Can you expand a bit on what you are trying to accomplish?
> > > >
> > > > Unless the part was made for that sales order there not being a link is normal. Especially for anything that is considered a stock part, MRP or a manual job could have been made it for inventory. Sales order would ship from stock/inventory and thus have no demand link back to a job.
> > > >
> > > > Not sure what the exact purpose of the report but you can link just on the part number but that would result in all jobs for that part being linked to all sales orders for that part very likely overkill.
> > > >
> > > > Have you looked at the part tracker? That has just about everything you want to know about a part. If the tracker doesn't meet your needs a dashboard that shows jobs on one tab and sales orders on another tab might be an option. Both would be synced on part number.
> > > >
> > > > Jim Kinneman
> > > > Encompass Solutions, Inc
> > > >
> > > > --- In vantage@yahoogroups.com, "sanfranc415" <sanfranc415@> wrote:
> > > > >
> > > > > I'm working on a Crystal Report that allows a estimator to enter a part number or range that would reproduce Part Adviser.
> > > > >
> > > > > I'm running into some problems with the linking of the tables.
> > > > >
> > > > > example:
> > > > >
> > > > > widget-a was produced for 3-jobs and 4-sales order, but the sales order does not have a link to a job. In this case the part is displayed on the 4-jobs, but it's missing the sales order that doesn't have a link to a job.
> > > > >
> > > > > --sf
> > > > >
> > > >
> > >
> >
>