9 Replies Latest reply on Apr 6, 2011 9:43 AM by Terri

    Auto-fill Dates

    Terri

      Title

      Auto-fill Dates

      Post

      See attached. I need this layout to calculate the date for the steps which are XX number of work days BEFORE the main date (so I'm back dating, not looking at the future as the layout currently shows). It computes the future dates correctly, but when I changed the relationship to < it doesn't compute correctly. Any ideas?

      http://www.4shared.com/file/bNL7nEiB/FutureWorkDays_from_Schedule_C.html

        • 1. Re: Auto-fill Dates
          philmodjunk

          You need this relationship:

          WorkingDates::Status = Schedule::cOpenKey AND
          WorkingDates::cOpen < Schedule::Main_Date

          While you have the Edit Relationship dialog open, click the sort button for WorkingDates and specify sorting the records by DaysDate in descending order.

          PS. I worked out the details by placing a portal to Working dates on the layout and making changes to the relationship until the portal listed the dates that the List function needs inorder for these calculations to work.

          • 2. Re: Auto-fill Dates
            Terri

            Forgive my ignorance...what is the cOpenKey? Obviously I have to add it to my schedule table, but what is it exactly?

            • 3. Re: Auto-fill Dates
              philmodjunk

              Sorry, forgot to add that explanation.

              cOpenKey is a calculation field with this expression:

              "Open"

              and it is set to return text as its return type. It serves as a "filter" to omit closed dates from the dates returned by the List function.

              • 4. Re: Auto-fill Dates
                Terri

                You rock...as usual.

                • 5. Re: Auto-fill Dates
                  Terri

                  Hi Phil, I'm sure this is a long shot, but it doesn't hurt to ask. The file I posted here and the changes you suggested worked perfectly on that file. But when I use it on my actual live database the date for Step 1 (etc.) disappears when I change that field to the calculation. I'm hoping maybe you have a quick idea as to why a calculation field will delete the data. I've compared the two files relentlessly for the last 3 hours. They look identical. Am I missing something?

                  • 6. Re: Auto-fill Dates
                    philmodjunk

                    Calculation fields don't delete data, they only compute values using data drawn from other fields. The date for step one is stored in a different field, (Main_Date on the demo) correct?

                    • 7. Re: Auto-fill Dates
                      Terri

                      You're right. I explained that incorrectly. My file currently has a date that is manually entered. That is the field I am turning into the calculation that uses the main date as the starting date and figures 60 working days prior to that. So when I say it disappeared, what I should have said is it comes up blank.

                      Oddly enough, when I copy and paste the calculation from the demo file to the live file it works correctly. I even put the calculation side by side and I see no difference whatsoever. I have no idea what is going on. If you have any ideas, great. If not, I'm going to chalk it up to the computer gremlins that taunt me every day.

                      • 8. Re: Auto-fill Dates
                        philmodjunk

                        Can't tell from here. Before you indicated that pasting the calculation from the demo file worked, I was going to suggest that maybe the related table of working days didn't have the needed records for the List function to list.

                        • 9. Re: Auto-fill Dates
                          Terri

                          I'm going to share my blunder just in case someone else is using this post to help their situation. "Hi, my name is Terri, and I'm an idiot."

                          You were right. The record I was looking at had calculated dates that ended up being in late 2010. My working days list is only for 2011. Once I changed the main date to the future (and the calculated fields land in 2011)...wallah!!!

                          I can see you shake your head in wonder that I am even in a role that would require such use of a computer!

                          Thanks again.