Link to Nowhere

[trenttc]

I can group by C.ID, then by P.SKU after parsing and reformatting P.ID_etc. I want to group by C.Name, then by P.SKU. The problem is that I can't just add a group by for C.Name (can't create an initial link between C.ID and P.IDs to allow grouping by C.Name). How do I build a list of IDs so I can group by C.Name? Grouped by C.Name has to be in a Group Header. CR11

C.Name (group by 1st)
C.ID
P.SKU (group by 2nd)
P.ID_etc
O.SKU
O.Qty (sum)

 

[lbass]

How are you linking the tables? Or if that is the issue, please show samples of the relevant fields to illustrate the problem.

-LB

 

[trenttc]

The linking is the issue. I parse/reformat P.ID_etc to get @ID ('01234 etc' becomes '0000001234'). Now I can group by @ID. Now that I have a vaild ID, I link to a subreport that displays C.Name. That is followed by SKU footer totals. Something like this:

0000001234 (Header Group 1)
Shoe, Jim (Subreport Display)
SKU 253 Qty 10 (Footer Group 2)
SKU 254 Qty 15

0000001235
Dover, Ben
SKU 123 Qty 10
SKU 233 Qty 20

I'd like to pass C.Name back to the main report and group by it (Group Header 1) before @ID (Group Header 2).

Dover, Ben
0000001235
SKU 123 Qty 10
SKU 233 Qty 20

 

[lbass]

You can't group on a shared variable from a subreport. You could, however, make the sub the main report and then group on the name field.

Please show a sample of each ID field before parsing/reformatting, and identify the datatype of each, and label each.

-LB

 

[trenttc]

If I group by formula @Name, how do I write it to reference @ID, point to C.ID, and get C.Name?

@ID formula:
stringvar array cust := split ({P.ID}," ");
totext(val(cust[1]),"0000000000");

 

[lbass]

Please show a sample of the actual database fields for both IDs and the name field(s). Are there two name fields???

-LB

 

[trenttc]

Just one name field. The first entry in P.ID is always the ID to link to.

C.Name - Shoe, Jim
C.ID - 0000001234
P.ID - 1234 45678

@ID - stringvar array cust := split ({P.ID}," ");
totext(val(cust[1]),"0000000000");

Can formula @Name use @ID as an index to get C.Name?
@Name - C.Name(@ID)

 

[lbass]

What is the content of {@Name}?

-LB

 

[trenttc]

Sorry, I don't have an @Name. I was just asking if it was possible to create a formula called @Name that uses the parsed value from P.ID (@ID) as an index to get to C.Name.
I will stop asking about @Name now.

 

[lbass]

Use the table C in the main report, and insert a group on {C.Name}, followed by a group on {C.ID}. Then insert a subreport that uses the P table and O table, and link the sub to the main report by linking {C.ID} to your formula {@ID} (the one based on P.ID}.

-LB