CRUD - Create select dropdown list autofills another input box ASP.NET Razor Pages

Question

I'm trying to create something like a combo box in excel. Selecting the Site will automatically select the JobNum and Manager. Site, JobNum, and Manager are from a separate table called master. Currently in the create form that I am trying to do, they are all dropdown list that pulls from the db master table.

Edit - Forgot to mention master table has a simple ID, just an incremental from 1. Added an image for how it looks from the DB

This is how it currently looks

Master table

How I want it to be - selecting site, autofills the bottom two

Master Table from DB showing all columns

Code for select list:

        public SelectList PYSiteSL { get; set; }
        public SelectList PYManagerSL { get; set; }
        public SelectList PYJobNumSL { get; set; }

        public void PopulateSiteDropDownList(FinanceAppARContext _context, object selectedSiteList = null)
        {
            var siteQuery = from s in _context.master
                            select s.Site;
            PYSiteSL = new SelectList(siteQuery.Distinct().AsNoTracking(), selectedSiteList);
        }
        public void PopulateManagerDropDownList(FinanceAppARContext _context, object selectedManagerList = null)
        {
            var managerQuery = from m in _context.master
                               select m.Manager;
            PYManagerSL = new SelectList(managerQuery.Distinct().AsNoTracking(), selectedManagerList);
        }

        public void PopulateJobNumDropDownList(FinanceAppARContext _context, object selectedJobNumList = null)
        {
            var jobnumQuery = from j in _context.master
                              select j.JobNum;
            PYJobNumSL = new SelectList(jobnumQuery.Distinct().AsNoTracking(), selectedJobNumList);
        }


        private readonly FinanceApp.Models.FinanceAppARContext _context;

        public CreateModel(FinanceApp.Models.FinanceAppARContext context)
        {
            _context = context;
        }

        public IActionResult OnGet()
        {
            PopulateSiteDropDownList(_context, PYSiteSL);
            PopulateManagerDropDownList(_context, PYManagerSL);
            PopulateJobNumDropDownList(_context, PYJobNumSL);

            return Page();
        }

and this is the frontend .cshtml

//Site
            <div class="form-group">
                <label asp-for="py21.Sites" class="control-label"></label>
                <select asp-for="py21.Sites" asp-items="Model.PYSiteSL" class="form-control chosen-select"><option disabled selected>-- Select Site --</option></select>
                <span asp-validation-for="py21.Sites" class="text-danger"></span>
            </div>
//Manager
            <div class="form-group">
                <label asp-for="py21.Manager" class="control-label"></label>
                <input asp-for="py21.Manager" class="form-control" readonly />
                @*<select asp-for="py21.Manager" asp-items="Model.PYManagerSL" class="form-control chosen-select"><option disabled selected>-- Select Manager --</option></select>*@
                <span asp-validation-for="py21.Manager" class="text-danger"></span>
            </div>
//JobNum
            <div class="form-group">
                <label asp-for="py21.JobNum" class="control-label"></label>
                <input asp-for="py21.JobNum" class="form-control" readonly />
                @*<select asp-for="py21.JobNum" asp-items="Model.PYJobNumSL" class="form-control chosen-select"><option disabled selected>-- Select Job Number --</option></select>*@
                <span asp-validation-for="py21.JobNum" class="text-danger"></span>
            </div>

Edited what @StriplingWarrior said, a typo.

Answer 1

In the .cshtml.cs page, you could create a handler method to retrieve data based on the select Site. Then, in the .cshtml page, you can attach the DropDownList onchange event, in this event, get the DropDownList selected value and use JQuery.Ajax to call the Handler method and get the related JobNum and Manager, then, sets the text box value.

More detail information, please refer the following sample:

Create the following models:

public class PYViewModel //page view model
{
    public string Sites { get; set; }
    public string Manager { get; set; }
    public string JobNum { get; set; }
}

public class MasterModel //EF entity
{
    [Key]
    public string JobNum { get; set; }
    public string Site { get; set; }
    public string Manager { get; set; }
    public bool IsActive { get; set; }
}

DbContext:

public class ApplicationDbContext : IdentityDbContext
{  
    public DbSet<MasterModel> Masters { get; set; }
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
}

After migration and add the data, the Masters table as below:

.cshtml.cs page:

public class PYPageModel : PageModel
{
    [BindProperty]
    public PYViewModel py21 { get; set; }
    public SelectList PYSiteSL { get; set; }  
    //you could change the applicationdbcontext to yours.
    private readonly ApplicationDbContext _context;
    public PYPageModel(ApplicationDbContext context)
    { 
        _context = context;
    }
    public void OnGet()
    { 
        //query the master table.
        PYSiteSL = new SelectList(_context.Masters.Distinct().ToList(), nameof(MasterModel.Site), nameof(MasterModel.Site));
    }
    public JsonResult OnGetGetJobNumManager(string selectSite)
    {
        //based on the selected site to filter data.
        return new JsonResult(_context.Masters.Distinct().Where(c=>c.Site == selectSite).Distinct().ToList());
    }
}

.cshtml page:

@page
@model RazorPageSample.Pages.PYPageModel
<form method="post">
    <div asp-validation-summary="ModelOnly" class="text-danger"></div>
    <div class="form-group">
        <label asp-for="py21.Sites" class="control-label"></label>
        <select asp-for="py21.Sites" asp-items="Model.PYSiteSL" id="ddlsite" class="form-control chosen-select"><option disabled selected>-- Select Site --</option></select>
        <span asp-validation-for="py21.Sites" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="py21.Manager" class="control-label"></label>
        <input asp-for="py21.Manager" class="form-control" readonly />
        @*<select asp-for="py21.Manager" asp-items="Model.PYManagerSL" class="form-control chosen-select"><option disabled selected>-- Select Manager --</option></select>*@
        <span asp-validation-for="py21.Manager" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="py21.JobNum" class="control-label"></label>
        <input asp-for="py21.JobNum" class="form-control" readonly />
        @*<select asp-for="py21.JobNum" asp-items="Model.PYJobNumSL" class="form-control chosen-select"><option disabled selected>-- Select Job Number --</option></select>*@
        <span asp-validation-for="py21.JobNum" class="text-danger"></span>
    </div>
</form>
    @section Scripts{
        <script>
            $(function () {
                $("#ddlsite").on("change", function () {
                    var site = $(this).val();
                    $.ajax({
                        url: `?handler=GetJobNumManager&selectSite=${site}`, 
                        type: 'Get', 
                        beforeSend: function (xhr) {
                            xhr.setRequestHeader("XSRF-TOKEN",
                                $('input:hidden[name="__RequestVerificationToken"]').val());
                        },
                        success: function (result) {
                            var manager = [];
                            var JobNum = [];
                            $.each(result, function (i, item) {
                                manager.push(item.manager);
                                JobNum.push(item.jobNum);
                            }); 
                            $("#py21_Manager").val(manager.join(","));
                            $("#py21_JobNum").val(JobNum.join(","));
                        }, 
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                });
            });
        </script>
    }

The result as below: