Do you have a NameFirst and NameLast fields or just a Name field?
Sex field is M or F choices or is it Male / Female?
A unique identifier is to allow access to specific records.
You could have a calculation field result text that would concantenate FirstInitial + UID + SexInitial
That would be easier than incrementing the number following the FirstInitial.
How to populate a field with a unique ID
Answer ID: 9903Last Updated: Feb 27, 2012
An important best practice when creating a relational database is to have a unique ID for all your records. Having a unique ID allows you to quickly and easily create relationships between tables as you are assured you can uniquely identify a record.
If you have an existing table that already has records in it but does not have a unique ID field, it is very easy to create one and populate it using FileMaker Pro. It involves creating an additional field to store the unique ID (if a field does not already exist to store it in your database) and using the Replace Field Contents feature on that field.
The first thing you need to do is create a field to store the unique ID. This field can be called anything you like. In most cases it will be a number field but it doesn’t have to be. You will want to configure the options on this ID field so that it auto enters a serial number so that when you create new records it will continue to generate unique IDs for those new records.
- Open the file in FileMaker Pro that you wish to modify.
- Click FILE > MANAGE > DATABASE… to open the Manage Database dialog box.
- If your FileMaker Pro file contains multiple tables, verify that the table you wish to modify is selected in the table pull down menu in the upper left hand corner of the Manage Database dialog box. When you have verified the table, a list of fields in that table will be displayed.
- If you already have a field defined to be used, skip to step 6
- To create a new field to contain the unique ID, type the name of the field in the FIELD NAME box, change the TYPE to number and then click CREATE. This will add the field to the selected table.
- From the list of fields, double click the field you want to modify. The Options dialog for that field will be displayed
- Click the AUTO ENTER tab. Check Serial Number. In the NEXT VALUE field, type in the value that you want your unique IDs to start with. In the INCREMENT BY field put in the value that you want the new IDs to be incremented by. For example, if you put “1” in the NEXT VALUE field and “5” in the INCREMENT BY field, the next few records that were created would have values of 1,6,11,16,21…
- Click OK to save the options for this field
- Click OK to save the changes and exit the MANAGE DATABASE dialog.
At this point you have created a field to hold your unique ID but the field is still empty. To populate this field when you already have existing records requires the use of REPLACE FIELD CONTENTS.
NOTE: Replace Field Contents works on the current found set of your database. Therefore it can be used to modify a subset of your records if you so desire by performing a find on the records that you wish to modify first before performing Replace Field Contents.
- In BROWSE mode, click into the field that you want to be the unique ID field.
- Click RECORDS > REPLACE FIELD CONTENTS to bring up the REPLACE FIELD CONTENTS dialog box.
- Choose REPLACE WITH SERIAL NUMBERS and enter in your desired INITIAL VALUE and INCREMENT BY fields. Verify that Update Serial Number in Entry Options is checked. This option will update the auto enter information you configured for this field so the next new record will have an appropriate ID automatically added.
- Click REPLACE
How do I define automatic data entry in a field?Answer ID: 5531Last Updated: Oct 04, 2011
- The Replace Field Contents function cannot be undone. Be sure this is an action you want to perform before doing it – especially on an existing field that might have data already in it. Using Replace Field Contents on a field that already has data will erase the existing data in favor of what Replace Field Contents is configured to do. If you are in doubt about performing this action, then create a backup of your FileMaker Pro file first.
- If you do not set the auto-enter options for the field first, then the option to update the serial number information will not be available in the REPLACE FIELD CONTENTS dialog box and you will have to manually update the options for the Unique ID field with the next serial number value.
- If desired you can also combine text as part of your unique serial number. This might be helpful in cases like part numbers that might be a combination of text and numbers. In the “Next Value” field in the “Field Options” dialog, format your serial number using text and numbers. For example, if you used ABC1, then FileMaker Pro would create unique IDs like ABC2, ABC3, ABC4, etc. FileMaker Pro will only increment the last number used in the “Next Value” field. So, for example a “next value” of ABC1XYZ1 will get incremented as ABC1XYZ2, ABC1XYZ3, ABC1XYZ4, etc.
To save time and ensure accuracy, you can set FileMaker Pro to automatically enter data in text, number, date, time, and container fields. For example, FileMaker Pro can enter the current user's name into a field.
To set options for automatic data entry:
1. With the database open, choose File menu > Manage > Database (previouslyFile menu>Define>Database).
2. In the Manage Database dialog box, click the Fields tab.
3. If your database contains more than one table, select the appropriate table from the Table list.
4. Select a text, number, date, time, or container field from the list of existing fields, or define a new one.
5. Click Options (or double-click the field name).
6. In the Options for Field dialog box, click the Auto-Enter tab, then select options for the field.
To Do This Enter the record creation date or time Select Creation, then choose Date or Time from the list. Enter the record creation date and time (timestamp) Select Creation, then choose Timestamp from the list. Enter the name of the person who creates the record Select Creation, then choose Name from the list. Enter the account name that creates the record Select Creation, then choose Account Name from the list. Enter the record modification date or time Select Modification, then choose Date or Time from the list. Enter the record modification date and time (timestamp) Select Modification, then choose Timestamp from the list. Enter the name of the person who modifies the record Select Modification, then choose Name from the list. Enter the account name that modifies the record Select Modification, then choose Account Name from the list. Assign a serialized number to the field in each record
Select Serial number.
- For Generate, choose On creation to generate serial numbers when records are created.
- For Generate, choose On commit to generate serial numbers when records are committed.
For next value, type a starting value (like 100), then type the increment in increment by.
Enter the value from the same field of the last record you accessed
Select Value from last visited record.
Note This value will be from the last record accessed in the table in which this options has been set.
Enter data that you specify Select Data, then type up to 255 characters. Use the keyboard arrow keys to scroll through the text box, if needed. Enter the result of a calculation
Select Calculated value (or click Specify), then enter a calculation. See Defining calculation fields for information about the dialog box.
Select Do not replace existing value for field (if any) to prevent overwriting data already present.
Enter a value that's copied from a field in a related record Select Looked-up value (or click Specify), then define the lookup. See About lookups for more information. Prohibit a user from modifying a value that you have defined to be auto-entered Select Prohibit modification of value. Turn off automatic data entry Clear all selected checkboxes.
Could I ask why you need this?
You don't need it as a unique identifier - a serial number will do this.
You don't need it to identify Males, as a search on that field will find them
You don't need it to find or sort by first letter of the name, as a simple calculation field will do that
Since you must first "Find" all records who has a first Name intial begins with your new record target [your example "R"], I know of no calcualtion that can perform a "Find".
So to answer your question: Use a script.
As David points out, there many ways to make a Unique IDentifier that are easily generated.
Script should be in Table that creates the New Record should call the Script when First Name and Gender field are not Empty.
Perform Find on First Name Insterting only the Left( FirstName,1) & "*" in FirstName field
Get(FoundCount) of Found set and add 1 to increment
Then form your UID with text fields if you want to maintain the format R + XXX + M
you must maniipulate you Numberical Found count to text with leading alphanumeric zeros until it is great than 100 or you will get "R1M" or "R1F"
Finish the UID with by text concante Initial & "Count" & Left( Ginder,1)
You don't need to do the Find first, as a self-relationship based on the first letter of the name (and gender, if you wanted) could generate the same result as the found count, so it could be a calculation.
Davidanders - Thanks for the information . I got some detail about this thing. :)
Sorbsbuster - I need this as the custom unique ID. Sometimes people don't always use 1,2,3 and so on. And also thanks for the information. I'll try the script tonight and post the working here.
Jim Gurley - Your expression does help me in auto-enter but it does not reallly work but I think doing it in a script would work. I'll post about this thing later. Thanks :)
There's a difference here between a primary key--this is almost always a straight up serial number in FileMaker--and some kind of "coded identifier" where users can extract information from the identifier if they know the "code". Good database design avoids using this second identifier as a primary key. Instead, you set it up as a data field so that you can use it in searches and sorts, but not to link to related records.
Once you have encoded "meaning" into your identifier, you open the door to the chance that your users will insist on changing the format or content of this encoded information. If that happens and it's a primary key, you have a major job in front of you to update this field and all the foreign key fields it links to without scrambling the links. With this field as just a data field, you can just edit the one field and all your relationships will still work.