[Solved] Preventing duplicate entry

Creating and using forms

[Solved] Preventing duplicate entry

Postby LJ Bettona » Wed Jan 09, 2008 12:39 pm

It's the newbie again.
To better understand what's going on in the database, I decided to create a library DB. I think I've got all the tables and relations right.
I have created a form for entering a book with drop downs linked to the genre and author tables. It seems to be OK.
I have also created forms for entering data on the author and genre tables. This is where the fun comes in.
When I am entering an author, a book title or a genre, I need to be able to verify that the data has not been entered twice, i.e. the author isn't already there.
I have tried to find a place where I am asked if I want this criterion for the field. I've edited the author table but don't seem to be able to locate an option for no duplicates in the name field.
I've tried messing with it in edit mode for the form - can't find such an option.
I have found a place (I don't remember where) for filters but there is no help with how to construct such a filter, nor is the help file helpful.
I have searched the help file, but only get validation criteria that one would use to ensure a social security number is of the correct length, etc.
I have searched the forum but 'duplicate entry' and 'verify' don't come up with anything like this.
Have I missed something in the database creation step? Should I have determined this criterion for the field at that time?
This is such a fundamental function of database entry that I cannot believe it isn't part of the table construction somewhere. Nor can I understand why it can't be modified on the fly.
Thanks for any help.
Last edited by LJ Bettona on Fri Jan 18, 2008 9:02 pm, edited 1 time in total.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Preventing duplicate entry

Postby DrewJensen » Wed Jan 09, 2008 2:53 pm

If you want to insure no duplicates in a field then the most accurate way is to create a unique index on that field.

Open a table definition editor ( from the Tables section, highlight the table name, right click, select edit )

In the table definition designer select Tools -> Index design

Now select the field you want and add an index, be sure to tick the box for unique.

OK - now there is still a problem of course:
"ted" and "Ted" are 2 different strings - case sensitive and all.

In the embedded Base database you can solve this also. You would need to create the field of type VARCHAR_CASEINSENSITIVE instead of the VARCHAR type.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Preventing duplicate entry

Postby LJ Bettona » Thu Jan 10, 2008 7:08 pm

Uh, Drew, what you didn't tell me was that I would lose all my data when I created the index. That's 156 author names down the drain. If that isn't what was supposed to happen, any ideas why it did? Otherwise, it was a piece of cake! I knew there had to be a simple way to do it.
Thanks
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Preventing duplicate entry

Postby DrewJensen » Thu Jan 10, 2008 7:13 pm

NO that is not supposed to happen - building an index should never lose data. The only error should be if you try to produce a unique index and there are duplicates that the create index fails and gives you this fact as an error message, so you can find and fix the data.

Can you give me the details on your environment - OS, and version of OO.o?

I know this doesn't help you.

 Edit: Just tried setting a unique index against a column I knew to have duplicate data and got the error I expected - no loss of data.
Went in - made the needed changes and created the unique index - no loss of any data.

This was with OO.o 2.3.1 and XP... 
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Preventing duplicate entry

Postby LJ Bettona » Thu Jan 10, 2008 7:43 pm

Bad news, Drew. I did what you said, but when I tried entering duplicate data, it was accepted.
When I created the index, there was already an index listed - SYS_IDX_117 which I assumed was the key field index.
When duplicate data was accepted, I went back to look again at the indices. The index1 that I created wasn't visible on the list. But, if I tried to recreate it, I was told it existed and wasn't allowed to create it again. I was also not allowed to create a further index - index2.
As I was working with a simple two field table and all the data had been lost anyhow, I deleted the table and created a new one in case that one had gotten corrupted somehow. But the same things happened again - couldn't create an index1 even though one isn't shown and I hadn't even tried yet. This happened even when I used a new table name and field names.

One funny thing happened. When I click the 'new index' icon, the name index1 appears under the sys idx line. It's already highlighted in blue. There are no fields showing in the index field and sort order boxes.
When I click on the index field drop down arrow, the whole window re-displays and now the index1 line is blue all the way across and the field names are displayed with ascending under sort order. The way this happens 'feels' like something's been saved, if you understand me.
Can you tell me how to do a screen capture and paste it into one of these things? Then you'd have a better idea about what's going on. A picture is worth a thousand words!
Thanks for any help
PS - you must have posted while I was writing this. My environment is in my signature. I keep wondering if the problem is that I am working under X11
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Preventing duplicate entry

Postby kabing » Thu Jan 10, 2008 8:07 pm

Did you by any chance also change the field type from VARCHAR to VARCHAR_CASEINSENSITIVE?

That would have wiped the data in that field, although you should have gotten a warning that the field couldn't be changed, and asked if you wanted to delete the field and create a new one.

I've added a unique index in a database it behaved just as Drew described; I did not lose any data. But I don't work in X11, either.

kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
kabing
Volunteer
 
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Preventing duplicate entry

Postby LJ Bettona » Thu Jan 10, 2008 8:20 pm

That would be what lost the data! But I've been getting so many 'Can't change the field' messages that I just ignored it. My fault. Thanks
But this doesn't account for the problem of not being able to create an index when I know I haven't created one yet! Very strange.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Preventing duplicate entry

Postby kabing » Thu Jan 10, 2008 9:32 pm

Can you tell me how to do a screen capture and paste it into one of these things? Then you'd have a better idea about what's going on. A picture is worth a thousand words!


I don't know if this will work with X11, because I don't use it at all, but it's worth a try (Note this is specific to Macs):
-First hide all your applications except for OOo (Command-option-H will usually do the trick)
-If you have your Applications Folder in your dock, click on it; otherwise get to it through the Finder.
-Find and start the Grab app; it's in the Utilities folder of the Applications folder unless you've moved it.
-If the Finder is obscuring what you need to take a screenshot of, switch back to OOo (or X11??), then switch to Grab
-In Grab, go to the Capture menu and choose the appropriate type of shot (selection, window, etc.)
-A floating window will appear; if you show selection, you can now drag over what you want a screen shot of; if you chose window, you'll be asked to prep the window and click a button.
-Save the screenshot.

There are also some keyboard commands that don't use Grab, but I can never remember what they are. So I keep Grab handy and use it instead.

I'm not sure about procedure for posting images here, but you probably need to move the image to someplace on the web. ImageShack is a good freebee spot. Once it's there, you use the image tags in your post and provide the image url. (Which ImageShack provides for you).

kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
kabing
Volunteer
 
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Preventing duplicate entry

Postby LJ Bettona » Sat Jan 12, 2008 12:47 pm

Thanks for the help. I've managed to grab the images I want and I've uploaded them to ImageShack. (Great site. Thanks for the info.) But I'm now out of time so I can't post my findings just now.

It's becoming quite a complex problem. I'm documenting my actions and will probably create a doc with the details. Watch this space as they say.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Preventing duplicate entry

Postby LJ Bettona » Sun Jan 13, 2008 11:38 am

Hi,
Here is as detailed a summary as I can provide of what I did and how the index failed.
1 - deleted all traces of former CD databases and tables from Finder and from the trash basket
2 - created a new database (MusicList) using the wizard (all three former attempts were visible on the options list of old databases to work with - worrying)
3 - was able to re-use MusicList as a database name so some sort of basic check was being done
4 - created MArtist table using Categories template and renaming the fields ArtistID (primary key) and ArtistName; created unique index on ArtistName
5 - created MusicTypeList using Categories template - fields MTypeID and MTypeName, but the unique index failed citing 'index already exists on "MType"; note that the field MType does not exist on this table!
6 - repeated the procedure to create MFormat table. This is what happened:
Image
By jerseypanther at 2008-01-12

Any clues as to what I'm doing wrong?

It seems to me that I can create the first unique index but all subsequent ones fail. This happens consistently no matter which database I try to create - CDs, DVDs, Library, etc. And the field names cited are not the field name I am trying to index. As a unique index is fundamental to the database I really want, I have to get this sorted before I can move on.

Thanks for any help.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Preventing duplicate entry

Postby kabing » Sun Jan 13, 2008 9:16 pm

I'm unclear whether you created the unique indexes in the wizard or in Table Design View?

Does this tutorial help you at all? It's part of a longer tutorial on Base written for users of NeoOffice. Since NeoOffice is based on OpenOffice.org, the instructions should work fine for OpenOffice.org, if you keep the following things in mind:
    1) references to the command key in a Neo tutorial are the equivalent to the control key in OOo. (i.e. Command-C becomes Control-C) Note that I'm not sure if this is true with OOo for X11.
    2) control-clicking is the same as right clicking
    3) icons sometimes look different, as NeoOffice 2.2.2 has a custom icon set.

kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
kabing
Volunteer
 
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Preventing duplicate entry

Postby LJ Bettona » Wed Jan 16, 2008 12:23 pm

I'm unclear whether you created the unique indexes in the wizard or in Table Design View?

I created the table in the Wizard but couldn't see how to create a unique index except for the keyfield. So, when I get to the end of the Wizard I click ' modify table' (or some such prompt) and it takes me to table edit view. I then proceed as though I had accessed the table from the main dbf screen and clicked edit.

I checked the tutorial and the instructions for a unique index are exactly what I did, so no help there. I am now going to try to SQL it. Wish me luck.

I looked at neooffice but it didn't say that it had a database so I didn't download it. I think I would prefer not to work under X11 as an emulator because the response times are slow and I always think anything you put between you and what you want to have happen is another place where disaster can strike.

Talk to you later.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Preventing duplicate entry

Postby LJ Bettona » Wed Jan 16, 2008 12:32 pm

I think I've cracked it!!!!

I went back to the tutorial and checked it with a fine tooth comb and there it was - an instruction to name the index! That's what's been wrong. All the indexes had the same name. I went to MType and created a unique index on MTypeName (which had failed before) but named the index first! And it worked!

I'll try this on the other problem indices before I start truly celebrating, but I think there's a light at the end of the tunnel. I feel so stupid!

Thanks for all the help!!
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Preventing duplicate entry

Postby kabing » Wed Jan 16, 2008 3:32 pm

I'm glad you got it to work.

LJ Bettona wrote:I looked at neooffice but it didn't say that it had a database so I didn't download it. I think I would prefer not to work under X11 as an emulator because the response times are slow and I always think anything you put between you and what you want to have happen is another place where disaster can strike.


As you can see, NeoOffice does include the Base module. Do you remember where you were looking that you didn't see it mentioned? (I'm wondering if a web page didn't get updated).

I don't know how much faster it is than X11...It can be slow about some things. And it is always a little behind OpenOffice.org in terms of version number. But I've been quite happy with it.

kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
kabing
Volunteer
 
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: Preventing duplicate entry

Postby LJ Bettona » Thu Jan 17, 2008 9:49 am

Thanks for all the help. I have now created unique indexes on all the tables in all my databases.

As for Neooffice I went to http://www.neooffice.org home. In the blurb to the left of the screen dump it seems to mention all the applications available and leaves out base. And the screen dump also lacks a base application.

I have now downloaded it and will try it out.

Thanks again.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
LJ Bettona
 
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: Preventing duplicate entry

Postby kabing » Thu Jan 17, 2008 2:46 pm

You're welcome. I've alerted the NeoOffice team to the website issue.

kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
kabing
Volunteer
 
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA


Return to Forms

Who is online

Users browsing this forum: No registered users and 3 guests