I am tying to write a report that connects overdue purchase orders (PO) to any sales orders (SO) that they relate to.
The MRP system creates 2 tables of supply and demand references (PSAD.ID's and ID Links) that can link through up to five levels of components in the manufacturing process.
My report has to use the two tables repeatedly to follow the path from PO to SO. The thread can terminate at any level with a SO result.
However once a result has been found (a SO) the report cannot prevent itself continuing to search for further entries along the maximum length of possible thread.
Obviously i can suppress any unwanted data found after a result is achieved but this often causes the report to run very slowly and often finds a result that seems to have no solution. This causes crystal to lock up and can only be killed using task manager. It is as if i need to control the links between tables to terminate after a result is found.
I've tried to use subreports to limit the continuing search to only those entries that have not found a result but: at any stage the thread may split into more than one route, each of which must be pursued. I can only return one result from my subreport (the last one in the list), so i am missing much important data.
As simple as I can explain this:
Purchase Order PO.XYZ (for Component C1) >>>> used in Works Orders W1,W2,W3.
W1 >>> used to make Component C2 >>> needed for SO.123 and SO.234
W2 >>> C2 >>> SO.345
W3 >>> C3 >>> W4 (Component C4) and W5
W4 >>> C5 >>> SO.456
W5 >>> C5 >>> SO.567
Or in terms of the tables:
PSAD table----------------Link table-------------------------PSAD table---------------------------------Link Table etc, etc
ID01 (orderNo1)>>>>>>demandID/supplyID >>>>>> ID02(orderNo2)/ID03(orderNo2) >>>> repeats as before
There are a few possible trip ups as any oversupply at any stage goes into stock and has a zero demandID causing a lengthy search for a nothing.
I seem stuck between applying too many record selection filters and it returns nothing and it getting stuck in an endless search through millions of entries with no end. Any suggestions please.
I'm using Crystal Reports 2011 and SQL.DB files.
The MRP system creates 2 tables of supply and demand references (PSAD.ID's and ID Links) that can link through up to five levels of components in the manufacturing process.
My report has to use the two tables repeatedly to follow the path from PO to SO. The thread can terminate at any level with a SO result.
However once a result has been found (a SO) the report cannot prevent itself continuing to search for further entries along the maximum length of possible thread.
Obviously i can suppress any unwanted data found after a result is achieved but this often causes the report to run very slowly and often finds a result that seems to have no solution. This causes crystal to lock up and can only be killed using task manager. It is as if i need to control the links between tables to terminate after a result is found.
I've tried to use subreports to limit the continuing search to only those entries that have not found a result but: at any stage the thread may split into more than one route, each of which must be pursued. I can only return one result from my subreport (the last one in the list), so i am missing much important data.
As simple as I can explain this:
Purchase Order PO.XYZ (for Component C1) >>>> used in Works Orders W1,W2,W3.
W1 >>> used to make Component C2 >>> needed for SO.123 and SO.234
W2 >>> C2 >>> SO.345
W3 >>> C3 >>> W4 (Component C4) and W5
W4 >>> C5 >>> SO.456
W5 >>> C5 >>> SO.567
Or in terms of the tables:
PSAD table----------------Link table-------------------------PSAD table---------------------------------Link Table etc, etc
ID01 (orderNo1)>>>>>>demandID/supplyID >>>>>> ID02(orderNo2)/ID03(orderNo2) >>>> repeats as before
There are a few possible trip ups as any oversupply at any stage goes into stock and has a zero demandID causing a lengthy search for a nothing.
I seem stuck between applying too many record selection filters and it returns nothing and it getting stuck in an endless search through millions of entries with no end. Any suggestions please.
I'm using Crystal Reports 2011 and SQL.DB files.