Linking 2 tables on specific fields

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

Hello knobby,

I think I did not read carefully you request, or I should have noticed what you were writing : you can show all the calls related to a client without using the accountnumber as a link between them.

Furthermore, you can not in MVD link your calls to your clients on the accountnumber. This needs a lot of scripting and is just not practical for the average user. But you will need it in your import file if you want to be able to link your imported calls with your clients.
But with a very slight modification, you can achieve what you want.

I noticed you did not mention any ID for either you clients or your calls tables. Such denormalized tables can not be build with MVD, for the same reason as stated previously, it is not easy nor useful for the average user.

One more thing before giving you the sample project : forget about my complicated solutions : I tend to script as much as I can (because I love it). I am known on the forum for that and I sometimes tend to forget that the simpler, the better

Now, on to your project :

1 client can have multiple calls. So I just added a reference to the id of the client in the calls table.

Take a look at the Button1 (hidden when the application is running) and how it is configured. It's a search button, fetching the calls table according to the client you select in the left tablegrid (the tablegrid has increm. search parameter linked to button1), and displaying the results in the right tablegrid

Had a new call with the button and you'll see it appear right away. No code, no script, just pure MVD.

Sorry if I frightened you with my complicated solutions, I'll try to keep it simple next time.

Cheers

Mathias

PS : if you need help for the CSV of Excel import, do not hesitate. There will be script needed for that because your file to import will not contain the client id.
1- read one line of the file and get accountnumber
2 - get the client id based on accountnumber (sql query)
3- insert the data in the calls table (second sql query)
4- read next line...

Knobby,
I am familiar with Alpha 5. I have used it as well and still do on occasion. You are right that you can set relationships on any field in Alpha. However, I always used auto increment integers for primary keys, much like MVD does. It's really a better database design to do so. Having said this I used Mathias project and adjusted it to include the CSV import so you can see how it all works using MVD's relationship definition. There is nothing fancy with the code. The only error checking I did was to check to see if there is no matching account number in the CLIENTS table. Errors are imported into an Error table so you can see which calls were not imported. Hope this helps.

Thanks everyone for your help and input. I'm still trying to understand how I can make this work.

So..... it appears that I will have to manipulate my CALLS data before I import it, to add a field which converts my "accountnumber" field into the id for the corresponding record in the CLIENTS table. Is this correct?

I don't understand what is meant by "foreign key".

I thought what I was trying to do would be really simple but it appears not to be the case.

I don't have a project to show yet as I'm just playing around to see how I can achieve what I want, which is to show a list of calls made by someone (the list is generated in another application) against their name. The list of calls contains the data "accountnumber", "date/time", "duration" and "cost". It does not contain any CLIENT details other than the account number. I've attached a csv file showing the data which will need to be imported and linked to each client. For example, when you look at the data in the csv file I am accountnumber 100.

The more I think about this the more I get confused!

Thanks again for your help. Unfortunately I cannot import any data - I get and error which says "07/31/2016 20:18:26 is not a valid date and time".

But it looks like you have added the account number to the calls records via this script. Is this correct? If so, and I have to do this level of scripting to accomplish the simple task of linking two tables together then I think that MVD is much too complicated for me.

This was to be a simple little starter project to get me used to MVD before I attempted something more complicated like a sales/stock control system. This would need tables to be linked together via multiple ways and fields. Very simple to achieve using Alpha 5, but appears nigh on impossible for a relative beginner like me using MVD.

I was about to reply to try to clarify things when I noticed Derek already did. I couldn't have said it any better. Thank you Derek. If I may add more info for Knobby. This may not be evident in the beginning for folks first learning MVD, but whenever you create a table in MVD, an auto increment unique primary key called "Id" is defined for you automatically in each table behind the scenes. If you have a parent-child relationship (linking 2 tables in your terminology), all you have to do in the child table (Calls in your case) is to define a relationship field and select the parent table (Clients in your case). MVD, as Derek mentioned, will define the relationship automatically for you. This is the Foreign Key. The field name is defined automatically for you as "id_parenttable" ("id_Clients" in your case).

If you go into the Database tab in either my project or Derek's project and click on the Database Schema button next to the New Table button, you will be presented with the Database schema which will show the primary keys "Id" in each table and the relationship between the Clients table and the Calls table. It really is an easy process in MVD and much simpler than with Alpha where you have to define and create "Sets" to do the same thing. As I mentioned before I am familiar with Alpha and even though you can set the relationships on other user fields, I never did or do. I always created the parent-child relationship sets using ID key fields, much like MVD does automatically. It's just good database design to do so.

Just to reiterate what Derek said, the only purpose for the script in my project was to populate the Calls table from the CSV file. Again, you do not need to manipulate your CSV file to import into MVD. Derek's example is a good one to show the relationships (linking of tables) without the confusion of the import script which obviously threw you off a bit. I apologize for that confusion and I surely hope I haven't confused you more.