Calendar with user-specific computed dates

[Nislin]

Hey everyone,

I’m building an app for young parents who’s baby is about to arrive our has just arrived and one of the features I want to offer is a calendar that would show important dates for various mom and child’s test, doc’s visits, vaccinations and so on.

So far I have two “key” dates - date of conception and date of child birth and I have about 20 “important dates” that should be computed based on one of those dates (I also have offset period for each date if that matters). Ofcourse each user will provide their own “key dates” (in user profile settings during initial onboarding) and right now I’m struggling to create a table that would enable me do that.

As I understand date of conception and date of birth should be in Users table as columns (along with name, email, d.o.b, etc.) but how should the “Events” table look like so that every user can have a calendar with dates recommended particularly for them?

[Nicolas_Joseph]

Hi @Nislin

First of all, congratulations on your baby. And welcome!

You perfectly describe the expectations and the way to get to the point (Users table to store unique dates for each user, Key Dates to make it dynamic and easy to maintain…).
So, here’s the last steps to get to the result:

1. Add two columns in the Users table to store Date of conception (DoC) and Date of birth (DoB) data (as you mentioned)

2. Create a Key dates table with - for the moment - at least 3 columns:

   • Name of the event
   • From which helps you define if the starting point is DoC or DoB
   • Duration to know how many days needs to be added to the From date
   

3. Add a Query column to retrieve the Active User:
   

   

4. Retrieve the DoC and DoB data for this specific user thanks to 2 new Lookup columns. Here’s the set up of DoC (just duplicate it and change DoC to DoB for the second one…):
   

   

5. Create two new Math columns to calculate targeted dates. This will be DoC + duration and DoB + duration in this walkthrough:
   

   

6. Lastly, find which is the correct date to display using a If → Then → Else column (DoC or DoB):
   

   

You should obtain something like this. Dates for each event is related to the values available in the active user profile row so everything is totally dynamic here:

Originaly, I created the If → Then → Else column prior to the 2 Math columns to optimize the logic. However, when I tried this, the result of the calculation was not relevant, probably due to casting operations. Because date calculations could become very tricky and it’s only adding one more column here, I totally assume this choice

Let us know if this works as expected!

[ThinhDinh]

That’s a clever suggestion!

[Nislin]

Hey @Nicolas_Joseph , that is exactly what I was looking for! Huge thanks to you for such a detailed instruction!

I spent several hours trying to find a solution on YouTube, Chatgpt and Glide’s AI assistance and just got the result by following your guide, thank you once again, sir!