I have Database A, which tracks Action Items or scheduled meetings, etc. Basically a to-do list with a "do date" date field and, among other things, a property for priority, and one of those priorities is "scheduled." Something like this:
| ActionItem | Priority | Do Date |
|---|---|---|
| AI 1 | High | 5/9/2022 |
| AI 2 | Scheduled | 5/9/2022 |
| AI 3 | Scheduled | 5/9/2022 |
| AI 4 | Low | 5/10/2022 |
I also have Database B, which includes details about each date that are relevant to planning my day, like will it be WFH or WFO, did I sleep well or not, blah blah blah...
Like this:
| Date | Type of Day | Sleep |
|---|---|---|
| 5/9/2022 | WFH | Good |
| 5/10/2022 | WFO | Bad |
| 5/11/2022 | WFO | Bad |
I would like to add a column in Database B that relates to Database A, specifically whether an Action Item has "Scheduled" in PRIORITY, but only if it matches the Date field in Database B for that row. Basically, a new column that is "Scheduled Action Item(s) for This Day."
| Date | Type of Day | Sleep | Scheduled Action Item(s) |
|---|---|---|---|
| 5/8/2022 | WFH | Good | NULL |
| 5/9/2022 | WFO | Bad | Action Item 2, Action Item 3 |
| 5/10/2022 | WFO | Bad | NULL |
At the moment, when I relate the two databases, ALL of the Action Items show up in the Relations column and there are a LOT across ALL of the days. So I can't easily find the scheduled ones. I've also created a formula field to check a box if the Action Item is scheduled, and then an in-database relationship to return a new property column with the text in the Action Item property if it's a scheduled Action Item. But I'm not really sure how that helps me find a solution to my original problem...
I'm also thinking it might be ideal if the new column, Scheduled Action Item(s) for This Day, in Database B, could autofill from Database A when the Action Item is marked as Scheduled???
Help much appreciated!! Thank you!
