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. 

7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • John Halls
    • John_Halls
    • 7 days ago
    • Reported - view

    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
      • 6 days 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
    • 7 days 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
    • 6 days 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
    • 6 days 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
    • 6 days 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
    • 5 days ago
    • Reported - view

    Oh and thanks both of you for your help.

    Like 1
Like Follow
  • 5 days agoLast active
  • 7Replies
  • 56Views
  • 3 Following