[SOLVED] Ticket search using date field works with before/after but not between

[randman1]

Hello community. Our field techs are self-dispatched, meaning that they respond directly to customer trouble reports and deal with the paperwork afterwards. Tickets may be created the same day or a day or two after the service was performed. As such, I have created a dynamic field named Service Date to hold the actual date the service was performed. Weekly and monthly reports will be based upon this Service Date and not the creation date of the ticket.

When I do a ticket searching using Service Date (before/after) as the filter field, the expected tickets are returned. But, when I use Service Date (between), as the filter field, no tickets are returned... at all. I can expand the to and from dates out several years but get nothing. Am I missing something simple that can explain this behavior? The "between" method is the one that is necessary for reporting.

Thanks,

[RStraub]

Which search are you using ? The one from the main navigation Ticket->Search?

I just tried the very same and it works as expected for me.

Do you have any other filters that might interfere with your result ? Do you have read-permissions to the queue?

[randman1]

Yes, the search I'm using is from the main menu Tickets > Search.

I am using no other filters when performing the Service Date (between) search.

I am at the early stages of evaluating the program so I only have 1 queue and 3 agents defined so far. I have tried searching while logged in as either of the two agents that are the ticket owners.

To be clear, you are testing with a dynamic field as well?

[randman1]

I did some monitoring of the SQL statements during each of the search methods. I setup each search with the desired settings to where I only had to click the Search button to run. Before clicking Search, I enabled general logging in MariaDB. Then I clicked Search. This method captured all SQL statements for each search event

Using the Service Date (before/after) search method which is working:

Code: Select all

...
SELECT DISTINCT st.id, st.tn, sq.name FROM ticket st INNER JOIN dynamic_field_value dfv1 
ON (st.id = dfv1.object_id                        
AND dfv1.field_id = 3)  INNER JOIN queue sq ON sq.id = st.queue_id  WHERE 1=1 AND sq.group_id IN (1,2,3,4)  AND ( dfv1.value_date >= '2017-06-09 00:00:00' ) AND ( dfv1.value_date <= '2017-06-12 23:59:59' )
ORDER BY sq.name DESC LIMIT 2000

Using the Service Date (between) search method which is not working:

Code: Select all

...
SELECT id, name, field_order FROM dynamic_field WHERE valid_id IN (1) AND object_type = 'Article' ORDER BY field_order, id

These are the first statements after the app saves the search criteria as "last-search". The (before/after) statement makes a call to dynamic_field_value. When I manually run the SQL statement, it returns the 3 expected records then continues to get the articles and other related data to build the tickets.

The (between) statement makes a call to dynamic_field and returns no records since I have not defined any dynamic fields for articles. I cannot see ant other SQL statements related to getting related ticket data. There are no calls to dynamic_field_value at all which it has to since I'm providing criteria on the value. I think this is why I'm not returning any records but don't know why the (between) search isn't more similar to the (Before/after) search and calling the dynamic_field_value table

[RStraub]

yes, I tried it with a dynamic field "date/time". But mine was for a ticket not an article.

Is yours setup as article-dynamic field ?

[randman1]

No. The dynamic field is defined for a ticket. That's what surprised me when I saw the SQL statement.

So, I see two things not right with the SQL statement when I use Service Date (between) for the search filter:
- The FROM table is dynamic_field rather than dynamic_field_value
- It's looking for an Article object rather than a ticket object
Other than some SELECT INTO statements just prior to the one I show above that store the values for last-search, there are no other statements that include the date span. I don't think the app is asking the DB the right questions

The query that I show for Service Date (before/after) is right since it's looking for the value of dynamic field value id 3 greater and less than the provided dates... and it returns records.

[randman1]

I think I figured it out. Your help led me to adding a new dynamic field with date/time rather than just date. This improved things but searches were still inconsistent. After some more digging, I found that when I removed "Prevent entry of dates in the future" from the dynamic field definition that searches on both dynamic fields worked as expected. This would explain why when I opened up the date span to a decade or so, it still returned no records since the end date was in the future. It also explains why the (before/after) worked since the app would not have entered a date in the furure.

So... I guess the lesson here is that "Prevent entry of dates in the future" not only affects the behavior of how data is entered in the dynamic fields, it also affects the searches on those dynamic fields.

I'll be revising my search techniques. Thank you for your help!

[RStraub]

Whoa, that's weird. Nice find though.