Sorting Work To List by Start Date and Works Order Want Date

[cr7user]

Hi,

I have created a shop floor work to list using crystal 7. I have sorted the operation table by scheduled start date.

When multiple operations are scheduled to start on the same day, I would like to use a secondary sort by works order finish date.

Crystal displays the works order finish dates if the sub id is zero, but leaves it blank if the sub ids are greater than zero. As a result of this, my sort criteria doesn't work.

Is there a way to display the works order finish dates using a formula (irrespective of what the sub ids are) and then sort that field. Kindly help.

PS. I have attached a sample of my report records.

 

[lbass]

Your links don't work. You should provide some sample data here that illustrates the issue, and which shows fields labeled by both table and field, as in {table.field}.

If you have multiple tables, explain how they are linked, what kind of join, etc.

-LB

 

[cr7user]

Hi LB,

Thanks for your response. I have attached the report this time.

The tables in the report are Work_order, Operation and Shop_Resource linked by equal joins and work_order and Part tables linked by left outer join.

Each works order will have its sub id as zero and has a finish date. A list of sub-assemblies are asscociated with it (sub ids greater than zero). Each sub-assembly has a set of operations (listed under operation table) to manufacture the component.

I am trying to sort the report by operation scheduled start date and works order finish date, but can't use the latter field cos it returns null values for sub ids greater than zero.

I need the finish date for my sort criteria. Kindly help.

 

[cr7user]

LB,

This is what I am expecting to see in the report.

WO Sub ID Start Date WO Finish Date

W2000 0 15/06/2010 20/07/2010
W2001 1 15/06/2010
W2002 2 16/06/2010

The report is displaying the above info (although there finish date records in the works order table for lines 2 & 3).

If they can be displayed on the detail section of the report, then my output should be as follows:


WO Sub ID Start Date WO Finish Date

W2000 0 15/06/2010 20/07/2010
W2001 1 15/06/2010 21/07/2010
W2002 2 16/06/2010 22/07/2010

Thanks in advance for all your help.

 

[lbass]

Sorry, still couldn't download it. Where are the dates coming from for the sub IDs that are greater than zero?

-LB

 

[cr7user]

Sub ids greater than zero are sub-assemblies indented under the main assembly (Which always has zero as sub id and a finish date).

Lines with sub ids greater than zero do not have a WO finish date. I want to use the main assembly's finish date on those lines.

Is it possible to do this?

 

[lbass]

But in your sample you are showing different finish dates for sub IDs than what is shown for the main assembly. From what you are saying, they should be the same, shouldn't they?

Also, how do you know what subIDs/work order numbers for sub assemblies go with what main assembly work order numbers? I don't see a field that relates the two, unless it is the first four characters of the Work Order number?

-LB

 

[cr7user]

If you look at my sample, there are three different work orders and hence three different finish dates. My problem starts when I want to display the finish date of the main assy on the detail section of sub ids greater than 1.

For example, lets say works order W12345 is for a computer assy (sub id = 0). It has two sub-assemblies (Monitor (sub id = 1) and CPU (sub id = 2)). The works order finish date is 21/07/2010.

Report works fine if sub id = 0 as shown below.

WO Sub ID Start Date WO finish Date
W12345 0 25/06/2010 21/07/2010

But report does not display the WO finish date (Although the info exists in the table) for sub ids 1 & 2 as shown below.

WO Sub ID Start Date WO Finish Date
W12345 1 25/06/2010
W12345 2 25/06/2010

So I can't do sort by start date and WO finish date if I have null records. Is there a way to display the WO finish date on the detail section?

 

[lbass]

Please look at your previous post - 15 Jun 10 11:32. You are implying that these relate to the same work order somehow.

Using your last post, what do you expect to see? I'm assuming:

WO Sub ID Start Date WO Finish Date
W12345 1 25/06/2010 21/07/2010
W12345 2 25/06/2010 21/07/2010

You could sort without adding a finish date to the detail line, by grouping on WO and then doing a group sort based on the maximum of WO Finish Date. Would that work for you?
You could then add a finish date to the detail section by using a formula:

maximum({table.finishdate},{table.WO})

-LB

 

[cr7user]

I have a group on WO, but I have 5 other groups before that in the sorting order. Can I do group sort based on maximum of work order finish date in Crystal 7. Can't find that option in my report writer.

I tried your formula maximum({WORK_ORDER.DESIRED_WANT_DATE},({WORK_ORDER.BASE_ID})) and inserted it on the detail section.

It still returns a null value on sub ids greater than zero. I am stumped.

 

[lbass]

In 7.0 it is probably called TopN/group sort.

What tables are you working with and which tables contain which fields?

-LB

 

[cr7user]

I am using 4 tables Operation, Work Order, Part and Shop Resource.

The main fields are work order base id, work order sub id , operation resource id, operation start date, operation finish date and work order finish date.

I have tried TopN/Group Sort and it doesn't seem to sort it.

 

[lbass]

Please explain how you implemented the group sort.

What about {WORK_ORDER.DESIRED_WANT_DATE}--please try to use the table and field names consistently.

How does the operation finish date fit into this? How does it relate to the WO finish date?

-LB

 

[cr7user]

Work order finish date and work order desired want date are the same - Apologies for my inconsistency.

A works order consists of multiple operations to manufacture a part. For example, a works order with a desired want date of 28/06/2010 can have 3 operations to make a part.

Operation 1 can have an operation finish date of 21/06/2010, op 2 could have an op finish date of 24/06/2010 and op 3 finish date could be 28/06/2010 depending on the duration of each operation.

My sort criteria are as follows.

Group 1 - Operation Resource ID as in Turing Cell, Milling Cell etc.

Group 2 - Operation Resource Type as in CNC Lathe, Manual Lathe etc

Group 3 - Operation Start Date

Group 4 - Work order desired want date (Which doesn't work cos of null records)

Group 5 - Work Order Base id (Same as work order number)

Group 6 - Work Order Sub id (Sub assembly numbers)

Group 7 - Operation sequence no

 

[lbass]

Try removing Group 4. Then place the formula:

maximum({WORK_ORDER.DESIRED_WANT_DATE},({WORK_ORDER.BASE_ID})

...in the detail section. Also right click on WORK_ORDER.DESIRED_WANT_DATE} and insert a maximum on it at the WO Base ID group level. Then go into group sort->WO Base ID tab and select maximum of desired want date as the sort field.

-LB

 

[cr7user]

Did that LB. It still returns null records for sub ids greater than zero and the sort doesn't work.

 

[lbass]

I tested a group sort with null values in the group, as well as placing the maximum formula in the detail section. It works here, so I guess I am missing something about your data. Perhaps someone else can help.

-LB