Dropdonwn in grid from a related table

[Francesco Pruneri]

Sorry for the question, but I have tried many ways to do something and failed; even after reading the forum and documentation. My goal : grid with a list of ToDo's. i have a TodoTypeId field which refers to another table where there are different types of todo's. The tables are linked and the sql query works perfectly.

Now I would like, when I am in Add or Edit mode of the grid, that in a dropdown the list of all todo types comes out. I really have several problems. Already starting with the display of the "GridForeignColumn" column. the code I wrote and which you see below does not display anything. Basically the GridForeignColumn field "breaks" the display.

Then with a template I wanted to proceed to the second step.

@page "/testfk"

@rendermode RenderMode.InteractiveServer
@inject ITodoActivityService TodoActivityService
@inject ITodoTypeService TodoTypeService

<h3>TestFK</h3>

<SfGrid DataSource="@attData" Height="315" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })">

    <GridEditSettings AllowAdding="true" AllowDeleting="true" AllowEditing="true"></GridEditSettings>

    <GridColumns>

        <GridColumn Field=@nameof(TodoActivity.Id) HeaderText="ID" IsPrimaryKey="true" Width="120"></GridColumn>

        <GridForeignColumn Field=@nameof(TodoActivity.TodoTypeId) HeaderText="Tipo ID" ForeignKeyValue="Code" ForeignDataSource="@typesData" Width="150"></GridForeignColumn>

    </GridColumns>

</SfGrid>

@code {

         public List<TodoActivity> attData { get; set; }

    public List<TodoType> typesData { get; set; }

    protected override async Task OnInitializedAsync()

    {

        typesData = await TodoTypeService.SGetTodoTypes();

        attData = await TodoActivityService.SGetTodoActivities();

    }

}

[Monisha Saravanan]

Hi Francesco Pruneri,

Greetings from Syncfusion.

Before proceeding further with your requirement kindly share us the below details from your end.

1. Share us the model class of both the tables (Grid and Foreignkey table).
2. Share us whether the foreignkey field has same value in both the tables or different value.
3. If possible, show us the screen snip of datas present in the particular field.
4. Share us the video demonstration of the reported issue.
5. If possible share us an simple issue reproduceable sample or a sample with duplicate database.

The above requested details will be very helpful for us to validate the reported issue at our end.

Regards,

Monisha

[Francesco Pruneri]

Hi there, i prepared for you another example from scratch ! Example Lead -> User 

Model Classes :

public class Lead

{

    public int Id { get; set; }

    public string? Description { get; set; }

    public Nullable<int> UserId { get; set; }

    public User? Users { get; set; }

}

public class User

{

    public int Id { get; set; }

    public string? FirstName { get; set; }

    public string? LastName { get; set; }

}

SQL Query from Balzor 

      SELECT [l].[Id], [l].[Description], [l].[UserId], [u].[Id], [u].[FirstName], [u].[LastName]

      FROM [Leads] AS [l]

      LEFT JOIN [Users] AS [u] ON [l].[UserId] = [u].[Id]

Result by Management 

10000000 Primo Lead 1002 1002 Luigi Pirandello

10000001 Secondo Lead 1002 1002 Luigi Pirandello

10000002 Terzo Lead 1001 1001 Alessandro Manzoni

10000003 Quarto Lead 1001 1001 Alessandro Manzoni

10000004 Quinto Lead 1002 1002 Luigi Pirandello

10000005 Sesto 1001 1001 Alessandro Manzoni

Data Leads 

10000000	Primo Lead	1002
10000001	Secondo Lead	1002
10000002	Terzo Lead	1001
10000003	Quarto Lead	1001
10000004	Quinto Lead	1002
10000005	Sesto	1001
NULL	NULL	NULL

Data Users 

1000	Dante	Alighieri
1001	Alessandro	Manzoni
1002	Luigi	Pirandello
1003	Giovanni	Pascoli
1004	Giosuè	Carducci
1005	Ugo	Foscolo
1006	Giuseppe	Ungaretti
NULL	NULL	NULL

Code for grid

<SfGrid DataSource="@leadsDataSource" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })">

    <GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings>

    <GridColumns>

        <GridColumn Field=@nameof(Lead.Id) HeaderText="Id" IsPrimaryKey="true" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

        <GridColumn Field=@nameof(Lead.Description) HeaderText="Description" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

        <GridColumn Field="Users.LastName" HeaderText="From Users LastName OK" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

        <GridColumn Field=@nameof(Lead.UserId) HeaderText="UserDD" Width="150" EditType="EditType.DropDownEdit">

        <GridForeignColumn Field=@nameof(Lead.UserId) HeaderText="Wrong FK" ForeignKeyValue="LastName" ForeignDataSource="@usersDataSource" Width="150"></GridForeignColumn>

        </GridColumn>

    </GridColumns>

</SfGrid>

The result is that I have no records in grid !!!

THX so muche to fix this problem. I must have many of this grids with FK

[Francesco Pruneri]

Entire code for page 

@page "/"

@rendermode RenderMode.InteractiveServer

@inject ILeadService LeadService

@inject IUserService UserService

<PageTitle>Home</PageTitle>

<br />

<SfGrid DataSource="@usersDataSource" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })">

<GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings>

    <GridColumns>

        <GridColumn Field=@nameof(User.Id) HeaderText="Id" IsPrimaryKey="true" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

        <GridColumn Field=@nameof(User.FirstName) HeaderText="FirstName" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

        <GridColumn Field=@nameof(User.LastName) HeaderText="LastName" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

    </GridColumns>

</SfGrid>

<br />

<br />

<SfGrid DataSource="@leadsDataSource" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })">

    <GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings>

    <GridColumns>

        <GridColumn Field=@nameof(Lead.Id) HeaderText="Id" IsPrimaryKey="true" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

        <GridColumn Field=@nameof(Lead.Description) HeaderText="Description" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

        <GridColumn Field="Users.LastName" HeaderText="From Users LastName OK" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

        <GridColumn Field=@nameof(Lead.UserId) HeaderText="UserDD" Width="150" EditType="EditType.DropDownEdit">

        <GridForeignColumn Field=@nameof(Lead.UserId) HeaderText="Wrong FK" ForeignKeyValue="LastName" ForeignDataSource="@usersDataSource" Width="150"></GridForeignColumn>

        </GridColumn>

    </GridColumns>

</SfGrid>

@code {

    // oggetto per lista tipo leads

    public List<Lead> leadsDataSource = new List<Lead>();

    // oggetto per lista source leads

    public List<User> usersDataSource = new List<User>();

    protected override async Task OnInitializedAsync()

    {

        leadsDataSource = await LeadService.SGetLeads();

        usersDataSource = await UserService.SGetUsers();

    }

}

[Monisha Saravanan]

Hi Francesco Pruneri,

Thanks for the information.

we would like to clarify that by using Grid foreign key column we can perform data-mapping between two tables only if both ForeignKey table & Grid table have same mapping value and field. But in your shared model class (Leads and Users) doesn’t have a same mapping field (UserId)

So we suggest you to use ForeignKeyField to map the field from the foreign key table. We have prepared a simple sample as per your shared reference. Kindly check the below attached sample and code snippet for your reference.

Sample:

https://blazorplayground.syncfusion.com/embed/VDVJDWDzLHysRZSH?appbar=true&editor=true&result=true&errorlist=true&theme=bootstrap5

Note: you can use field name alone when both the model shares the same field name (UserId). But in your scenario there is no UserId field in User class. So to map another field name from User class we suggest you to use ForeignKeyField at your end.  

Here we have defined Id column as foreignkey field.

 

<SfGrid DataSource="@Employees" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })">

 

    <GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings>

 

    <GridColumns>

 

        <GridColumn Field=@nameof(Lead.Id) HeaderText="Id" IsPrimaryKey="true" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

 

        <GridColumn Field=@nameof(Lead.Description) HeaderText="Description" TextAlign="Syncfusion.Blazor.Grids.TextAlign.Left" Width="120"></GridColumn>

 

        <GridColumn Field=@nameof(Lead.UserId) HeaderText="UserDD" Width="150" EditType="EditType.DropDownEdit">

 

        <GridForeignColumn Field=@nameof(Lead.UserId) ForeignKeyField="Id" HeaderText="Wrong FK" ForeignKeyValue="LastName" ForeignDataSource="@Orders" Width="150"></GridForeignColumn>

 

 

        </GridColumn>

 

    </GridColumns>

 

</SfGrid>

 

@code {

    public List<User> Orders { get; set; }

    public List<Lead> Employees { get; set; }

 

    public class Lead

    {

       public int Id { get; set; }

 

        public string? Description { get; set; }

 

        public Nullable<int> UserId { get; set; }

 

    }

    public class User

    {

        public int Id { get; set; }

 

        public string? FirstName { get; set; }

 

        public string? LastName { get; set; }

    }

}

Please get back to us if you have further queries.

Regards,

Monisha

[Francesco Pruneri]

good morning and thank you, sorry I misunderstood the functionality of Grid foreign key columns. Thank you very much for the suggestion and I apologise for wasting your time.

Please consider that 

"Orders" for me is "Lead" 

"Employees" is "User"

considering this, should I change something in your code?

public List<User> Orders { get; set; }

public List<Lead> Employees { get; set; }

Sorry but I'm getting confused

[Monisha Saravanan]

Hi Francesco,

We have changed the datasource name as per your shared code. Kindly check the modified sample for your reference.

Sample: https://blazorplayground.syncfusion.com/embed/hDBzXWXFKyyqgdsD?appbar=true&editor=true&result=true&errorlist=true&theme=bootstrap5

To provide proper mapping between two tables we suggest you to use ForeignKeyField. Kindly try the below suggestion (Include ForeignKeyField as “Id” in your “Wrong FK” column) to resolve the reported issue.

     <GridForeignColumn Field=@nameof(Lead.UserId)  ForeignKeyField="Id"  HeaderText="Wrong FK" ForeignKeyValue="LastName" ForeignDataSource="@usersDataSource" Width="150"></GridForeignColumn>

Please get back to us if you still face the reported issue or if you have further queries.

Regards,

Monisha

[Francesco Pruneri]

Hi there, 

many thanks I really appreciate what you do for the developer community

[Monisha Saravanan]

Hi Francesco,

Welcome. We are glad to hear that the reported issue has been resolved at your end. Kindly get back to us if you have further queries. As always we will be happy to assist you.

Regards,

Monisha