Allocating expiring products to dated inventory requirements (Supply chain planning)

[Kristinbell]

I'm working on a workflow to allocate product that is expiring to requirements that are on specific days. I've attached a spreadsheet of how the logic works. Each week you have a new allocation you need to serve, and a few batches of product with associated unusable dates. You can see in the Excel example, the Allocation quantity of 50 is served in full by the first batch, leaving the other two batches in a bucket of unusable. The second week, there's not enough product, so there's leftover allocation to be noted. I've attached a workflow with dummy data of how far we've gotten. We can make the logic work for the first line, but we're struggling to make it work for the subsequent lines. Also, this needs to be scaled up to many product/locations/timeframes, but only allocated for matching product/locations, taking into account the time frames. Any ideas?

[OTrieger]

Hello @Kristinbell 

If you manage to get the logic done for the first line then why don't you use a Batch Macro?
And then each time the batch macro will handle 1 line and will union all the lines at the Batch Macro output. Have you tried it?

[CoG · Accepted solution]

I've found, in cases like this, that iterative macros work better. I'm not actually sure how to get a batch macro to accomplish these requirements. Here is a sample with various outputs depending on your needs:

Hope this helps and Happy Solving!

[KGT]

Andrew's macro above should get you what you need.

As another resource, if you want to look at a formed example of assigning supply to demand, you can also check out the Sample workflow under "Help > Sample Workflows > Use scripting and automation tools > Build a Macro > Assign supply to demand". 

[Kristinbell]

This is amazing, thank you so much for your help!