Using if statements and date calculations to display yes or no based on variable.
I'm new to this program so forgive me, I'm trying to implement an idea rapidly without understanding how complex...or not complex I am going with my attempt at functions... and failing.
This is a basic vaccine calculator. If an entry received his/her vaccine on a date and has 21 days before returning for the second. On the 21st day a cell will display a green check mark icon which lets us know that they are eligible for us to call with a reminder. <--- simple example.
There are several vaccine types with different numbers of days for them to return. Also, after the second vaccine they will have 5 months before they return, and after the 3rd if they are over 50 they'll have 4 months before they return for the final vaccination.
Here is an ugly sketch,
(dose 1 date) if Vaccine type = Pfizer and today = (dose 1 date +21)
then eligibility = true
else if
(dose 1 date) if Vaccine type = Moderna and today = (dose 1 date +28)
then eligibility = true
else if
//maybe one or two more vaccine types i.e. Janssen or a couple of foreign brands//
(dose 2 date) If today (dose 2 date >/= 5mo)
then eligibility = true
else if
(dose 3 date) and age >49 and today(dose 3 date >/= 4mo)
then eligibility = true
End
If eligibility=true, display a green checkmark icon in the corresponding cell.
I'm gonna keep looking at videos and manuals and hammering away at it until something works or breaks... but if someone could polish the above turd I'd greatly appreciate it.
-
Hi
I've not fleshed this out but I would use a lookup table and check against it for patients eligible for a further dose.
Create a script to run through the patients and create a join record when this happens, to join the patient to the lookup. The join table can hold additional fields to track actions going forward.
Regards John
-
John Halls
Thanks, the db is already built into one table (and a vestigial "vaccine" table which is not being used...effectively). I'd need to do some table revisions to go this route, which I am not objecting to. I'll work on a second db that will use this kind of formulation which will probably work a little better for me. Before I do though I'm going to check out the script below.
-
-
A handy date command is days(date1,date2). This will return a number that a normal human can understand. If you create a formula field called DaysSinceLastVax you could write something like:
days(vaxDate,today())And it will return a simple number.
Another command to thing about is the switch command. Instead of doing a long line of nested if statements, you can switch on vaccine name so it could look something like:
switch Vaccines do case "Pfzier" if DaysSinceLastVax > 21 then "Jab Time" end case "Moderna" if DaysSinceLastVax > 28 then "Jab Time" end endNow this all depends on how you track your vaccine data. Do you use a simple choice field? Or do you use a dynamic choice field?
I've made a test db that does this so if you have trouble with it then I can upload it.
-
Sadly, that vestigial table for vaccines that I made "was" a simple choice field but I opted for a text field because it was easier to "color code" the brand per cell for easier and more rapid identification. Plus I was trying to figure out lot numbers and expiration dates for each which I felt was beginning to get a bit unwieldy at the time. One brand might have 20 lot numbers during the course of the year, and then they might change their name, i.e., Pfizer to Comirnaty. However, for the sake of new found functionality I am willing to exercise that atrophied vestigial table if it's to my advantage. I'll give this a try I'll get back with both you and John Halls with my anticipated tales of success.
-
I was thinking about a similar situation as I was creating my test DB. I wondered if you might need to track different vaccines by manufacturer. Then of course even within the same product there are going to be many lot numbers.
One way would be to create a Manufacturer table, then a Product subtable, then a LotNum subtable of Product.
-
I think I'll work on breaking the main table up into subtables, soon. Right now I worked it out...kind of, by putting a number of conditional rules in 3 different field. It's a soft solution until I start squaring myself up to something a bit more solid. but it does give me a starting point on the scripting part, the days('field'', today()) was helpful. figuring out != null on the blank cells etc. For today... it's ok. I'll start working on writing the "switch" functions just to see how they work. Then I'll start breaking up the fat clumsy table with the conditionals.
-
Oh and thanks both of you for your help.