The primary, fastest, and probably cleanest way of creating and maintaining a Link between the dataviews, is by the use of the drag-and-drop method.

There is no Link tab within the definition itself, so any Link needs to be forged on the designer canvas.

There is another alternate way to create and maintain a Link through the Link button which is discussed in Workbook Dataview - Link Button - Excel File Definitions.

Every dataview that is created will have two (2) additional fields automatically generated and inserted into the dataview by Statelake, to enhance and support the building of any dataview relationships. These internal-use only fields are called ID and MASTER_ID, and are not included in any mapping process.

image-20240430-015122.png

While in theory you can link any field in one dataview to any field in any other dataview, it is usual to only link a MASTER_ID of one dataview into the ID of the dataview being linked into.

So for instance, you would Link the MASTER_ID of an invoice lines dataview into the ID of a dataview for the invoice headers. Or you would Link the MASTER_ID of a dataview holding contact names and details into the ID of a dataview holding the company information - and not vice-versa.

In this example we have a list of random details in an Excel spreadsheet, spread across three ( 3 ) different dataviews. The TESTING_TIMES_EXCEL dataview is set to be the top level, with TEST_BUNNY set for the 2nd level, and DETAILS the last. So TEST_BUNNY will be linked into TESTING_TIMES_EXCEL.

image-20240430-020100.png

To add this Link relationship, the MASTER_ID field from the TEST_BUNNY dataview will be dragged and dropped onto the ID field of the dataview TESTING_TIMES_EXCEL .   

Click on TEST_BUNNY .MASTER_ID, and drag it so that the label shows as per the example, then unclick to drop it.

The label should read “Link detail TEST_BUNNY .MASTER_ID to master TESTING_TIMES_EXCEL .ID”.

You will see the label change to this as soon as the tip of your pointer lands on the ID field of TESTING_TIMES_EXCEL.

image-20240430-020443.png

Once released, three ( 3 ) things will happen. 

  • The TEST_BUNNY dataview will be re-named to TEST_BUNNY linked to TESTING_TIMES_EXCEL,

  • A one‑to‑many relationship link line and symbol will appear between the two dataviews - the one “single” link pointing at ID in TESTING_TIMES_EXCEL, and the “many” pointing at MASTER_ID in the renamed dataview TEST_BUNNY linked to TESTING_TIMES_EXCEL.    This link line is not placed exactly on the fields, but will appear at the top of the items that are linked.

  • And ID in TESTING_TIMES_EXCEL will be bolded, as will MASTER_ID in TEST_BUNNY - to indicate the fields that are linked.

    image-20240430-020753.png

The link between the two datasets has now been successfully completed.  

The colours of the link lines have no meaning.

They are random and arbitrary.

The colours of the link lines may be different every time that the definition is opened and the dataviews are viewed on the designer canvas.

You can edit the link by double-clicking on the link line, which will open an editing window. For information about this window, please review Workbook Dataview - Link Button - Excel File Definitions.

Columns and window borders can be resized as required by dragging them with the mouse.  

Close the definition canvas by selecting Close from the File menu.   You will return to the File Definition window.   Click Save to save and close the module.   

Always remember to select SAVE here – else all of your hard work linking these datasets will not be saved.