Function to Check the Unique ID with custome client Code

[nentendo]

HI All friend

i am new to this and working on my Database. i have 10 clients and every client got their own value (ie "CO" for Coke), I have one block who got the Drop Down menu for selecting 10 client as Co, NA, AA, etc. beside that i am generating the Unique ID (000001) and Beside that Project Name:

I Got One table name "Project"

-------------------------------------------------------

| CO | 0000001 | Project Name |

-------------------------------------------------------

"First" Field would be drop Down "second" is Automatic and third is type the project Name

Question 1:

How to create a unique ID for each client when some one selecting the Client code based on Drop Down fro example Selecting Co or NA, but i want to make sure when ever some one select the client Code, function check the Unique Value to make sure its not duplicate and if not than it would generate the Unique Number, Trick is ever client code got its Unique ID Start with 000001, so if one Clint got 10 Project the unique ID should check and start with 11 and if some client got 50 project, and by selecting the client function should check and put 00051. and so on with other clients.

Question 2:

In this project i am assiging the few client service name for those Project and these project assigned to more than one project manager. Is there any possibility in FileMaker, when i select more than one project manager for one project and select create Project, they would receive emails saying "Co0001CokeLogo" project has been created.

I really appreciate Help.

thanks

[comment]

I suggest having an auto-entered serial number ID field in each table (i.e. ClientID in the Clients table, and a ProjectID in the Projects table). In the Projects table, add a ClientID field (with no auto enter options). Define the relationship as:

Clients::ClientID = Projects::ClientID

To select a client by code rather then by ID, define a value list using values from field Clients::ClientID, also displaying values from field Clients::Code, and set it to 'Show values only from second field'. Use validation to make sure the code is unique in the Clients table. There is no need to duplicate the code in the Projects table.

Numbering the projects by client is unreliable, and probably not necessary.

I didn't understand your second question.

[nentendo]

Hi Friend

1- Sorry i am new to this Filemaker and sorry for asking it but is there any way to show me a small example of file. with relationship.

2- Sorry if i didn't explained the second question right, if you look at my picture i uploaded, in "Client service" Tab i am selecting Guldeep and France, i need some sort of function or Script, when i select one or more client service people and HIT 'Create estimate" or SAVE button, it automatically send email to appropriate selected people to alert them

is this possible.

thanks

[comment]

Re #1: see if this helps.

SelectByCode.zip

Re #2: I think you should assign people to a project by using another table, e.g.:

Projects -< Roles >- People

The you can easily find people who have a role in a project and send them mail.