This is probably really basic but I am having difficulty understanding how to use the Foreign Key data type in the project builder. I have added the foreign key to one of the tables. I keep expecting a way to add the table/field it is linked to in the Foreign table column but I can't seem to figure out how.

Please help.

Hello elohim, and Welcome to J-Cook and the Forum!

Pretty sure I can help you nail this one.

Here is a step-by-step in beginners terms:

Let's assume you are going to create 2 Tables. Address and Countries. When entering data into the Address Table using a View Layout Form you don't want to have to type the Country Name every time. You want a List of Country Names to select from instead.

Table 1 - Address
Data Types: Street (String), Town (String), Country (String)

When adding the Data Types for this Table drag the Foreign Key Data Type in for the Country Field, and name it Country Name. (Or whatever really)

This is assuming you wish to pull the Country Names from the next Table, Countries.

Table 2 - Countries
Data Types: Country Name (String)

Now, the important step. This creates the link between the two Tables and creates the Foreign Key.



Done! Now you have successfully created a Foreign Key in the Address Table which links to the Countries Table.

Now when you create a New PAGE (Layout) in the Back End (for example), a Back End Item Layout view, using the Address Table you will be able to click on the Fields Tab on the Left hand side and drag in the Fields from the Address Table (Street, Town) and expand the Country Field and you will see 'Country Name' which you can drag into this layout as well.

I'll leave the rest up to you to experiment with because that's half the fun and enjoyment of J-Cook! Hope that all makes sense. If not we encourage you to keep posting questions because that's what we're here for.

Regards,

Edward.

Thank you! That was very helpful. I did not catch that I could drag the table over to make the connection.

I do see however, that it defaults to connecting to the ID field of the second table. What if the connection between the two tables is something other than the automatically generated ID. For instance I have two tables with an organization id in them that would connect the two. How do I make that connection in the builder?

In my circumstance I have a table of organizations and a table of addresses. I want to connect the organization to multiple addresses by linking the org_id fields.

Unfortunatly, it is not possible to change the 'id' name.

Others users are asking for this, and I put it in the list of futures possiblities.

Welcome here, and enjoy the service.