I believe that we discussed this in a previous thread.
If we modify your relationship:
Where cDateList is a field with a return separated list of dates, we can omit all such dates by omitting them from this list of dates in the cDateList field.
There are several ways to produce that list. One way is to have this relationship:
Employee::__pkEmployeeID = DatesGraded::_fkEmployeeID
with a field, DateGraded in DatesGraded to record the date and with one such record for every date from DateStart to DateEnd--something that can be generated from a script, but with the dates an employee is absent deleted from the table or excluded from the relationship.
Then cDateList can be a calculation field set to return text defined as:
List ( DatesGraded::DateGraded )
You are correct, sir! We have spoken about this database before but not this specific functionality.
Once again, my novice knowledge is going to show...
I have two layouts mentioned above: "Rank" and "Scorecard". Currently, in these layouts, if I enter the global start and end dates, it returns the Metrics within this range. If I wanted to see John Doe's Metrics for the month of January, I'd enter the start and end date and it would provide me with only the Metrics for that time period. I believe the method you mentioned would remove this functionality. Am I mistaken on this? Whould I need to change the layouts to make this functional again?
The second part of my confusion is the need to confirm that I understand correctly with the addition of a few questions. The steps to implement what you suggest would be:
1. Create a new table "DatesGraded"
2. In DatesGraded, create a field for employee ID and DateGraded
3. Relate it to Employees by Employees::EmployeeID=DatesGraded::EmployeeID
4. Create a field in Employees as cDateList defined as a calculation =List(DatesGraded::DateGraded)
5. Create a script upon action, it deletes dates from Start date to End date in "DatesGraded".
My first question on this is how do all dates get populated in the table "DatesGraded"? My next question would be how do I make my layouts work with this? As mentioned above, I still would need to be able to enter a start and end date and only see that period of Metrics.
Starting from step 5, you would want a script that CREATES records in DatesGraded from date start to date end.
But then to modify the date range to omit specific dates, you would delete those specific records for the given employee.
I had envisioned a fixed interval for date start and date end. We'll need to modify the relationships, not the layouts, to adjust for flexible date ranges:
Employee::__pkEmployeeID = DatesGraded::_fkEmployeeID AND
A portal to DatesGraded could then include a delete portal row button to delete dates you want to omit from the date range.
A looping script can use the start and end dates to create those needed for a given employee.
Hmm, but hang on, the more I think about this one, the more i want to turn this "inside out" to make the data entry for specifying dates to omit easier. We can adjust this approach, I think, so that records present in the related table will specify dates to be omitted rather than dates to be included as I have set up now. Stay tuned for a follow on post...
Let's try this relationship:
cOmitDates ≠ Date
And the relationship to the related table (with a name change to reflect the reversed function of this table):
Employee::__pkEmployeeID = DatesOmitted::_fkEmployeeID
and now cOmitDates should be defined as:
List ( DatesOmitted::Date ) & ¶ & 1
Make sure to clear the "do not evaluate if all referenced fields are empty" check box.
You need to clear this checkbox and include the & ¶ & 1 in this calculation to make sure that cOmitDates is never empty.
Now you can set up a portal to datesOmitted on your Employees table where you can add dates for any days that you want to exclude from the grading calculations. You can even add a field to this table and place it in the portal to record the reason this date is to be omitted.
Okay... So this:
cOmitDates ≠ Date"
Is referring to the two tables, Stats and Calls, correct?
I think I've got it. Just a bit of testing to see if it is working.
So I created the table "DatesOmitted" and related it with an EmployeeID=EmployeeID. I then created cOmitDates with the above formula making sure that I unchecked the box for do not evaluate if empty. I then related above to my other tables. Now my data does not pull up properly. When pulling up the Employees table and displaying cOmitDates, It has a "?" and no values. Did I do something wrong?
My issue still persists. My previously mentioned "Rank" and "Scorecard" no longer produce any data. Here is:
did you call it "cOmitDates" or "cDateList"? you should have one or the other, but not both.
What return type did you specify for this field? I'd guess from the question mark that you specified "date" when you should have specified "text".
It may look wrong to match a calculation field that returns text to a field of type date in your relationship, but it does work.
Apologies. I copied from the previous comment. I do not have "cDateList" only "cOmitDates". I did have the field as "number" but corrected that to "text" now. The field cOmitDates appears to be correct now with ¶ 1. But my layouts do not function.
Odd. It started working when I put one date in one employee's "DatesOmitted" table. All other employees it does not function with.
So. All my relationships were correct and the fomula was as you mentioned. I attempted several iterations of "List(OmittedDates::date) & ¶ & 1". I tried the following:
List(OmittedDates::date) & ¶ & 1
List(OmittedDates::date) & ¶ & 1/1/2010
List(OmittedDates::date) & ¶ & "1"
None worked until I finally tried List(OmittedDates::date) & ¶ & "1/1/2010" and it began to work. As long as I put "1/1/2012" in quotes, it doesn't matter what date I put in there. Any reason why this would happen? I see no problem with putting in a old date and omit it through portal filtering later but was just curious if you had any clue why this happened and why it worked?
Apologies back. I ran a few tests and found that every items in cOmitDates must be a valid date. Change your calculation to:
List(DatesOmitted::dateToOmit) & ¶ & "1/1/0001"
I'm also finding that a portal to my employee data table (the one that has to filter by date range and employee ID) isn't updating automatically when I add/remove/change an omitted date record. I've had to use Refresh Window [Flush Cached Join Results} to get it to update so you may need to use that script step to force an update after changing data in the portal to the employee's omitted dates.
Oh. I didn't even think about checking that. My layout that gives the user an option to omit dates is not on the same layout that the data is given. Would I need to use the refresh window as you mention?