FORUMS: list search recent posts

major problem; commas in our database

COW Forums : Square Box CatDV

<< PREVIOUS   •   VIEW ALL   •   PRINT   •   NEXT >>
Nigel Askew
major problem; commas in our database
on May 25, 2010 at 2:25:39 pm

Hi, we've been using Cat DV for a few months now, we have it set up on 4 machines all feeding into our database, and we have 4 people in who are tagging our footage with keywords. our problem is that a couple of them didn't get the hang of the comma/semi colon difference when they first started and have tagged quite a few clips with commas instead of semi colons. this means that instead of values like "blue" "red" "green" (separate) we have values like "blue, red, green" which is obviously not helpful. we could correct it all by hand but this would take a long time.

we've been told to use bulk edit, but this doesn't perform the function we need, it seems to replace the contents of a selected box with the contents of another box or a preselected value. i was hoping search and replace would do the job as you can specify a value to replace, rather than a whole field, i.e. search for , replace with ; but it doesn't seem to work like that.

i've tried this in many variations, using regular expressions etc, i was told that /n is a substitute for a comma as a separator of terms, but i haven't been able to use this successfully.

can anyone help?


Return to posts index

Rolf Howarth
Re: major problem; commas in our database
on May 25, 2010 at 3:43:03 pm

If you configure a user-defined field as a "multi-grouping" field then it can contain multiple keywords as separate items. When you view one of these fields in a single row the list of values is shown separated by a semicolon but internally it's actually stored as a newline separator.

Normally you don't need to know that and just use the CatDV user interface to add or remove keywords to a clip as required. If you need to "patch up the data" because it was entered incorrectly (and you don't want to fix each one manually!) you can do so using Tools > Search And Replace. Choose the field you want to edit, enter the old delimiter eg. ", " under Search For, and enter "\n" under Replace With. Don't enter the quotes but do enter a space after the comma if that's how the list is currently delimited. Check the "Use regular expressions", then press OK.

The ability to enter \n as the replacement was only added a couple of months ago so make sure you're using a recent version of CatDV (the current version is 8.1.2, see http://www.squarebox.co.uk/download1.html).



Return to posts index

Nigel Askew
Re: major problem; commas in our database
on May 25, 2010 at 4:19:20 pm

Hi Rolf,

Thanks very much for your message, I have previously tried what you suggested on 8.1.1 and it didn't work, and i've just downloaded 8.1.2 and it still doesn't seem to want to work.

Here's a picture showing what settings i'm using (there's a space after the comma) and the offending field with the commas in,

http://i47.tinypic.com/wj8f0g.png

let me know what you think,

thanks,


Return to posts index


Rolf Howarth
Re: major problem; commas in our database
on May 25, 2010 at 7:48:24 pm

Are you seeing this like 40s/n50s/nfeminine?

You need \n not /n



Return to posts index

Nigel Askew
Re: major problem; commas in our database
on May 26, 2010 at 10:58:45 am

"Are you seeing this like 40s/n50s/nfeminine?"

Sorry, i'm not sure what you mean, I'm seeing it in CatDV as it appears in the picture, with commas in it. That field (themes/trends/styles) has all sorts of different values in it, and sometimes they've been separated by commas.

the quickest way i've found of correcting the commas is to group by the field i'm checking for that catalogue (themes/trends... for example) and scanning down the list of terms until I see a value like '40s, 50s, feminine' which sticks out for it's length. then i go in, click on the star at the end of the text box, type each value seperately and tab down to the plus sign, then back up for the next one. once they're all in i remove the original value. as you can imagine this is quite time consuming.

i was hoping i would be able to use the settings in the picture i sent to replace the comma with a semi colon or a \n to seperate the values that have already been entered.


Return to posts index

Nigel Askew
Re: major problem; commas in our database
on May 26, 2010 at 11:12:08 am

Sorry, it's early, i understand what you mean now

i had tried both /n and \n and left the incorrect one in for the screen shot, but i tried both

it doesn't seem to want to find ', ' at all, it said '0 clip(s) updated' and didn't put the incorrect '/n' in when i tried that, as you imagine it would

i don't know why it's not recognising the comma, it's the same whether i have regular expressions turned on or off,

maybe you could make a new catalog where you are, enter some values with some commas in and attempt to search and replace them if you have time, if not don't worry, it would just help to know i'm not missing something obvious


Return to posts index


Rolf Howarth
Re: major problem; commas in our database
on May 26, 2010 at 10:37:50 pm

Hmm, difficult to know what's going on then, as it worked in my tests. Maybe your current delimiter isn't a comma followed by a space (it could be some other non-printing character). Try copying and pasting the delimiter from the field into the search and replace dialog, and make sure there are no spurious extra characters in your 'search for' field. You could also try doing some simpler search-and-replace commands as a test, eg. replacing one word or one letter with another one to see if that works.



Return to posts index

Nigel Askew
Re: major problem; commas in our database
on May 27, 2010 at 10:58:17 am

I FIGURED IT OUT :D

i did a search and replace ON the semi-colon character ;

it worked perfectly, goodness knows why my copy works like this, but i've managed it, so i'm pretty happy now, that's going to save me hours of work.

i appreciate your patient help, i know how frustrating remote support can be, thanks very much


Return to posts index

Rolf Howarth
Re: major problem; commas in our database
on May 27, 2010 at 12:57:30 pm

Ah, that makes sense. Remember how I said the different values of a multi-field are stored internally separated with a new line character but displayed with a semicolon when they're shown one line? Because of that, it would be very confusing if any of the values themselves contained a semicolon, so any semicolons are turned into commas before they are displayed.



Return to posts index


Nigel Askew
Re: major problem; commas in our database
on May 27, 2010 at 1:33:41 pm

cool, well thanks again, this is going to save me so much time, and perhaps if someone else comes to you with the same problem it will help them too :)


Return to posts index

bryson jones
Re: major problem; commas in our database
on May 25, 2010 at 3:54:44 pm

Hey there, you need a MySQL wizard to actually edit that stuff deep in the db.

Rolf or Kevin, if you have time to offer that service, let us know.

If not, I can hook you up with someone who could likely clean that up for you. Buzz me offline and we'll see what we can do.

bryson

bryson "at" hidefcowboy.com

hidefcowboy.com


Return to posts index

bryson jones
Re: major problem; commas in our database
on May 25, 2010 at 5:13:12 pm

Sorry guys, my reader wasn't up to date and I didn't see that rolf was on it!

Keep us posted on that.

bryson

bryson "at" hidefcowboy.com

hidefcowboy.com


Return to posts index


Bob Zelin
Re: newbie question; commas in our database
on May 29, 2010 at 3:58:57 pm

Hi -
pardon my ignorance - could you please explain the difference between commas and semicolons. I was unaware that users should be using semicolons to seperate keywords in notes, instead of commas.

Thanks -
Bob Zelin




Return to posts index

bryson jones
Re: newbie question; commas in our database
on May 29, 2010 at 4:03:22 pm

Hey Bob,


This was in the "multi" fields where the system is putting in the separators between multiple keywords out of the pull down list. You just don't want silly humans typing in a different separator than the system. That's what caused all of this, mixing separators which is bad for database systems.

There are no bad questions, I have a few I need to ask right now too. lol

bryson

bryson "at" hidefcowboy.com

hidefcowboy.com


Return to posts index

Rolf Howarth
Re: newbie question; commas in our database
on May 29, 2010 at 4:57:56 pm

This was a fairly technical thread about how to recover from misformatted data, which was entered while CatDV wasn't configured properly. Normally you don't have to worry about commas or semicolons!

It relates to user-defined "multi-grouping" fields, which can be used used to enter keywords:

In a normal "grouping" field (perhaps one called "Weather") you can define picklist values such as "Sunny", "Overcast", "Rain", "Stormy", "Windy", "Night" and then when you're logging you can choose one of these values from a drop down. You can also type a few letters and have it auto-complete, or press the up and down arrows to step through matching values until you see the one you want.

If you want the field to be able to take multiple values instead of a single value make it a multi-grouping field. To edit a multi-grouping field click on the '*' alongside the field and it will bring up a chooser where you can see all the available keyword values and the ones which have been selected. If you tag a clip with both "Rain" and "Stormy" that will be shown as "Rain; Stormy" but the semicolon is just displated as a shortcut so they can be shown on one line, it's not part of the data.

The problem was what happens if the field was originally defined as a plain text field and someone enters a semicolon, then changes the definition so it's multi-grouping and needs to fix the data so CatDV knows that they are really separate values.



Return to posts index

<< PREVIOUS   •   VIEW ALL   •   PRINT   •   NEXT >>
© 2017 CreativeCOW.net All Rights Reserved
[TOP]