locked
query filtering advice? RRS feed

  • Question

  • hi!

    i have requirement to filter table based on specific related property:

    i'll try to explain on generic example:

    ie. I have table of Products, Products have relationship with Categories (like Fruits, Beverages, Candies).. I want to allow specific user(s) to filter Products where category is Beverages, and some other user(s) to filter Products where categories are Fruit and Candies, I could probably do that with some hard coding inside query, but i would like to have this dynamic (specific user log into app and when he/she opens Products screen, he/she can see only products in specific Category), can this be achieved? thanks!

    Kivito


    Nobody expects the Spanish Inquisition! (M.P.F.C.)

    Friday, May 31, 2013 2:52 PM

Answers

  • ok, this seems to work (again on generic example):

    i have "usersReg" mapped to "categories" across "usersReg_categories" table in many2many relation..

    in categories_filter method:

    Private Sub Categories_Filter(ByRef filter As System.Linq.Expressions.Expression(Of System.Func(Of Category, Boolean)))
    Dim user = Me.Application.User.Name
    filter = Function(o) o.UsersReg_Categories.Any(Function(x) x.UserReg.user_name = user)
    End Sub

    and it works on autogenerated screen also.. for now.. :)

    Kivito


    Nobody expects the Spanish Inquisition! (M.P.F.C.)

    • Marked as answer by Kivito Monday, June 3, 2013 5:01 PM
    Monday, June 3, 2013 4:59 PM

All replies

  • Kivito

    You need to create one table UsersCategories, and assign categories to the user, after create the queries for the screens and use the user for filter the products.

    Relation:

    ======

    Users (One)

    UserCategories (Many)


    Norman

    Friday, May 31, 2013 2:59 PM
  • hi Norman!

    thanks for response, but i didn't find a way to use it in that way, i had to create mapping table between categories and users (many to many), so i can assign which categories specific user can see, but this leads to another problem:

    how can i tell lightswitch which query to use for dropdown (autocomplete) box in autogenerated "addAndEditModal" window (as global query)?? i guess i should use "_filter" method on server, but didn't try this before.. thanks!

    Kivito


    Nobody expects the Spanish Inquisition! (M.P.F.C.)

    Monday, June 3, 2013 6:40 AM
  • ok, this seems to work (again on generic example):

    i have "usersReg" mapped to "categories" across "usersReg_categories" table in many2many relation..

    in categories_filter method:

    Private Sub Categories_Filter(ByRef filter As System.Linq.Expressions.Expression(Of System.Func(Of Category, Boolean)))
    Dim user = Me.Application.User.Name
    filter = Function(o) o.UsersReg_Categories.Any(Function(x) x.UserReg.user_name = user)
    End Sub

    and it works on autogenerated screen also.. for now.. :)

    Kivito


    Nobody expects the Spanish Inquisition! (M.P.F.C.)

    • Marked as answer by Kivito Monday, June 3, 2013 5:01 PM
    Monday, June 3, 2013 4:59 PM