My example will work only if the Region property is a string which seems to not be the case. Thus I will recommend a different approach.
Region.Region eq "${security.user.roles[0]}". This should filter based on the role name.This is all provided I have guessed your schema correctly 
Closer. It’s filtering but it filters out everything, every time, for everyone so I think the Schema is wrong. I know “Region”.Region is correct as the $expand worked to give the region value to the grid. I’m not sure if Region:“Region” is correct though. The field is named Region at the table level but when I look at the property in my grid it is labeled Region1
Ah yes - a C# class cannot have a property with the same name hence Region1. Try changing the $filter to Region.Region1 eq "${security.user.roles[0]}"
It still filters out everything.
What does ‘filters out everything’ mean? Returns empty result set? Can you paste the JSON output of the getDeals method without $filter set (but with $expand)? You can check this in your browser’s developer tools in the network tab.
Also I need to know if the value the user role is correctly set. You can display it by adding a Label component to your page and setting its Text property to ${security.user.roles[0]}
Yes the result set is empty.
Here is the response.
{"ID":245,"Created":"2017-04-20T00:00:00Z","Customer_SO":"2017-33","Supplier_Pay_Date":null,"StatusCALC":"Approved","BL_Date":"2017-05-18T00:00:00Z","Cost_Total":"1","Deal_Terms_Sales":null,"Sales_Price_Total":"1","Gross_Margin":"1%","GPCommission":"1","Freight_Pay_Date":"2017-05-18T00:00:00Z","QTY_MTS":1.00,"Cost_MTS":"1","Freight_MTS":null,"Other_Costs_MTS":null,"Sales_Price_MTS":"1","Copy_Deal":null,"Commission_1":null,"Commission_2":null,"Commission_Notes":null,"Commission_Total":null,"Consignee":null,"Created_By":"Cory","Customer_AR_Date":"5/18/2017","Customer_PO":null,"Customer_Specs":null,"Deal_Notes":null,"Deal_Owner":"Cory","Deal_Terms_Supplier":null,"Export":"Q","Freight_Notes":null,"Freight_Vendor":null,"GMCommission":0.06,"Gross_Profit":"1","Import":"Guayaquil","Invoice_No":null,"Misc_Costs":null,"Notify":null,"Shipper":"Company","Status":"Approved","Supplier_PO":null,"Title":"S","Deals_Supplier":4,"Deals_Product":21,"Deals_Customer":22,"Deals_Region":1,"Supplier":{"ID":4,"Supplier1":"Someone.","SContact":"Some Person","SINCOterm":"CFR","SPayTerms":"90"},"Product":{"ID":21,"Product1":"Widget","Packaging":"25KG Bags","Packing_Notes":null,"PGradeSpecs":"Some Notes","Specific_Gravity":null,"Specific_Gravity_Notes":null},"Customer":{"ID":22,"Credit_Approved":"Y","Customer_Address":"somewhere","Customer_Contact":"Bob","Customer_Email":"bob@company.com","Customer_INCOterms":"FOB","Customer_Phone":"888-555-1234","Customer_Name":"MY Customer","Customer_Payment_Terms":90,"Credit_Amount":null,"INCOTerms_Value":"FOB"},"Region":{"ID":1,"Region1":"Peru"}}
The result looks as anticipated. This would mean that security.user.roles[0] is not as expected. You can check it by data-binding the Text property of a Label to ${security.user.roles[0]} or just inspect the browser request when $filter is set according to previous instructions.
It looks to be valid.
Here is the response from the $filter
{"error":{"code":"","message":"The query specified in the URI is not valid. Syntax error: character '"' is not valid at position 18 in 'Region.Region1 eq "Peru"'.","details":[],"innererror":{"message":"Syntax error: character '"' is not valid at position 18 in 'Region.Region1 eq "Peru"'.","type":"Microsoft.OData.ODataException","stacktrace":" at Microsoft.OData.UriParser.ExpressionLexer.NextToken()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseComparison()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalAnd()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalOr()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpression()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpressionText(String expressionText)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilterImplementation(String filter, ODataUriParserConfiguration configuration, ODataPathInfo odataPathInfo)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilter()\r\n at Microsoft.AspNet.OData.Query.FilterQueryOption.get_FilterClause()\r\n at Microsoft.AspNet.OData.Query.Validators.FilterQueryValidator.Validate(FilterQueryOption filterQueryOption, ODataValidationSettings settings)\r\n at Microsoft.AspNet.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.ValidateQuery(HttpRequest request, ODataQueryOptions queryOptions)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.<>c__DisplayClass1_0.b__3(ODataQueryContext queryContext)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, Func2 modelFunction, IWebApiRequestMessage request, Func2 createQueryOptionFunction)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, IWebApiRequestMessage request, Func2 modelFunction, Func2 createQueryOptionFunction, Action1 createResponseAction, Action3 createErrorAction)"}}}
Please try setting the $filter to Region.Region1 eq '${security.user.roles[0]}' (single quotes vs. double).
No change after modifying the quotes.
OK. Last suggestion to try:
Set $filter to Region/Region1 eq '${security.user.roles[0]}' and $expand to Region
If this doesn’t work either please zip the meta directory of your application and send it over to info@radzen.com so we can inspect it further.
Progress!! So it is now filtering out records that do not match the role membership. The problem is it is only looking at the first role that matches. For example a user is a member of Peru and Mexico it is only returning records for Peru.
In this case you need to use or to filter by the two values:
Region/Region1 eq '${security.user.roles[0]}' or Region/Region1 eq '${security.user.roles[1]}'
The other option is the OData in operator:
Region/Region1 in ('${security.user.roles[0]}', '${security.user.roles[1]}')
Success,
This does work. This does mean that I would have to allow for as many potential regions as exist and would have to update the code even if a user added a new potential reason. Perhaps that could be added in the future to allow for a dynamic lookup.
Thanks!
I noticed when filtering the datagrid that it was not filtering the search results. I modified the $filter parameter for the load data on the grid and it works to filter out when typing but when I backspace to clear the filter the grid ends up in an infinite loading where the grid is not populated. I have to refresh the page to get the grid to reset.
Code used on grid filter
${event.filter} and (Region/Region1 eq '${security.user.roles[0]}' or Region/Region1 eq '${security.user.roles[1]}'or Region/Region1 eq '${security.user.roles[2]}'or Region/Region1 eq '${security.user.roles[3]}'or Region/Region1 eq '${security.user.roles[4]}')
Screenshot of grid stuck after removing column filter.
When you delete everything event.filter becomes empty. This creates invalid OData filter expression such as and Region/Region1 eq 'USA' (the filter expression cannot start with and and or).
The solution is to use a condition when using event.filter - ${event.filter ? event.filter + ' and ' : ''}. Here is how to use that in your expression:
${event.filter ? event.filter + ' and ' : ''}(Region/Region1 eq '${security.user.roles[0]}' or Region/Region1 eq '${security.user.roles[1]}' or Region/Region1 eq '${security.user.roles[2]}' or Region/Region1 eq '${security.user.roles[3]}' or Region/Region1 eq '${security.user.roles[4]}')
I can suggest a shorter version which will allow you to use arbitrary number of roles without modifying the filter expression any more:
${event.filter ? event.filter + ' and ' : ''}Region/Region1 in (${security.user.roles.map(role => "'" + role + "'").join(', ')})
The latter uses the Array.map and Array.join functions to create a string in the form ('Role1', 'Role2', 'Role3') regardless of the number of roles.
That makes sense now why it was hanging up. The last suggestion works perfectly.
This worked also for me. Is there any hope that a thing like Region/Region1 in ${security.user.roles}
may work?
Thanks,
Mario
That won't work but this will:
Region/Region1 in (${security.user.roles.map(role => "'" + role + "'").join(', ')})
I'm also to try filter data in a datagrid by user role, I have a single table "Funds" and column 'Fund'. My roles match the entries in Funds. 100 funds 100 roles. I've tried to filter on load event
${event.filter ? event.filter + ' and ' : ''}Fund in (${security.user.roles.map(role => "'" + role + "'").join(', ')})
Also have a admin role that needs to access the all.
Help appreciated.
