Advanced Searches

[Steven L. Dasinger]

Short answer is no. There are no 'Last User Date' type fields (that I know of).

The closest I can find is DateModified. There are also LastQtyChangeDate and LastValueChangeDate but I am not sure how useful those two are.
At the Title level there are ComicTitles.TitleCreatedDate and ComicTitles.TitleModifiedDate. (ComicTitles would change to BookTitles if you are looking at Books)

A couple of points.
For comparisons, use date format of YYYY-MM-DD. ex. I.DateModified >= '2022-10-01'

Some of these fields may have 'date-time' values (something llike yyyy-mm-dd hh:mm:ss). However SQLite does not have a true Date-Time data type. If they are date-time, using the DATE function on the field to extract the Date part of the data. ex. DATE(I.DateModified) >= '2022-10-01'
I am not sure which fields contain Date or Date-Time data. You may have to experiment to find what returns consistent results.

PS DateModifed is are any changes. I would not recommending using on the day you ran the Update as it will modify a lot of rows.

 

[Brian J. Stewart]

On 10/28/2022 at 6:44 AM, Steven L. Dasinger said:

Short answer is no. There are no 'Last User Date' type fields (that I know of).

The closest I can find is DateModified. There are also LastQtyChangeDate and LastValueChangeDate but I am not sure how useful those two are.
At the Title level there are ComicTitles.TitleCreatedDate and ComicTitles.TitleModifiedDate. (ComicTitles would change to BookTitles if you are looking at Books)

A couple of points.
For comparisons, use date format of YYYY-MM-DD. ex. I.DateModified >= '2022-10-01'

Some of these fields may have 'date-time' values (something llike yyyy-mm-dd hh:mm:ss). However SQLite does not have a true Date-Time data type. If they are date-time, using the DATE function on the field to extract the Date part of the data. ex. DATE(I.DateModified) >= '2022-10-01'
I am not sure which fields contain Date or Date-Time data. You may have to experiment to find what returns consistent results.

PS DateModifed is are any changes. I would not recommending using on the day you ran the Update as it will modify a lot of rows.

 

Hmm, i just tried

(DATE(I.DateModified) >= '2022-10-21' AND (I.QtyInStock > 0))

And this did show me issues that I own, that had been modified since Oct 21 unfortunately this field appears to be shared with the weekly content updates.   I was hoping for a I.UserDateModified, so that I could specifically find issues I had altered myself.

[Fred Slota]

Is there an accessible UNIQUE in Advanced Search?

Use case - I've currently got connecting cover information entered in Custom Text 3 & 4, with a unique for each collage string in Text4.  I'm curious to know how many different -ptychs there are (and to help find typos) so would like to generate a search of unique Text4 entries...

 

As I typed, I realized that in this case, I can search for

(Text3 LIKE "%1st%")

Currently up to 543 issues in 162 -ptychs

 

For a count, but for typo checks the original request still stands.

 

 

[Steven L. Dasinger]

Short answer is No. Every row in CB has a unique ID (which is why the Group By box was useless and removed).

However, if you can be more specific with data, examples, and desired results, I may be able to find a way around this.

[Fred Slota]

Among others, I have 9 issues across the various Extreme Carnage titles that all should have "9-ptych covers across ECA 1/B,-1/C, ECO 1/E, EC:A 1/C, EC:L 1/C, EC:P 1/B, EC:R 1/C, EC:S 1/B, EC:T 1/C" as their CustomText4.

 

I can easily search for Text4 values, and get results including all 9 issues.  I would like to get a search where I only receive 1 (assuming they are all spelled correctly).

 

I suppose I could always export results to Excel and do further winnowing, but if I could do it all in ComicBase, that would be better.

Edited February 19, 2023 by Fred Slota
Absolute -> Extreme

[Steven L. Dasinger]

This works with some restrictions.
If you have multiple 'ptych' set of the same name, you will only get 1 row for that set name.
For example
9-ptych for Carnage titles.
9-ptych for Fantastic Four titles

You will ONLY get one of them (either Carnage or Fantastic Four) and not both.

If you have multiple 'ptych' sets of different names, you will get 1 row for each of the different set names.
For example
9-ptych for Carnage titles
4-ptych for Fantastic Four titles

You will get 1 title for each of the sets (both Carnage and Fantastic Four) for a total of 2 rows.

I.CustomField3 LIKE '%ptych%' AND
I.Title in (
            SELECT Min(A.Title) as Title
             FROM ComicIssues A
             WHERE A.CustomField3 LIKE '%ptych%'
             Group by A.CustomField3
            )

[Fred Slota]

I think that's partly correct.

 

This winnowed out if the -ptych is across different titles; I only got one return.

But there are many -ptychs that are across separate issues of the same title.  That search still gave multiple returns for a given Text4 in this case.

 

I was hoping there was a simpler method...  might just be easier in Excel...  Thank you.

[Steven L. Dasinger]

This one will return only 1 Title/Issue per distinct named *ptych' name set.

 

I.CustomField3 LIKE '%ptych%' AND
I.Title || I.ItemNumber in (
      Select C.Title || C.ItemNumber
      From (Select B.Title , Min (B.ItemNumber) as ItemNumber
            From ComicIssues B
            WHERE B.CustomField3 LIKE '%ptych%'
              AND B.Title in (
                              SELECT Min(A.Title) as Title
                              FROM ComicIssues A
                              WHERE A.CustomField3 LIKE '%ptych%'
                              Group by A.CustomField3
                             )  
            Group by B.Title
           ) as C
         )

 

[Fred Slota]

Looking at all that, I appreciate it and I'm sorry I asked in the first place.

 

[Steven L. Dasinger]

Don't be. I like the challenge and this is the 'fun' stuff...

[Chris Vaughan]

Anyone help me with advanced search?  I'm wanting one that shows all comics from Marvel that do not have a cover.

[Chris Vaughan]

I'd also like to be able to search for images that are below a certain size too.

[Steven L. Dasinger]

This should work.

ComicTitles.Publisher = 'Marvel' AND (I.PictureHeight IS NULL OR I.PictureHeight < 200)

I used Picture Height (but you could use Picture Width) with an arbitrary value of 200 pixels.

However, a few bits of information.
Sometimes the Picture fields (Height, Width, File Size, Modified) information isn't correct or starnge. For example, they may have contain 0 instead of being NULL. Because of that, you might need to run File Tools->Rebuild Lists using the long running 'Picture files lists'.

I have run this successfully on my database but I have all the full-sized covers.
I am not sure if the 'quick-start' version populates the Picture fields the same way. I would hope it does but I haven't tested it.

PS You need to check for both Less than a value and NULL since NULL is not a value.

[Chris Vaughan]

Okay, I tried both Height and Width, and tried NULL and 0, and neither worked right.  It returned Marvel comics but they all had covers already.  I already tried the Rebuild Lists first.

[Steven L. Dasinger]

Did you use the Quick Start install for the covers?

Have you run the Rebuild Lists for Pictures?

[Chris Vaughan]

I ran Rebuild for Pictures, I don' know what the Quick Start is.  I've had the program installed forever.

[Steven L. Dasinger]

What Edition of CB do you have? Pro or Archive?

If it is Archive, you have an option to download / install the pictures with a Quick Start or downloading the entire full-size covers.

The Quick Start installs smaller 'thumbnail' size covers. With Preferences setting 'Automatically download larger pictures...' checked, whenever you access a cover it will automatically download the full-size cover.

 

 

[Chris Vaughan]

It's Archive, but I guess I'm not understanding.  Where is this Quick Start?  I'm not seeing it anywhere.

[Steven L. Dasinger]

Go to the ComicBase website.

Under My Accounts, select Registrations.

Click the green arrow under download next to you active subscription.

Here you can download the CB program, the Quick Start version of pictures and CB Media.

If you click the Other radio button, you get to where you can download all the full size covers.

[Chris Vaughan]

Won't this overwrite my existing covers?

[Steven L. Dasinger]

I am not asking you to install them. Just whether you did or not.

I don't know if the install of these populate the Picture fields correctly or not.  May affect the query I supplied.

Hopefully the Rebuild will fix the problem.

[Chris Vaughan]

I installed them initially.

[Lance Fittro]

Try running the search, select a block of the results that have cover images shown, right click, and select Download Covers. If you are an Archive user, this will replace any of the tiny placeholder thumbnail images with the larger-resolution images from the database for those selections. If you then run the same search again, you should see the number of results returned is reduced (although you may need to try first with another publisher to see this result, if 'Marvel' returns more than the max. 5000 results on your first go).

This was my experience when trying the search just now. I can't recall if I ever used the Quick Start picture load, or if it's a result of not downloading full-res covers with each of the weekly downloads, but it does appear that the search picks up the thumbnail placeholder images in the results where those are present on my machine. Presumably if I downloaded the full-res image archive again and updated my local database with those images, it would eliminate most of those false positives in the returned results.

Put another way, it appears the search returns results based on the cover image sizes in your local database as they exist at the time of the search. As soon as you click on one of the covers, if available, it will download a larger cover image from the ComicBase server. If you have a high-speed internet connection, the delay in downloading that image when displaying the larger cover is not noticeable, so that it appears as if the search returned some false results that already had larger cover images. Once you have downloaded full-resolution cover images for all available covers though, the search should return consistent results which meet your expectations.

Try it with 'Bongo' for a smaller publisher with manageable number of results. First time for me had 179 results. Ctrl+A to select all, Download Covers, re-run search, and it's consistently 19 results thereafter (all having no cover image except 32/A which has a tiny scan in the master database).

[Steven L. Dasinger]

10 minutes ago, Lance Fittro said:

returns more than the max. 5000 results

The maximum number of results returned depends on the amount of memory you have.

The more memory, they more that can be returned. For example, I have 32GB of memory and the Max allowed in 2,000,000.

PS there is also a Max Results box you can set the limit to which could be smaller that the Max allowed.

[Lance Fittro]

Steve is right. I read too quickly over the note in the search window which indicates that I can change it by changing the grid maximum rows, which I currently have set at 5000.

The point I was making though is that, for me, the search worked well and is much appreciated. Although it did reveal that I've not kept up with maximizing my local cover scan library in some time.