Question

Searching for Nulls

How To Search and Retrieval

Updated June 26, 2019

asked on May 28, 2019

Nulls, we all know are always fun (sic); I am trying to clear up some mass imported files where the employee_id has been left blank on inactive employee records.

I have read this post (https://answers.laserfiche.com/questions/53146/Empty-null-value-field-search) and so far it has not worked for me, and there are not too many other threads that refer to Null field entries during searches.

The 'Employee_Id' field is only on the distinct Salem_HumanResources template.

LF created the following query that is not working:

{LF:Name="*", Type="D"} & {LF:LOOKIN="\HR\INACTIVE"} & {[]:[Employee ID] = ""}

I have also modified it to look directly at entries that have the Salem_HumanResources template as well:

{LF:Name="*", Type="D"} & {LF:LOOKIN="\HR\INACTIVE"} & {[Salem_HoumanResources]:[Employee ID] = ""}

I have tried those queries and added a space between the " " at the end as mentioned in the linked post and that did nothing. Also tried trimming down the queries just to the final stanza {[Employee ID] = ""} and that returns nothing either.

I am only looking for documents, no shortcut links or folders.

We are on Rio 10.3, I am using desktop and web clients to try to run this.

Any thoughts???

Thanks

rob

0 0

Replies

replied on May 28, 2019

Hi Rob, I tested this using a blank 'Name' field on a template called 'Field Security'.

I could not create the query just using the Customize Search activities. But when I added the following to the Search Syntax and included that in the query, it worked. No other search activities were enabled; just Search Syntax.

{[Field Security]:[Name]=""}

0 0

replied on May 29, 2019

urgh....Thank you Peter, but that (with my template and field entered) returned no results. I added a space between the quotations and it still returned nothing.

I tried {[Salem_HumanResources]:[Employee ID] <> ""} and that returned all of my employees that have an entry, but no records that are blank. So,I know that the template and field name is correct....

Did a little test that proved to be interesting.

I removed the Employee Id from known record and then ran {[Salem_HumanResources]:[Employee ID] = ""} and that 1 record that I just removed the Employee ID from popped up.

It seems that the entries that have no entry in that field, in the specific template are not even recognized as having that field? Should that entry show up with a null field if it was not populated to begin with or only when it was populated and a value removed?

My issue almost seems to parallel this one: https://answers.laserfiche.com/questions/82607/Advanced-Search-for-empty-NULL-string-values-inconsistent I think that these records were imported in Rio 10.2 and we upped to Rio 10.3 a month or so later.....

0 0

replied on May 29, 2019

following the second linked thread I delved into SQL.

I queried the propval table with the entry_id (tocid) of the entry I removed the Employee ID from to identify which Prop_ID is my Employee ID field.

in this table, Prop_id 39 is my Employee ID field.

Query looking at the Propval table where prop_id is "39" returns all entries with values. There are no blanks or nulls returning on that query.

It looks like this template, and attached files may not be reflected correctly with all the template fields?

I know that I had discovered that due to multiple contacts at our VAR that some of our imports were imported slightly differently and so we ended up with multiple templates and fields for the same type of data entries, but that did not happen with the bulk import of our archived HR documents.

tl:dr: direct sql query shows that entries that do not have Employee Id are not NULL, or blank, field entries, but non-existent field entries instead.

0 0

replied on June 12, 2019 • Show version history

If I understand correctly, the documents you are looking for are actually missing the Employee ID field completely?

If they still have the Salem_HumanResources template, you could try the following:

{[Salem_HumanResources]} - {[]:[Employee ID] = "*"}

The second part, {[]:[Employee ID] = "*"}, specifies the field where any value is present with any or no template, so by excluding that criteria, you should be able to find documents where the field was not applied.

If the template is not applied either, you can substitute any other criteria that will include the documents you want to search on.

0 0

replied on June 4, 2019

Anyone? No one else has really come across this, or something like this?

0 0

replied on June 26, 2019

Hi Rob, I would strongly recommend trying to identify a single case (record) that you can use to clearly define what is or is not the case first. If the Employee ID field is not even associated with these entries, then any query comparing the value in that field will fail.

I think Brianna's query is the right approach, but it doesn't sound like we are sure that these documents are even on the Salem_HumanResources template at this point. Please see if you can identify at least one of these records either by other metadata or even perhaps by file create date. (Find one entry in the source data that was uploaded and look for the closest entry with a valid Employee ID value. Then look for everything created around that time [by the same user, if possible] and scroll for the faulty one.) Once we have one faulty record, we should be able to get a true assessment of how it loaded and create a search for the whole set.

2 0

You are not allowed to follow up in this post.

Question

Question

Searching for Nulls

Replies

Sign in to reply to this post.