Two different columns with options

fpinho

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.

allan

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

fpinho

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();

allan

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

fpinho

Thanks, works well.
Regards.

allan

Awesome - nice one!

Allan