Formula Date field based on conditions
I have a Formula Date Field Titled "Tentative Discharge Date". I also have a dropdown field titled "Reason for Placement", and a Date field title Placement Date, I want the following to happen with th...
Forum Discussion
Del,
Great. In case you didn't get the second message I just added there's a duplicate line in the resulting part of the formula. The noSymptoms result is in there twice, and the second occurrence doesn't have a comma after it. You'll need to delete the one that is incorrect and add a comma..
Neil
Getting an error below
var bool newPlacement = [Entrance Reason] = "New Placement";
var bool changedRoom = [Entrance Reason] = "Customer Changed Room";
var bool newRoom = $newPlacement or $changedRoom;
var bool customerReturned = $newRoom and ([Entrance Reason] = "Customer returned after Pre-Clearance departure");
var bool positiveContact = $newRoom and ([Reason for Placement]="Customer had contact with known positive");
var bool customerSymtomatic = $newRoom and ([Reason for Placement]="Customer is showing symptoms");
var bool customerPositive = $newRoom and ([Reason for Placement]="Customer is positive");
var bool noSymptoms = isNull([Last Date COVID-19 Test Completed]);
var bool noSymptomsDate = isNull([Date of First Symptoms]);
var date symptomsDate = if ($noSymptomsDate, ([Placement Date] + Days(10)), ([Date of First Symptoms] + Days(10)));
var bool noCovidDate = isNull([Last Date COVID-19 Test Completed]);
var date covidDate = if ($noCovidDate, ([Placement Date] + Days(10)), ([Last Date COVID-19 Test Completed] + Days(10)));
var bool fourteenDays = todate([Placement Date] + Days(14),
If ($positiveContact,$fourteenDays,
$customerSymtomatic, $symptomsDate, <<<Error expecting bool but found date
$customerPositive, $symptomsDate,
$customerReturned, $symptomsDate)
------------------------------
Del
------------------------------
var bool newPlacement = [Entrance Reason] = "New Placement";
var bool changedRoom = [Entrance Reason] = "Customer Changed Room";
var bool newRoom = $newPlacement or $changedRoom;
var bool customerReturned = $newRoom and ([Entrance Reason] = "Customer returned after Pre-Clearance departure");
var bool positiveContact = $newRoom and ([Reason for Placement]="Customer had contact with known positive");
var bool customerSymtomatic = $newRoom and ([Reason for Placement]="Customer is showing symptoms");
var bool customerPositive = $newRoom and ([Reason for Placement]="Customer is positive");
var bool noSymptoms = isNull([Last Date COVID-19 Test Completed]);
var bool noSymptomsDate = isNull([Date of First Symptoms]);
var date symptomsDate = if ($noSymptomsDate, ([Placement Date] + Days(10)), ([Date of First Symptoms] + Days(10)));
var bool noCovidDate = isNull([Last Date COVID-19 Test Completed]);
var date covidDate = if ($noCovidDate, ([Placement Date] + Days(10)), ([Last Date COVID-19 Test Completed] + Days(10)));
var bool fourteenDays = todate([Placement Date] + Days(14),
If ($positiveContact,$fourteenDays,
$customerSymtomatic, $symptomsDate, <<<Error expecting bool but found date
$customerPositive, $symptomsDate,
$customerReturned, $symptomsDate)
------------------------------
Del
------------------------------
- Typo on this line:
var bool fourteenDays = [Placement Date] + Days(14),Change the comma (in red) to a semi colon - so this:var bool fourteenDays = [Placement Date] + Days(14);
Please replace that line and try againNeil - var bool fourteenDays = todate([Placement Date] + Days(14); <Error expecting ")"
------------------------------
Del
------------------------------ - Missed that - change the line from:
var bool fourteenDays = ToDate([Placement Date] + Days(14);
to (add a parenthesis at the end before the semi colon)
var bool fourteenDays = ToDate([Placement Date] + Days(14));
- Del,Hang on for a second - I think I see a couple of typos..On May 26, 2020, at 11:42 AM, Neil Schneider <neil@team707.com> wrote:Missed that - change the line from:
var bool fourteenDays = ToDate([Placement Date] + Days(14);
to (add a parenthesis at the end before the semi colon)
var bool fourteenDays = ToDate([Placement Date] + Days(14));
- Del,In one of my transcriptions I messed up this line...
var bool fourteenDays = [Placement Date] + Days(14);
It should bevar date fourteenDays = [Placement Date] + Days(14);
fourteenDays is a date, not a boolean.Not sure what happened..NeilOn May 26, 2020, at 11:52 AM, Neil Schneider <neil@team707.com> wrote:Del,Hang on for a second - I think I see a couple of typos..On May 26, 2020, at 11:42 AM, Neil Schneider <neil@team707.com> wrote:Missed that - change the line from:var bool fourteenDays = ToDate([Placement Date] + Days(14);
to (add a parenthesis at the end before the semi colon)
var bool fourteenDays = ToDate([Placement Date] + Days(14));
- Del,Just checking - any luck with the formula?NeilOn May 26, 2020, at 11:57 AM, Neil Schneider <neil@team707.com> wrote:Del,In one of my transcriptions I messed up this line...
var bool fourteenDays = [Placement Date] + Days(14);
It should bevar date fourteenDays = [Placement Date] + Days(14);
fourteenDays is a date, not a boolean.Not sure what happened..NeilOn May 26, 2020, at 11:52 AM, Neil Schneider <neil@team707.com> wrote:Del,Hang on for a second - I think I see a couple of typos..On May 26, 2020, at 11:42 AM, Neil Schneider <neil@team707.com> wrote:Missed that - change the line from:var bool fourteenDays = ToDate([Placement Date] + Days(14);
to (add a parenthesis at the end before the semi colon)
var bool fourteenDays = ToDate([Placement Date] + Days(14));
- Hi Neil, running some tests today actually.
I think we need to change the formula to account for the "Initial Placement Date" for the Entrance Reason = Customer returned after preclearance departure
When Entrance Reason is Customer returned after preclearance departure , the Tentative Discharge Date should be 10 days after the "Initial Placement Date"(Minimum summary date I plugged in)
------------------------------
Ermias Bean
------------------------------ - And yes this is the same person. QB was acting finicky when I tried to log in
------------------------------
Ermias Bean
------------------------------ - Ermias,I got a message reject on my last send. If you didn't get it I was asking you to resend the formula as you have it now. I'm not sure I have a current version..
- Sure;
var bool newPlacement = [Entrance Reason] = "New Placement";
var bool changedRoom = [Entrance Reason] = "Customer changed room";
var bool newRoom = $newPlacement or $changedRoom;
var bool customerReturned = $newRoom and ([Entrance Reason] = "Customer returned after preclearance departure");
var bool positiveContact = $newRoom and ([Reason for Placement]="Customer had contact with known positive");
var bool customerSymtomatic = $newRoom and ([Reason for Placement]="Customer is showing symptoms");
var bool customerPositive = $newRoom and ([Reason for Placement]="Customer is positive");
var bool noSymptoms = IsNull([Last Date COVID-19 Test Completed]);
var bool noSymptomsDate = IsNull([Date of First Symptoms]);
var date symptomsDate = If ($noSymptomsDate, ([Placement Date] + Days(10)), ([Date of First Symptoms] + Days(10)));
var bool noCovidDate = IsNull([Last Date COVID-19 Test Completed]);
var date covidDate = If ($noCovidDate, ([Placement Date] + Days(10)), ([Last Date COVID-19 Test Completed] + Days(10)));
var date fourteenDays = [Placement Date] + Days(14);
If ( $positiveContact,$fourteenDays,
$customerSymtomatic, $symptomsDate,
$noSymptomsDate, $covidDate,
$customerPositive, $symptomsDate,
$noSymptomsDate , $covidDate,
$customerReturned, $symptomsDate)
------------------------------
Ermias Bean
------------------------------
