Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Formula issue on a one-to-many relationship table Post Reply Post New Topic
Author Message
TC Tran
Newbie
Newbie


Joined: 24 Oct 2011
Online Status: Offline
Posts: 19
Quote TC Tran Replybullet Topic: Formula issue on a one-to-many relationship table
    Posted: 13 Dec 2016 at 5:31am
Hello, I have created a formula to attempt to display a value when the record from a one-to-many relationship table does not certain a certain values. I have not been successful in producing this formula. My formula is below:

IF NOT ({TBL_INC_FOLLOWUP.FOLLOWUP_SUB_TYPE} IN ["Manager Sign-off", "Director Sign-off"]) THEN "Need Reviewer Sign-Off" ELSE "?"

When a record in the one-to-many relationship table contains the value I'm excluding the result from the formula displayed "Need Reviewer Sign-Off". And when the record does not contain the value I'm excluding, it just display a null value. Is there something wrong with my formula?


I really appreciate any help you could provide in advance!!!

Thank you,
Thanh Tran
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 13 Dec 2016 at 10:49am
The biggest issue in a one-to-many relationship especially if there are nulls involved.   You should have a condition to check for nulls  i.e.,

if not(isnull({TBL_INC_FOLLOWUP.FOLLOWUP_SUB_TYPE}) ) then
IF NOT ({TBL_INC_FOLLOWUP.FOLLOWUP_SUB_TYPE} IN ["Manager Sign-off", "Director Sign-off"]) THEN "Need Reviewer Sign-Off" ELSE "?"

Also the formula editor should be set for 'Default Values for Nullls'

IP IP Logged
TC Tran
Newbie
Newbie


Joined: 24 Oct 2011
Online Status: Offline
Posts: 19
Quote TC Tran Replybullet Posted: 14 Dec 2016 at 10:21pm
Hello Kevlray,

Thank you for your assistance! I revised the formula logic and set formula editor to "Default Values for Nulls" per your suggestions and it doesn't seem to work. For the record where TBL_INC_FOLLOWUP.FOLLOWUP_SUB_TYPE contains "Manager Sign-off" or "Director Sign-off" the result from the formula still displays "Need Reviewer Sign-Off". Do you have any recommendations?

Thank you,
Thanh
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 15 Dec 2016 at 3:11am
are you trying to get one value result from multiple rows of data?
IP IP Logged
TC Tran
Newbie
Newbie


Joined: 24 Oct 2011
Online Status: Offline
Posts: 19
Quote TC Tran Replybullet Posted: 16 Dec 2016 at 3:12am
Hello DBlank,

Below is an example of a record that contains all 4 values from the FollowUp_Sub_Type table (one-to-many relationship) and I'm trying to create a formula to look when a record does not contain a value of either "Manager-Sign-off" or "Director Sign-off" to display "Need Reviewer Sign-off" in a calculated field.

{TBL_INC_FOLLOWUP.FOLLOWUP_SUB_TYPE}
Chart Review
Consult - Risk
Review - Risk
Investigation

Thank you,
Thanh
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Dec 2016 at 3:27am
I think you have a primary key like a document_id and that document has multiple rows of data associated to (your 1:many description) and you want to 'assign' a singular value to that document based on looking at all of the rows.
In crystal you generally you would approach this by grouping on the document_id and using a group summary of some sort to derive your value.
Int his case you could assign a numeric value to your 'sign off' field via a formula
example using a formula called 'ReviewSignOff' as
if table.signofffield in ["Manager-Sign-off","Director Sign-off"] then 1 else 0
Now you sum this formula at the document group level
sum(@ReviewSignOff,table.document_id)
if that sum >0 you have a sign off so you can apply a formula to that logic to get the display value you want
// formula ShowSignerResult
if sum(@ReviewSignOff,table.document_id)>0 then "Need Reviewer Sign-off" else ""
Place this formula in the group header or footer to show the result for each group
IP IP Logged
TC Tran
Newbie
Newbie


Joined: 24 Oct 2011
Online Status: Offline
Posts: 19
Quote TC Tran Replybullet Posted: 19 Dec 2016 at 4:43am
Hello DBlank,

Thank you so much for your help! This suggestion works beautifully!!! I really appreciate your help!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.