Grouping formula?

[doc1000101]

Completely stumped on this one.

Table 1 layout:
PO Job Suffix
123 123456 001
456 123456 000
987 123456 010

Table 2 layout:
Job Suffix Due Date
123456 001 12/12/2018
123456 000 12/10/2018
123456 010 11/11/2018

I need to group by job due date where the suffix = "000". Where I am having trouble is there are multiple records with the same job number but all have a different suffix (with different due dates). So my group will display all PO's for all jobs, but be grouped by the due date on suffix "000"

display:
Group=12/12/2018
details:
job:12345 suffix 001 due date: 12/10/2018 PO:987654
job:12345 suffix 000 due date: 12/12/2018 PO:456789
job:12345 suffix 005 due date: 11/11/2018 PO:789456

Group=12/15/2018
job: 234567 suffix 000 due date: 12/15/2018 PO:684153
job: 234567 suffix 010 due date: 11/15/2018 PO:456789

Any thoughts/advise?

 

[kray4660]

So do you even want to see the other PO's? It would be easy to filter those out. But you could write a formula that uses the date for PO's that have a suffix of 000.

If {suffix} = '000' then {due date}

 

[lbass]

You can group on Kray’s formula for Group #1 and then group or sort on job and then due date.

-LB

 

[doc1000101]

Thanks for the replies.
I tried using that formula and I get lots of blanks. I assume because the first record it comes across might not be the 000.
If the top record is the 000 I get the due date, if its not I get a blank.

Yes I want to show all jobs and all po's (order doesn't matter). Only thing that matters is they are grouped by the 000 due date.

 

[lbass]

Try using section suppress->suppress blank section.

-LB

 

[doc1000101]

lbass: unfortunately that only suppresses the group name (that's all that is in the group header), but still prints group 2 (job) and details (all jobs and all po's).
I'm going to start from scratch on this one and see if I did something goofy.

 

[lbass]

You can add a suppression formula to those sections also, using:

{@group#1formula}=date(0,0,0)

-LB

 

[doc1000101]

I was able to put If {suffix} = '000' then {due date} in a sub report to pull the correct date, however it doesn't look like I can group off of that.
Any ideas on how to do that / if possible?

 

[lbass]

What happened when you grouped on kray’s formula and then formatted the group headers and footers for every group to suppress when the group formula = date(0,0,0)? This should have worked. I’m assuming the date field is actually a date datatype.

-LB

 

[doc1000101]

I get an error saying a Boolean is required here.

 

[lbass]

Where in the report are you getting this error? In the group selection area or in the section expert where you are trying to suppress unwanted results?

It might help for trouble shooting purposes if you show the group selection formula and the formula you are using for suppression.

-LB