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.
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