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.
Forgive my ignorance...what is the cOpenKey? Obviously I have to add it to my schedule table, but what is it exactly?
Sorry, forgot to add that explanation.
cOpenKey is a calculation field with this expression:
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.
You rock...as usual.
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?
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?
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.
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.
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!