HELP Seeding Unique ID from separate Table

[scissorlad]

Hello

I am trying to manage seeding my Unique ID with a concatenation of CreatedBy (Account Logged in) and a number from a separate seeding table rather than using the Filemaker Auto Enter functionality.

Background: I have worked in many RBMS over the years but admittedly this is my 2nd Filemaker project.

Here is my scenario:

- Multiple Individuals performing Data entry from Biz Reply Cards into single FileMaker Database (let's say tblCustomer is our main data entry table).

- Each individual must have their own sequential seeding process for Unique IDs due to the way we store our library of Biz reply Cards after they have been entered. I would like to inherit from a seed table something like tblSeed below.

tblSeed:

User[color:gray]____NextRecordID

Sam[color:gray]____178

Jess[color:gray]____212

Dave[color:gray]____119

The process I envision would be:

1) Sam Creates a New Record

2) RecordID = "Sam178" (The UniqueID would be the concatenation of User + NextRecordID )

3) Sam's row in tblSeed would increment by one

This is what I understand so far:

- can set a column "CreatedBy" and populate with Get(AccountName)

- Join "tblCustomer" to "tblSeed" via CreatedBy --> User

Questions that form up:

+ how to retrieve the NextRecordID value from tblSeed for the concatenation process?

+ how to increment by one tblSeed's NextRecordID value after retrieving?

I have been looking for days in the USer Groups and in books and haven't found the specifics on this.

Any help would be appreciated. Thanks !!!

[John Mark Osborne]

I created a solution years ago in FileMaker Pro 3 that does just what you want. I just converted it to FileMaker 8.5 and it works great. It doesn't require any scripts which is nice.

http://www.filemakerpros.com/Serialize_by_Category.zip

Make sure you use an auto-enter serial number for your primary key fields for your structural relationships and use this technique simply for reference or supporting relationships.

[T-Square]

You're looking for headaches in this.

You would be better off dispensing with the overloaded field altogether, and use a combination of fields to achieve the same end (By "overloaded", what I mean is using one field [RecordID] to track more than one piece of information [username, Record's place in the User's Sequence]).

With the field storing the username, you can create a calculation (using a selfjoin relationship sorted by ID and/or creation time, and the user field) to display "SAM-178" if that's what you want. You have no overhead of a seedfile, no maintenance issues, fewer headaches.

David

[scissorlad]

Thanks John! Looking forward to giving it a whirl. Will post back results.