Database Design Documentation
Database Design Documentation
Subtables are great tools for managing one-to-many relationships in data. However, in some cases, storing all data exclusively in Subtables may not be convenient. For example, when you need to analyze sales details from the past month using reports or export purchase records across datasets. In such situations, you can use the Generate new sheet from Subtable to display each Subtable record from the main sheet as an independent record in a new sheet.
In Design Mode, click the Form Tools, and choose the New sheet from subtable option under Sheet Linking.
In the window, the Subtables in the sheet will be listed. Select the Subtable for which you want to generate a new form and confirm the settings: The new sheet will link back to this form by selecting a "field in this Subtable". Choose how the new sheet will link back to this sheet, so the system knows how data in the new sheet links to the original sheet's Subtables. Please select the Unique Value field from the original sheet, like "Order Number". This will link each order detail on the new sheet to the original sheet through the "Order Number".
Once confirmed, click Create new sheet, and the Subtable fields will become independent fields in the new sheet.
In the new sheet, in addition to all fields from the Subtable, there will be fields linked to the original sheet, such as the "Order Number". This field helps you identify which Subtable data is associated with which record in the original sheet. The linkage between the new sheet and the parent sheet will be managed through Link and Load.
Therefore, the "Order Number" field in the new sheet, which links to the parent sheet, determines which record in the parent sheet's Subtable corresponds to the new sheet's data.
Conversely, creating a Subtable in another sheet is also possible. For more details, please refer to Show References From Existing Sheets.
When you create new fields to the new sheet and want to add them back to the original Subtable, navigate to Form Tools to view all the current links listed under New sheet from subtable in the Sheet Linking section, then click on the sheet name.
In the pop-up window, add the fields you want to include on the right side.
This allows you to display the new fields on your original Subtable.
You can add a button for quick switching to create a new record on the new sheet by accessing the list of linked sheets under New sheet from subtable in the Form Tools menu, and then clicking 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 new sheet.
You can then add new records in the new sheet, and the corresponding linked fields from the parent sheet will be automatically populated.
Note: If you want to remove the action button, you may refer to this article.
When you have generated a new sheet from your Subtable, you can create links on text fields within the original Subtable. Clicking these links will take you to the corresponding record on your new sheet.
For instance, if there is an "Accounts" sheet with a Subtable for "Contacts", the "Contacts" sheet is generated from this Subtable.
In the Design Mode of the Form Page, navigate to Form Tools. Click the gear icon next to the sheet generated from your Subtable, listed under New sheet from subtable.
Select the "Contact Name" field under Create a link on this field to go to the new sheet.
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 sheet generated from this Subtable.
For more detailed information about Generate New Sheet From Subtable, you can refer to the following tutorial video.
