By using this website, you agree to our Terms of Use (click here)
Hey Everyone,
I need to create a Generic Inquiry that pulls data for time cards for a specific date range. There are columns available in the Results tab that reference the start date of the week in question on the Time Card Summary table. However, when I try to find those columns on the Conditions tab, they do not exist. The only column I can find is the Week ID column. To further compound my confusion, I am unable to filter the results of a Generic Inquiry by the Week Start Date as well. The column displays in the Inquiry but no filter options are available when you click on the column header. Also, when you build a filter with the filter icon in the toolbar, the Week Start Date is not listed in the columns available for selection.
What am I missing? How do I pull just a list of time card entries for a specified date range?
Hey Shawn,
I'm not exactly sure why, but some DAC fields are too complex to be filtered on. "Too complex" is my explanation. Something is special about them, but I'm not exactly sure what it is.
Any chance that you are using Custom Week Configuration on the Time & Expenses Preferences screen?
If so, then you could join over to EPCustomWeek on EPTimeCard.WeekID = EPCustomWeek.WeekID, then pickup EPCustomWeek.StartDate.
If not, then I would be pretty optimistic that if you create a SQL View and create a DAC based on it using this method from Doug Johnson, then you would be able to filter on YourSQLViewDAC.WeekStartDate. The reason I would be optimistic is that the SQL View will act as its own DAC, but without all of the "special" stuff that the regular DAC has.
On that same point, I think it's a good theory that SQL View DACs should perform faster in general than regular DACs (again because they have less "stuff" in them), but I haven't done any testing to verify this.
@timrodman I think the Too Complex is equivalent to a CROSS APPLY in T-SQL. It happens after all the rows are fetched making it a bad candidate for a condition and certainly if you filter on one in a GI with a large volume of rows then the performance is poor (or times out as it did for my client). I did as you suggest and actually added the EPCustomWeek.StartDate to the GI and then filtering happened so much faster and I could also do a condition on it if I wanted to.