Companies and Contacts without a self-join

[iamthegrey]

I am trying to set up a contact database for someone who knows a little bit about Filemaker Pro and they have asked me a stupid question, but I now I like the challenge of trying to figure out a way to do it. The question is - in my contact database I have a calculation field that creates an account number based on a company name when it is input, but there is also a unique contact ID number for each contact in the databse. Is it possible to generate a serial value for contact ID number for each contact within a company that is unique to that company.

For instance if I have three records in my file:

Jim at ABC company

Steve at BCD company

And Ralph at CDE company

Each one gets their own unique account number

ABCCO for Jim

BCDCO for Steve

and CDECO for Ralph

They each also get their own contact ID within the company and I want those values to be sequential within each company

So Jim would be 001

Steve would be 001

and Ralph would be 001

So that if I had a concatenation field to provide them each with a unique identifier

Jim's would be ABCCO001

Steve would be BCDCO001

Ralph would be CDECO001

And any new contact that was at ABC Company would end up with account number ABCCO, contact ID 002, and unique identifier ABCCO002.

So that is the challenge. Forgive me if there is an obvious answer but I am fairly new to FileMaker and entirely self taught. Thanks in advance for the help.

[bruceR]

Immediately forget this approach.

Generally, cardinal rule of databases is that record ID values should be meaningless numbers.

Use native auto-increment serial numbers for the ID.

If the ID has meaning (ABCDCO) what happens when the company changes names or you spelled it wrong.

[djlane]

Your contacts table should have a Unique ID / Primary Key, eg. “pk_Contacts” generated by Filemaker on creation – not by script but on creation.

Your Accounts (companies) table should also have a Unique ID, generated in the same way.

The Accounts table should also have a field eg. “fk_Contact” which will store the foreign key (pk_Contacts) of the account. This is the field you use for your relationship.

On the relationship graph create a new table occurrence and relate CONTACTS::pk_Contact to ACCOUNTS::fk_contact

When you create a new contact, use a lookup to the Main Account table (ie not the Table Occurrence in the relationship above) to insert the foreign key into the fk_Contact field.

If you place a portal to the Contacts on a layout in your Accounts table, you will see all the contacts for that account.

[comment]

Forgive me if there is an obvious answer

The obvious answer is that time marches on. For example, Ralph is at CDE now - but he may join ABC tomorrow. To keep up with your scheme, you would need to re-enter all Ralph's details into a new record, instead of simply changing the CompanyID foreign key.

[TheTominator]

I think the attached file does what you want.

The calculated values are unique among all existing records since each new value is designed to be 1 higher than the existing ones for that company, but the values are not unique if you compare them to deleted records. You can delete the the most recently added employee of a company and have the old highest value reassigned to the next hire.

Note that the calculated serial values are not used as keys for any relationships in this example (except in the dedicated relationship used to calculate the next unused compound value). Auto-entered numbers are used as the primary keys for the Contact and Company tables.

SecondarySerial.fp7.zip

Edited June 12, 2010 by Guest

[iamthegrey]

Thanks all for the help!

[lisaniel]

Thanx for sharing the information

Job costing