[Resolved] Problems with filters on DATE custom fields

[Author]

Posts

[Doug Watt]

I am trying to: use a custom field of the DATE type in a view to show all of the posts in the current month

Link to a page where the issue can be seen: hidden link

I expected to see: approx 30 posts paginated over 5 pages

Instead, I got: 4 posts on 1 page

I am currently building a site for a cartoonist and I have created a custom post type called "Comics" which includes in the custom fields one called "Cartoon Date" which I want to use in filters in various views to display comics on the front end.

I currently have three such views on pages as follows:

1. hidden link - which is a listing of all of the cartoons with a Cartoon Date within the current month. (this is the problematic one)

2. hidden link - which is a listing of all of the cartoons with a Cartoon Date within the previous month

3. hidden link - which is a list of recent the Sunday cartoons (a different format)

I am not having problems with 2. and 3. They are working well and will be exactly what I want when all supporting images have been uploaded.

However the list of cartoons for the current month is only displaying 4 posts instead of approx 30 posts. The current filter is shown in the image: current filter.jpg. I have tried different variations such as "number between THIS_MONTH() and TODAY()" and "number equal or greater than THIS_MONTH()" ...but it doesn't change.

I should also mention that the vast majority of posts were imported from a csv file using WP All Import on a day within this current month and therefore the "Published Date" of the vast majority of posts is 23rd July, 2019. Only a small number have been entered manually since that time.

Any help would be greatly appreciated.

Doug
====

[Luo Yang]

Dear Doug,

Thanks for the details, I can log into your website, and see the problem.

Here are what I found:
I have tried to remove those custom field filters from view "Current Month Archive Dailys":
hidden link
I get the same result in front-end: 4 posts on 1 page

So the problem is abnormal, the problem persists with or without those filters, please try these:
1) In case it is a compatibility problem, please deactivate all other plugins, and switch to wordpress default theme 2019, deactivate all custom PHP/JS code snippets, and test again

2) Also check if there is any PHP/JS error in your website:
https://toolset.com/documentation/programmer-reference/debugging-sites-built-with-toolset/

3) If the problem still persists, please provide database dump file(ZIP file) of your website, also point out the problem page URL and view URL, I need to test and debug it in my localhost, thanks
https://toolset.com/faq/provide-supporters-copy-site/

[Luo Yang]

Thanks for the details, I am downloading the files, will update here if there is any found.

[Luo Yang]

Here are what I found:
There should be some problem when you import posts and custom fields from csv file.

The custom date field of Toolset Types plugin stores value in timestamp format (1544659200 ... ), but in your database, some Comic posts are using date format(10 July 2010 ... ), see screenshot: wpcf-cartoon-date.JPG

I suggest you try these:
1) Edit your csv file, convert the date format values to timestamp, for example, with MS Excel:
hidden link

2) Import the csv file using WP All Import again

[Doug Watt]

Many thanks, Lou Yang.

This is quite surprising, and yet it certainly answers what has been happening.

The VAST majority of the >6000 records were entered via csv file and I definitely had the date -> timestamp conversion in place.

I think that maybe the first import of data brought in the date field in a date format and I could get Toolset to recognize anything. My research showed the need to convert to timestamp and so I re-imported the data in an "update records" mode. I guess that wasn't done carefully enough.

I have also added some records through a CRED form and also entered some through the normal WordPress dashboard. Would these methods mean that some values could be entered into the Cartoon_Date field as date format?, or would CRED recognise that the date should be stored as timestamp?

I have one more question on timestamp, but I'll ask you after I get this resolved.

Thanks,

Doug
====

[Luo Yang]

I assume we are talking about this post form in your website: Create Comics (ID: 22645)

I have tested it in my localhost, it can setup the custom date field "Cartoon Date" in timestamp format correctly, but it will not save any value into custom checkbox field "Sunday", but your post view is using filter on that checkbox field:
Sunday is a number different from 1
So it will conduct another problem, in your database:
- some "Comics" posts do not have a "Sunday" field value,
- some "Comics" posts have "Sunday" field value: 0
- some "Comics" posts have "Sunday" field value: 1

So in your case, I suggest you use Views filter hook "wpv_filter_query" to setup that specific field filter, for example:
1) Edit the post view "Current Month Archive Dailys", remove filter:
Sunday is a number different from 1

2) Add below codes into your theme file "functions.php":

add_filter('wpv_filter_query', function($args, $settings, $view_id){
    if($view_id == 41349 ){
		$args['meta_query'][] = array(
			'relation' => 'OR',
			array(
				'key' => 'wpcf-sunday',
				'compare' => 'NOT EXISTS'
			),
			array(
				'key' => 'wpcf-sunday',
				'compare' => '=!',
				'value'	=> 1
			),
		);
	}
	return $args;
}, 999, 3);

And test again.

More help:
https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query

[Doug Watt]

Many thanks.

An update on the database first.

The page that you took the screenshot of contains the only invalid records. So, there were 5 records that were incorrectly entered a few times each. These must have been in the very early stages of the project ..before I became aware of the timestamp issue. All of the other records in the database are in timestamp.

Thank you for your help with this.

I'll tackle the "Sunday" issue next. Thanks for the code.

There are two other similar fields in which I want to identify cartoons as being "featured". I'll need to look at this code for that also.

I'll update you tomorrow .....or later tonite. 🙂

[Doug Watt]

I can now confirm that there are no records in which the cartoon-date field is in other than timestamp format.

However, the matter has not resolved. It still behaves the same way.

I know that there are still a few of the very early records in which the same date has been used for the Cartoon Date. Could duplicates be causing the issue?

Doug
====

[Doug Watt]

It is now resolved.

I think it was fixed by exporting the fileds sunday, featured-strip and featured-cartoon and filling a "0" in all of the blank fields. I then imported that csv and tried one more time ...hey, presto.

Thank you so much for all of your help Luo Yang.

Doug
====

[Luo Yang]

Thanks for the confirm, can we close this thread?
Feel free to create new ticket if there is other new questions.

[Doug Watt]

My issue is resolved now. Thank you!