| Author |
Message |
TC Tran
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 19
|
   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 Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
TC Tran
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 19
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Dec 2016 at 3:11am |
|
are you trying to get one value result from multiple rows of data?
|
|
IP Logged |
|
TC Tran
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 19
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
TC Tran
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 19
|
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 Logged |
|
|
|
