locked
Filtering data based on a users membership of multiple groups RRS feed

  • Question

  • Hi all,

    I have been puzzling over this one for a few days and thought I would ask the experts.

    I am trying to filter data in a preprocess query against any of the sales territories that may be associated with a salesperson. The key being that the salesperson will certainly have more than one territory.

    I have succeeded in filtering data based on a single membership using the excellent walkthrough provided by Richard Waddell

    Dim person As SalesPerson = (From persons In Me.DataWorkspace.ApplicationData.SalesPersons Where persons.UserName = Application.Current.User.Name).FirstOrDefault()

    Dim salesTeamId As Integer = If(person Is Nothing, -1, person.SalesTeam.Id)

    query = From theSales In query Where theSales.SalesTeam.Id = salesTeamId

    This works brilliantly however I want to broaden this. I have created a Territory table (Containing 'North', 'South' etc) and a Sales_Person_Territory table which captures the territories associated with the individual user.

    Each sale is related also to the territory table and so each sale will have a single territory

    The linq query i need to create would therefore filter the sales based on the territorys of the sale and whether those were in the Sales_Person_Territory list for that particular Sales person

    I have tried several approaches none of which have been successful. I would have thought that this kind of filtering is a pretty common need for Lightswitch. Could anyone give me some pointers?

    Thanks

    • Edited by edcredagas Friday, October 10, 2014 3:54 PM
    Friday, October 10, 2014 3:49 PM

Answers

  • Please be careful and double check syntax (and logic structure) cause I'm coding manually without your tables.

    In your Sales pre-process query, after retrieved salesTeamId you could do something like this

    Dim idTerritoriesIn = (From t In Territories
         Where t.SalesTeams.Any(Function(st) st.SalesTeam.Id = salesTeamId)
         Select t.Id).Execute.ToArray
    
    query = query.Where(Function(s) idTerritoriesIn.Contains(s.Territory.Id))
    Hope this helps.


    Marco

    • Marked as answer by edcredagas Monday, October 13, 2014 4:45 PM
    Saturday, October 11, 2014 9:07 AM

All replies

  • Please be careful and double check syntax (and logic structure) cause I'm coding manually without your tables.

    In your Sales pre-process query, after retrieved salesTeamId you could do something like this

    Dim idTerritoriesIn = (From t In Territories
         Where t.SalesTeams.Any(Function(st) st.SalesTeam.Id = salesTeamId)
         Select t.Id).Execute.ToArray
    
    query = query.Where(Function(s) idTerritoriesIn.Contains(s.Territory.Id))
    Hope this helps.


    Marco

    • Marked as answer by edcredagas Monday, October 13, 2014 4:45 PM
    Saturday, October 11, 2014 9:07 AM
  • Awsome, thanks Marco, i will give it a try.
    Monday, October 13, 2014 9:17 AM
  • Hi Marco,

    With a bit of tweaking that worked a treat. The finished code is below for reference. I had planned to abandon the concept of team in favour of the many to many relationships of salesperson and territory so:

                'Populate an array of territory ids where the the previously identified salesTeamID is equal to the sales
                Dim idTerritoriesIn = (From t In SalesTerritories
                     Where t.SalesPersonTerritories.Any(Function(st) st.SalesPerson.Id = person.Id)
                     Select t.Id).Execute.ToArray
    
                'return sales where territory is in the array idTerritoriesIn
    
                query = query.Where(Function(s) idTerritoriesIn.Contains(s.SalesTerritory.Id))
    

    However by keeping team in your example I realise that I can change the relationships to map territories to teams and that way map the relationships in two stages making management far easier.

               Dim idTerritoriesIn = (From t In SalesTerritories
                Where t.SalesPersonTerritories.Any(Function(st) st.SalesTeam.Id = salesTeamId)
                     Select t.Id).Execute.ToArray

    Either way this solves my problem and makes RLS in lightswitch far more appropriate to my own business need than the  = Application.User.Name examples that I have been finding.

    Thank you very much

    James

    Monday, October 13, 2014 4:45 PM
  • Thanks James for your confirmation.

    Glad it helped.


    Marco

    Monday, October 13, 2014 5:43 PM