Jump to content

Advanced Searches


Gregory Hecht

Recommended Posts

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 3 months later...

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.

 

 

Link to comment
Share on other sites

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 by Fred Slota
Absolute -> Extreme
Link to comment
Share on other sites

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
            )

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
         )

 

Link to comment
Share on other sites

  • 3 months later...

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.

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Capture.JPG

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...