Select closest date and return associated number
I am knee deep in filemaker, but I have an issue I'm hoping for some help with. We have a certain number of orders that we know are going to occur each year, but we don't know exactly when we will be released to start on them and the delivery date varies so that it is not a constant lead time.
In order to find out if we are ahead or behind the curve, I'm trying to develop a system that automatically spreads them out evenly over the year and assigns "slots" for each theoretical due date. Then we can see which ones are unfilled and which ones are still open (so we can sound the alarm bell if needed). I'm starting with a table that calculates the "slots" based on evenly spaced dates. This much I can do, but the next part is what is tripping me up. In a separate table, I want to be able to enter in the actual due date of the order when we receive it, then have it compare that date with the theoretical date table to determine which "slot" this order would fit into. Ideally, if the due date changes and we update the form, I would like for the slot number to automatically update as well.
Any ideas? Thanks for your help!