In LightSwitch how would I create a code written relationship on a datasource between tables? RRS feed

  • Question

  • I have three lists from SharePoint.  One called Exclusions, one Controls, and the third standards.  Controls links to Standards through an ID which is fine, and I have that relationship created.  The third table is a table that contains one field called Code.  This field links to the standards table by the Code as Standards.Code and Exclusions.Code will match on occasion and the relationship will work.    The problem is that Controls does not have a Code field.  The code for Controls is made up of the Standard.Code field + a "." + the Control_Number.  I can create a computed field on control to create the correct field to relate to the Exclusions list with :

    partial void Code_Compute(ref string result)
                // Set result to the desired field value
                result = this.Standard.Code + "." + this.Control_Number;

    The problem is I can not then create the relationship between this computed field and the Exclusions list.  Is there a way to do this in code?  If so how would I do it?  I am using Visual Studio 2015 and Lightswitch Data sources for the data which link through to Sharepoint.  

    Data examples:
    Exclusions.code = "IM"   Standards.Code = "IM"
    Exclusions.code = "IG"    Standards.Code = "IG"
    Exclusions.code = "IG.1"  Controls.Code = Lookup to related Standard.Code("IG") + "." + Control_Number("1") which gives "IG.1" - I can create the computed field fine, I just want the relationship to exist between Exclusions.code and the computed Controls.code. 


    Wednesday, September 23, 2015 3:26 PM


  • Hi Chris,

    You cannot create relationships in LS using computed properties.  You could workaround this but it would require adding fields in your SharePoint lists and some code to update these fields.

    For example, save the Standard.Code value in a field called StandardCode of both Controls list and Excusions list and also save the ControlNumber in the Exclusions list. Then you could setup a relationship between:

    Exclusions.StandardCode = Controls.StandardCode and

    Exclusions.ControlNumber = Controls.ControlNumber

    To populate these fields, you could use data service code in the _inserting and _updating methods.

    Note: if these lists get populated in SharePoint, outside of LS, then you'd have to maintain these fields there too which can be a challenge.

    Alternatives include adding a separate table for ControlExclusions or perhaps using a WCF data service.



    • Proposed as answer by Angie Xu Monday, October 5, 2015 8:35 AM
    • Marked as answer by Angie Xu Tuesday, October 6, 2015 8:10 AM
    Wednesday, September 23, 2015 4:37 PM