×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Grouping formula?

Grouping formula?

(OP)
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

RE: Grouping formula?

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}

RE: Grouping formula?

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

-LB

RE: Grouping formula?

(OP)
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.

RE: Grouping formula?

Try using section suppress->suppress blank section.

-LB

RE: Grouping formula?

(OP)
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.

RE: Grouping formula?

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

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

-LB

RE: Grouping formula?

(OP)
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?

RE: Grouping formula?

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

RE: Grouping formula?

(OP)
I get an error saying a Boolean is required here.

RE: Grouping formula?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!