Get customername using customernumber


#1

Hello,

I have 2 tables "Orders" and "Customers"
Tables are linked with relation using Primarykey and Foreignkey :

Customers
Customernumber (PK)
Customername

Orders
Ordernumber(PK)
Orderdate
Customernumber (FK)

In Radzen page "Orders" I have a grid that displays all records form Orders-Table:

Ordernumber,Orderdate,Customernumber
123, 30.12.2018, 1000

What I want to do is to display Customername next to Customernumber in Orders grid like so:
Ordernumber,Orderdate,Customernumber,Customername
123, 30.12.2018, 1000, Peter

I have add new column to grid in Orders-Page but I dont know how to display Customers.Customername using Order.Customernumber


#2

Infer your data source first so Radzen knows about the new foreign key relationship:

  1. Go to the data screen
  2. Click on your data source
  3. Click Infer and Next.

Then you can delete the existing DataGrid and add a new one. Use the "New ..." option from the Data property dropdown to configure the grid. It will allow you to pick columns and should automatically display the CustomerName column.


#3

thank you for your answer.

But if I delete the grid then I will lose all changes that I have made to that grid, for example I have changed all column-widths and changed all column-titles and column-order...so when I delete the grid then I have to do all changes again ?


#4

Yes, you will lose those changes. You can try the following instead (which the grid data-binding wizard does automatically otherwise):

  1. Find the Invoke method which calls getOrders. There should be one or two instances - one in the Load event of the page and one in the LoadData event of the DataGrid.
  2. Set the $expand parameter of the getOrders method to Customer (in all invokes of the getOrders method).
  3. Add a new column to your data grid and set its Template property to ${data.Customer.Customername}

#5

Thank you, this helped me.

Should this work in Master/Details page, too?
(I have tried but it does not work....maybe I am doing something wrong)

I have details grid, its data property is bound to master-grid row-select...

In details grid I have employee number
But instead of employee-number I need to display employee-name that I can get from employee table.


#6

What is the master grid and what is the detail? In any case you can specify the $expand parameter as a comma separated list:


#7

Hello,

In the screenshot you use rowexpand, in previous example LoadData event of page and grid was used.

I dont know if rowexpand is correct for my problem because I think that I have no rows to expand.... I have 2 separat grids that are bound together using one:many relationship.
I will try to explain :

I have two tables : ProductionOrders and ProductionOrdersDetails
I have a page with 2 grids
grid0: displays all ProductionOrders
grid1: display ProductionOrdersDetails of selected row in grid0

This works.

In grid1 I have employeeNumber.
But now I must display additional column in grid1.
This column must get its data from table "employees" using employeeNumber and read employeeName and display employeeName in grid1

I send screenshot, maybe it helps to understand what I try to do.

thank you


#8

In this case my initial suggestions should work too - set the $expand parameter and the Template of the column of the ProductionOrders DataGrid.


#9

OK, thank you..... so I must set $expand in page dataload event and in grid1 dataload event like this ?

$expand:
value = employees

And template for column like this? :

Template:
${data.employees?.employeename}


#10

It depends on the name of the table. If it is "Employees" the property should be "Employee":

$expand=Employee

Template:
${data.Employee.EmployeeName}

Bear in mind that the $expand and Template are case-sensitive.


#11

I have add $expand in page dataload event and in grid1 dataload event.
And I have set template in grid column to ${data.Employee.EmployeeName}

But now both grids on page are empty, no records are displayed


#12

This usually means that some error has occurred. You can check Radzen's output window for any server-side exceptions. Most probably something isn't spelled right.


#13

If I add in Page DataLoad event:
$expand=Employee

Console output is:

: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
Executed action App2.Controllers.Rootserver.ProcessordersController.GetProcessorders (server) in 5.5577ms
info
dotnet: : Microsoft.AspNetCore.Hosting.Internal.WebHost[2]
Request finished in 6.1032ms 400 application/json; odata.metadata=minimal; odata.streaming=true; charset=utf-8

There is value 400 (should be 200 ?)

I am sure that "Employee" is spelled correctly


#14

I can see this error text in Browser:
Could not find a property named 'Employee' on type 'App2.Models.Rootserver.Processorders'.


#15

Maybe the problem is that there is no relationsship between the first grid (this grid displays data of table "Productionorders") and Employee table?
Such relationsship does only exist for the second grid (this grid displays data of table "ProductionorderDetails"), there I have a foreign key to Employee table

So it seems that on Page LoadData event when I do $expand=Employee there is a relationsship missing?
If so, how can I fix it without creating relationsship between Productionorders and Employee ?


#16

Yes, you need a relationship at DB level (foreign key constraint) in order for the $expand parameter to work. If you don't have such a relationship you have to use the approach I have described in your other thread.


#17

OK... this has worked, thank you for your help.