move data to another field?

[MikeB]

When I started using DVDPedia several years ago, there was no field for "location". I made a custom field and called it location, so I have it. Now I would like to move the data in the custom location field to the regular location field. Is there a way to do this automatically?

[sjk]

I think the only way to do this is by directly modifying the SQlite database (e.g. using sqlite3), similar to methods/examples in other topics (search for sqlite* to find 'em). Conor or Nora may be able to provide the specific commands; I'm a db dunce.

[Conor]

SJK is correct, since it's not a common occurrence there is no GUI interface yet to transfer an entire field to a new field, you need to issue an SQL command. Although it sounds daunting it will take no more than 2 minutes.

1. Quit DVDpedia.

2. In your Finder navigate to your database file in ~/Library/Application Support/DVDpedia/Database.dvdpd and select it and choose duplicate under the File menu simply for backup purposes.

3. In your Applications-->Utilities find the program called "Terminal" and start it up.

4. Copy the following lines and paste them into TextEdit and update "zCustom1" to the correct number of the custom field you want to move then copy paste the amended commands into the Terminal (you can count the number of the custom field left to right in the DVDpedia preferences):

Code: Select all

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd
update zEntry set zLocation = zCustom1 where zLocation is null;
.exit

5. Launch DVDpedia and enjoy. If things went well you can empty out your old custom field and reclaim it by selecting all your entries and using the multi edit, check the custom field and leave it blank.

[MikeB]

Thanks for the reply, both of you, but from the command you gave, Conor, will this delete the entries or move them? I currently have two fields both named "Location". I am trying to move the data from the custom field (named "Location") to the regular field named also "Location". Should I first rename the custom field to something else? Or have I misunderstood and the command you gave will work without renaming anything?

[Nora]

The zCustom1 (or whichever Custom field it is that you renamed to 'Location') will tell the program exactly which field it is, regardless of what you might have named it. The display name is kept in the preference file and the database only knows the field as zCustom1-10. So do make sure you pick the right number.

The command as Conor wrote it out will only overwrite the data in those fields where there is NO information, i.e. if one of your regular location fields already holds data, it will NOT overwrite that. If you want to overwrite all data regardless then you'd have to change the commands to:

Code: Select all

sqlite3 ~/Library/Application\ Support/DVDpedia/Database.dvdpd
update zEntry set zLocation = zCustom1;
.exit

If you want to check that you got the right custom number you can use the following command in between the first command "sqlite3 ~/Library/..." above and "update zEntry set".

Code: Select all

select zCustom1 from zEntry;

Will print out a long list of the values in custom1.

[MikeB]

Thanks both of you for the help. It took me twice to get the command to work because I had forgotten that the "real" database file is in my Shared fold, not in my home folder. But, once I changed the path in the command, it worked fine.

One reason I wanted to do this was to add the location field when I do a text export of the database (I put it on my iPhone). I use the MySmallList template.