Sequential file #'s even when a file is deleted

[jbbeaird]

Most likely I'm a novice FMP user and I need some basic help. I've searched the forum for my exact question and I've seen some pretty close but not close enough for me to understand completely. So here goes...

I am using file numbers to keep track of my employees (I have an employment agency so I am always adding people to my data base). I don't know how to get FMP7 to sequentially auto enter the next number.. ie: 4056, 4057, 4058, 4059 etc. I know how to get FMP to add 1 to the value number you select such as 4056 + 1. But if the person entering the data has to delete the file for some reason, then the upon adding the next file... instead of 4056 + 1 = 4057, the new number will come up as 4058. Instead of 4056, 4057, 4058, 4059 etc... I get 4056, 4058, 4059 etc. (because of the deleted file 4057).

Is there a way (And I know there must be... I'm just so new at this I don't know how to do it) to get FMP to realize that the sequential file was deleted and it has to restart the counting from 4056 rather than 4057 (These numbers are only for an example).

Forgive me for not being as clear as I know I should be but this is the best way I know how to explain it.

Thanks in advance for you help.

jbbeaird

[email protected]

[-Queue-]

First, let's clarify your terminology. You say 'file' when you mean 'record'. A file contains records, but they are not the same thing. Second, it can be very dangerous to do this sort of thing, unless you are using a unique auto-entered serial for your relationships, and not the pseudo-serial employee number to which you are referring. However, it is possible and relatively simple to set up a system like you require.

You need a self-relationship based on the serial field, or any other field that will always have data, using the X (Cartesian join) operator. This will match each record to every other record in the table. Specify this relationship to sort based on your pseudo-serial, in descending order. Then make your employee number an auto-enter calculation of Case( IsEmpty(selfrelationship::serial); 1; selfrelationship::employeenumber + 1 ). Since selfrelationship is sorted by descending employeenumber, selfrelationship::employeenumber references the largest employee number. Adding 1 gives us the new number.

[jbbeaird]

Thanks for reminding me about the importance of terminology when speaking of data bases etc. In my mind I was thinking in terms of my employees file numbers rather than FMP system of records.

Could you explain to me why the way I'm using employees file numbers can be dangerous?

I really... really appreciate you taking the time to post a reply and I don't expect you or anyone else on this forum to teach the basics of FMP to me. But since I am sooooooooo new at trying to do the nuts and bolts of FMP (I've been a user but have had to start from scratch on learning and creating my data base), I really don't understand your solution. But that's ok... Just because you took the time to write it to me is greatly appreciated. I will spend my own time trying to figure it out and accomplish your suggestion rather than expecting you to do the work of me.

Again, thank you for taking the time to reply to my post. This is a great fourm, I am impressed by all of you who read these posts and help us with our problems

[-Queue-]

If you are using these employee file numbers for relationships and are allowing employee numbers to be changed, any relationships which rely on this number are killed. Also, if you re-use a number, it is now linked to other records incorrectly. This is why I strongly recommended you use a unique auto-entered serial number that cannot be duplicated, preferably one that is unrelated to the data it holds and not modifiable by anyone. It need never be seen by your users or have any meaning to them. It only needs to be understood by the database.

Feel free to ask if you don't understand any of the terms I used, such as self-relationship or Cartesian join.

[jbbeaird]

Again let me thank you for your willingness to help all of us who are new to working with FMP.

I realize that I might not have made my problem clear.

All I want to do is find a way that will fill in the field that I'm calling File Number with sequential numbers and keep up with the numbering system. But sometimes there has been instances where by accident I've hit the "NEW FILE" button and then deleted the file before anything was entered. When I do, FMP looses count. Instead of the next sequential number being added... FMP counts the deleted file and will enter the next number after that.

Chastise me for being redundant and I'll forget this entire thing.

Thanks in advance.

jbbeaird

[-Queue-]

Understood. The technique I posted above should eliminate that problem as it only takes existing records into account.