Use Date value from one table to compare to dates in another table

Ask for Help
1

Stuck with this one…

Imagine I have two tables:

Table A:

  • Has just a single row, with multiple columns
  • One of those columns is a date (update: it’s actually an if-then-else column, operating on date columns. An important point I initially overlooked…)

Table B:

  • Has multiple rows, and multiple columns
  • One of those columns is a date

What I want to do is use the Table A date column in Table B, in an if-then-else column to compare it to the dates in Table B. Problem is, when I bring it in as either a Single Value or Lookup via a (single) relation, Glide no longer thinks it’s a date, so I can’t do date-type comparisons (after, before, etc).

Screen Shot 2021-05-05 at 3.01.22 PM
Screen Shot 2021-05-05 at 3.01.22 PM2006×1088 269 KB

Anyone have an idea how I can work around that?

… actually it just occurred to me that I can probably convert all the dates in both tables to integers, and then do a direct numerical comparison - but that feels really clunky :woozy_face:

2

Hello
Add a math column to convert a lookup column to a date

image
image655×622 27.6 KB

2 Likes
3

Thanks!
Off to get my COVID jab now :syringe:, but will give that a try later

4

That’s the one I reckon

5

Ah, my solution, it doesn’t work
I did some tests and if the source of your lookup is an if column (for example) it doesn’t work.
For the moment, well no solution.

EDIT :
uh checkmate
I looked for several solutions but nothing.
Obviously the “before, after, …” conditions only work with Date or Date source fields.
Maybe after your :syringe: , you will have a brilliant idea.

6

Are you using the SV > latest?

7

SV->First.
Latest, etc aren’t options. I think the problem is (as @Manu.n pointed out), that the source column is an if-then-else (operating on date columns). Even though it gives a date as a result, it seems that Glide no longer treats it as a date when I pull it into my second table.

I’m going to have to fiddle with this, and think about it a bit more.

Completely open to any bright ideas :slight_smile:

8

I found a solution, which was essentially this:

It’s actually not that ugly. It will work.

2 Likes
9

I’m stuck on this same issue - dates that are looked up not being considered as dates in an if-then column for comparison by before or after. Another layer in my case is that the looked-up dates are then rolled up to find the earliest or latest. Since this thread is several years old, I thought I’d check if you have any new insights here. Or if your method hasn’t changed, what’s the “not that ugly” way to convert dates to integers to compare?

10

For dates only:

YEAR(D)*10^4+MONTH(D)*10^2+DAY(D)

For datetime (without seconds):

YEAR(D)*10^8+MONTH(D)*10^6+DAY(D)*10^4+HOUR(D)*10^2+MINUTE(D)

With D being the datetime object in question.

11

Thanks! I’ll take it from your reply that converting to numbers is still the best way to work around dates not being treated as dates in if-then columns.

1 Like
12

Yes, I think so, at least for now.