Hello Knobby,
If I am not mistaking, creating a relationship between tables in MVD is easily done by using the relationship type of field at design time. But the foreign key is always linked to the id field in MVD. I do not think you can change that behavior directly in MVD.
I see two different approches to your problem :
What you can do to stick to your specifications is add this constraint manually, keeping in mind that during the all conception process, each time you will recompile your application add start with a blank database, this feature will drop
To add a foreign key by script, you'll need something like :
ALTER TABLE calls
ADD FOREIGN KEY (accountnumber)
REFERENCES clients(accountnumber)
You'll have to double check that script, I'm working by head, drinking my morning coffee in the garden and not fully awake yet
As I said, this modification will drop each time you delete your database and recreate a new one with MVD. Once the script was run once, as long as you keep the same database file, even if you recompile MVD, the modification will stand.
What I usually do in such cases is, on start of the application I distribute, I check a flag in a parameters table to see if this is the first time the application is run. If yes, the the script fires before anything else, if no then the application starts normally.
Also keep in mind that you are creating constraints manually, so you'll have to check yourself for consistency. You said that the calls would be imported. Make sur there is no accountnumber in your imported file that would not be present in your clients table or you'll get errors.
The second approach would be to add a relationship table between the clients and the calls table. You could name it clients_calls and just add a relationship to the clients table and to the calls table. This would probably be easier than modifying the database by script.
Anyway, I hope this is not to theorical as an explanation. Don't let yourself be discouraged by this little problem, there is always a solution and people here on the forum will help you further if needed. Do not hesitate to ask.
Cheers
Math
I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor