locked
Relationship issue - Multiplicity is not compatible RRS feed

  • Question

  • I had tremendous assistance yesterday but unfortunately during the install of security tokens tech support rebooted my system without considering my open files and corrupted my work.  I found myself having to start my nearly completed project over again.

    I took the opportunity to inject some improvements in my project based n input from the three folks that helped me out yesterday and now I have one more gotcha that I'm dealing with that has me scratching my head.  Once I'm by this I should be home free.

    First let me explain I'm dealing with table structures that I have built external to LS due to some specific requirements we have.  What I'm running into is that I'm getting the following error when trying to setup the relationship.

    What would cause this and how do I correct this. my tables have provided the necessary provisions that I believe should drive the process but it still is having issues with it.  Below is the smaller of the two tables being linked as an example it only has one field that has significant data that is application specific, the rest are present only because of LS. :

    Families is a list of security families that sits at the top of the app and from there I wish to drill down into other aspects. Ie: from here I will go in to family groups. From there into particular articles related to those groups, from there specific instances etc.  So, to take this a step further all of the tables I've built have these characteristics short of the 'FAMILIES' field so that LS is happy to do what it does with them what it would with it's own tables.

    The trouble is that I'm getting this Multiplicity message and have no clue how to combat it. HELP!


    Ken Carter

    Thursday, September 24, 2015 6:48 PM

All replies

  • Hi Ken,

    Sorry for your trouble.  It's acting like SecFamily.Families is not required.

    I see in your screen shot it shows NOT NULL which is good.  Any chance it was not required in SQL Server when you first attached to external table?  Does it show required checked for that property in the LS table designer?

    Josh

    Thursday, September 24, 2015 8:11 PM
  • Yes... but the other side of this is where my concern is in that it is pointing to a view which is not checked and I can't control because it IS a view. Now the kicker... is that this same view worked perfectly fine before the incident earlier in the week that forced me to redo this. So the question is why? I can and will figure out ways around it just stuff like this drive me crazy.

    I did go and change the source side that you're looking at to NULL I will flip it back to NOT NULL.  From the apps perspective it doesn't matter because it all is static data that doesn't get changed and the use will not be allowed to modify 'this' information. so none of it is going to be null in the first place. Just have to have it set right to get the drill down working correctly.


    Ken Carter

    Thursday, September 24, 2015 11:27 PM
  • Does multiplicity error go away if you change it to 'zero or one' to many?
    Thursday, September 24, 2015 11:48 PM
  • If you flip it that way it comes up with the following error:

    

    One to zero or one relationships must be mapped on the key fields and

    only the key fields of both sides of the relationship. 



    Ken Carter

    Friday, September 25, 2015 12:47 AM
  • Well I got around that error and got on to a new one... I seem to jump from one issue to the next but am learning as I go. Hoping that this is the last hurdle before I'm off and going. Could someone tell me what this is asking me to do on the primary side of the relationship? I thought I had fulfilled all the requirements but apparently I have not.

    Here is my table layouts for both tables:

    Would like to at least get this solved so I can get this laid out over the weekend.  Any clues or suggestions would be appreciated.


    Ken Carter

    Friday, September 25, 2015 2:24 PM
  • Hi Ken,

    LS supports using only the Primary key field(s) on the one side of a relationship between two tables in the same data source.  To make this relationship, you'd have two options:

    1) modify the view on the foreign side to include the ID of the related SecFamilies then you can use ID on the primary side in the LS relationship.

    2) attach to the same database twice in your project and make a 'virtual relationship' between the two entities.

    If this answer makes you want to pull your hair out, then maybe describe what your entities represent and what you're trying to do with this relationship.  Like "user picks a family then you want to requery a drop down of related groups" 

    There may be another way(?)

    HTH,

    Josh

    Friday, September 25, 2015 4:14 PM
  • All this data exists in one table if I could figure out how to get LS to deal with it properly.  Let me see if I can lay it out in an elegant fashion effectively here.

    Hope that give you a clearer idea of what I'm trying to do with it. I'm a bout ready to give up on doing it with LightSwitch, but the process after this I think LightSwith would be choice for... it's just leading up to it that is driving me crazy with this legacy data to accommodate.


    Ken Carter

    Friday, September 25, 2015 4:49 PM
  • Thanks Ken. Now I understand you're dealing with legacy data and your 'tables' in LS are really database views.

    Now supposing I cannot talk you out of using this legacy data you'll have to take a good look at this article:

    http://blogs.msdn.com/b/lightswitch/archive/2014/05/20/attaching-to-sql-views.aspx

    Basically a nice feature of LS is that you can decide what field(s) constitute the 'key' for each attached view.  I order to make the view updatable, the fields you pick for key must be required fields in the underlying tables and the combination must result in a unique row in the view.

    Assuming you don't really need the views to be updateable and that your SQL for SECFamilies View is something like:

    SELECT ARSFamily, Min(ARS_Index) as ARS_Index
    FROM  dbo.csr_standards_cmsars
    GROUP BY ARSFamily;

    You should be able to set the isKey flag in the LS Table Designer to true for ARSFamily and false for the others.  Doing this should fix the relationship error in your image above.

    Setting IsKey on your other view to both ARSFamily and ARSDocTag (assuming that combination is unique for your next level in the hierarchy) should help on your next relationship to Elements.

    Finally, I assume Elements is a table that your users on LS app will actually Add\Edit.  That table should have and autonumber ID field, rowversion, created/by, modified/by and also ARSFamily and ARSDocTag for use in relationship to the second view.

    Let us know how it goes.

    HTH,

    Josh

    Friday, September 25, 2015 6:18 PM
  • I'm circling back to update you all on my progress (and yes there is progress) with the project. I took the information that was provided above and realized two things. 

    Frist one was that Re-engineering my legacy data tables was likely my best approach to get to a solution rather than hack around blindly in an interface I knew little about being so new in dealing with it.  You all have provided me with so such a better set of specifications that it would seem that I had a much better chance of establishing a usable data set from the legacy data to leverage and eliminate a LOT of frustration going forward.

    Next, I also, took the approach that short of one view to narrow the listing of families to the 28 that exist I only needed the one view the rest of this initial layout can be done from a single table.

    I'm happy to report that everything is working great as far as having the relationship links working now so that is all set. Here is a lay out of  what I ended up with:

    The only problem I am having now is that when I click on the top level it doesn't drill down. ie: I can click on any given family member and I will come into the second level as if I clicked on the first one.

    I can navigate all the way though if I want but I need some way to tell LS to use the selected family member to look up only that family members information in the ARFS_standards_cmsars table and not everyone else.  It should be narrowing the scope of information and it is not.

    Any thoughts?


    Ken Carter

    Monday, September 28, 2015 4:35 PM
  • By the way I figured you want to know how I'm getting from the top screen to the group listing screen that is with a tap show... captured here..

    


    Ken Carter


    • Edited by kencar Monday, September 28, 2015 4:43 PM
    Monday, September 28, 2015 4:42 PM