| Author |
 Topic  |
|
|
immortalthor
343 Posts |
 Posted - 09/21/2009 : 13:26:48
|
| Is there a way to run a report or even do a find on all issues I have in stock that are missing cover images? |
|
|
Stealth_DBA
USA
2351 Posts |
Posted - 09/21/2009 : 13:40:01
|
immortalthor, It depends on what edition of CB you are using. If you have Pro or Archive, you can use the Advanced Find. I don't think you can do the search without it.
With Advanced Find, the columns you may use are:
I.PictureFileSize
I.PictureHeight
I.PictureWidth
I.PictureModified
Using some (or all) of these, you can find Issues without Pictures or Issues with only 'Low Res' pictures. |
CB Archive Edition V16 Windows 7 64 bit |
 |
|
|
immortalthor
343 Posts |
Posted - 09/21/2009 : 16:06:59
|
| How do I use it though? I don't understand what I'm doing on it. And I'm using the Archive edition. |
|
|
|
Stealth_DBA
USA
2351 Posts |
Posted - 09/21/2009 : 16:36:04
|
immortalthor, I just wanted to make sure you could do this before going into details.
Click on the Magnifying glass in top right corner, use Menu item Comics -> Find Comics..., or use CNTL-F to start Find.
Click on the Drop Down Find button (top left) and select Advanced Find.
Type in the following:
SELECT: I.TITLE, I.FULLISSUE, I.PICTUREHIEGHT, I.PICTUREWIDTH, I.PICTUREFILESIZE, I.PICTUREMODIFIED
WHERE: I.[QUANTITY IN STOCK] > 0 AND I.PICTUREFILESIZE IS NULL
ORDER BY: I.TITLE, I.COMICTYPE, I.ISSUENUM, I.VARIATION, I.PRINTING
Then press the FIND button (top right corner).
This will show the Title, FullIssue number, and the various Picture field values (you don't have to show them all. I just did that to give you the option.) for every issue you own which does not have a value (IS NULL) in the PictureFileSize field. The ORDER BY sorts the output to match what CB uses.
Here is a more complex variation that will find issues with missing covers OR Low Resolution (small) covers. The values you use for the comparison can be changed to match you concept of Low or High Resolution.
SELECT: I.TITLE, I.FULLISSUE, I.PICTUREHEIGHT, I.PICTUREWIDTH
WHERE: I.[QUANTITY IN STOCK] > 0 AND ( (I.PICTUREFILESIZE IS NULL) OR (I.PICTUREWIDTH > I.PICTUREHEIGHT AND I.PICTUREWIDTH < 1000) OR (I.PICTUREWIDTH <= I.IPCTUREHEIGHT AND I.PICTUREHEIGHT < 1000) )
ORDER BY: I.TITLE, I.COMICTYPE, I.ISSUENUM, I.VARIATION, I.PRINTING
This does basically the same check of only issue you own AND either
PictureFileSize IS NULL (No Cover) OR
PictureWidth is Greater Than PictureHeight (think wrap around cover) AND the PictureWidth is Less Than 1000 pixels OR
PictureWidth is Less Than or Equal to PictureHeight (standard cover) AND the PictureHeight is Less than 1000 pixels.
You can change the 1000 in either or both checks is you have a different break point between a Low Res and a High Res cover. |
CB Archive Edition V16 Windows 7 64 bit |
|
|
|
immortalthor
343 Posts |
Posted - 09/21/2009 : 18:11:57
|
Okay, cool. I've got it somewhat going, but the results are flaky. It's finding issues that I have and there IS a cover, some issues that DON'T have a cover, and it's missing some issues entirely that I have in stock and DON'T have a cover. Here's what I've got entered.
Select:
I.[Title], I.[FullIssue], I.[PictureHeight], I.[PictureWidth], I.[PictureFileSize], I.[PictureModified]
Where:
I.[Quantity in Stock]>0 and I.[PictureFileSize] is null
Order:
I.[Title], I.[ComicType], I.[IssueNum], I.[Variation], I.[Printing]
is there something out of order, or worded wrong. If I'm also looking for things like TPB's and 2nd and 3rd printings, etc, would anything change? I'm not really into the programming thing, sorry...  |
Edited by - immortalthor on 09/21/2009 18:49:18 |
|
|
|
Stealth_DBA
USA
2351 Posts |
Posted - 09/21/2009 : 18:58:16
|
immortalthor, there is nothing to be sorry about.
Most of the problems you are experiencing appear to be related to the way CB processes. I just entered a bunch of Issues and have not displayed those Titles since they were entered. When I ran the same query you have, it also returned Issues which did have Covers. This is because the database (for some reason) had NOT updated the various 'Picture Fields'. When I clicked on the Issues and displayed the Title, the 'Picture Fields' (for the Issues in that Title) wwere updated. When I ran the Find again, these Issues did NOT show up the second time.
I can only assume, when the Weekly Update downloads covers, it doesn't update the database 'Picture Fields' (I would have thought it did). The only solution I can think of is to visit each of those Titles to get the 'Picture Fields' populated with values. If you don't have a lot of them you can run the find and then double click on each issue (only need to do this once per Title if you have multiple Issues from a Title listed) and then run the Find again.
I do not understand why it is NOT showing the other Issues you own but are missing covers.
Try this. In the SELECT, type in I.* (and nothing else). This is an SQL short cut for ALL columns in a table. In the WHERE type in I.[QUANTITY IN STOCK] > 0 AND I.TITLE = 'title-name' (where title-name is the name of a Title that has Issues without covers that are NOT being displayed by the Missing Cover Find).
When you have this result, find the Issue(s) without covers and scroll right to find the Picture Fields and see what is in them. If there are values (and no Covers) then there may have been a cover at one time but the values were not removed.
I do not have this situation in my database (as far as I can tell). Every Issues that is missing an Issues is listed in the Find result (along with some that did but did not have values in the 'Picture Fields'). |
CB Archive Edition V16 Windows 7 64 bit |
|
|
|
immortalthor
343 Posts |
Posted - 09/22/2009 : 04:06:25
|
Cool, thanks Stealth!!
After playing with it a little more, I discovered something of a quirk. If I change
I.[Quantity in Stock]>0 and I.[PictureFileSize] is null
to
I.[Quantity in Stock]>0 and I.[PictureFileSize]=0
I get different results. It seems like one gives the first half of the list, and the other gives the last half. In the words of Ace Ventura -- "Alrighty, then!" |
Edited by - immortalthor on 09/22/2009 04:28:12 |
|
|
|
sternber
327 Posts |
Posted - 09/22/2009 : 10:17:53
|
immortalthor, I'm not up on SQL (but I know Stealth is). You may be able to restructure your query do something like:
I.[Quantity in Stock]>0 AND (I.[PictureFileSize]=0 OR I.[PictureFileSize] is null)
Then you'd get both "halves" of your list.
Hope this helps
|
Thanks,
Adam |
|
|
|
immortalthor
343 Posts |
Posted - 09/22/2009 : 17:00:40
|
| That got it! Thanks guys! |
|
|
|
threeDs
USA
67 Posts |
Posted - 04/11/2012 : 04:18:19
|
The advanced find query is exactly what I needed. I wanted to find all the issues (in-stock) that are missing covers.
Select:
I.TITLE, I.FULLISSUE, I.PICTUREHEIGHT, I.PICTUREWIDTH, I.[PictureFileSize]
Where:
I.[Quantity in Stock]>0 AND (I.[PictureFileSize]=0 OR I.[PictureFileSize] is null)
Order:
I.TITLE, I.COMICTYPE, I.ISSUENUM, I.VARIATION, I.PRINTING
What I noticed is the report has issues with no covers but also issues with the small image icon. Does anyone know why the issues with a small image are included? The issue picture parameters agree with the Find query. I would expect some value for the small images.
Thanks,
Dennis |
CB Blu-Ray Archive Edition v16.0 / Windows 7 64-bit
Check out Kickstarter and support up-and-coming writers / artists! |
|
|
|
Stealth_DBA
USA
2351 Posts |
Posted - 04/11/2012 : 04:30:32
|
threeDs, It is possible some of the Picture info columns are not populated with values (not sure why they are not). At a guess the Weekly update Picture downloads may not be updating the fields
Before running you query, you can try using Menu item File->File Tools, click on Rebuild Lists, check Issue Information AND Rebuild Pictures List. And this is a slow process as it indicates.
After that, try your query again and see what happens.
PS I ran the query and even Large images are not showing up, so it doesn't appear to be related to the size of the cover scan. |
CB Archive Edition V16 Windows 7 64 bit |
|
|
|
threeDs
USA
67 Posts |
Posted - 04/11/2012 : 17:11:50
|
Stealth_DBA, rebuilding the lists worked. The advanced find reports make sense now. With the advanced find along with the cover scan knowledge, I am almost dangerous.
Thanks again. |
CB Blu-Ray Archive Edition v16.0 / Windows 7 64-bit
Check out Kickstarter and support up-and-coming writers / artists! |
|
|
| |
Topic |
|
|
|
Advanced Find/Report
