On your relationship graph, simply connect Name, Code, Serial number to foreign keys of same type in that table. The connections will be AND logic require all 3 to be true to Relate. Then to prevent change, save them in a non Available field that is Primary "frozen" key, if the company name changes, the Frozen Name won't..
PS: For some reason, many want to make a compound Serial Number. Why? I am not sure, but FMP makes it too easy to associate a simple number to Names and Country Codes and Portals can find them.
Thank Jim but this isn't a relationship between 2 tables. This is creating a ID number for a Client and is on the very first table. All other tables are connected to that one Client table. Is what you described still applicable? Maybe I am not understanding something?
Yes it is applicable. We seem to think a table can be related by just one "line", but you can connect muliple fields [lines].
The Connections or relastionships are very flexible with logical operators too. Most helpers will suggest to avoid Names and stay with Unique Numbers to avoid, duplication, typos, and changes in names.
In your proposed ID method I would suggest simpifing to just the Unique Number ... ONLY. Which will remain the Company Identifier "forever", no mat
ter Name or Country Code changes.
You can FIND all Companies with same Country codes. You can save a non changeable field as Original Name....to allow name changes in future.
Lets say you want to generate a Contact Event table that was related to that pkClientID.
Then just that single number will link all Contact Events to that fkClientID. Why make it complex adding a Name and Country Code to relate the tables.
But if you desire all 3 then...
pkName, pkCode, pkClientID in the Client Table
fkName, fkCode, fkClientID int the Contact Event Table
Which means all 3 pk and 3 fk must match to get a related record. Make sure field types match.
Jim, I don't want all three. What I want is to build the __pkClientID from 2 other fields in the same table. One is Country Code and the other is Company Name. So if the US is the country code and the company is called Apple, then the _pkClientID will be US-APP. This primary key is then used in the connections/relationships to the next table.
This then brings me back to the original questions of how I can "freeze" the primary key while having it based on the 2 fields from that same table
Sorry, how do you mean "use a single number"?
You reference "Do not replace..." checkbox. I take that to mean that you are in the autoenter dialog...and that is probably your initial problem.
Make your assembled field a calculation field, not an autoenter.
Then define it as Table::CompanyName & Table::CountryCode
This references your company name field, but certainly won't replace its contents.
It is OK to have a calculated field be a key field.
In your Client Table...
Create a Number field say .... pkClientID. In the Options... Auto Enter Sequence number starting at 1 increment by 1. Make that field non Modifiable by USER.
In all other Tables... like the Contact Event table I used for example...
Create a Number field say... fkClientID. Relate the pkClientID>------<fkClientID and Allow creation of records in the Relationship.
When you make a New Record in the Contact Event table... you put in the fkClientID=pkClientID showing which client made the Contact Event. There are many ways to do that, depending on how you set up your DB.
In your Client Table...
Ninja, I have done as you suggested but if I change the Company name field it replaces the contents in the __pkClientID calculation field, so just the same as the AutoEnter one. Thoughts? And yes I tried checking the "Do not store calculation results--recalculate when needed" but still same problem.
Sorry Jim I don't really want to create a Client from another table. I'd rather create the client first then go from there. The thing is I want to compound the __pkClientID. I've already had it setup with an incremental number and for my purposes wasn't great.
Anyone else have an idea as Ninja's suggestion doesn't seem to work unless I'm doing something wrong.
I would never try to produce a pk from two or more fields. I'd always use either an internally generated serial number or an auto-entered Get (UUID) to unqiuely identify each record in the table.
THe only way, I've have such a field in my database is to support specific user requirements to to facilitate specific search/reporting needs and then I would have just those fields in the parent table and would not use it in relationships--which would mean that I am not using it as the pk field.
Ditto what Phil said. You can still have your concatenated field. Just. Don't use it as a primary key. There's not really any reason to.
yes, ditto to the 4th power and add sugar on top.
@Phil I HAD to make a Compound Invoice number for one solution since for Posterity reasons, as you noted, they had everyone trained on those archaic type serial numbers.
I still used a "secret" numeric field to Relate records and just displayed the Archaric compounded number for them.
Ok thanks guys. I'll use it as a display only then and have an auto-generated one in the background. Thanks for the advice.