Search for label not in entry Request

Codebook
1

Is it possible that one might do a search that will return results for a NOT query?
I have a Label called “Validated”. I have a large number of entries and I am trying to validate that everything is correct in my data. I can’t find a way to find entries that do not contain the “Validated” label. It would be nice to be able to do a search for entries that the “Validated” label is not in.

Hope that’s clear

2

Hey @lwetzel

Thanks for using Codebook and posting to the discussion forum with this question.

It isn’t currently possible to perform advanced search queries (such as NOT) directly within the Codebook search bar, although improving the search capability to be more flexible and include some of these advanced query capabilities is something that we have considered in the past and may be nice to include in Codebook in the future.

One recommendation I do have which would work currently is using a program called DB Browser for SQLite to open and query your encrypted Codebook database.

Disclaimer: Don’t modify your Codebook database outside of the Codebook interface as changes will not be picked up by Codebook’s tracking mechanism and won’t propagate to other copies of Codebook when syncing. The query in these steps only reads from the encrypted Codebook database which is safe to do

Here are the steps:

  1. Download the latest version of DB Browser for SQLite using the Download tab from the page linked above. Install it on your system.
  2. Launch DB Browser for SQLite and access File menu > Open Database, open your strip.db encrypted Codebook database file at the location [1] – Enter your Codebook Master Password when prompted.
  3. Once your encrypted database opens, select the Execute SQL tab near the top of the window. Paste this query in the text box and click the “play button” to execute the query:

This query is incorrect, see below for the corrected query: Search for label not in entry Request - #7 by mmoore

SELECT DISTINCT(name) FROM entries e
INNER JOIN fields f ON f.entry_id = e.id
WHERE f.type_id <> (SELECT id FROM types WHERE name = 'Validated')
  1. This will provide you with a list of Entry names which don’t have a Validated label present. If you’d prefer you can export the results from he query within DB Browser for SQLite to a CSV by following the steps here: Save query results as csv - #3 by justinclift

[1] Codebook encrypted database locations:

Windows:

%APPDATA%\Zetetic LLC\Strip\strip.db

macOS:

~/Library/Group Containers/PD7G6HRMGV.net.zetetic.STRIP/Strip/strip.db

1 Like
3

Thanks for the reply Micha.

Does the query return records that contain the field ‘Validated’? If so how do I change it to return only records that don’t have the field ‘Validated’ at this point?

4

The query above will return the Entry names which do not have a field which uses the Validated label.

5

It is returning a mixture of both those that do and those that don’t.

6

@lwetzel

A couple of additional questions:

  • Do you have more than one label with the name “Validated” in your Codebook database?
  • Can you re-create a test Entry structure with sample data (and screen shot it) which is improperly returning in the query?

Edit: Apologies, let me modify the query, I was just able to reproduce it. Give me another minute.

7

@lwetzel

Can you give this query a try:

SELECT e.name FROM entries e WHERE e.type != '1' AND e.id NOT IN
(SELECT f.entry_id FROM fields f WHERE f.type_id = (SELECT id FROM types t  WHERE t.name = 'Validated'))

I believe that should do it. Let me know the results.

1 Like
8

Yes. That looks like a better return. Those I checked were indeed the records I need.

Thanks

9

Excellent. Glad I could help and sorry for the misstep there on the initial query. We’ll continue to discuss/consider improvements to the search mechanism within Codebook itself for the future. In the mean time, feel free to reach out again if you have any other questions or feedback.

10

Hello, again Micah,

Ok so just to take this a step further what would be the query that would get me results that show that the names of entries where the field with “Validated” label does not contain “yes”?

11

Hey @lwetzel

No problem. This adjustment to the query should do it I believe:

SELECT e.name FROM entries e WHERE e.type != '1' AND e.id NOT IN
(SELECT f.entry_id FROM fields f WHERE f.type_id = (SELECT id FROM types t  WHERE t.name = 'Validated') AND f.value = 'yes' COLLATE NOCASE)

This will return entry names where one of the following is true:

  1. They have no field present with a Validated label.
  2. They have a field with a Validated label, but no value in it.
  3. They have a field with a Validated label with a value, but the value isn’t yes (case insensitive)

Let me know if that does the trick. Thanks!

12

Yes Micah,

The query is working fine. Wish I know the Query language so that I could break each out separately but, it is finding what I need.

Thanks a bunch.