0 Replies Latest reply: Nov 14, 2017 1:30 PM by Matt McIlvride RSS

    Date filter shows multiple dates when key is identical

    Matt McIlvride

      I have spent some time fitting our data to the model offered in Henric's excellent Canonical Date post. I am working on an application where my data looks like this:

      AttemptsModel.PNG

      The Attempts table is a list of tasks with unique keys. When an employee attempts a task, a new record is created to show the attempt. This creates a many-to-one relationship between attempts and unique tasks. The key and date are brought into the DateBridge table and in this instance the status is set to 'Attempted'. The calendar table is then brought in to use as the canonical date for filtering purposes.

       

      The problem I am facing is that when I filter on a date, other dates turn up in the results if a key has a listed attempt on another date. For example, if I have the following data in the Attempts table:

       

      KeyAttemptDateEmployee
      0012017-11-06Sridar
      0022017-11-06John
      0032017-11-05Marvin
      0032017-11-06Marvin

       

      If I have a filter that uses the Calendar table's Date field, and I set the filter for 2017-11-06, I would expect to see three records, but instead I am seeing all four since two share a key.

       

      How can I get the filter to show only records for the date specified, even if there is another record for a key?