1 2 3 4 Previous Next 46 Replies Latest reply on Apr 1, 2011 2:32 PM by Terri

    Auto-fill fields with calculation

    Terri

      Title

      Auto-fill fields with calculation

      Post

      Hi,

      I am trying to find a way for FM to auto fill dates based on the date I put in one "master" field. Hopefully the explanation below is enough to explain.

      Master Field - Jan 1

      Field 1 = 2 days later than master field or Monday January 3rd

      Field 2 = 4 days later than master field or Wednesday January 5th

      Field 3 = 7 days later than master field or Saturday January 8th **BUT** if this day lands on a Saturday it needs to subtract a day and land on Friday January 7th. (in other words, field 1, 2, 3, etc. can only equal a Mon-Fri business day).

      Field 4 = 8 days later than master field or Sunday January 9th **BUT** if this day lands on a Sunday it needs to add a day and land on Monday January 10th.

      Any ideas? Thanks!

        • 1. Re: Auto-fill fields with calculation
          philmodjunk

          All of these are simply a matter of adding a number of days to the date in the "master field" and then adjusting to land on a weekday. I'm interpreting your example to be that dates that evaluate to Saturday should return the previous Friday and dates that evaluate to Sunday should evaluate to the following Monday.

          Let ( d = MasterField + 2 ; d - ( dayofweek ( d ) = 7 /* Saturday */ ) + ( dayofweek ( d ) = 1 /* Sunday */ ) )

          That's for Field 1, Fields 2, 3 and 4 just add a different number of days.

          • 2. Re: Auto-fill fields with calculation
            Terri

            Thanks Phil,

            I work better by understanding what the formula means. Can you explain what the following part means?

             

            (d) = 7 /* Saturday*/

            Thanks!

            • 3. Re: Auto-fill fields with calculation
              philmodjunk

              That should be: - ( dayofweek ( d ) = 7 /* Saturday */ )

              Dayof week returns a number 1 through 7 that identifies the day of the week. If it returns 7 then, dayofweek ( d ) = 7 returns True which has the value of 1, so 1 day is subtracted from the computed date, d. Any other day of the week makes this False and the value 0 is then subtracted, leaving the date unchanged.

              /* Saturday */ is just an inline comment to help explain what this part of the calculation does. Anything enclosed between /* */ will be ignored by FileMaker.

              • 4. Re: Auto-fill fields with calculation
                Terri

                Thanks Phil!

                Silly question I'm sure, but do I actually use (d) or is that supposed to be my field name? To make this easier, I can give you my field names.

                Master field - drop date

                Field 1 - first routing

                Thanks!

                • 5. Re: Auto-fill fields with calculation
                  Terri

                  Scratch that Phil, I figured it out. It worked perfectly based on your instructions. You rock...again!!

                  • 6. Re: Auto-fill fields with calculation
                    Terri

                    Phil, I did have one other component that may cause a hiccup. I want to be able to override that calculated date if needed. Sometimes it will fall on a Holiday, or sometimes it will fall on a day that we just can't meet that deadline. Is there a way to format the field so that it does the calculation but yet I can still type over it if needed? Please say yes.

                    • 7. Re: Auto-fill fields with calculation
                      Terri

                      HI Phil,

                      I figured out the best way to override the date. You obviously can't with a calculation field. And the auto-enter won't work either because I need to be able to calculate based on a master field date that is individual to each record. So I figured out script that goes to each field I want and does a calculation to enter the appropriate date based off the master field date. But I need to modify that calculation slightly. I originally said if the date lands on Saturday, take it back to Friday, if the date lands on Sunday, move to Monday. However, what I really want it to do use only business days - no weekends. So if the master date is the 2/1/11 and I want the date in field one to be 10 days from that date, I need it to fall on 2/15/11.

                      Any ideas?

                      • 8. Re: Auto-fill fields with calculation
                        philmodjunk

                        And the auto-enter won't work either because I need to be able to calculate based on a master field date that is individual to each record.

                        I assume you want the dates to update any time the "masterfield" is modified? Otherwise, an auto-entered calculation could be used and you could override the date simply by typing a date directly into the field--as long as the "do not replace existing values..." option is selected for that field.

                        You don't really need a script for this as you could add an "overide" date field and modify the calculation to compute a date based on the masterfield only if the override date field is empty. If it is not, return the value of the override field.

                        "10 days" is an example with an interval different from any you have given previously (2, 4, 7 & 8 days). What's the total range of possible values here?

                        And it's possible to use a related table where you document which dates you are open to have a solution that accounts for both weekends and holiday closures. We use that method here when state law specifies a waiting period before we can pay a customer for their non-ferrous scrap metal so that we can tell them what day to come back for their cash.

                        • 9. Re: Auto-fill fields with calculation
                          Terri

                          I assume you want the dates to update any time the "masterfield" is modified?- CORRECT (unless the date is overriden)

                          You don't really need a script for this as you could add an "overide" date field and modify the calculation to compute a date based on the masterfield only if the override date field is empty. If it is not, return the value of the override field. - THIS SOUNDS RIGHT UP MY ALLEY, PLEASE EXPLAIN.

                          Total range of values BUSINESS DAYS ONLY - NO WEEKENDS:

                          Master date = base date for calculation - product conception

                          Field 1 = 24 days from master date

                          Field 2 = 26 days from master date

                          Field 3 = 27; field 4 = 28; field 5 = 31; field 6 = 33; field 7 = 37; field 8 = 38; field 9 = 40; field 10 = 47; field 11 = 52; field 12 = 60.

                          The number of days are not consecutive because certain steps in the process take longer than others (each field is a step in the process). The dates calculated (or the fields) are the "deadlines." The product goes from infancy (master date) all the way to end date (field 12 or the 60th day).

                          Adding another table is something I would like to avoid if at all possible. I see your point, but this needs to be basically a working calendar that people look at for the product they are working on. They use it as a schedule and really just need to see the appropriate dates.

                          I was working on a script that incorporated the Let funtion and the Case function. Let d=master date +24 (for field 1), then cases are if DayofWeek is 1-5, leave it alone; if DayofWeek is 6 add two days; if DayofWeek is 7 add one day. I know it is much more involved than that, with a script step needed for each field, but this is the basic idea.

                          • 10. Re: Auto-fill fields with calculation
                            philmodjunk

                            Adding another table is something I would like to avoid if at all possible. I see your point, but this needs to be basically a working calendar that people look at for the product they are working on. They use it as a schedule and really just need to see the appropriate dates.

                            I don't think you understand what I am suggesting. This table would be invisible to your users. It would automatically update and a single user would only need to interact with this table (as little as once a year if you know your holiday schedule for the entire year) to enter the dates of any week days for which you will be closed. All your users will see are deadlines that calculate automatically and include holidays in their calculation so that you don't have to "override" any dates manually.

                            Define your table, WorkingDates as follows:

                            DaysDate ( Date )
                            Status ( text uses an auto-enter calculation and relationship based on cLastWeek to copy status from same day last week if status in last week's record is "closed Always")
                            gCloseDate ( Global, Date )  used to specify that a date is a holiday
                            cDateOpen ( Calculation: If ( Status = "Open" ; DaysDate ; "" )
                            cLastWeek ( DaysDate - 7 )

                            A simple table or list layout can be used to manage this table. Status is formatted with a value list of three possible values: Open, Closed and Closed Always

                            A relationship: WorkingDates::DaysDate = LastWeeksDates::cLastWeek (LastWeeksDates is a table occurrence of WorkingDates) is used to automatically mark days of the week where you are always closed when you create a new record for the same day of the week. Dcoumenting the fact that you are closed on a given date due to a holiday is as simple as finding that record and selecting "closed". Scripts can use a global field such as gCloseDate to make this as simple as just entering the holiday's date in this field. Another script can generate a years worth of records for you very quickly.

                            With such scripted support, you can create 7 records for the current week so that you can designate weekend dates as closed always, then run a script to get records to the end of the current year, then enter each holiday date. This is a process that need be done only once a year by a single person.

                            With that table in place you can use this relationship to compute your dates:
                            YourTable::MasterDateField < WorkingDates::cDateOpen

                            Then this calculation will return a date 24 working days in the future from the date entered in the master date field:

                            GetAsDate ( GetValue ( List ( WorkingDates::cDateOpen ) ; 24 ) )

                            • 11. Re: Auto-fill fields with calculation
                              Terri

                              Thanks! I appreciate your thought into this avenue, but we don't only override for holidays/weekends. We also override for things such as vacation days (when the person who is charge of that step in the process is not going to be at work) or if there are system problems. These are really the more common reasons that holidays/weekends. And to be honest, sometimes the deadline may need to be overridden to land on a Saturday if we have had problems with the process and are coming down to the wire and doing overtime. So really, the main need is to automatically fill in the date at product development (master date), have all the fields fill in accordingly minus the weekends and then we can override as needed throughout the process.

                              With this in mind, was I on the right track with the script?

                              • 12. Re: Auto-fill fields with calculation
                                philmodjunk

                                Script or calculation can be used. The trouble with a script is that you have to be careful to use it consistently everytime a value changes where the calculations can often be set up to update automatically.

                                Try this expression instead of my last to handle an "override" date:

                                If (IsEmpty ( OverRideDate ) ; GetAsDate ( GetValue ( List ( WorkingDates::cDateOpen ) ; 24 ) ) ; OverRideDate )

                                • 13. Re: Auto-fill fields with calculation
                                  Terri

                                  Where am I putting this override date field? Is it separate from Field 1? I'm confused now. I'm afraid the simplicity of this solution got lost in translation.

                                  I agree, I would love to use a calculation rather than a script - it is that darn override that I'm stumped by. Let's start over with that in mind. I need one field to either show the calculation or the override. Is that possible?

                                  I am so sorry if you are getting frustrated with me.

                                  • 14. Re: Auto-fill fields with calculation
                                    philmodjunk

                                    I'm not at all frustrated. This is a challenging problem and there are other options that work. I prefer this one as it's a bit more flexible to work from a workingDays table to handle most of the weekend and holiday issues. It's also good for handling last minute changes. (We met our milestones and the boss is throwing a party on Friday, better mark that day as "closed" so that everyone's deadlines will extend an extra day...)

                                    I've seen calculations that would skip the weekends for you and they aren't trivial.

                                    Simply define OverRideDate as another date field in the same table as your deadline dates and place it on your layout somewhere where you can edit it as needed. If it's empty, the dates will calculate from the WorkingDays table. If it's not, the date in this field will become the deadline. Since you have multiple deadlines, you might need one such field per deadline.

                                    It's also possible to set up these deadlines as separate records in a related table. The calculations work out much the same, but now it's easier to work with the overrides and your overall system becomes more flexible as you can now add more deadlines simply by adding more records where your current system would require adding more fields and calculations. Thus, a change in business practices can be accomodated through data - entry instead of a data base redesign.

                                    It's also possible to set up a more sophisticated WorkingDays schedule that includes availability data for each person on the project--such as whether they are on holiday.

                                    I'm not recommending you jump into either of these last two suggestions immediately. I'm just planting the seed for future enhancements you may want to consider.

                                    1 2 3 4 Previous Next