Database Design Documentation
Database Design Documentation
When designing links between sheets, in addition to creating a new sheet from a Subtable, you can create a Reference Subtable from another sheet to automatically list related records.
For example, if you have a "Sales Order" sheet and an "Accounts" sheet, you may add the "Sales Order Information" as a Subtable in the "Accounts" sheet to view each customer's order history. This feature quickly consolidates relevant records from the database.
To add a Reference Subtable to list related order records in the "Accounts" sheet using the Show References From Existing Sheets, ensure that the "Sales Orders" and "Accounts" sheets both have the same Linked Field with Link & Load.
Note: The setting method for Linked Fields in independent fields and Subtable fields will differ. Below are explanations for both methods.
1. Linked Fields in Independent Fields
2. Linked Fields in Subtable Fields
First, enter the Design Mode of the "Sales Orders" Form Page. In the From Tools menu, under Linked Sheets, ensure it is linked to the "Accounts" sheet in Link & Load.
Next, return to the Design Mode of the "Accounts" sheet and select Show References From Existing Sheets under Sheet Linking.
It will automatically list all linked sheets. You can directly find and select the sheet you want to add as a Reference Subtable from this list.
Hover over the linked Subtable, which will turn green. Hold down and drag it to the desired position.
In the field selector window, use the arrows to place the fields you want to include in the Subtable under Selected Fields and move the ones you do not wish to display under Available Fields. Adjust the order of fields under Selected Fields using the up and down arrows.
Once completed, click the Insert Subtable button and save the design. The Reference Subtable will then be added to the sheet.
You can see that the Reference Subtable in the "Accounts" sheet automatically consolidates all past order records for that customer.
First, confirm that the Subtable contains fields linked to the sheet from which you want to insert a Reference Subtable in the Form Tools menu.
Use the New sheet from Subtable to create an independent sheet from that Subtable.
Then, similar to Linking fields in independent sheets, use the Show References From Existing Sheets to populate the Reference Subtable with data from the new sheet.
For example:
1. There is an "Employee Management" sheet that has a Link and Load relationship with the "Project Management" sheet through the "Owner" field in the Task Subtable.
2. You want to display all "Project Tasks" assigned to an employee as a Subtable within the "Employee Management" sheet.
3. Utilize New sheet from subtable to create a new "Task" sheet from the "Tasks" subtable in the "Project Management" sheet.
4. Use Show References From Existing Sheets to add a Reference Subtable with data from the new "Task" sheet.
When you add new fields to the source sheet and want to include them in the Reference Subtable, navigate to Form Tools to find the source sheet listed under Show References From Existing Sheets in the Sheet Linking section, then click on the sheet name.
In the pop-up window, add the fields you want to include to the Selected Fields.
You can add a button for quick switching to create a new record on the source sheet that this Subtable refers to. Navigate to the list of linked sheets under Show References From Existing Sheets in the Form Tools menu, then click the gear icon next to the sheet.
Click on the "Create button" and name the button in the window.
Clicking the button will directly open the source sheet.
You can then add new records in the source sheet, and the corresponding linked fields will be automatically populated.
Note: If you want to remove Action Button, you may refer to this article
You can create links on text fields within the Reference Subtable to take you to the corresponding source sheet.
For example, there is an "Accounts" sheet with a Reference Subtable containing "Sales Order Record", linked using Show References From Existing Sheets to the "Sales Orders" sheet.
In the Design Mode of the Form Page, navigate to Form Tools. Click the gear icon next to the "Sales Orders" sheet, listed under Show References From Existing Sheets.
Select the "Orders Number" field under Create a link on this field to go to the new sheet. If the referenced source sheet has Multiple Versions, you can set a default version to display.
After saving and exiting the Design Mode, you will notice that the selected field values have become links.
Clicking on any of these links will take you to the related record in the source sheet.
For more detailed information about Show References From Existing Sheets, you can refer to the following tutorial video.
