Jump to content

Advanced Find Guide/Manual (work in progress)


Recommended Posts


Here is a first draft of information about the Advanced Find. Let me know if something needs to be added, deleted, modified, or explained better.

********************************************

I believe the database currently used by CB is SQLite.

Here is a link to the SQL (Structured Query Language) for SQLite Select.

https://www.sqlite.org/lang_select.html

Here is a link to the Built-in Scalar SQL Functions

https://www.sqlite.org/lang_corefunc.html

Because of the limits put on Advance Find, a lot (most) of the SQLite documentation is of no use but it will get you the syntax for the ones you would use.

(NOTE: I am supplying these links in case they help someone. Personally, I would use them as examples of how NOT to write a manual. I know what I am doing with SQL and I find these (while accurate) are to read/follow.)

********************************************

The Query functionality in the Advanced Find is limited to just the WHERE clause.
You can't control the Select (what columns are returned), the From (the tables involved), or the Group By (used with Aggregate Functions like Sum, Count, Average, etc.). The main reason for this business decision is that every row returned has to be unique to allow them to be updated (changed) individually.

Below are sections that are explained in more detail below:

Data Types
Comparisons
Order By
Random Notes

********************************************

DATA TYPES:

To make it simple, there are just four Data Types (there are actually sub-types for some of these).

Text:

This is character data and can contain any alpha/numeric/special characters. All Text searches are case-insensitive (ignores case of letters). The constant compare value needs to be contained in single 'tick' marks (double 'tick' marks also work but single is the more standard way).

I.[Title] = 'batman'

This will return just the one Title 'Batman" as it is an exact match (and shows that Case doesn't matter)

Date:

This contains Date information. It has to be a valid date. (i.e. Feb 31, 2021 is NOT a valid date). Despite the display of the date in CB as m/d/YYY, the date in SQLite is in ISO standard format of YYYY-MM-DD. This is important because...

I.CoverDate = '2020-12-01' will return rows (assuming there is data with that value).
I.CoverDate = '12/1/2020'  will NOT return any rows.

Also it has to be an exact match as all the digits are important:

I.CoverDate = '2020-12-1' will NOT return any rows.

And like Text, Date constants need to be contained in single 'tick' marks.

Number:

This contains Numeric information (Integer or Decimal).

I.QtyInStock = 2
I.IssueNum = 0.5
I.IssueNum = -1

Binary:

This is represented by a Check-box. It is a True/False or 1/0 value. As you can see it has two states. Basically, it translates to 'Is Checked' or 'Is Not Checked'.

These are equivalent and will return rows where the CustomCheck2 check-box IS Checked

I.[CustomCheck2] IS True
I.[CustomCheck2] = 1

And, as you might guess, change to False or 0 to find if a check-box Is Not Checked.

********************************************

COMPARISONS:

When using the Where clause, you are normally comparing a Column value to a constant to find something. Some comparison operators are:

=      Equal To
<>   Not Equal To
<     Less Than
>     Greater Than
<=   Less Than or Equal To
>=   Greater Than or Equal To

These work as you would expect:

I.Title =  'Batman' (Finds Only exact match)
I.Title <> 'Batman' (Finds All except 'Batman')
I.Title <  'Batman' (Finds All from !Gag! (Harrier) to Baticomic)
I.Title >  'Batman' (Finds ALL from 'Batman & Robin (Panini Deutschland)' to '…One to Go')
I.Title <= 'Batman' (Same as < but also includes 'Batman')
I.Title >= 'Batman' (Same as > but also includes 'Batman')

BETWEEN:

Syntax is BETWEEN x AND y
It is inclusive (meaning the value of X and Y will be included in the results).
It is equivalent to using the >= and <= together. For example, these produce the same results:

I.IssueNum BETWEEN 5 and 10
I.IssueNum >= 5 AND I.IssueNum <= 10

Either of these will find all IssueNum values of 5, 6, 7, 8, 9, and 10 (assuming only integer values and the value exists).

Note: X has to be Less than (or equal) to Y for it to work. This is easier to see if you use the >= and <=

I.IssueNum BETWEEN 10 and 5 is equivalent to
I.IssueNum >= 10 and I.IssueNum <= 5
There can't be a value that is both Greater than 10 and Less than 5

IN:

Syntax is IN (value1, value2,...)
Searches for a list of possible value instead of just one.
It is equivalent to using multiple = comparisons. For example, these produce the same results:

I.IssueNum IN (5, 8, 25, 32) (Note: the values can be in any order)
I.IssueNum = 5 OR I.IssueNum = 8 OR I.IssueNum = 25 OR I.IssueNum = 32

Either will find all IssueNum values of 5, 8, 25 and 32.

IS:

This one is a little different it only has a couple formats:
IS True
IS False

It is mainly used for Binary Data Types.
(NOTE: there is also an IS NULL but that should rarely be needed. What is does is check to see if a column contains NULL. NULL is nothing. It is not a 'space' or an empty-string. Rarely, you may need it if a column in CB allows NULL and you need to find them.)

LIKE:

Syntax is I.Title LIKE 'Batman%'
This will find values but uses wild-card symbols to allow finding non-exact matches.
The wild-card values are Percent ( % ) and Underscore ( _ ) where a % represents zero to many characters and _ is one and only one character.
(NOTE: You can use multiple _ in a row to indicate a specific number of characters.)

Examples:

I.Title LIKE '%Batman%'
will find anything that contains 'Batman' in it
(NOTE: Just because % is at the beginning doesn't mean that there has to be something in from of 'Batman' in the Title.)

I.Title LIKE 'Bat%Man'
will find anything that starts with 'BAT' and ends with 'MAN' and may or may not have other values between them.

I.Title LIKE '_Batman'
will find anything that that starts with a single character before 'Batman'
(NOTE: There has to be a value as this will NOT return just 'Batman')

I.Title LIKE '_atman' (one underscore)
will return anything that starts with some single character and ends with 'atman' (i.e. 'Batman', 'Catman, 'Ratman')

I.Title LIKE '__man' (two underscores)
will return anything that starts with two characters and ends with 'tman' (i.e. 'Batman', 'Catman', 'Hitman', 'Ratman')

I.Title LIKE '______' (six underscores)
will return anything with 6 characters (i.e. '10 Gen','Action', Batman', 'Zordon', etc.)

(There are also some NOT versions, like NOT BETWEEN, NOT IN, and IS NOT but NOT logic is best left to Expert Advanced users (or insane ones...). Having said that, they can be useful on occasion so I am at least mentioning them)

Boolean Logic Expressions:

The Where clause uses Boolean Logic Expressions. Besides the more familiar operators (<, >, =, etc.), it also includes AND, OR, and NOT.
They are used to concatenate single comparisons into more complex comparisons. For the Where clause, the comparisons have to end up as TRUE to return rows.
With AND, all comparisons have to be True for the Where clause to evaluate as True and return a row.
With OR, at lest one comparison has to be True for the Where clause to evaluate as True and return a row.

Here is a list to show how this works (the values True and False are being used as the comparisons to emulate the result of comparisons):

AND- All have to be True:
'True'  AND 'True'  evaluates to TRUE , row returned.
'True'  AND 'False' evaluates to FALSE, row NOT returned.
'False' AND 'True'  evaluates to FALSE, row NOT returned.
'False' AND 'False' evaluates to FALSE, row NOT returned.

OR- At least One has to be True:
'True'  OR 'True'  evaluates to TRUE , row returned.
'True'  OR 'False' evaluates to TRUE , row returned.
'False' OR 'True'  evaluates to TRUE , row returned.
'False' OR 'False' evaluates to FALSE, row NOT returned.

This is also the case for more that two comparisons:

'True' AND 'True' AND 'True'  evaluates to TRUE,  row returned.
'True' AND 'True' AND 'False' evaluates to FALSE, row NOT returned

'True'  OR 'False' OR 'True'  evaluates to TRUE,  row returned.
'False' OR 'False' OR 'False' evaluates to FALSE, row NOT returned.

You can use both AND and OR in the same Where clause but you need to be aware of the Order of Precedence (the order the expressions are evaluated in).
AND is processed before OR. To make it easier on you, it is best to use Parenthesis to control the order the comparisons are done.
Consider this:

I.Title = 'Batman' OR  I.IssueNum = 2 AND I.Printing = 2  OR I.Variation = 'HC'
I.Title = 'Batman' OR (I.IssueNum = 2 AND I.Printing = 2) OR I.Variation = 'HC'

At first glance, the first one is hard to tell what is going to happen. The second one, with parenthesis, is what it actually being done.
With the parenthesis, it is easier to see that it will return Any Batman rows, along with Any rows with IssueNum 2 & Printing 2, along with Any rows that have a Variation of HC.
While it is still a complex query, it should be easier to understand what will happen.

Here is another similar comparison where the only difference is the ANDs and ORs:

 I.Title = 'Batman' AND I.IssueNum = 2  OR  I.Printing = 2 AND I.Variation = 'HC'
(I.Title = 'Batman' AND I.IssueNum = 2) OR (I.Printing = 2 AND I.Variation = 'HC')

Again, while both will return the same rows, with the parenthesis, it is easier to see that this will return Any Batman with IssueNum 2, along with Any row that has Printing 2 and Variation HC.

********************************************

ORDER BY:

There isn't much to say about the Order By clause. It pretty much does what you would expect. Sorts the resutls as requested.
The one thing not obvious is you can control the direction of the ordering with ASC (the default value) or DESC.

I.Title ASC would sort A-Z and/or 0-100 (ASC is the default and you don't need to add it)
I.Title DESC would sort Z-A and/or 100-0

You can mix and match the use of ASC and DESC
I.Title ASC, I.IssueNum Desc
I.Title Desc, I.IssueNum Desc, I.Variation ASC

Keep in mind the Data types when doing sorts. Number and Dates sort as you would expect. Text sorts characters from left to Right. If you have what looks like numbers in a Text field, the sort order would be  1, 10, 100, 2, 20, 200, etc. It would not be 1, 2, 10, 20, 100, 200.

The following will sort the results the same way (or at least very similar) to the way CB sorts its display:

I.Title, I.IssueNum, I.ItemType, I.Variation, I.Printing

********************************************

[start 2022-01-14 addition]

DATA TYPES:

Strftime:

Strftime allows you to access a date in different ways. The basic syntax is:

strftime(format, column-name)

Some of the more useful formats are:

%m         month: 01-12
%d         day of month: 00
%w         day of week 0-6 with Sunday=0, Saturday=6
%Y         year: 0000-9999

NOTE: the format character is case-sensitive. Use upper/lower case as shown.

The Month format is probably the most useful as it can be used with both CoverDate and StreetDate.
While Day of Month and Day of Week can be used with either, most CoverDate Day values are 1 (excepting items put out multiple times a month) and wont' really get you useful results.

While the Year function does work, it is not as efficient as using BETWEEN:

strftime('%Y', I.CoverDate) = '2002' takes 23 seconds to process
I.CoverDate BETWEEN '2022-01-01' and '2022-12-31' takes 1.75 seconds to process

You can also combine this with the BETWEEN function to limit the year ranges for a result.

Some examples:

Find all items with a day of '15':

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

Find all items with a day of '15' for years 2000-2009:

strftime('%d', I.StreetDate) = '15' AND I.CoverDate BETWEEN '2000-01-01' AND '2009-12-31'

Find all items with a day of Wednesday':

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

[end 2022-01-14 addition]

********************************************

RANDOM NOTES:

What is Item # and why not to use it in Order By:

While Item # is the displayed value, it isn't the best field to do Finds with, in most cases.
Item # is composed of 4 other columns. They are, in order, ItemType, IssueNum, Variation, and Printing.

Item # 1/HC-2 is composed of:
ItemType: none used (or regular issue but don't try to find regular issue in ItemType as it is only a display item)
IssueNum: 1
Variation: HC
Printing: 2

If you try to use Item # in the Order by, it will sort all ItemTypes first. Also, Item # is a Text field and NOT a numeric. You will also get 1, 10, 100-109, 11, 110-119, 12, 120-...

This is why it is better to use the various components of the Item # instead of Item # itself when sorting to match CB order:

I.Title, I.IssueNum, I.ItemType, I.Variation, I.Printing

======

Use of [] in Column names:

Some of you may be curious what the brackets ( [] ) around column names are for (and why I don't use them).
If the creator of a Table use a column name with a space (i.e. 'Issue Number') then it is required to enclose it in brackets ( [Issue Number] ) when referencing it so the database sees it has a 'single' name. If there is no space (i.e. IssueNumber), then the brackets are optional. Since the SQL processor adding the columns doesn't know if there are spaces or it, it defaults using brackets, just in case. It doesn't hurt to have them and not need them.
However, to me, they just clutter up the display of the query (the less I have to look at and ignore, the better) so I never type them in when I write a query.
A similar point could be made for parenthesis. Most SQL processors put way too many, unnecessary parenthesis in their statement. Don't get me wrong. As I showed above, parenthesis can (and sometimes must) be used to make the query either easier to understand or do what you want. But the over-use of them can make for a cluttered query. (Okay, my pet peeve part of this manual is over (for now...))

======

[end 2021-12-30 addition to cover different Types and Columns]

How to use Publisher Title columns in Advanced Find:

One thing you may notice, is that only ISSUE columns are available in Advanced Find in the drop-down box. In the old days (pre-CB 2020) there used to be "I" (Issue tables) and "T" (Title tables) where you could access Publisher in Advanced Find. The "I" and "T" were qualifiers to indicate which table a column is in.
---
Quick aside (geek alert)... The "I" in I.Title is the qualifier (or identifier) of a Table as defined in a From clause (since the From clause has not been displayed, you can see it directly). It is only really needed if you have more than one Table in the From and you Join them together (i.e. Issue table and Title table). The syntax would look like this:

From Issue_Table I inner join Title_Table T on I.Title = T.Title

The only thing you need to get from this is that the column Title is in both Tables. Because of this it needs to be qualified when reference (i.e. I.Title for the one int the Title_Table)
---
Sadly, with CB 2020, only "I" table columns are (readily) available.
However, there is a second way to qualify a column and that is with the actual Table name.
So, while you can't select Publisher from the drop-down box, you CAN use it by qualifying it with the actual table name

like this:

ComicTitles.Publisher    = 'Marvel'
BookTitles.Publisher     = 'Ballantine'
MagazineTitles.Publisher = 'Time'

ComicTitles.[CustomCheck1]    IS TRUE
BookTitles.[CustomCheck1]     IS TRUE
MagazineTitles.[CustomCheck1] IS TRUE


NOTE:

This is a non-supported feature which may or may not work in future releases of CB.
(Maybe HC will make Title columns available in the Advanced Find in the future (hint, hint, hint, please...?)

[start 2021-12-30 addition to cover different Types and Columns]

  • Like 5
Link to comment
Share on other sites

  • 1 month later...
  • 11 months later...

I took a few stabs but never got anything to work in advanced, especially since I couldn't get a basic portion to work int he standard find.

FIND SERIES where COUNTRY CODE is or not "WHATEVER".   The title edit field for a series shows publisher country as "United States [image of flag]" but I can't make it work in search.  Tried US, USA, United State, MERICA, etc.

The below generic using "is not" and "list only my issues in stock" returns all my titles, if I say *is* for exact match I get nothing.    Looking for some working examples to make this work, so that I can then use advanced query to do similar fancier things.

image.png.88a4c00347863f139b68640e694193ef.png

Edited by Brian J. Stewart
Link to comment
Share on other sites

On 12/29/2022 at 10:42 AM, Steven L. Dasinger said:

The problem is the difference between what is actually stored in the table and what is displayed in the program.

The table has the abbreviation (i.e. US) while the display is 'United States'

PS In Advanced Find it would be (for Comic Books):

ComicTitles.CountryCode IS NOT 'US'

 

 

Thanks Steve, updated yours to the following to find non-USA books I owned without any hassles.

((ComicTitles.CountryCode IS NOT 'US') AND (I.QtyInStock > 0))

Link to comment
Share on other sites

  • 4 months later...

How would I access the values stored in the grading table to calculate the current price? I have set and locked my on prices and want to find issues that are less than a percentage of the current price. For example

I.[Price] < I.[ValueYear4] * .5

will find if my prices are less than 50% of the year4 NM value. So I need to replace the ValueYear4 with the condition adjusted value using the grading table multipliers.

Link to comment
Share on other sites

Greg, I am not sure if this gets you what you are looking for. It uses ValueYear4 to get the Current Price which is not accurate.
If you have Locked the Price, the actual value you want for that week is not stored in the database (I don't believe).
However, I checked a new database and all the Price and ValueYear4 matched in value (except for 2 which looked like they rounded to nearest whole dollar).

What this does is uses the Condition value in a CASE expression to convert to the Percent value then multiplies with the ValueYear4 to get the adjusted price per Condition.

 

I.Price < (I.ValueYear4 *
CASE I.Condition
  WHEN 'MT'     THEN 1.5 1.05
  WHEN 'NM'     THEN 1
  WHEN 'NM-'    THEN .9
  WHEN 'VF-NM'  THEN .8
  WHEN 'VF'     THEN .66
  WHEN 'FN'     THEN .33
  WHEN 'VG'     THEN .20
  WHEN 'G'      THEN .13
  WHEN 'FA'     THEN .08
  WHEN 'P'      THEN .02
END)
* .5

PS If you have other than the Standard Conditions (CGC for example), you will need to use Setup->Grading... to get the Text value (under Cond) and the Percent value (under % of NM) and add additional WHEN ... THEN ... statements.

Edited by Steven L. Dasinger
corret MT percent value.
  • Thanks 1
Link to comment
Share on other sites

Thanks, Steve. I'm just using the values to identify books that I may have underpriced and need to do some more investigation so this should be fine even if the year 4 value isn't exact. I was thinking there was maybe a table that I could use the condition as a key to find the multiplier value to make the calculation, like python has.

Link to comment
Share on other sites

I don't think they keep current value (Price) anywhere but in that field. When you Lock that field, the Price CB sends down in the update wont' be in the database.

If there is a separate table that holds Condition and it's multiplier, I don't know it.

The Case statement I supplied does the same thing as what it sounds like you are looking for. Generate the current Prince (based on ValueYear4) modified by the multiplier for each (standard) condition and compare it to the Price you have.

So, does this get what you need or is there something else?

Link to comment
Share on other sites

The case statement (after I added a qty in stock > 0) did exactly what I expected it to do. However, it's not as useful as I expected since the display of the year4 value is still the NM value, which makes comparison tedious.

Maybe what follows belongs in a different thread. My problem is coming from using the lock price in the price field to set my selling price. The documentation suggests there is a Selling Price field available but I can't seem to find that in the columns to display setup or the fields drop down in the advanced find in Professional. Does that field still exist? How does one allow the updates in the price field but keep set prices for the selling price?

Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
×
×
  • Create New...