Excluding Records for Mailing Labels 2

[dbew]

Hi everyone,

Our database has a table that contains participant records. For some participants, there will be more than one record associated with his/her Social Security Number.

This is due to the fact that our participants can re-enroll in our program and are assigned to various worksites. For example John Smith, a participant, is assigned to one worksite (XYZ Dry Cleaners) on 01/10/2003 and leaves/exits the worksite on 01/30/2003. John can re-enroll with us and is assigned to a different worksite (ABC Hardware) on 02/25/2003.

We need to create labels to mail information to only the CURRENT worksites that hire our participants. I can successfully create the worksite mailing labels in Crystal 8.5. For example:

John Smith (999-99-9999)
ABC Hardware
2222 Main Street
Oakton, VA 20382
(Worksite assigned to John on 02/25/2003)

John Smith (999-99-9999)
XYZ Dry Cleaners
4444 South Avenue
Chantilly, VA 24657
(Worksite assigned to John on 01/10/2003, and in which he left on 01/30/2003)

But as you can see, the report displays not only the current worksite (ABC Hardware) but also the inactive worksite (XYZ Dry Cleaners). I do NOT want the inactive worksite to appear.

Can someone tell me how I can work around this? If it helps, a participant's CURRENT worksite label (if the participant has more than one worksite) always appears first like the example above.

Thanks so much!!!

dbew

 

[HowardHammerman]

One approach would be to group on Social Security number and then create a MAXIMUM summary field on the assignment date.

In the select expert enter the following record selection formula:

{table.assignmentdate} = maximum({table.assignmentdate},{table.socialsecurity})

Now supress the group header and footer.

Run your labels! Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[synapsevampire]

What is generally helpful is to share your tables structures, type and version of the database.

If you have a date field that shows when he left, then add something akin to the following to the record selection formula (Report->Edit Selection Formula->Record):

isnull({yourtable.yourexitdate})

This should eliminate rows that don't have an exit date, but it's hard to say given that you've shared nothing about your data structure.

-k

 

[dbew]

Thank you for the suggestions, synapsevampire and Howard!

synapsevampire, I am pulling from two tables from our ERP called Lawson 7.2.2.4 (running off of an AS/400 database):

Table 1:
Participant Name
Participant Social Security Number
Participant Enrollment Date
Assigned Worksite ID

Table 2:
(Assigned) Worksite ID
Worksite Name
Worksite Address Line 1
Worksite Address Line 2
Worksite City
Worksite State
Worksite Zip Code

Unfortunately (and as strange as it seems) the field that contains the Worksite Exit Date has not been populated yet. We recently upgraded our ERP and we never recorded that date in the old system. Can we just use the most recent Enrollment Date from Table 1?

 

[HowardHammerman]

Sure you can. That was the logic that I provided. Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[synapsevampire]

Sounds OK, and I think the Howard pretty much nailed it here.

If you have trouble with that, another means might be to group by participant name and order by Participant Enrollment Date.

In the participant group footer place the address columns, it should be only the last address.

-k

 

[dbew]

Thanks you guys!

Let me try this and get back to you!

THANK YOU AGAIN for taking the time to help me out!!!

 

[dbew]

One approach would be to group on Social Security number and then create a MAXIMUM summary field on the assignment date.

Question: I'm kinda still new in Crystal. How would I create a MAX summary field?

 

[synapsevampire]

In my alternative to Howard's approach, it would be better to group by SSN too, as names may be dupes. Either way works.

For Howards solution, try the following in the record selection formula:

{table.assignmentdate} = maximum({table.Participant Enrollment Date
},{table.Participant Social Security Number})

-k

 

[dbew]

synapsevampire & Howard,

Okay, so these are the steps I need to perform:

1. Group by SS#
2. Add the following to the Record Selection formula:
{table.assignmentdate} = maximum({table.Participant Enrollment Date},{table.Participant Social Security Number})

3. Suppress the group header and footer


Will this lead to a label format such as Avery 5160 where there are three columns of 10 rows each? Please say it will because that's our label sheet!

 

[synapsevampire]

When you create a new report, one of the choices is Mail Label, and the Avery 5160 is a canned template choice.

-k

 

[dbew]

Right. I just wanted to make sure that creating a grouping and suppressing will not affect this.

Thank you for confirming!

 

[dbew]

And synapsevampire, just want to make sure:

You typed:

{table.assignmentdate} = maximum({table.Participant Enrollment Date},{table.Participant Social Security Number})


Did you purposely use {table.Participant Enrollment Date}? Or should that also be {table.assignmentdate} like Howard had it before? Howard said:

{table.assignmentdate} = maximum({table.assignmentdate},{table.socialsecurity})


I'm just waiting for the report to finish running, so I want to make sure I know what to do before I begin. Thanks for your patience.

 

[synapsevampire]

Sorry, it's assignment date.

-k

 

[dbew]

Thanks, synapsevampire!

If you wanted to know my experience with this report, I've been trying to perfect it for some time. My original course of action was to suppress the inactive worksite labels using the following formula:

Not onfirstrecord and {ado.EMPLOYEE.FICA-NBR} = previous ({ado.EMPLOYEE.FICA-NBR})

This was placed in the Format -> Section -> Details -> Suppress -> Formula Editor

This worked until management asked that I sort the labels by zip code order (ascending). Well, the above formula only worked when the multiple records were next to one another (same worksite despite multiple assigned dates). But if a participant had a different worksite address for each assigned date, then the inactive would show.

I think the addition of the formula you and Howard are suggesting will solve this because it will be part of the Selection Formula and not just a suppression (which basically just hides the records but doesn't remove them from the report itself).

 

[dbew]

synapsevampire & Howard,

When I enter this formula:

{table.assignmentdate} = maximum({table.assignmentdate},{table.socialsecurity})

I get this message after checking for errors:

This function cannot be used because it must be evaluated later.

Did I miss a step? I created a group using SS#. Then I entered the above formula in the Record Selection Formula Editor.

 

[dbew]

Does anyone know why I get the following message in the Record Selection Formula Editor:

This function cannot be used because it must be evaluated later.

I've grouped my records by Social Security Number and I wish to only have the most recent Assigned Date. This is the formula I am attempting to add to the Record Selection Formula Editor: {table.assignmentdate} = maximum({table.assignmentdate},{table.socialsecurity})

 

[dbew]

synapsevampire & Howard,

I enter this formula in the Group Selection Formula Editor instead:

{table.assignmentdate} = maximum({table.assignmentdate},{table.socialsecurity})

Amnd it worked. Thanks!