Forum Discussion
Sharepoint Linked Table - Help
In Sharepoint list, the only way to designate and enforce Referential Integrity on a relationship between two tables is the Lookup Field mechanism. In the screen shot, although the two fields are named the same, the data in them may or may not match up at all. I'd have to see the actual fields in SharePoint to be able to offer suggestions.
The problem with Lookup fields is that they often DISPLAY one value, but are bound to a different value, i.e. the Foreign Key. To the human eye just looking at the DISPLAYS, there is no way to know what the bound, underlying Foreign Key is.
Invest some time in learning how to designate Lookup fields in SharePoint with what they call "Data Integrity" or something similar. That's the closest you'll get to Referential Integrity.
The other query method you describe, with no relationship between tables, creates a Cartesian Product. Each record in the first table is returned along with each record in the second table. Hence thousands of pairs of fields in your result. There are niche cases where a Cartesian Product is useful, but not in a relational table.
Thank you for your response and assistance, see below comments.
I have tried to run a query between the two tables but no joy. The [Mix Type] column in the insitu concrete table is a 'choice column' in Sharepoint, as this List was developed before I introduced the [Concrete_Mix_Design_Costs] table. I was hoping that as the two field are named the same and have matching information i could create a relationship between them, but I am unable to do so.
When I add the [Concrete_Mix_Design_Costs] table into the query and add the [Cost/M3] field i get zero results if I create a link between [Cost/M3] in both tables.
If I remove this link the Access query give me a a result that has a each price from the [Concrete_Mix_Design_Costs] table multiplied by each record, therefore 3000+ results instead of 365.
I get your point regarding the naming of the Cost Field and will see if i can change this going forward. I hope i have explained my issue a bit clearer. Thanks
In Sharepoint list, the only way to designate and enforce Referential Integrity on a relationship between two tables is the Lookup Field mechanism. In the screen shot, although the two fields are named the same, the data in them may or may not match up at all. I'd have to see the actual fields in SharePoint to be able to offer suggestions.
The problem with Lookup fields is that they often DISPLAY one value, but are bound to a different value, i.e. the Foreign Key. To the human eye just looking at the DISPLAYS, there is no way to know what the bound, underlying Foreign Key is.
Invest some time in learning how to designate Lookup fields in SharePoint with what they call "Data Integrity" or something similar. That's the closest you'll get to Referential Integrity.
The other query method you describe, with no relationship between tables, creates a Cartesian Product. Each record in the first table is returned along with each record in the second table. Hence thousands of pairs of fields in your result. There are niche cases where a Cartesian Product is useful, but not in a relational table.George
Ok got you regarding the lookup fields to create the relationship, I should have asked the question earlier would have saved me a lot of messing.
Going forward I will change the choice field to a lookup field it will work better for me done this way as it will keep the source Data better controlled.
I will do as you suggest and have a look at the mechanics behind linked Sharepoint lists.
Thanks again for your help.
Henry
