Formula issue on a one-to-many relationship table

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

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'

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

are you trying to get one value result from multiple rows of data?

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

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

Hello DBlank,

Thank you so much for your help! This suggestion works beautifully!!! I really appreciate your help!