Advanced Searches

[Gregory Hecht]

Hi folks!  

During today's Livestream, Pete suggested that we set up a dedicated thread here in the forum for people to post their syntax etc. for Advanced Searches that they do.  I've pinned the thread so it should be easy to find.  

I confess, I have not done much with Advanced Search b/c I don't know the syntax.  But if I knew the syntax, then I feel confident that I can readily do things with it.  I would not be surprised if there are other CB users in the same or similar boat.  Does anybody have a link to a simple, straightforward document listing out the basics for Advanced Search syntax etc?  

[Scott Mahan]

So, I use Custom Date 1 for "date entered", (which OBTW it has always seemed to me that it's peculiar that this is not a standard field). So after you get done scanning barcodes you go search for:
I.[CustomDate1] is null and I.[QtyInStock] > 0 

Then you do Edit, Select all,  Quick Change, Custom Date 1, (it will default to today), Make Changes.

[Troy E. Charlton]

Find All My Owned With Cover Date For A Year (2017) & Sorted By Value
Items Where...
I.[CoverDate] >= "2017-01-01" and I.[CoverDate] <= "2017-12-31" and I.[QtyInStock] > 0
Order By
I.[Price] desc

Owned Platinum Age Comics & Sorted By Value
Items Where...
I.[CoverDate] > "1897-01-01" and I.[CoverDate] < "1938-03-31" and I.[QtyInStock] > 0
Order By
I.[Price] desc

Owned Golden Age Comics & Sorted By Value
Items Where...
I.[CoverDate] > "1938-04-01" and I.[CoverDate] < "1956-08-31" and I.[QtyInStock] > 0
Order By
I.[Price] desc

Owned Silver Age Comics & Sorted By Value
Items Where...
I.[CoverDate] > "1956-09-01" and I.[CoverDate] < "1969-12-31" and I.[QtyInStock] > 0
Order By
I.[Price] desc

Owned Bronze Age Comics & Sorted By Value
Items Where...
I.[CoverDate] > "1970-01-01" and I.[CoverDate] < "1984-12-31" and I.[QtyInStock] > 0
Order By
I.[Price] desc

Owned Modern Age Comics & Sorted By Value
Items Where...
I.[CoverDate] > "1985-01-01" and I.[CoverDate] < "2050-01-01" and I.[QtyInStock] > 0
Order By
I.[Price] desc

Owned Comics Of Low Value/Reading Value & Sorted By Value
Items Where...
I.[Price] < 2.00 and I.[QtyInStock] > 0
Order By
I.[Price] desc

Find Every Singled Annual Published By Marvel Comics & Sorted By Value
Items Where...
ComicTitles.Publisher = 'Marvel' AND [ItemNumber] LIKE 'Anl %'
Order By
I.[Price] desc

Find Every Singled None-Variant Comic Published in 2020 Who's Value At Least $50 Greater Than Original Cover Price & Sorted By 2020 Value
Items Where...
I.[CoverDate] >= "2020-01-01" and I.[CoverDate] <= "2020-12-31" and I.PRICE -I.[CoverPrice] > 50.00 and  I.[QtyInStock]>=0 and I.[ItemNumber] not like '%/%'
Order By
I.[ValueYear4] desc

Owned Comics That Have SUPERMAN...Starting Title & Sorted By Value
Items Where...
I.[Title] LIKE 'SUPERMAN%' and I.[QtyInStock] > 0
Order By
I.[Price] desc

Owned Comics SINCE 2000 That Have Had At Least A $20 Value Increase Over Previous Year & Sorted By Value
Items Where...
I.[CoverDate] >= "2000-01-01" and I.PRICE - I.[ValueYear3] > 20.00 and I.[QtyInStock] > 0
Order By
I.[Price] desc

[Fred Slota]

Anyone want to supply an "Order By" syntax that sorts in ComicBook Order?

[Steven L. Dasinger]

Item # is made up of 4 parts. You need to sort by each one separately.  Try:

I.[ItemType], I.[IssueNum], I.[Variation], I.[Printing]

This is if you are doing one Title.  If you are doing multiple Titles, then add I.[Title] as the first thing.

 

[Fred Slota]

Is the AlphabetizedTitle field still accessible to Advanced Find, as that will put the titles in the same order as in ComicBase?

[Steven L. Dasinger]

Not officially. but try this:

ComicTitles.AlphabetizedTitle

Hope it works.

[Fred Slota]

I added a bunch of issues with the app, adding the current date, "1/7/21" in the Notes field.  After imported into ComicBase, searched for the issues in ComicBase order:

 

Comics with "1/7/21" in the Notes field, sorted in ComicBase order
Items Where...
I.[Notes] LIKE "%1/7/21%"
Order By
ComicTitles.AlphabetizedTitle, I.[ItemType], I.[IssueNum], I.[Variation], I.[Printing]

[Steven L. Dasinger]

And, did it work as you expected?

[Fred Slota]

I believe so, which is why I posted a recipe that used it.

[Lance Fittro]

On 8/19/2020 at 5:45 PM, Gregory Hecht said:

Does anybody have a link to a simple, straightforward document listing out the basics for Advanced Search syntax etc?  

While I see a lot of examples of saved searches, which does help, I'd like to restate Greg's original request for a resource that explains the basic syntax for the Advanced Search. Some of the fields are more intuitive as to how to search on them than others. I don't have a specific search that I'm trying to accomplish at the moment, but would like to have a sort of instruction manual for how to use the tool handy when the need arises.

[Walt Grogan]

Here's another way to retrieve all cover dates for a year...

Find all comics with a cover date of a specific year

 strftime('%Y',I.[CoverDate]) = '2022'

 

[Steven L. Dasinger]

While strftime('%Y',I.[CoverDate]) = '2022' does work, using:

I.CoverDate BETWEEN '2022-01-01' AND '2022-12-31'

is easier to remember and more efficient.

Query with function strftime takes 23 seconds to process while Between takes 1.75 seconds.
(Ran each query several times and got the same results each time. For those interested, the reason is the function has to be applied to every row before the comparison is made. The Between just has to compare.)

PS In case someone hasn't found it, there is an Advanced Find Guide/Manual (work in progress) for those who need a little more information about Advance Find.

[Walt Grogan]

Steven, thanks for checking the timing on that. While slow, strftime can still useful if you want to do a query like this where you want to find all issues of a title that came out in a certain month. Here are all the issues of the 1973 series SHAZAM! that have a cover date of April. Note that the month has to be a 2-digit value. 

 strftime('%m',I.[CoverDate]) = '04' and (I.CoverDate BETWEEN '1973-01-01' AND '1978-12-31') AND I.Title = 'SHAZAM!'

 

Edited January 14 by Walt Grogan

[Steven L. Dasinger]

Correct. I like functions and was glad you posted about it. I was just writing something up on that...

While the Year component may not be as efficient, it can be used for Month, Day of Month, and (possibly) Day of Week. For example

If you want all items put out in a particular Month (January in the example):

strftime('%m', I.CoverDate) = '01'

You can also constrain it to a Year range (Month January between 2010 and 2019):

strftime('%m', I.CoverDate) = '01' and I.CoverDate Between '2010-01-01' and '2019-01-01'

The Day of Month and Day of Week are probably most useful for Street Date as Cover Dates normally have Day of 1 (with bi-monthly or weekly being some exceptions)
Not sure if they are useful or not but if you need them:

All items with a Day of '15':

strftime('%d', I.StreetDate) = '15'

All items that came out on a Wednesday (Sunday = 0, Saturday = 6):

strftime('%w', I.StreetDate) = '3'

NOTE: the % value is case-sensitive. %m works. %M does not.

[Walt Grogan]

Here's a more useful example that would get a majority of the first-parts of the annual JLA/JSA team-ups in Justice League of America -- although not perfect since I'm only checking against the month of August.

 strftime('%m',I.[CoverDate]) = '08' and (I.CoverDate BETWEEN '1963-08-01' AND '1987-05-01') AND i.Title = 'Justice League of America'

 

Edited January 14 by Walt Grogan