Repository Advanced Searching - Identifying entries with Alpha characters

posted on June 1, 2022 • Show version history

We are attempting to do some repository cleanup. We have an ID field, lets call it Emp_ID, that should only contain numeric values. Unfortunately, the field was not set as a number type, rather a text type, so we have accumulated a large number of entries where things (such as addresses, emails, general sentences, etc.) have been input in that field.

What I am envisioning is a way to search in the repository, for use with Workflow later, to identify how many entries in the repository contain non-numeric values within the Emp_ID field. I am pretty certain this can be done with wildcards and/or other advanced search techniques. Has anyone done something similar and shed some light on a best practice or method?

0 0

replied on June 1, 2022 • Show version history

Hi there-

A couple of options. Yes, there is wildcard type logic you can use to look for letters in the field. Square brackets [ ] look for anything in the bracket, so you can put the range a-z within brackets and then put wildcards on either side to find any docs where that field has letters: *[a-z]*

You can put other things in there, too, like a space or a symbol. Note that if your concern is any non-numeric character it gets a bit more complicated.

For that case, you may want to query SQL directly. The fields are in the propval table and each field has a unique ID that you can see in the Admin Console.

Note that you should NOT update this directly. Instead, run the query in Workflow, iterate through all the rows, and use the tocid column with a Find Entry activity to interact with and update the document in question.

So for the query, if my field id is 19, I can run this query to get all of the documents that have things besides numbers.

SELECT *
  FROM [propval]
  WHERE prop_id = 19
	AND ISNUMERIC(str_val) = 0
	AND str_val NOT LIKE ''

2 0

replied on June 2, 2022

Thanks Pieter. Will give these two options a try and see which works best for us.

0 0

Discussion

Discussion

Repository Advanced Searching - Identifying entries with Alpha characters

Comments 2

Follow-Ups 0

Sign in to reply to this post.