Jump to content
Salesforce and other SMB Solutions are coming soon. ×

help creating auto entry serial # by class


radiohead

Recommended Posts

Help! I am trying to come up with a way that I can auto enter serial unique numbers into a field according to groupings.

 

This is to create tracking numbers for use with specific clients. I would like my database entry to go like this:

 

Create new tracking number

User has to specify client name

User selects "jones"

Auto entry is field that is "jones100"

 

Easy so far, next entry goes..

 

Create new tracking number

User has to specify client name

User selects "jones"

Auto entry is field that is "jones101", (one greater than previous)

 

Here's where I'm stuck...

 

Create new tracking number

User has to specify client name

User selects "smith" this time

I would like the Auto entry for this field to start at the beginning number so it would be "smith100"

 

I can't figure out how to auto enter a serial value that will count up based on the group it is being classified by (smith vs. jones vs. brown vs....).

Link to comment
Share on other sites

radiohead ...

 

How about a scripted solution for entering the Last and First names that includes:

 

* validity checking for name entries (won't allow empty fields)

 

* setting the ID field from a calc that uses a self nameL::nameL relationship to get the Max ID for that name + 1

 

You'll have to remove users' access to the "New Record" command so they can't bypass the "New Record" script. You can do this with password configuration or with the SecureFMâ„¢ plugin from New Millenium Communications.

 

Aside from that, it does what I think you want to accomplish. You can grab my

"Name Specific IDs.fp5" example here.

 

By the way, I hope you are not going to use this ID relationally, i.e., as a primary key to link your people records to other records in the system. People change their last names for all kinds of reasons (marriage, avoiding the IRS wink.gif , etc.). If someone changes their last name, and you change their ID, you will need a contingency script to find and 'update' the ID in any related records. Read this thread, especially the post by "LiveOak" that outlines some excellent considerations for the construction of primary keys.

 

If you have any questions, email me or post here.

 

Good luck smile.gif

Link to comment
Share on other sites

If you want a way to do this without using a script, you might be interested to take a look at the demo file which can be downloaded from:

 

http://www.nightwing.com.au/FileMaker/demos1.html#d7

 

It shows an automatic method for tracking and issuing separate sequences of serial numbers. cool.gif

 

Be aware however, that either this or the method that Jim suggested carry a risk if used in multi-user mode, in that if two users create a record for the same group at exactly the same instant, the same serial may be issued to both. It may not be a concern in your case, but it is as well to consider it in advance. wink.gif

Link to comment
Share on other sites

In multi-user solution manual record locking should be implemented. We have this in place for 5 years, and it was working reasonably well.

However, last year we improved even that record lock by following search for just issued serial number. If that "next" value is already used in db, try to use next value and that is looping until there is not serial number used.

Link to comment
Share on other sites

Ray ...

 

Your solution is beautiful. When I was trying to design a strictly Auto-Enter solution, I kept running into circular references in my calc invovling the serial field, but your use of the LookUp would have solved that. Kudos!

 

Anatoli ...

 

Would love to see a code snippet of your looping process to prevent duplicate IDs being assigned to different records.

 

Thanks to you both.

Link to comment
Share on other sites

The g_Temp_numb is increased by 1 until there is not matching record in main Order file

 

Usually the loop is running only once, because the other Record locking works OK. But couple of times that failed on HP Brio with W'98, which I've found as the worst hardware for running FileMaker.

 

# " get serial number from SystemFile"

Set Error Capture [ On ]

Set Field [ g_Temp_numb, SystemFlagA::LastOrder ]

Set Field [ g_ID_numb, zx_RecordID ]

Loop

Set Field [ g_Temp_numb, g_Temp_numb +1 ]

Enter Find Mode

Set Field [ ID_Order, g_Temp_numb ]

Perform Find

Exit Loop If [ Status( CurrentFoundCount) = 0 ]

End Loop

Enter Find Mode

Set Field [ zx_RecordID, g_ID_numb ]

Perform Find

Link to comment
Share on other sites

Thank you all for your help--and so quickly as well! I haven't quite got a grasp on all of your suggestions yet, but I love taking things apart and seeing how they work. Thank you all.

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use