My current form filters the secondary data connection using an empID. Then I use formatting rule to hide non-overlapping period. Which works well but since our data is growing, I'm wondering if there's another way I can improve performance by using a second filter using dates?
Current Form Rule: when control (empID) is changed,
- Set field value: empID (Secondary/Query) = empID (Main)
- Run Query
Formatting rules for the repeating table (Secondary), hide row if
- End Date (Main) < Start Date (Secondary) OR
- End Date (Secondary) < Start Date (Main) OR
- ID (Secondary) = ID (Main)
I only want to retrieve any data that overlaps with current request.
Example Secondary Data Source:
- Employee1 08/01/2017 08/01/2017
- Employee2 08/01/2017 08/01/2017
- Employee1 08/14/2017 08/28/2017
If current request is Employee1 08/15/2017-09/01/2017, I only want to return the 3rd item from Secondary data source. Rather than retrieving all data associated with Employee1