Need to be able to filter records based on User Role

I have a need to filter a result set, say Deals by the Region of the Order. The Region can be either USA, Mexico, or South America. I have Roles defined called USA, Mexico, and South America respectively. One the Page Load event I think I need to add a condition to the “set getDealsResult to ${result.value}” property creation but I’m not sure the syntax. Something like ${getDealsResult.Region} = ${security.user.isInRole(“Mexico”)}

Hi @joshwilliam,

How does the Region property look like? Is it a string e.g. ‘USA’, ‘Mexico’? If it is you can try something like this

Set Property

  • Name: getDealsResult
  • Value: ${result.value}.filter(item => item.Region == ${security.user.roles[0]})

The region is a related table that contains two fields, ID and Region. There is a 1:N relationship from the region table to orders. Each order can only be assigned a single region. Will the filter attempt to filter based on the related ID or the actual region given your example?


1 Like

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.

  1. When invoking the getDeals method set the $expand parameter to Region. This will expand the related region.
  2. Set the $filter parameter to 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 :slight_smile:

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”:“”,“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 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]}')


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.


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 (${ => "'" + role + "'").join(', ')})

The latter uses the and Array.join functions to create a string in the form ('Role1', 'Role2', 'Role3') regardless of the number of roles.

1 Like

That makes sense now why it was hanging up. The last suggestion works perfectly.