DataGrid help needed

Hi Guys,

Looking for some help with odata + datagrid. I have data in two different odata pageload event. (getsiteslistsitems - SharePoint)

1- grid0 - UserSkills - listitems
2- grid1 - UserName - listitems

Basically from grid0(UserSkills) I get the LookupId for UserId (21 for example is Username - Test User 2) which I need to cross reference with grid1(UserName) to get UserName.

My goal here is to have just one grid0 with User Name in there with data from both UserSkills & UserName.I tried couple of things but that didn't work.

Looking for some guidance in achieving this result.


If the SharePoint data source supports the $expand parameter you should be able to do this. The easy way is to let Radzen do it for you when you create the datagrid.

  1. Drag a new datagrid component to a page.
  2. Under properties for the grid, select the operation (probably getUserSkills)
  3. Leave response set to Value
  4. Check UserName under the Include section. Next (This is what causes the system to query both tables joined together.)
  5. Select your paging, sorting, and filtering options. Next
  6. Select columns. In this case you will want to leave User ID selected. Radzen will automatically populate this with User Name in the grid.
  7. Click Save.

The User ID column in the grid should have a template property which has automatically been populated to read something like: ${data.UserName?.UserName} This references the joined table data.

1 Like

Hmmmm I think I would need little bit more pointer as I tried to follow instructions provided but could not get step number 4.

Just a heads up there is two different sharepoint list which I have to query for which i have two different getsiteslistsitems 1-) getUserSkills and 2-) getUserName and then i used $expand with fields to get the data I want.

I confirmed with google dev tools I can see two different items?$expand=fields with corresponding details.

Once I have the data from Page Load event I bind my datagrid to ${getUserSkills} and then create specific columns to call its fields property i.e fields.userlookupid & fields.userskillid. My goal here is to use the fields.userlookup to call getUserName.fields.username. For example if fields.userlookupid is 21 I would want to get getUserName.id[21].fields.username type thing.

My end goal is to have one grid for consolidated data from two different odata getsiteslistsitems.

I am not sure if Radzen can achieve this for odata? as I have seen mssql can have server side custom method invokes which can be used to get this done.

I think I understand that your datasource may work differently than the oData sources I am familiar with. To be certain I am communicating the standard process clearly, I'd like to suggest working through an example using the sample oData source provided by Radzen. If this process does not work for you, then I'll have to defer to the Radzen team. The sample datasource contains three tables. Two of the tables have a relationship that is very similar to your request. The OrderDetail and Product tables in the sample datasource share a similar relationship to your sharepoint lists. OrderDetail contains a ProductId field which references the Product table. Product table contains a ProductName field.

First, create a new odata data source. Click the 'use sample OData service' button and name the data source Sample. Click 'Next'

Infer Entities. Note the field named OrderDetail.Product. This field does not exist in the database, but is created in the data model which references the related Product. Of course, the related product is looked up by using the foreign key value stored in the ProductId field. The 'Product' field is what will be called out in the expand parameter.

Click 'Finish' and close the DataSources page.

Now setup a new page and add a datagrid to it. Pull the data from the OrderDetail table by invoking the 'getOrderDetails' data source method. Your datagrid should contain all the fields including the ProductId field. The datagrid in the designer should now be populated with sample data:

Next, select the datagrid and select the events tab. Open the load data event. Select the Invoke for getOrderDetails. Add a new Parameter by clicking the '+' icon.

Scroll to the bottom and set the new parameter to $Expand with a value of Product. This will tell the system to include the expand parameter in the oData request and specify the Product information to be expanded.

Click 'Done'

Next, select the datagrid and select the properties tab on the right. Scroll down to the Columns section and find the column for ProductId.

Set Template to: ${data.Product?.ProductName}
Set Type to: String
Set Format to: undefined (very top selection that is blank)
Set Sort Property to: Product.ProductName
Set FilterProperty to: Product.ProductName
Set Title to: Product Name
Leave Property set to: ProductId

Running the application should show the ProducId column now shows the related ProductName for each record.

As I noted earlier, this works very well for oData sources I am familiar with. However, I'm not sure about Sharepoint lists. If necessary perhaps @korchev can offer some insight.
Good luck!

2 Likes

Note while following the above example I experienced some unexpected behavior in v2.32.4 and earlier. See the following post for details: