Expression to filter values based on today's date.

[Dubams]

I want to filter table 1, based on date conditions from table 2.

In other words,  if the end date < today's date and start date is >= today's date, task/room cell should display "not available"

I have tried using filters and other expressions but keep getting, "you cannot compare list with date."

If I were using google sheets, I'd just use an expression like the following, then drag down.

=IF(AND('Room Booking'!F2>today(), 'Room Booking'!E2<=today()), "N/A", "Available").

I want to write an expression on appsheet that would do something similar to the google sheet expression above.

Thank you.

[dbaum]

In your expression you're likely referencing a a table's entire column of values across rows (which is a list) rather than a column's value from a single row.

If you can't figure it out, share your expression and table/column structure and someone likely can provide targeted advice.

[Dubams]

I believe that's the issue. I'm finding it difficult to dynamically reference a particular cell on a different table from the table of interest.

That is, I would like to check a "room booking" table whether the end date is after today, then use that to slice the "room" table, displaying only available rooms.

Here's what I had tried...

if(and(Room Booking[End]>today(),"N/A","Available"))

[dbaum]

---

@Dubams wrote:

Room Booking[End]>today()

---

This is the portion that triggers the error "you cannot compare list with date."

Room Booking[End] references the Room Booking table's entire list of values in the End column. Essentially, you need to modify that part of the expression to reference just the relevant row's value from its End column.

---

@Dubams wrote:

If you can't figure it out, share your expression and table/column structure and someone likely can provide targeted advice.

---

[Dubams]

Thanks, I was able to fix it using lookup. 

Cheers.