How do I create a relationship between two tables?

[ericky1k1]

I’m using Five to create a database (in the tables interface). What’s the fastest way for me to create a one-to-many relationship between two tables?
I have one table called Suppliers. and one called Services. I want to have the two tables related, so that one supplier can have many services.

[Jo-Anne]

Hi ericky1k1,

Five’s Table Wizard makes it very fast for you to create a one-to-many relationship between tables as all you need to do is select the table to create the relationship with and Five will handle creating the Foreign Key field and index.

Below is a short video to show you how to create the relationship between your Suppliers and Services tables.

How to Create a Relationship Between Two Tables | Low-Code For Real Developers | Five.Co

Steps to Create a One-to-Many Relationship Between Two Tables

1. Select Tables in the menu.

2. Click the New Table Wizard button in the form app bar.

3. Type Suppliers in the Name field.

4. Click the Add Fields button.

5. Add the required fields and their definitions.

6. Click the Save button in the Table Wizard app bar.

7. Click the New Table Wizard button in the form app bar.

8. Type Services in the Name field.

9. Click the Add Fields button.

10. Add the required fields and their definitions.

11. Click the Next button.

12. Click the Add Relationships button.

13. Click the lookup icon in the Table field and select Suppliers.

14. Click the Save button in the Table Wizard app bar.

[jorgensen_lars]

I am following the example of building the Portfolio application.
The five tables in the application are now created: portfolio, exchange, sector, stock and stockPrice but during creation I did not create the relationship between the tables. With the tables created I cannot see where I can select a table and create the relationship with other tables. Can you guide me?

Kind Regards,

Lars Jorgensen

[Jo-Anne]

Hi Lars,

Just to clarify, did you create these tables through Five’s Table Wizard? Am I correct to assume that these tables are now saved in Five but the relationships between the tables are not created and you want to form these relationships without having to delete your tables and start again?

Unfortunately, once a table is saved in Five, you can not use the Table Wizard to edit the table, however, you can edit the record to create relationships through the Table Form.

With the tables you currently have saved in the Portfolio Application, you will need to edit two of the tables:

1. The Stock table to create foreign relationships with the Exchange and Sector tables, and

2. The StockPrice table to create a foreign relationship with the Stock table.

Edit the Stock Table

1. Navigate to the Tables view.

2. Select the Stock record in the list.

3. Click the Fields tab.

4. Click the Add Fields button.

5. Type ExchangeKey in the Field ID field.

Note:
If the relationships were formed in the Table Wizard the naming convention for key fields is the name of the table followed by the word ‘Key’. We will keep with these naming conventions here to ensure all your functions and queries work as you progress through the Portfolio documentation.

6. Click the lookup icon in the Data Type field and select GUID.

Notes:

• Five uses GUIDs (Globally Unique Identifiers) to uniquely identify the field.
• If you select a GUID, Five will fill in the data size automatically.

7. Click the Required switch.

8. Click the lookup icon in the Display Type field and select _Lookup.

Note:

When it is a foreign key relationship, we establish the display type to be a lookup, this ensures a value is displayed in the field and not the GUID.

9. Click the Save button in the form app bar.

10. Repeat this exact process to add the SectorKey field.

11. Click the Indices tab.

12. Click the Add Indices button.

13. Type ExchangeKey in the Index ID field.

14. Click the lookup icon in the Key Type field and select Foreign.

15. Click the lookup icon in the Foreign Table field and select Exchange.

16. Click the lookup icon in the Dependent Index field and select ExchangeKey.

17. Click the Index Fields tab.

18. Click the Add Index Fields button.

19. Click the lookup icon in the Field field and select ExchangeKey.

20. Click the Save button in the form app bar.

21. Click the Save button in the form app bar above the list.

22. Repeat this exact process to add the SectorKey index, however, you will be referencing the Sector table.

23. Click the Save button in the form app bar on completion.

Notes:

• You will be asked in the Table Upgrade window how you would like to fill in the fields for any previous records in the table, simply type 0 for each field.

• Your relationships will now be established for the Stock table. You will need to perform the same procedure on the StockPrice table to establish the foreign relationship with the Stock table.

When in the Table Wizard you can simply form these relationships by:

1. Click the Next button.

2. Click the Add Relationships button.

3. Click the lookup icon in the Table field and select a table.

Five will handle creating all the properties for you this way.

Please keep an eye out for our very soon to be released Version 2.1 as we have a database modeler where you can simply drag the relationships between tables!!!

<

[jorgensen_lars]

thanks a lot, Jo-Anne for the explanation. Now I see how the relations between the tables are created outside the Table Wizard.

[jorgensen_lars]

And Yes, I created the tables outside the Wizard but recreated them with the Wizard so I got the relations to work.

[Jo-Anne]

You’re welcome Lars. And it’s great to hear you are getting your way around creating tables in different ways in Five!