Order of appearance formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22443
Printed Date: 05 May 2024 at 5:19am
Topic: Order of appearance formula
Posted By: Andrewarbogast
Subject: Order of appearance formula
Date Posted: 07 Sep 2017 at 3:32am
So I have 5 fields that I'm trying to combine in a formula by the order they appear. This is what I have so far but I know I'm missing something. Please help. Thanks
if {tbl_adult_intake_assess_V2.assessment_date}="" then {tbl_DA_Adult_Mental_HA.Data_Entry_Date}
else {tbl_adult_intake_assess_V2.assessment_date}
if {tbl_DA_Adult_Mental_HA.Data_Entry_Date}="" then {tbl_adult_intake_assessment.assessment_date}
else {tbl_DA_Adult_Mental_HA.Data_Entry_Date}
if {tbl_adult_intake_assessment.assessment_date}="" then {tbl_DA_Mental_Health.Data_Entry_Date}
else {tbl_adult_intake_assessment.assessment_date}
if {tbl_DA_Mental_Health.Data_Entry_Date}="" then {tbl_v2_adult_intake.assessment_date}
else {tbl_DA_Mental_Health.Data_Entry_Date}
if {tbl_v2_adult_intake.assessment_date}="" then "Missing MHA"
|
Replies:
Posted By: Andrewarbogast
Date Posted: 07 Sep 2017 at 3:41am
|
I put AND in between each argument and then it says that a date needs to be where the "" are.
|
Posted By: Andrewarbogast
Date Posted: 07 Sep 2017 at 3:56am
I put this in instead but the "remaining text does not appear to be part of the formula" message appears
if isnull({tbl_adult_intake_assess_V2.assessment_date})then{tbl_DA_Adult_Mental_HA.Data_Entry_Date}
else {tbl_adult_intake_assess_V2.assessment_date}
else
if isnull({tbl_DA_Adult_Mental_HA.Data_Entry_Date})then {tbl_adult_intake_assessment.assessment_date}
else {tbl_DA_Mental_Health.Data_Entry_Date}
else
if isnull({tbl_adult_intake_assessment.assessment_date})then {tbl_DA_Mental_Health.Data_Entry_Date}
else {tbl_adult_intake_assessment.assessment_date}
else
if isnull({tbl_DA_Mental_Health.Data_Entry_Date})then {tbl_v2_adult_intake.assessment_date}
else {tbl_DA_Mental_Health.Data_Entry_Date}
else
if isnull({tbl_v2_adult_intake.assessment_date})then 'Missing MHA'
|
Posted By: DBlank
Date Posted: 07 Sep 2017 at 4:27am
you can't do the double "else"...
Instead you can do this...
if A then B else
If C then D else
IF E then F else G
If you are dealing with NULLS make sure to set the formula to use defaults for NULLS and also make sure you have the sequence in the correct priority order.
|
Posted By: kevlray
Date Posted: 07 Sep 2017 at 4:29am
I see in your second formula that you have multiple else per if statement. That is not allowed.
In the first formula. Each If statement will evaluate and because of how the formula is set up, only the last result will be returned. So I am not sure what kind of result you trying to achieve.
|
Posted By: Andrewarbogast
Date Posted: 07 Sep 2017 at 4:53am
The formula worked but not the result i wanted.
My goal result is show Date1, if date1 is blank, show date2, if date2 is blank, show date3, if date3 is blank, show date4, if date4 is blank, show date5, if date5 is blank show "Missing MHA"
|
Posted By: kevlray
Date Posted: 07 Sep 2017 at 6:04am
You need to reverse your logic. Check to see if this works for you.
if {tbl_adult_intake_assess_V2.assessment_date}<> "" then
{tbl_adult_intake_assess_V2.assessment_date}
else if tbl_DA_Adult_Mental_HA.Data_Entry_Date}<> "" then
{tbl_DA_Adult_Mental_HA.Data_Entry_Date}
else if {tbl_adult_intake_assessment.assessment_date}<> "" then
{tbl_adult_intake_assessment.assessment_date}
else if {tbl_DA_Mental_Health.Data_Entry_Date}<> "" then
{tbl_DA_Mental_Health.Data_Entry_Date}
else if {tbl_v2_adult_intake.assessment_date}<> "" then
{tbl_v2_adult_intake.assessment_date}=""
else "Missing MHA"
|
Posted By: Andrewarbogast
Date Posted: 07 Sep 2017 at 6:26am
|
This formula highlights the "" and asks for a date.
|
Posted By: Andrewarbogast
Date Posted: 07 Sep 2017 at 6:42am
When I try this formula:
if isnull({tbl_v2_adult_intake.assessment_date}) then
{tbl_v2_adult_intake.assessment_date}
else if isnull({tbl_adult_intake_assessment.assessment_date})then
{tbl_adult_intake_assessment.assessment_date}
else if isnull({tbl_adult_intake_assess_V2.Data_Entry_Date}) then
{tbl_adult_intake_assess_V2.Data_Entry_Date}
else if isnull({tbl_DA_Adult_Mental_HA.Data_Entry_Date}) then
{tbl_DA_Adult_Mental_HA.Data_Entry_Date}
else if isnull({tbl_DA_Mental_Health.Data_Entry_Date}) then
{tbl_DA_Mental_Health.Data_Entry_Date}
it works but doesn't show the last two dates in the formula.
|
Posted By: Andrewarbogast
Date Posted: 07 Sep 2017 at 6:56am
So I'm able to get this formula to work for my last two dates: if isnull({tbl_DA_Mental_Health.Data_Entry_Date})then
{tbl_DA_Adult_Mental_HA.Data_Entry_Date}
else {tbl_DA_Mental_Health.Data_Entry_Date}
but then not sure how to add the rest of the formulas to it.
|
Posted By: Andrewarbogast
Date Posted: 07 Sep 2017 at 7:12am
Ok...haha sorry for all the posts. I have tested each of these 5 statements individually and they work. The question is how do I join them together?
//if isnull({tbl_DA_Mental_Health.Data_Entry_Date})then
// {tbl_DA_Adult_Mental_HA.Data_Entry_Date}
//else {tbl_DA_Mental_Health.Data_Entry_Date}
//
//if isnull({tbl_DA_Adult_Mental_HA.Data_Entry_Date})then
// {tbl_adult_intake_assess_V2.Data_Entry_Date}
//else {tbl_DA_Adult_Mental_HA.Data_Entry_Date}
//if isnull({tbl_adult_intake_assess_V2.Data_Entry_Date})then
// {tbl_adult_intake_assessment.assessment_date}
//else {tbl_adult_intake_assess_V2.Data_Entry_Date}
//if isnull({tbl_adult_intake_assessment.assessment_date})then
// {tbl_v2_adult_intake.assessment_date}
//else {tbl_adult_intake_assessment.assessment_date}
if isnull({tbl_v2_adult_intake.assessment_date})then "MHA Missing"
|
Posted By: DBlank
Date Posted: 07 Sep 2017 at 7:58am
You can't really stitch them together that way.
I assume there is a hierarchy to the 5 fields, meaning that if field 1 is not empty it is the value to use, then if field 2 is not empty it is the value to use, etc.
Is this a correct assertion? If so, what is that hierarchical sequence?
|
Posted By: Andrewarbogast
Date Posted: 07 Sep 2017 at 8:05am
Yes you are correct. the hierarchy is:
{tbl_v2_adult_intake.assessment_date}
{tbl_adult_intake_assessment.assessment_date}
{tbl_adult_intake_assess_V2.Data_Entry_Date}
{tbl_DA_Adult_Mental_HA.Data_Entry_Date}
{tbl_DA_Mental_Health.Data_Entry_Date}
I also tried this formula and it works for the first three fields but not sure how to add in the last two.
if isnull({tbl_v2_adult_intake.assessment_date})
then
(
if isnull({tbl_adult_intake_assessment.assessment_date})
then
{tbl_adult_intake_assess_V2.Data_Entry_Date}
else
{tbl_adult_intake_assessment.assessment_date}
)
else
{tbl_v2_adult_intake.assessment_date};
|
Posted By: DBlank
Date Posted: 07 Sep 2017 at 8:23am
if not isnull({tbl_v2_adult_intake.assessment_date} ) then totext({tbl_v2_adult_intake.assessment_date})
else if not isnull({tbl_adult_intake_assessment.assessment_date}) then totext({tbl_adult_intake_assessment.assessment_date})
else if not isnull({tbl_adult_intake_assess_V2.Data_Entry_Date}) then totext({tbl_adult_intake_assess_V2.Data_Entry_Date})
else if not isnull({tbl_DA_Adult_Mental_HA.Data_Entry_Date}) then totext({tbl_DA_Adult_Mental_HA.Data_Entry_Date})
else if not isnull({tbl_DA_Mental_Health.Data_Entry_Date}) then totext({tbl_DA_Mental_Health.Data_Entry_Date}) else "MHA Missing"
|
Posted By: kevlray
Date Posted: 07 Sep 2017 at 8:23am
Back to the error message. You were checking the fields against a "", so I made the assumption the fields were strings. The issue you are going to have is the formula has to return a single data type. It will not return a date or a string (last if statement). My formula would work if you replaced the = '' with the isnull check (as you did in the previous post), but also for the last if statement to work where you have the phrase "MHA Missing", the result of each if statement will have to be converted to a string (i.e, CSTR or TOTEXT).
|
Posted By: Andrewarbogast
Date Posted: 07 Sep 2017 at 8:42am
|
Thanks!!! The formula worked great.
|
|