ID field and record number need to be similar

[bubette]

I have created a database of 90 records. On the top of each record I have a record number symbol. Also on each record I have an ID field. Its options are: indexed, auto-enter serial, required value, unique, numeric only, allow override. When I initially planned the database, these two numbers were synchronized, so that the Id field was the same as the record number. Since then, I've added more records. Now they are no longer the same. Sometimes 2 records have the same ID number, which means that one record will have incorrect information in the portal fields. I think that I may have chosen incorrect options for the ID field, but I'm not sure how to change it so that I don't have duplicate ID fields and incorrect data in the portal.

I would greatly appreciate any assistance you could give me. Thank you.

[PeterHW]

Hi Bubette,

I assume with record number symbol you mean '@@' in a portal ? You have to realize that the record number is totally arbitrary and that there is no need for it to be identical to the ID field. For instance, if you have deleted records and created new ones, the ID field and record number will not be the same.

The record number a certain record has is purely dependent on its position it has in the relationship on which the portal is based. For instance, I assume your portal shows the related records very straightforward, i.e. only based on the ID field of the main table. In this case, the related records are shown based on entry date (i.e. an older record has a smaller record number than a newer record). But if you view the same related records through a sorted portal (try it ! Or view the file I've attached), the record number will become totally different.

The ID field should be used to uniquely identify a certain record. Normally, it wouldn't even be visible to the user. Duplicates should NEVER be allowed, therefore, you should check the "Prohibit modification ..." checkbox in the auto-enter menu and uncheck "allow user to override..." in the validation menu.

If you wish to sync the ID field with the "record number" you can do the following. First choose "Show all records", then put the cursor in the ID field and choose Replace Field Contents/Replace with Serial number and check the "update serial number..." and click OK.

However, note that this will ruin any other relations that may be dependent on this ID field !

HTH,

Peter

RecordNumber.zip

[bubette]

Hi Peter,

That you for returning such quick help.

The record number symbol is '@@' . I understand how the number changes, depending on the position of the record so, you're right, it doesn't need to be the same as the ID.

"The ID field should be used to uniquely identify a certain record. Normally, it wouldn't even be visible to the user."

How would I make that field and the number it contains invisible to the viewer?

"Duplicates should NEVER be allowed, therefore, you should check the "Prohibit modification ..." checkbox in the auto-enter menu and uncheck "allow user to override..." in the validation menu."

The ID field, which is a number Type, now has these options: indexed, auto-enter serial, Can't Modify Auto, Required Value, Unique, Numeric Only.

Could you explain the option, for Auto-Enter, Serial number is checked, Generate On Creation, next value ______. Where does the number, that is in the next value, come from?

Thanks again for your help.

Bubette

[PeterHW]

Hi Bubette,

glad to help. I apologize up front for the long message ahead, but I felt it was necessary.

How would I make that field and the number it contains invisible to the viewer?

Easy, don't put it on any layout :

No seriously, you need to learn to distinguish between

1. The unique ID that is used to identify each record and should be the basis for any relationships with other tables.

2. The ID code that you/your users use to find certain records.

Of course, a single field can serve both purposes, but it's advisable not to do so. The first ID field (also known as a primary key) is a unique 'behind-the-scenes' field that your users have nothing to do with. The actual format of this field does not even have to mean anything to your users, since they won't be seeing it. The only REAL requirement for this field is that it is guaranteed, absolutely, not a single trace of a doubt, going to be UNIQUE ! You should see the lengths some people go to to guarantee the uniqueness of this primary key field, but for your purpose I don't think this will be required.

Let's say you want to use a single ID field, both as the primary key (i.e. to base relationships on) and as a meaningful, searchable ID field which your users can use to find records.

Let's assume you have a simple customers table, with the following scheme for your ID field : 3.000 for the first record, and for each new entry 1 is added to that number, i.e. 3.001, 3.002 etc. You base all your relationships to/from this table on this ID field. Now, during the life of this table, records will of course be added but also deleted. For instance, let's say the record with ID 3.025 is deleted from the table at some point.

Now, imagine one of your users decides when reaching ID 4.021 :"Hey, wouldn't it be a great idea if I renumbered all our customers so that they are consecutive again and we don't have this irritating gap between 3.024 and 3.026 ?"

Yes, this sort of thing actually happens - as well as many other scenarios which might seem unlikely to you now. Imagine what will happen to all your relationships. If you have an Order table, related to your Customers table, the order that was originally related to the customer with ID 3.027 is now related to customer ID 3.026 (as well as all other orders from customers above 3.026)

Sorry to make this such a long-winded tale, but I felt it important to explain the difference between a primary key and "just-some-ID" field to you.

Could you explain the option, for Auto-Enter, Serial number is checked, Generate On Creation, next value ______. Where does the number, that is in the next value, come from?

The next value is the value that will be assigned the next time you create a new record. It depends on what the value of the last serial number was (i.e. when you last created a new record) and the increment.

For instance, if the last time you created a new record, the serial 19 was assigned, and the increment is set to 1, the next value will be 20.

I hope I've cleared things up for you a bit and not made things even worse

Let me know if I can help you any further.

Regards,

Peter

[bubette]

Hi Peter,

Thank you for the explanation. No, it wasn't too long. But I think I made a mistake. I deleted some fields and now I can no longer enter data into the portal fields I have for the layout clinical data. I've enclosed some jpegs to try to clarify my problem. I have that you can read them.

Bubette

Edited October 28, 2005 by Guest

[PeterHW]

Hi Bubette,

you need to compress the files before posting them to the board using the "manage files" link below the message composition box.

If you have deleted a field that was used for the relationship, that would be a problem, but then you also would not be able to view the records in the portal. Post the JPEG's again, or if you can, your files, and I'll be glad to look at them.

Regards,

Peter

[PeterHW]

Alright, I now see the JPEG's. Why did you place all the fields in a separate Common Info table, in stead of in the Main table ?

I assume that Pic ID in Clinical data is an auto-enter serial as well ? If so, the relationship you have created where the ID in Main should match both ID AND Pic ID in Clinical data is not likely to work. I don't know to what field Pic ID should have previously matched ? Is it possible that you have added Pic ID to the relationship by accident ? Filemaker should have warned you if you were about to delete a field that was being used by a relationship (i.e. the field that was previously used as the match field for Pic ID). I would suggest removing Pic ID from the relationship (delete the line in the Relationship window that says "AND ID=Pic ID"), this will probably fix the problem that you cannot enter new records anymore.

In any case, it is always a good idea to have a backup of your files ready in case you delete important fields.

Anyway, let me know if there's anything else.

Regards,

Peter

[bubette]

Sorry Peter, the story is not getting any better. Unfortunately I seem to be learning the very hard way. I made some changes, again to try and correct previous mistakes. Now I'm getting the messages above and can't open the database to fix it. Can't make new records either. :)

[PeterHW]

Hi Bubette, why can't you open the files anymore ? Maybe the quickest solution would be to post your files (compress them first) so I and others can take a look.

Regards,

Peter

[bubette]

Thank you so much for your help. I've enclosed a zipped file.

Okay, I tried to send the file and it's too big.

Upon working something out, so that I could send you a smaller file, I decided to do a file recovery on the messed up file. Lo and behold, lots of blocks of data were recovered and it now works that way it should.

Yeah!!! I'm so happy! I was prepared to redo the entire 90 bird records.

One question, how do I do a range search, for example, find all of the records for 2005? I know that I can't just type in 2005 in the date field. The information doesn't seem to be in HELP file.

[bubette]

My recovered file is now experiencing the same problems that I discussed above. Those 2 messages about unique values and not modifiable keep popping up when I try to get out of the record. I can't even close it. I'll have to force the program to quit.

Is there a way to send a FMP file without the data? Then it wouldn't be as big and I could send it.

[stanley]

Bubette:

Go to Save As... and save the file as a Clone of the current file. This will save without any records. Make sure you don't over-write your current file!

-Stanley

[Ender]

Those 2 messages about unique values and not modifiable keep popping up when I try to get out of the record.

For auto-enter serial numbers, it's not necessary to have the "Unique" option checked. In large files, this option tends to slow record creation as it has to check that the new value is not used in any existing records.

It sounds like your auto-entered serial number range is covering a range of numbers that are already used.

Is there a way to send a FMP file without the data?

File->Save A Copy As...Clone (no records)

[bubette]

I've enclosed a clone of the data base that I was working on. I ended up doing another recovery on the recovered file. Then I saved a clone of that. The file is now veeerrrry slow. Also, it wouldn't let me add more comments to the clinical data file.

If someone could take a look at it, I would really appreciate it.

Thank you.

_Clone.fp7.zip

[Ender]

What are we supposed to be looking for??

[Ender]

A couple things:

The "ID" field in the Clinical data table should not be an auto-enter serial number, since this is a foreign key. Also, if you wish to create Comments through the Clinical Data portal, you'll need to hit the checkbox on the relationship to allow creation of records through the relationship.

[Ender]

What I can't figure out, is what's making it beep on commit. Perhaps the file is corrupt?

[LaRetta]

Do you mean the Class field? Or any field? Because the Class field is global auto-enter data Aves. It beeps when I exit that field after typing something else -- but there weren't any records yet, which is why it beeps maybe.

Are you seeing something different, Mike?

[Ender]

Ah, the beep is from the spell check options.

[bubette]

Sorry. With all of the problems I was experiencing I may have lost sight of specific problems. In the clinical data table, I can seem to enter new data.

[bubette]

I just thought of something: I'm having difficulty entering new information into the 3 fields (comment, date and weight) that are part of the clinical table. I need to edit the relationship to allow the creation of records, (in this table via this relationship) but I'm not sure how to do that. Is there anyone that could help me?

[Ender]

I need to edit the relationship to allow the creation of records...

On the relationship graph, double click the relationship joining the two tables. In the "Edit Relationship" dialog, there's a checkbox to Allow creation of records through the relationship.

[bubette]

Hi Ender, Thanks again for replying. I did try that. I've enclosed a jpeg of the choices that I made. Then I received an "error" message. I've also enclosed a jpeg of that. Should I have that extra duplicate table?

[Ender]

I don't know why you were prompted to add another table occurence. It should not be necessary; the exiting structure already has a direct link between 'Clinical data' and 'Common Info' (this is the relationship that should be edited.)

[David Hart]

To find all records in the date field for 2005:

enter 1/1/2005...12/31/2005

(Or any other range you like)

[bubette]

Thank you everyone for your help. I was just going to ask the question about doing a date search when I noticed that David had already answered it.

from an appreciative novice