How to display current production Queue length in 'days' automatically

[RebeccaofSunnyb]

Still learning about Airtable, so I may be missing a built-in solution, but in case I’m not here’s my problem I hope someone can help me with:

I’m helping a small business that’s switched to making sewn masks (originally a sewing school), so I’ve built a base that tracks {Orders}, {Inventory}, {Customers}, and {Mask Styles}.

A common question from customers is when their mask will be done and ready for pickup. It takes about 1 hour to make each mask and the work days are minimum 8 hours every day (even weekends). Based on the number of unfinished masks currently in the queue, I want the number of days and/or the date everything currently in the queue would be finished to be calculated & displayed automatically.

The {Inventory} table is Kanban-like, so among others has a Single select field showing what {Stage} of completion the mask is at: ‘Planning’, ‘Fabric Chosen/Cut’, ‘Sewing’, and ‘Done!’.
I just want to count masks that are not ‘Done!’ - let’s call it “totalWIP” - divide by 8 to get the length of the queue in days, and/or use the DATEADD(TODAY(), “totalWIP” + 1, ‘days’) to give me the actual date that the business can give any new customers as the earliest they’ll get their masks (extra +1 in there is a buffer day to help account for any trouble getting supplies, elastic is getting low & more expensive/harder to find…).

I’m able to filter out the ‘Done!’ records pretty easily in the Main Grid View, but counting the total number of records left behind & the ability to use that variable in a function that displays in the same space as the Blocks do is eluding me. I’m relearning JavaScript so that I can take a crack at the ScriptBlock, but guessing it’ll take some time & wanted to tap into the Airtable community ‘hivemind’ as well.

Thanks for your time!

[Mohamed_Swella1]

Hi @RebeccaofSunnybrook1,

There are several ways to do so. First thing that comes to mind is having a rollup field in another table that counts the Done items and one that counts the All items then a formula to subtract them and get the Remaining.

BR,
Mo

[RebeccaofSunnyb · Accepted solution]

I’ve actually got a formula field in my {Inventory} that assigns a “0” to “Done!” orders and a “1” to everything else & already have a rollup field in my {Order} table that shows those totals, so getting the data isn’t my problem, it’s where to place the final formula that takes the rollup total & does the math as mentioned above…

But good news! I think I found the solution - while exploring more of the Blocks, I found the “Summary” block, which while it doesn’t support formulas DOES show the total number of masks in-progress like I want, so I think I’ll just do what @Sergie_Magdalin did ( Formulas for Blocks ) & create another formula field in my table for a 2nd Summary block to add & display for the days~