Auto-Enter Serial problem

[levonk]

Hi,

I created a database that is being used in a multiuser environment. It has a table with a auto-enter serial field, and a primary key that is calculated in a script using this field.

Entries to this table are made from several workstations, and later during the day, they are revised by a supervisor, and either accepted or rejected. Rejected entries are deleted. This creates holes in the serial numbers, and the primary key. I tried several methods to overcome this but have yet to find a good solution. Here is what I tried:

1. Create a temporary table and only move them when they are accepted. Problem: The records need to have the same primary key when they are entered and after they are accepted.

2. Instead of auto-enter, a script would check for the first available number, and use that. Problem: if two records are created at the same time, they may get the same number.

3. Create a table with available serial numbers, and delete them as they are used. Problem: None yet, but I am not sure.

Is there any other way to achieve this?

[lawaid]

Hi levonk:

Rejected entries are deleted. This creates holes in the serial numbers, and the primary key.

So long as this is the business process, the dilemma will persist. The path you're considering (changing IDs) presents more conundrums than you currently have.

Would marking a "status" field as "Accepted" or "Rejected" just not do? Many find it beneficial to not delete records; rather, they handle it through a record's status. It permits a gapless sequence of IDs.

[levonk]

I do not wish to change IDs, but create ones that will fill the gaps.

Marking a status will have the same effect for those who are working on the system. They will never see the ones marked rejected, so the numbers that are in use will have the gaps. This will also increase the size of the table significantly, and considering the number of chid tables, the database will be huge, and slow.

[comment]

4. See:

http://fmforums.com/forum/showpost.php?post/172165/

[levonk]

Thanks comment. I will look into it and see if it can be adapted to my problem.

The major problem I have is that the number should be the same when it is created and after it is accepted. So, I cannot assign a number after it is revised.

[levonk]

I created a script that tries to do the job, and I want to post it here so people can tell me if it will work. Do I have to retype the whole script in, or is there a way to copy/paste it? I tried copy/paste but it does not work.

[comment]

The major problem I have is that the number should be the same when it is created and after it is accepted.

Wouldn't it be enough to assign this number when the entry is accepted? This way each number would be assigned once - permanently.

IIUC, you want to assign numbers temporarily, and recycle those have been rejected? This would require limiting the creation of new records to a scripted method, among other complications - and the end result would be practically the same. So why is this so important?

[bruceR]

I created a script that tries to do the job, and I want to post it here so people can tell me if it will work. Do I have to retype the whole script in, or is there a way to copy/paste it? I tried copy/paste but it does not work.

Print script to Preview; copy text from Preview.

[levonk]

The table has many related child tables and the ID, which is dependent on the serial number, is the match field. The application is finished and being tested. It was one of the later requirements of the customer to be able to use the deleted IDs to keep the number from getting to high. So, to assign the number after the entry is accepted will entail a lot of work and changing most of the relationships, and a whole bunch of scripts.

Thanks Bruce. Here is the script I wrote:

Freeze Window 

Set Error Capture [ On ] 

Go to Layout [ “Deleted_Records” (Deleted_Records) ] 

Go to Record/Request/Page [ First ] 

Open Record/Request 

If [ Get(LastError) = 301 ] 

	Loop 

		Go to Record/Request/Page [ Next; Exit after last ] 

		Open Record/Request 

		Exit Loop If [ Get(LastError) ≠ 301 ] 

	End Loop 

End If 

If [ Get(LastError) ≠ 101 ] 

	Set Variable [ $File_ID; Value:Deleted_Records::File_ID_Number ] 

	Delete Record/Request [ No dialog ] 

	Go to Layout [ “Table” (Table) ] 

	New Record/Request 

	Set Next Serial Value [ Table::File_ID_Number; GetNextSerialValue("Table File Data";"Table::File_ID_Number") - 1 ] 

	Set Field [ Table::File_ID_Number; $File_ID ] 

	Set Field [ Table::File_ID; $$PHC & Right("00000" & GetAsText(Table::File_ID_Number);5) ] 

Else 

	Go to Layout [ “Table” (Table) ] 

	New Record/Request 

	Set Field [ Table::File_ID; $$PHC & Right("00000" & GetAsText(Table::File_ID_Number);5) ] 

End If 

Go to Layout [ original layout ] 

There is also a script to delete a record which adds the number into the Deleted_Records table.

The script works well in a single user environment. I need to make sure that in a multiuser environment there are no glitches.

Thanks.

[comment]

The table has many related child tables and the ID, which is dependent on the serial number, is the match field.

The serial number assigned to the record on creation should NOT change, so those child records would continue to be related. There simply would be ANOTHER number assigned on approval. No relationship needs changing and the implementation is as simple as it gets:

Set Field [ Numerator::FK ; Table::PK ]

[levonk]

So, anybody think this script will work?