Grid Filtering on time

Hi Joyce,

Whenever a user attempts to filter the Service WorkOrders>Labor Grid and set a filter date of “<= “to a specific date, all of the items meeting the filter criteria display expect for the “=” item.

Grid Setup –

Column One = Service Start Date with Custom Filter. The Custom Filter is set as: Operator Field “Less than or equal to”, Operand Field “4/30/2007 3:56 PM”

When we apply this filter to the Grid all workorders before 4/30/2007 3:56 PM are displayed in the Grid, however, the one workorder that was selected for the “Operand” criteriaand equals this date does not display.

This issue occurs regardless of which Service Start Date we choose.


Hi Bob

I can not recreate this in the Labor grid in the Service navigation pane - if I select a specific date as the Operand, and the Operator or Less than or equal to, the results always include the selected date and everything before that date.

I’ve also tested with a Firebird database vs SQL database, and it works every time for me.

Are you using the latest version of AyaNova v3.4.3.0?

I would suggest retrying this - confirm that you have <All Rows> selected so in case you have more than 100 records resulting casuing the labor record with 4/30/2007 3:56 PM to not show.

I am not sure what else to suggest - has anyone else experienced this with the latest version of AyaNova v3.4.3? (this was an issue with an earlier version of AyaNova, but was fixed a number of subversions ago).

  • Joyce

Hi Joyce,

We are using v3.4.3.0 and SQL Server 2005 SP2

I was able to duplicate the problem; however, after reading your reply I went back into the grid and discovered there are more filters in use in columns that are out of view.

We had setup and saved filters for customers with whom we do quarterly billing months ago and we all have become accustomed to just clicking on the saved filter then selecting a custom Start Date and Time query for the Quarter.

The filtering in place where this issue occurs is a bit more complex than a single filter and maybe this may be where this issue is originating from?

We always make sure to select “All Rows” before performing any filtering then select the Saved Query.

Here are the additional filters in the saved Query -

Column 7 – Consists of 1 filter –
Operator - “= Equal to”, Operand - “Ready For Invoicing”

Column 10 – Consists of 3 “OR” filters -
1st Filter – Operator - “Contains”, Operand - “1st Customer Name”
2nd Filter - Operator – “Contains”, Operand – “2nd Customer Name”
3rd Filter – Operator – “Contains”, Operand – “3rd Customer Name”

The 1st customer name is HQ who also has 23 branch office Client records, and the subsequent customers have individual Client records.

Also a month back the “3rd Customer” in the Column 10 filter got married and had a name change so we had to edit their Client record and then find and change all the saved filters. On a side note we discovered that changing the name in the Client record does not update the Client Name saved in Query filters. This is an important point to be aware of when it comes to billing from Saved filters.

I think instead of asking you to try and re-create this issue that I should just delete all of the saved filters and start over and see if that fixes the issue. I’ll let you know how I make out.

Thanks for your prompt response!

<SPAN id=_ctl1_ctlTopic_ctlPanelBar_ctlTopicsRepeater__ctl1_lblFullMessage>

Hi Joyce,

We continue to experience grid filtering issues - screenshot attached.

I created a new filter starting with ‘All rows’ and ‘No filter’, then filtered the ‘Client’ column to a single client. Next I created a custom filter of ‘Greater than’ 12/27/2006 04:11 PM for the ‘Service Start Date & Time’ column.

As you can see in the screenshot the custom filter is set as I specified however the first date returned in the first row is ‘Equal to’ 12/27/2006 04:11 PM, not ‘Greater than.’

If I start over from scratch and apply the filter ‘Greater than or equal to’ with the same criteria I end up with the same result.

Maybe a corrupt database?


Hi Bob

Thank you for attaching the screenshot, that allowed me to see that seconds are not showing and that may be where the issue is.

-Exit out of AyaNova on this computer
-Set your Regional Settings on this computerto show seconds.
-Log back into AyaNova and confirm that seconds are now showing in Service Start Date & Time column
-Now create the Custom filter - select “Greater Than” as the Operator, and the specific date and time with seconds as the Operand, and apply and view the filtered results.
-Confirm - is only records that are “greater than” the selected speciifc date and time with seconds now showing?

  • Joyce

Hi Joyce,

That worked but now of coursethe ‘seconds’ field is back in our Workorder screens where the last thing wewant in ‘time’ fieldsis … more fields to deal with. :slight_smile:

I guess the only solution is when anyone needs to filter grids using ‘time’ they will have toremembertochange the RegionalTime settings…


Actually no Bob, I had you change the Regional Settings for troubleshooting - so that you could see why that record was coming up - because all times within the database include seconds, and when you selected a specific date and time with minutes you are actually selecting a date and time with minutes and seconds from the database.

If this is a common occurance that a lot of records have the exact same minute but different seconds,and you want to use the “Greater than” operatorwhen filteringthan yes, you will want to show the seconds in your Regional Settings so that you can be sure you are selecting the correct Operand. Otherwise if you do not want seconds showing, turn it off but with the understanding that if a record shows when using the “Greater than”, its because of the seconds that are involved.

  • Joyce