Gregory Hecht Posted August 19, 2020 Share Posted August 19, 2020 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? Link to comment Share on other sites More sharing options...
Scott Mahan Posted August 29, 2020 Share Posted August 29, 2020 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. Link to comment Share on other sites More sharing options...
Troy E. Charlton Posted December 11, 2020 Share Posted December 11, 2020 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 2 Link to comment Share on other sites More sharing options...
Fred Slota Posted January 5, 2021 Share Posted January 5, 2021 Anyone want to supply an "Order By" syntax that sorts in ComicBook Order? Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted January 5, 2021 Share Posted January 5, 2021 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. Link to comment Share on other sites More sharing options...
Fred Slota Posted January 7, 2021 Share Posted January 7, 2021 Is the AlphabetizedTitle field still accessible to Advanced Find, as that will put the titles in the same order as in ComicBase? Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted January 7, 2021 Share Posted January 7, 2021 Not officially. but try this: ComicTitles.AlphabetizedTitle Hope it works. Link to comment Share on other sites More sharing options...
Fred Slota Posted January 7, 2021 Share Posted January 7, 2021 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] Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted January 7, 2021 Share Posted January 7, 2021 And, did it work as you expected? Link to comment Share on other sites More sharing options...
Fred Slota Posted January 7, 2021 Share Posted January 7, 2021 I believe so, which is why I posted a recipe that used it. Link to comment Share on other sites More sharing options...
Lance Fittro Posted August 15, 2021 Share Posted August 15, 2021 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. Link to comment Share on other sites More sharing options...
Walt Grogan Posted January 14, 2022 Share Posted January 14, 2022 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' Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted January 14, 2022 Share Posted January 14, 2022 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. 1 Link to comment Share on other sites More sharing options...
Walt Grogan Posted January 14, 2022 Share Posted January 14, 2022 (edited) 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, 2022 by Walt Grogan Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted January 14, 2022 Share Posted January 14, 2022 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. 1 Link to comment Share on other sites More sharing options...
Walt Grogan Posted January 14, 2022 Share Posted January 14, 2022 (edited) 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, 2022 by Walt Grogan Link to comment Share on other sites More sharing options...
Brian J. Stewart Posted October 18, 2022 Share Posted October 18, 2022 I did some casual searching but could not find a list of all the fields used in the database. Has data dictionary been published somewhere listing all the item/title field names and date types? I got here trying to find the "Reserve Quantity" field. I made educated guess for I.ReserveQuantity and I.ReserveQty but the adv.find did like them. Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted October 18, 2022 Share Posted October 18, 2022 I am not sure if this is the correct column or not but try: MinimumQty Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted October 18, 2022 Share Posted October 18, 2022 By the way, Reserve Qty is in the drop-down fields list. Link to comment Share on other sites More sharing options...
Brian J. Stewart Posted October 18, 2022 Share Posted October 18, 2022 Thanks Steve. MinimumQty did work to find the "Reserve Quantity" field. I am using that to help find my dupes, that have not yet been broken out for-sale. #using this advanced query (I.[ForSale] IS False) AND (I.QtyInStock > 1) AND (I.[Marked] IS True) AND (I.MinimumQty < 1) I.QtyInStock DESC,I.Title ASC This gives me a rough-and-dirty list of duplicate issues I have not flagged for sale. I then duplicate the found item showing QTY 2+ Now I mark the new item with the exact QTY I want for sale, so that it is kept in a separate row in my database. then the original row gets updated to lower quantity (my keeper copies) and I also set the "Reserve Quantity" to the same value. My extra safe-guard to not sell these, when i run some safety queries later. I do realize I could just flag the whole issue as for-sale, and then use the reserve quantity for sale --- but I tend to err on the side of caution to ensure I don't sell books by mistake. And this query does not help when I have (6) copies of a single book in varying grades, a good place for me to start. I'll have to scour the forums for some adv.find examples to help me find those where ISSUE has QTY > 1 spanning multiple grades later. ---BRIAN Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted October 18, 2022 Share Posted October 18, 2022 I will save you some time. There is not way (or at least not easy and even then probably not) to find multiple 'duplicate issues) that are enter entered as separate issues. The cut down version of the SQL that is the advanced find isn't really able to do that. I will give it some thought and see if I can figure out a way. PS The main reason is you would need to 'Group By' go consolidate those issues into one unique item. Advanced Find does not have a Group By. Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted October 18, 2022 Share Posted October 18, 2022 By the way, if you want to do a little work, and you want to be really safe, you could have 2 databases. You personal (not for sale) database and your For Sale Database. Link to comment Share on other sites More sharing options...
Steven L. Dasinger Posted October 18, 2022 Share Posted October 18, 2022 Well, that wasn't quite as hard as I thought. Try this (if you need to only look for 'For Sale' and/or 'Marked' those can be added. I.Title || I.ItemNumber IN ( (SELECT A.Title || A.ItemNumber FROM ComicIssues A WHERE A.QtyInStock>= 1 AND I.Title = A.Title and I.ItemNumber = A.ItemNumber GROUP BY A.Title, A.ItemNumber HAVING COUNT(*) > 1 ) ) Link to comment Share on other sites More sharing options...
Brian J. Stewart Posted October 19, 2022 Share Posted October 19, 2022 16 hours ago, Steven L. Dasinger said: Well, that wasn't quite as hard as I thought. Try this (if you need to only look for 'For Sale' and/or 'Marked' those can be added. I.Title || I.ItemNumber IN ( (SELECT A.Title || A.ItemNumber FROM ComicIssues A WHERE A.QtyInStock>= 1 AND I.Title = A.Title and I.ItemNumber = A.ItemNumber GROUP BY A.Title, A.ItemNumber HAVING COUNT(*) > 1 ) ) Worked great Steven, I just added a ORDER BY I.Title ASC. Came back with less than 1000 books which kind of surprised me, but I think is accurate. I can then tweak this further later on to eliminate items marked for-sale already, or my items set with reserve quanity protection. ---BRIAN Link to comment Share on other sites More sharing options...
Brian J. Stewart Posted October 28, 2022 Share Posted October 28, 2022 Is there a data column something like "last edited by user", something to indiciate that I have personally modified an issue's row of data? Use cases: 1. Build a list of titles/issues added into inventory for 2022. 2. When you notice your inventory count is off, because you have been adding new series and books into the system that were missing but forgot to set QTY 0 before you were done, and now can't figure out where those books are hiding in your collection. query possibly similar to: (I.LAST_USER_EDIT BETWEEN '2022-01-01' AND '2022-12-31') Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now