3 Replies Latest reply on Jun 6, 2014 7:30 AM by davidgrooms

    Select closest date and return associated number



      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!

        • 1. Re: Select closest date and return associated number

               In a related question, when I'm calculating the theoretical slot dates, we know that the due date will always be on a friday or a Tuesday.  If I divide the number of days in a year by the number of expected orders, can I get the date to round up to the closest Friday or Tuesday date?  


          • 2. Re: Select closest date and return associated number

                 Let ( D = put your calculation for the next due date here ;
                         Case ( DayOfWeek ( D ) = 1 ; D + 2 ; //Sunday becomes next Tuesday subtract 2 if you want it to be preceding Friday
                                    DayOfWeek ( D ) = 2 ; D + 1 ; // Monday becomes next Tuesday
                                    DayOfWeek ( D ) = 3 ; D ; // Tuesday stays as Tuesday
                                    DayOfWeek ( D ) = 4 ; D - 1 // Wednesday becomes preceding Tuesday
                                    DayOfWeek ( D ) = 5 ; D + 1 // Thursday becomes next Friday
                                    DayOfWeek ( D ) = 6 ; D // Friday stays as Friday
                                    DayOfWeek ( D ) = 7 ; D - 1 // Saturday becomes preceding Friday

                 When matching the actual due date to your theoretical due date, is the date the only criteria or do you also need to match by some kind of customer ID?

                 I'm thinking that you could pull up all unfilled order records for a given customer--either in a portal or a found set and then sort them by due date so that the record with the oldest due date is the first record and this would be the record you want.

            • 3. Re: Select closest date and return associated number

                   Thanks Phil, that did the trick!!