NBN Forum / Search on Location Description

charliebarnes · 2010-07-23 16:43:17

Is there anyway to search on the text in the Location Description?

e.g.

Code:

WHERE LOCATION_DESCRIPTION LIKE 'Village'

This doesn't seem to work - presumably because it's a memo field?

MikeWeideli · 2010-07-24 18:09:54

SQL does allow 'Like' to be used on text fields so SELECT * FROM LOCATION WHERE LOCATION.DESCRIPTION LIKE('%Watsonian%') will work

Mike

charliebarnes · 2010-07-29 11:30:46

Hmm, doesn't appear to be working for me.

RobLarge · 2010-07-29 12:33:04

Works fine for me, although for queries in the Access database you need to use the wildcard * instead of %.

However, in your example above Charlie you have LOCATION_DESCRIPTION, whereas the correct syntax is LOCATION.DESCRIPTION and because you are using no wildcard your query will only return locations where the description is Village (or village etc.) with no other text in the field.

charliebarnes · 2010-07-29 13:03:34

Whoops, I meant Location.Description

We have quite a few locations with "Village" as the description that I wanted to pull out.

This is the SQL I'm using (which doesn't do what I expect):

Code:

SELECT LOCATION_KEY FROM LOCATION
WHERE LOCATION.DESCRIPTION LIKE 'Village'

Whereas this does (and gives me more than I want):

Code:

SELECT LOCATION_KEY FROM LOCATION
WHERE LOCATION.DESCRIPTION LIKE '%Village%'

But this does seem to work:

Code:

SELECT LOCATION_KEY FROM LOCATION
WHERE LOCATION.DESCRIPTION LIKE 'Watsonian Vice-county'

I'm wondering if there are some RTF characters in their or something?

RobLarge · 2010-07-29 13:52:36

If all the litems you want only have 'Village' as the description, why not use

WHERE LOCATION.DESCRIPTION = 'Village'

charliebarnes · 2010-07-29 15:42:05

Because

the data types text and varchar are incompatible in the equal to operator

RobLarge · 2010-07-29 16:25:18

Ah, that would make sense.

How about

WHERE LOCATION.DESCRIPTION LIKE 'Village%'

Which should only return those items where the description begins with Village.

RobLarge · 2010-07-29 16:29:52

Here's a suggestion, once you have a suitable selection, why not modify the location type term list to include an item called Village and set each of your locations' types to that value, then you should be able to filter them properly.

charliebarnes · 2010-07-29 16:34:14

WHERE LOCATION.DESCRIPTION LIKE 'Village%'

Something even weirder happens... it only finds one location, but clicking on that location or using the go to addin with the location key brings up a blank location window

Last edited by charliebarnes (2010-07-29 16:35:40)

charliebarnes · 2010-07-29 16:35:17

RobLarge wrote:
Here's a suggestion, once you have a suitable selection, why not modify the location type term list to include an item called Village and set each of your locations' types to that value, then you should be able to filter them properly.

Thats why I'm trying to filter them

charlesr · 2010-07-30 17:28:27

Code:

select * from LOCATION where cast(DESCRIPTION as varchar(max)) = 'Village'

RobLarge · 2010-08-02 14:35:37

Ah that would do it

charliebarnes · 2010-08-02 15:46:00

Its the RTF tags. This is what an export of one location description looks like:

Code:

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}{\f1\fnil MS Sans Serif;}}
{\colortbl ;\red0\green0\blue0;}
\viewkind4\uc1\pard\cf1\lang2057\f0\fs16 village\lang1033\f1 
\par }

Whereas Mikes suggestion of which uses Watsonian works because this is the location description:

Code:

Watsonian Vice-county

Removing the RTF tags from the decription field works. Thanks Matt!

charlesr · 2010-08-02 16:40:07

Ah, those RTF codes are pain. For anyone reading this, just in case you haven't come across this before, there's a function built into NBNData that strips RTF tags and leaves just plain text. It's called ufn_RtfToPlaintext.

I wouldn't mind if RTF formatting was removed completely from comments boxes. Does anyone actually use or rely on the RTF features?

charliebarnes · 2010-08-03 09:18:01

charlesr wrote:
I wouldn't mind if RTF formatting was removed completely from comments boxes.

Crossed my mind too.

RobLarge · 2010-08-03 10:00:38

One thing I have noticed with the RTF coding, is that if you copy some text from a document, which is in a 12 point font say, then paste into a recorder description field, the result is very large and if there's a lot of text, the result is a lot of scrolling needed to read it. I find I have to remember to convert any text into an 8 point in order to get a readable result on screen. It would be nice if this was possible within recorder, from the right-click menu, as are the bold & italic functions (which I must admit I use quite a lot in descriptive text).

So I agree that there is an issue with the rtf in description fields, but it is worth saying that in general terms it is not really good practise to be filtering on the contents of a free-text field, as it is too prone to errors. In most cases in Recorder I have found ways of using an existing field or co-opting one to suit my purposes.

charliebarnes · 2010-08-03 11:33:24

RobLarge wrote:
In most cases in Recorder I have found ways of using an existing field or co-opting one to suit my purposes.

Hence me wanting to filter out on location description so I don't have to change each one individually

Perhaps an option to remove formatting within Recorder would be useful, or only use it when specifically requested (Recorder adds RTF even when no bold, italic etc is used)

MikeWeideli · 2010-08-03 13:13:19

Some people do take a lot of care to format the comment fields. It doesn't do any harm to remove the formatting from your own data or from system supplied data, but it isn't perhaps a good idea to remove it from imported data just in case it is ever passed on. In XML reports you can remove the RTF and CR/LF easily enough, although you do need to install the NBN Export addin in order to get the UDF to remove CR/LF. Having new fields in the report Wizard which removed the formatting would be very useful and easy to do so perhaps this could be considered for the next release.

Mike

charliebarnes · 2010-08-03 14:19:37

MikeWeideli wrote:
but it isn't perhaps a good idea to remove it from imported data just in case it is ever passed on.

Wouldn't the rules of custiodianship come into force?

mbeard · 2010-08-04 10:33:09

charliebarnes wrote:
Wouldn't the rules of custiodianship come into force?

These rules sound interesting. Are they published anywhere?

Cheers

Mike

sallyrankin · 2010-08-05 08:17:25

There is information on custody/ownership in the Recorder 6 Help – see Help – Contents tab – Tasks – Exchanging data – Transfer of ownership. More information can also be found using the Search tab to search for ‘Custodian’ and ‘Custody’.

If you develop batch updates to do things like remove RTF you need to remember that they do what you write them to do. They won’t automatically obey Recorder’s rules. You have to write them to obey the rules, so they are dangerous unless authors know the rules and incorporate them.

Announcement

#1 2010-07-23 16:43:17

Search on Location Description

Code:

#2 2010-07-24 18:09:54

Re: Search on Location Description

#3 2010-07-29 11:30:46

Re: Search on Location Description

#4 2010-07-29 12:33:04

Re: Search on Location Description

#5 2010-07-29 13:03:34

Re: Search on Location Description

Code:

Code:

Code:

#6 2010-07-29 13:52:36

Re: Search on Location Description

#7 2010-07-29 15:42:05

Re: Search on Location Description

#8 2010-07-29 16:25:18

Re: Search on Location Description

#9 2010-07-29 16:29:52

Re: Search on Location Description

#10 2010-07-29 16:34:14

Re: Search on Location Description

#11 2010-07-29 16:35:17

Re: Search on Location Description

RobLarge wrote:

#12 2010-07-30 17:28:27

Re: Search on Location Description

Code:

#13 2010-08-02 14:35:37

Re: Search on Location Description

#14 2010-08-02 15:46:00

Re: Search on Location Description

Code:

Code:

#15 2010-08-02 16:40:07

Re: Search on Location Description

#16 2010-08-03 09:18:01

Re: Search on Location Description

charlesr wrote:

#17 2010-08-03 10:00:38

Re: Search on Location Description

#18 2010-08-03 11:33:24

Re: Search on Location Description

RobLarge wrote:

#19 2010-08-03 13:13:19

Re: Search on Location Description

#20 2010-08-03 14:19:37

Re: Search on Location Description

MikeWeideli wrote:

#21 2010-08-04 10:33:09

Re: Search on Location Description

charliebarnes wrote:

#22 2010-08-05 08:17:25

Re: Search on Location Description

Board footer