I am loading data in as seperate excel spread sheets.
Is there anyway to create alternative routes in a data structure. I refer the attached data structure.
Currently, the Lease table is linked from the Properties table via the Areas and Area to Lease Link Tables. I need to create a situation where this data structure remains while a link also exists directly between the Properties Table and Leases Table.
The reasons being is that all leases are attached to a property, however, only some leases have an area attached. So the current structure fails to recognise leases without area's when selections are made in the document.
I understand to do this a loop will be created, but pondered whether there is a formula (could be the wrong term) that can be written into the script to create a situation where both paths can be reference and the correct path choosen?
Or any other work around?
Any help / idea's much appreciated.
Let me know whether more info / further explanation is required.
Creating loops is not a very good idea. You'll get loosely coupled tables and those can give unpredictable, inconsistent and plain wrong results. You could try to create a link table that stores the relationships between the objects. See this document for an example.
I think the table relationships in your QV document might be wrong. I would assume that the link between leases and property would be a direct one, not via the areas table. If there exists a direct link between the propery and the leases table then I would join these tables on this relationship and remove the relationship between the property and the area's table. For the Area's table you could if it is only one value us an applymap to attach the correct column onto the leases table. If not just keep the existing table joined as it is.