Master/Detail Hierarchy based on two MS SQL Views

gotik911 · February 6, 2019, 2:32pm

Hello!

I'm using Radzen community edition and trying to create Master/Detail Hierarchy based on two MS SQL Views. I created empty page and added datagrids but I'm unable to set filters to get child rows on master row expand event.

I created Master/Detail Hierarchy using Table relation schema to see how filters are created but it's not working when I use SQL views.

I tried to create Master/Detail Hierarchy based on two Stored Procedures but I have same problem with on RowExpand event.

In both cases I get data from master table in master grid but when I expand row I got no rows from child datagrid.

Is it even possible to create Master/Detail Hierarchy based on two MS SQL Views or Stored Procedures and how to make relation between two data grids?

It's seems very easy using table relations based Page Schema but my master table is related to many other tables so SQL Views or Stored Procedures are much easier for me to use.

Thank you!

korchev · February 6, 2019, 4:38pm

Indeed the built-in page templates for master detail only work with tables. Still views and stored procedures should work too.

The built-in template uses the RowExpand event of the master data grid to filter the child records:

The current master record is available as ${event}. In the case from the screenshot (considering the Category - Product relationship) the event handler invokes the getProducts method and filters by CategoryID by setting the $filter parameter to CategoryID eq ${event.CategoryID}.

You should be able to do something similar with views/stored procedures. Do an Invoke data source method and filter it by the current master record.

gotik911 · February 6, 2019, 5:01pm

Thank you for your answer!
I'll be more precised... I have datagrid (master) created with SQL View "viewParametri_naloga". This SQL View is created using data from couple tables but ID field is "Primary key" in this View.
I have another datagrid (child) created using SQL View "viewProduktivnost_ploce". This SQL View is created using data from another table with column "ID_parametra_naloga" that is "Foreign key" in this View and contains values from master view "viewParametri_naloga.ID". This way I can relate all records from viewProduktivnost_ploce with record in viewParametri_naloga. I can load all rows from viewParametri_naloga in main datagrid and my goal is to load all child rows from viewProduktivnost_ploce where viewParametri_naloga.ID = viewProduktivnos_ploce.ID_parametra_naloga.

In the attached picture you can see what I tried to do but I can only load master datagrid with empty child rows.

Thank you!

korchev · February 6, 2019, 5:46pm

This code looks correct. The Data property of the child DataGrid should be set to ${data.viewProductkivnostPloces} (if it is defined in the Template of the Master DataGrid). You can check in your browser's developer tools to see if the getViewProductivnostPloces method is invoked - it should appear as a HTTP GET request in the Network tab.

gotik911 · February 6, 2019, 6:38pm

Something strange happened! When I expand row it gives me empty child row but if I click to sort any column in child row it loads all data (not filtered data but all data in viewProduktivnostPloce.

Do you have any idea why is this happening?

Thank you!

korchev · February 7, 2019, 8:36am

Two things may happen:

The query isn't right for some reason and doesn't return proper results. You can check that in the Network tab of your browser development tools. You should see a successful HTTP response (200) and a JSON response.
The query could return the right results but not set the right property. I see you are setting ${event.viewProductkivnostPloces} but I don't know what the child DataGrid is data-bound to. What is its Data property set to?

You can paste the JSON of that page here so I can inspect it further.

gotik911 · February 7, 2019, 12:25pm

Query in SQL view "viewProduktivnostPloce" is simple select query without WHERE condition. My goal is to filter this query on RowExpand event (please correct me if I'm wrong). Bellow you can find JSON response:

{"error":{"code":"","message":"The query specified in the URI is not valid. Could not find a property named 'ViewProduktivnostPloces' on type 'Petroprojekt.Models.Petroprojekt.ViewProduktivnostPloce'.","details":[],"innererror":{"message":"Could not find a property named 'ViewProduktivnostPloces' on type 'Petroprojekt.Models.Petroprojekt.ViewProduktivnostPloce'.","type":"Microsoft.OData.ODataException","stacktrace":" at Microsoft.OData.UriParser.SelectExpandBinder.GenerateExpandItem(ExpandTermToken tokenIn)\r\n at System.Linq.Enumerable.SelectEnumerableIterator2.MoveNext()\r\n at System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext()\r\n at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable)\r\n at System.Collections.Generic.List1.InsertRange(Int32 index, IEnumerable1 collection)\r\n at Microsoft.OData.UriParser.SelectExpandBinder.Bind(ExpandToken tokenIn)\r\n at Microsoft.OData.UriParser.SelectExpandSemanticBinder.Bind(ODataPathInfo odataPathInfo, ExpandToken expandToken, SelectToken selectToken, ODataUriParserConfiguration configuration)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseSelectAndExpandImplementation(String select, String expand, ODataUriParserConfiguration configuration, ODataPathInfo odataPathInfo)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseSelectAndExpand()\r\n at Microsoft.AspNet.OData.Query.Validators.SelectExpandQueryValidator.Validate(SelectExpandQueryOption selectExpandQueryOption, ODataValidationSettings validationSettings)\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)"}}}

My child DataGrid Data is bound to getViewProduktivnostPlocesResult

I think my parameter is wrong, but not shure!

OK! I deleted expand part and got this JSON message (Status 200):

{"@odata.context":"http://localhost:5000/odata/Petroprojekt/$metadata#ViewProduktivnostPloces","value":[{"ID_parametra_naloga":35499,"Stapova":125,"Stapova_uradjeno":77,"Stapova_slomljeno":1,"Upareno":0,"Datum":"2019-01-31T22:39:34.587Z","Ime":"Mladen Eri\u0107"},{"ID_parametra_naloga":35499,"Stapova":0,"Stapova_uradjeno":32,"Stapova_slomljeno":0,"Upareno":0,"Datum":"2019-01-31T22:56:22.863Z","Ime":"Mladen Eri\u0107"},{"ID_parametra_naloga":35499,"Stapova":0,"Stapova_uradjeno":16,"Stapova_slomljeno":1,"Upareno":0,"Datum":"2019-02-01T08:58:20.463Z","Ime":"Mi\u0107o Babi\u0107"},{"ID_parametra_naloga":35499,"Stapova":0,"Stapova_uradjeno":-1,"Stapova_slomljeno":0,"Upareno":0,"Datum":"2019-02-01T08:58:47.4Z","Ime":"Mi\u0107o Babi\u0107"}]}

It seems to be ok but my row is still empty and if I click to sort any of columns in child DataGrid it load all data from query (unfiltered).

Thank you so much for your help!

korchev · February 7, 2019, 12:39pm

What are the parameters of the getViewProductivnostPloces method? The error means that you are using a property in the query which does not exist.

gotik911 · February 7, 2019, 12:44pm

$filter

ID_parametra_naloga eq ${event.ID}

But please read my last post above, I edited it.

korchev · February 7, 2019, 12:48pm

You didn't respond to this:

What is its Data property of the child DataGrid set to?

Can't really guess what is happening without all the information I have asked for.

If the server response is correct then you have to make sure your DataGrid is bound to the page property that you have assigned the response to. I have mentioned that in my previous responses.

gotik911 · February 7, 2019, 12:52pm

I'm sorry, I edited previous post and made confusion!

korchev · February 7, 2019, 1:09pm

Then check my second response from this thread:

This code looks correct. The Data property of the child DataGrid should be set to ${data.viewProductkivnostPloces} (if it is defined in the Template of the Master DataGrid). You can check in your browser's developer tools to see if the getViewProductivnostPloces method is invoked - it should appear as a HTTP GET request in the Network tab.

gotik911 · February 7, 2019, 1:17pm

Thank you so much! You solved my problem!

Radzen is great but support is amazing!

gotik911 · February 8, 2019, 1:37pm

I have to ask for help again.

I'm trying to filter master DataGrid using DropDown control. I bound DropDown control to getViewNalogsResult and set everything like in the picture below.

Master DataGrid was bounded to getViewParametriNalogasResult and I changed it to ${data.ViewParametriNalogas}

Now when I run my app, master DataGrid loads gray with never ending spinner and if I select some value from DropDown it passes correct filter (status 200) but master DataGrid is still gray.

I tried to bound master DataGrid to getViewParametriNalogasResult (default method) and master DataGrid loads all data from query but again filter from DropDown won't filter master DataGrid.

Also, is there way to show more then one column data in DropDown's Text Property? My query selects couple of columns (Invoice number, buyers name, shipping date....) and I'd like to show some of this in DropDown control at the same time.

Can you pleas help me?

gotik911 · February 8, 2019, 1:48pm

Ignore first part of my question.... i only had to set Data property of master DataGrid to nalog (property that I set in DropDown.

I still have to find way to sort results in DropDown and to show more then one column.
I noticed that paging on master DataGrid not working properly. It always gives me 5 pages even if there is data for only two pages and if I select page number two, master DataGrid becomes gray and won't load data. Count property of master DataGrid is set to ${getViewParametriNalogasCount}

korchev · February 10, 2019, 7:53am

I still have to find way to sort results in DropDown and to show more then one column.

If your data source is populated from a data source method you can sort it via the order by parameter (you can use the Query Builder to set it). The Template property of the dropdown can be used to customize the way the items look.

I noticed that paging on master DataGrid not working properly.

You can check Radzen's Output window to see if there is any server-side exception that happens during paging.

gotik911 · February 11, 2019, 9:59am

I populated DropDown control using data source method based on SQL View and there is no proper way to use Order By in View so I created Stored Procedure and solved sort problem.

I still have to read documentation to learn how to use templates and show multiple columns in DropDown.

I disabled paging for now because it is not that important for me right now and I'll deal it later.

I just need to say that I used Radzen Security to automatically create login and user management mechanisms and I'm amazed with simplicity and functionality. You really did great job!

Thank you!