Order of appearance formula

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"

I put AND in between each argument and then it says that a date needs to be where the "" are.

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'

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.

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.

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"

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"

This formula highlights the "" and asks for a date.

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.

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.

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"

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?

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};

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"

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).

Thanks!!! The formula worked great.