0

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

8replies Oldest first
• Oldest first
• Popular
• 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

Like 1
• creative
• creative
• 1 mth ago
• Reported - view

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.

Like
• Fred
• Fred
• 1 mth ago
• Reported - view

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
end
``````

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

Like 1
• creative
• creative
• 1 mth ago
• Reported - view

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.

Like
• Fred
• Fred
• 1 mth ago
• Reported - view

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.

Like 1
• creative
• creative
• 1 mth ago
• Reported - view

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.

Like
• creative
• creative
• 1 mth ago
• Reported - view

Oh and thanks both of you for your help.

Like 1
• creative
• creative
• 1 mth ago
• Reported - view

Below you can see the end result of the solution(s). I'm still midway into sectioning off the main table into subs. I resurrected "vaccine brand" for the occasion. The check icon lets me know which pt to contact for subsequent vaccinations, and which ones they are requiring.

The "needs second dose" "1st Booster" "2nd Booster" are all conditional, while the vaccine brand doses (not shown) and the color "style" are using Switch Case statements, (clumsily) for some reason I can't count the case 1,2,3 etc. but instead 2,4,5 for it to display accurately. Likely because of the sequence I created them in the referenced table.

When I start feeling a bit more confident. I might attempt to script out the check mark columns into one column that will either state in the cell that they should be contacted for subsequent vaccinations, maybe to work in conjunction with the "status" column.

I did complete the vaccine brand table with subtables for lot numbers and a dose counter however since this particular db does not track "all" the patients we work with, (unless I get everyone on board), there will not be any accurate value in dose tracking. If or when I can get everyone on board it will be easier to track our dose, waste count and reconcile it with the states db.

Again thank you both for your help, I'm sure I'll need it again sometime.

Like 1
Like Follow