We have a submittal (construction company) log setup in airtable that overall we have working quite well. Submittals as one line per submittal, and each revision is it’s own line. The key field in the table is a formula that takes the submittal # + the revision # to make a unique ID. For example, we may submit Submittal 07 Rev0 for approval and it is rejected, so then we re-submit and have a new line that is Submittal 07 Rev1. This would look like this
Submittal ID Submittal No. Rev No.
SUB_007_00 007 00
SUB_007_01 007 01
Each submittal gets an “open” status when it gets sent to the client and then when it is returned, it gets updated as either “Approved”, “Revise and Resubmit”, “Approved As Noted”, etc.
What I am trying to do is create a view to only see “Revise and Resubmit” status for example, BUT I want to exclude all the lines that were superseded by a revision so that I don’t have a cluttered list.
If I submit SUB_010_00 and it is sent back as revise and re-submit, it should show in my filtered view. However, if I have submitted SUB_011_00 and it was revise and resubmit, and I have already done that and sent in SUB_011_01 and it was also revise and resubmit and I then sent SUB_011_02 which was approved, then none of those should show because the approved 02 revision supersedes 00 and 01 and thus the item is closed. Likewise if 00 was revise and resubmit and 01 was revise and resubmit but I had not yet made rev 02, then only 01 should show in the view because 01 superseded the 00 line.
Any clever way to do this? It seems that there needs to be a way to evaluate all the rows that have the same submittal number and then flag the highest rev number as filter based on that…but I haven’t figured that out yet.
It is important that each revision is it’s own line as we track duration/status/comments/etc for each revision and attach the outgoing and incoming submittals to the line. We have a similar issue with an employee training tracking log where we only want to see the most recent iteration of a training that an employee did (IE if they have taken the training 4 times and it is good for a year, the first 3 are likely all expired and we don’t need to see them, only the most recent training that would be coming up for expiration because it supersedes all others of the same type).
Thanks,
