Process Capacity setup

Josh_Cooper · ‎May 21, 2020

Airtable Masters,

I have set up related tables with rollups and lookups in the past, but this problem has me stumped. I am trying to determine a process capacity by looking at each order and determining the number of units due between the current time and the due date of the order. I have read several post where similar questions are asked, but no answers given. Is this even possible with Airtable?

Thanks in advance for any ideas!

Justin_Barrett · ‎May 21, 2020

@Mohamed_Swellam The issue here isn’t one of pure date difference. The way I read it, @Josh_Cooper is trying to determine whether or not an order can be filled based on a combination of time and a certain production rate. In other words, with an order of X units, due in Y days, and given that only Z units can be made per day, can the full order be completed by the deadline. Is that correct?

If so, I suggest adding a field to your [Items] table that is named something like {Max Per Day}, and which records the highest number of that item that can be made in a single day.

In the order table, I’m assuming there’s a link to the item, which you can use to roll up that {Max Per Day} value for the item, using SUM(values) to bring it in as a number. If your main order table contains one item per order record, I’m guessing that you’ll also have the item quantity and due date in that same record. If that’s the case, then this formula will let you know if the item’s production capacity has been exceeded or not:

IF(Quantity / DATETIME_DIFF(Deadline, NOW(), "days") > {Max Per Day}, "❌", "✅")

See Solution in Thread

Mohamed_Swella1 · ‎May 21, 2020

Hi @Josh_Cooper,

Can you give us more information or share a screenshot so we can help you better?

The formula for determining the duration between the current time and the due date of the order would be DateTime_Diff(NOW(), {Due Date}, 'hours')

BR,
Mo

Justin_Barrett · ‎May 21, 2020

@Mohamed_Swellam The issue here isn’t one of pure date difference. The way I read it, @Josh_Cooper is trying to determine whether or not an order can be filled based on a combination of time and a certain production rate. In other words, with an order of X units, due in Y days, and given that only Z units can be made per day, can the full order be completed by the deadline. Is that correct?

If so, I suggest adding a field to your [Items] table that is named something like {Max Per Day}, and which records the highest number of that item that can be made in a single day.

In the order table, I’m assuming there’s a link to the item, which you can use to roll up that {Max Per Day} value for the item, using SUM(values) to bring it in as a number. If your main order table contains one item per order record, I’m guessing that you’ll also have the item quantity and due date in that same record. If that’s the case, then this formula will let you know if the item’s production capacity has been exceeded or not:

IF(Quantity / DATETIME_DIFF(Deadline, NOW(), "days") > {Max Per Day}, "❌", "✅")

Josh_Cooper · ‎May 21, 2020

Thanks Justin and Mohamed for the quick replies!

Justin has the right Idea. I will have to do some table restructuring, which I was honestly trying to avoid, but I think that will work.

Thanks for the help!!