Two different columns with options

Two different columns with options

fpinhofpinho Posts: 4Questions: 0Answers: 0
edited July 2023 in Editor

Hello!
I have two different columns with options to a same table and same value.
The problem is, when data is load on the second column the value that shows on row is the LastName from the FirstName that is selected on the first column. Should show a different value or empty if is null on database.

Code:

  .Field(new Field("OrganicUnit.UserID")
                    .Options(new Options()
                    .Table("User")
                    .Value("UserID")
                    .Label("FirstName")))
  .Field(new Field("OrganicUnit.DirectorUserID")
                    .Options(new Options()
                    .Table("User")
                    .Value("UserID")
                    .Label("LastName")))

Need to solve this problem.
Thank you
Regards

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Replies

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    Hi,

    Can you show me your full PHP for this script, and also the client-side Javascript for DataTables and Editor please? Even better would be if you can give me a link to the page so I can see what is going on.

    Thanks,
    Allan

  • fpinhofpinho Posts: 4Questions: 0Answers: 0
    edited July 2023

    I forgot to say that is C#.
    I want to made this on server-side if it's possible.

    var response = new Editor(db, "OrganicUnit", "OrganicUnitID")
                    .Model<OrganicUnitDTO>("OrganicUnit")
                    .Model<JoinModelUser>("User")
                    .Field(new Field("OrganicUnit.UserID")
                        .Options(new Options()
                        .Table("User")
                        .Value("UserID")
                        .Label("FirstName")
                        .Where((q) => { q.Where("ProfileID", (long)ProfileType.COORDINATOR); }))
                        .Validator((value, data, host) =>
                        {
                            if (data.ContainsKey("OrganicUnit") && data["OrganicUnit"] is Dictionary<string, object> ActivityData)
                            {
                                if (ActivityData.ContainsKey("UserID"))
                                {
                                    if (value is string strValue && string.IsNullOrEmpty(strValue))
                                    {
                                        ActivityData["UserID"] = null;
                                    }
                                }
                            }
                            return null;
                        }))
                    .Field(new Field("OrganicUnit.DirectorUserID")
                        .Options(new Options()
                        .Table("User")
                        .Value("UserID")
                        .Label("LastName")
                        .Where((q) => { q.Where("ProfileID", (long)ProfileType.DIRECTOR); }))
                        .Validator((value, data, host) =>
                        {
                            if (data.ContainsKey("OrganicUnit") && data["OrganicUnit"] is Dictionary<string, object> ActivityData)
                            {
                                if (ActivityData.ContainsKey("DirectorUserID"))
                                {
                                    if (value is string strValue && string.IsNullOrEmpty(strValue))
                                    {
                                        ActivityData["DirectorUserID"] = null;
                                    }
                                }
                            }
                            return null;
                        }))
                    .LeftJoin("YearData", "YearData.YearDataID", "=", "OrganicUnit.YearDataID")
                    .Field(new Field("OrganicUnit.OrganicUnitID", "Options").Xss(false))
                    .LeftJoin("User", "User.UserID", "=", "OrganicUnit.UserID")
                    .Where("OrganicUnit.YearDataID", YearDataID, "=")
                    .Process(request)
                    .Data();
    
  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin
    edited July 2023

    Sorry - I should have seen that it was C#!

    If I'm understanding this correctly, you've got two foreign key references to the User table from OrganicUnit (UserID and DirectorUserID).

    That's absolutely fine, but it means you need two left joins for it as well! One to get the data for each.

    .LeftJoin("User DirectorUser", "DirectorUser.UserID", "=", "OrganicUnit.DirectorUserID")
    

    (Note that I've aliased the User table to DirectorUser to allow a second reference to the User table).

    You'll also need to add a model for the director user as well:

    .Model<JoinModelUser>("DirectorUser")
    

    Should do it - assuming you want the same information from the table for the director.

    Then on the client-side, refer to DirectorUser.LastName (etc) to display information about the linked director user.

    Regards,
    Allan

  • fpinhofpinho Posts: 4Questions: 0Answers: 0

    Thanks, works well.
    Regards.

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    Awesome - nice one!

    Allan

Sign In or Register to comment.