| Author |
Message |
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
   Topic: Order of appearance formula
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"
|
 IP Logged |
|
|
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
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.
|
|
IP Logged |
|
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
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'
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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.
|
|
IP Logged |
|
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
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"
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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"
|
|
IP Logged |
|
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
Posted: 07 Sep 2017 at 6:26am |
|
This formula highlights the "" and asks for a date.
|
|
IP Logged |
|
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
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.
|
|
IP Logged |
|
Andrewarbogast
Newbie
Joined: 21 Aug 2017
Location: United States
Online Status: Offline
Posts: 39
|
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.
|
|
IP Logged |
|
|
|
