Date field evaluation and table record population

[Darrin_Hawe]

(novel follows, better too much info than not enough I figure)

As part of an Airtable project/portfolio management system proof-of-concept, I have developed different tables which include (but aren’t limited to) the following information:

[OCIO Portolio] - Holds information on Projects
{Project Name} - Primary field
{Service Catalog Area} - Links to Service catalog Area table for projects.
{Phase} - Currently links to Phase table, may change to single select later

[Service Catalog Area] - Project Service Catalog Areas
{Name} - Primary Field
{Report} - Name of person responsible for reporting progress in specific Service Catalog Area

[WSR Entries] - Junction table containing project updates for Monday project updates
{WSR Entry ID} - Concatenated Primary field
{Project Name} - Linked to OCIO Portfolio table
{Date} - Update date currently linked to WSR Date (would like to change see below)
{WSR Date} - Calculated date field that calculates the next Monday based on {Date}
{Service Area} - Linked to [Service Catalog Area] table
{Report} - Lookup of [Service Catalog Area] {Report}

Two questions:

1. In the [WSR Entries] table, two grid views have been built - ‘Joe 9/2 Updates’ and ‘Sam 9/2 Updates’. There are two formula fields in the [WSR Entries] table, one called {Joe Display?} and the other called {Sam Display?} The {Joe Display} field is marked ‘true’ if (([WSR Entries]{Report} = Joe) AND ({Date} = “9/2/2019” OR {Date} = “8/26/2019”)). The filter for the ‘Joe 9/2 Updates’ is then set to display records that are marked ‘true’ in the {Joe Display} field. Same mechanism for the Sam view. Would have been great to use a filter, but Airtable filters can’t use both ANDs/ORs. This works as intended until I try to switch from using {Date} to {WSR Date}. Can Airtable not properly evaluate a date generated in a formula field?

2. While the date question is interesting and a nice to have, my entire proof of concept is stuck with the fact that I can’t find a way to generate entries in the [WSR Entries] table based on the [OCIO Portfolio] table. Every Monday, all projects with {Phase} = “In Progress” need to be somehow populated into the WSR entries table so entries can be updated. Cut/paste is not a good option (previous problems with previous systems in doing that). Is that kind of functionality not possible with Airtable?

I’m completely open to the fact that a different Airtable table design may work better, and if I am missing something I’m all ears.

[Bill_French]

@Darrin_Hawe,

There are far more skilled experts on this topic than I, but my gut tells me Roll Up may be what you’re looking for.

Airtable - Support

Guide to formula, lookup, rollup, and other computed fields

Computed fields (columns) are special field types where the values in the field are computed by Airtable automatically, rather than directly edited by users. These can be fields that allow you to c...

[Justin_Barrett]

Darrin_Hawe:

This works as intended until I try to switch from using {Date} to {WSR Date}. Can Airtable not properly evaluate a date generated in a formula field?

It can, as long as the end result is still a date. If you’ve used DATETIME_FORMAT to format the date in {WSR Date} in a specific way, then the end result is a string of text, not a date. If you’re not formatting the date, what is the formula you’re using? Something else might be amiss that’s changing the output type.

Regarding your second question, you indicated that you were open to considering a redesign of the base, so you might consider combining the [OCIO Portfolio] table with the [WSR Entries] table. Correct me if I’m reading your description incorrectly, but it feels like the items that you want to appear in [WSR Entries] are merely a subset of the items in [OCIO Portfolio]. [OCIO Portfolio] appears to have a master list of projects, while [WSR Entries] is where you start digging into projects in detail, but it’s ultimately still about the same projects. With that in mind, putting all of the project data into a single table makes more sense to me.

You would then use views to control what you see about each project, with a view targeting those that are marked as “In Progress” to see just their info, one where you might just put in rough details about new projects, etc.

[Darrin_Hawe]

@Justin_Barrett @Bill.French Thank you for you replies, I appreciate it.

@Justin_Barrett the formula that I use for calculating the next Monday is as follows:

IF(1-WEEKDAY({Date})<0,DATEADD({Date},1-(WEEKDAY({Date}))+7,‘days’),DATEADD({Date},1-(WEEKDAY({Date})),‘days’))

So if I am understanding your statement about dates, the formula result should be stored as a date.

Also, regarding the batch add I was looking for, I now have an automated process in Integromat that does that. The only thing I need to do to finish that part of the proof of concept is to find a way to add a record to the WSR entries if the [OCIO Portfolio] {Phase} is changed to ‘in progress’.

[Darrin_Hawe]

So I solved my issue number two with Integromat. I have two scenarios. One scenario watches the main portfolio table for change in phase to “in progress” and automatically adds a record to the junction table when the phase changes. The second scenario is a batch process that runs once a week early Monday morning, and automatically adds portfolio entries to the junction table that have the status of “in progress.”

Other than having to monitor how many operations I’m using monthly on Integromat, I was able to build a fully functional PoC that demonstrates how Airtable/Integromat can be used to improve our process.

I think I have removed the dependency on the calculated date field as I am using filters to accomplish the same thing. Doable given the IF containing both an AND and OR is no longer necessary.

[Justin_Barrett]

Sorry that I didn’t get back to you last week, but I was swamped with other work. I’m glad to see that you were able to get a working system, though!