Share String 1

[YANKRAY]

Using CR 10.0

I have Work Orders that have other work orders assigne to them. The original work order would be the "Parent" the others would be the "Child" orders.

The "Parent" has an association to the customer number in a field "CUST_NO" the Child orders do not.

Is there a way to create a new field that would identfy the "Child" with the "CUST_NO" because of the association of Parent to Child?

The "PARENT_NO" field (which I group on) is the same for the Parent and the child work orders.

My attempt is to get a grouping by "CUST_NO".

When I group by Parent no, my data looks like this.

PARENT WORK ORDER NO CUST_NO
576N0001 576N0001 2160005
576N0001 543567
576N0001 543568
576N0001 543569

I am trying to get a grouping by the CUST_NO 216005 then by the PARENT and its CHILD.

Thanks,
Ray

 

[lbass]

You could instead insert a maximum on the cust_no at the parent group level and then do a group sort. Would that be sufficient?

-LB

 

[YANKRAY]

LB,

I tried that, but I need the grouping so the user can visualize the Parent/Child to the CUST_no.

With the CUST_NO showing on the left, the user can go directly to that CUST_NO and see the Orders for that CUSTomer and print the page or pages that they need.

 

[lbass]

I guess you want it in the group tree for navigation purposes. Okay, then create a SQL expression:

(
select max(`Cust_No`)
from table A
where A.`Parent` = table.`Parent`
)

Substitute your actual table name for "table" and adjust the punctuation to reflect that of your datasource. Insert a group on this SQL expression and make it your Group #1.

-LB

 

[YANKRAY]

LB,

I am trying to implement the SQL statement but the grouping is using the Max Customer for all the records in the table.

The Max Customer number for the table is "610".

This is the only grouping there is.

Then grouped by the "Parent WO".

Ray

 

[lbass]

Please show the content of the SQL expression you created based on my suggestion.

-LB

 

[YANKRAY]

This is the SQL I created.

(
select max("AAII_WORK_ORDER_INFO"."CUST_ORDER_NO")
from "AAII_WORK_ORDER_INFO"
where "AAII_WORK_ORDER_INFO"."PARENT_WO" = "AAII_WORK_ORDER_INFO"."PARENT_WO"
)

 

[lbass]

You didn't implement it correctly. Please try copying the following into your SQL expression area:

(
select max("CUST_ORDER_NO")
from "AAII_WORK_ORDER_INFO" A
where A."PARENT_WO" = "AAII_WORK_ORDER_INFO"."PARENT_WO"
)

-LB

 

[YANKRAY]

Perfect LB,

Thank you for correcting me.

Ray