Formula Reset
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22482
Printed Date: 05 May 2024 at 1:48am
Topic: Formula Reset
Posted By: dmkanz
Subject: Formula Reset
Date Posted: 19 Oct 2017 at 8:16am
I have created a formula that extracts a contact by job listing but I need to be able to reset for each iteration.
Report Layout
Group by Customer
Group by Job
Report Example
ABC Customer
12345 Job 12345 Contact Name A
67890 Job 67890 Contact Name B
My formula Contact is
if {Description} like "*Client Reference*" or {Description} like "*Marketing Reference*" then {FirstName} + {LastName} else "Unassigned"
The problem I am having is if the customer has only one job listed it works just fine. But if the customer has multiple jobs it makes all the contacts as unassigned or blank except one.
I believe I need to reset the formula for each job iteration.
Thanks in advance
DMKanz
|
Replies:
Posted By: hilfy
Date Posted: 23 Oct 2017 at 7:54am
Is the contact assigned to the job or to the customer?
How are you linking to the table where the customer info is located?
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: dmkanz
Date Posted: 31 Oct 2017 at 3:49am
Dell
The contact is associated with the job
Thanks,
DMKanz
|
Posted By: dmkanz
Date Posted: 31 Oct 2017 at 4:23am
Dell
I am grouping by Customer and then by Job. A job can have multiple contacts.
i.e.
Customer ABCo
Job Contact Description
1234 Jones
1234 Smith
1234 Anderson
5678 Carlson Client Contact
9012 Larson
9012 Hanson Client Contact
What I am trying to do is display all jobs by contact that is "Client" but I also want to display all jobs that do not have a contact designated as "Client"
i.e.
Customer ABCo
Job Contact Description
1234 "Unassigned"
5678 Carlson Client Contact
9012 Hanson Client Contact
I tried a formula but I cannot get it to cycle through the contacts. It looks at the first contact and then moves on giving me an unassigned even though one of the contacts is designated as "Client:
Thanks in advance
DMKanz
|
Posted By: dmkanz
Date Posted: 31 Oct 2017 at 4:45am
Any help would be much appreciated
Thanks,
DMKanz
|
Posted By: hilfy
Date Posted: 31 Oct 2017 at 4:57am
Will the value for the description ever be null? If so, you have to specifically look for that.
Here's what I would do to solve the issue:
1. Add a group on the Description field and make the sort "In specified order" so you can sort to the top the contact types that you want to show in the report. Add any contact types that you do not want to see to an "Others" group (these are all options on the group configuration screen.)
2. Put the data in the Job group header instead of details. Suppress the contact group header and details.
3. Modify your formula to be something like this:
[code]
if not IsNull({Description}) then
if {Description} like "*Client Reference*" or {Description} like "*Marketing Reference*" then
{FirstName} + {LastName}
else "Unassigned"
else "Unassigned"
When fields can have a null value, you MUST check for null BEFORE checking for any other value.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: dmkanz
Date Posted: 31 Oct 2017 at 5:13am
That worked!
This forum is awesome - Dell thank you so much for the help!
I added the description group by and sorted by descending and it worked perfectly.
DMKanz
|
|