Editor Field Options - populate Select using Label Field Option: concantenate from joined fields

sbsu

I would like some assistance better understanding table join options for the purpose of joining database field data, the combination of which will be used to create more useful HTML SELECT options.

I tried some searching and found that a member asked a similar question targeting the .PHP framework. I believe my question is similar and I have been reviewing the .NET join manual for equivalent information.

I have been successful concatenating label fields when the fields exist in the same database table but have failed to concatenate label fields from secondary tables. Here are the database table and field details:

Primary Tables and fields ([tablename].fieldname):
[OperationsPurchaseOrder]
.PurchaseOrderFulfillmentManagerId

Secondary/Lookup Tables and fields ([tablename].fieldname):
[PurchaseOrderFulfillmentManager]
.PurchaseOrderFulfillmentManagerId
.FulfillmentManagerEmailAddress
.BranchId
.ItemClassId

[Branch]
.BranchId

[OperationsInventoryItemClass]
.ItemClassId
.ItemClassDesc

Task:
Using inline cell-editing, use a select dropdown list to perform inline cell-editing on field [OperationsPurchaseOrder].PurchaseOrderFulfillmentManagerId. Select is populated from items from the primary table and from joined tables. Select item label that is shown must be derived from the concatenation of multiple database fields, some of which exist in joined tables themselves. Select label should be a combination of: [PurchaseOrderFulfillmentManager].FulfillmentManagerEmailAddress + [Branch].Description + [OperationsInventoryItemClass].ItemClassDesc

Working code example:

                .Model<OperationsPurchaseOrder>("OperationsPurchaseOrder")
                .Model<Branch>("Branch")
                .Model<Department>("Department")
                .Model<OperationsPurchaseOrderFulfillmentManager>("OperationsPurchaseOrderFulfillmentManager")
                .Model<OperationsInventoryItemClass>("OperationsInventoryItemClass")

                .Field(new Field("OperationsPurchaseOrder.PurchaseOrderFulfillmentManagerId")
                    .Options(new Options()
                        .Table("OperationsPurchaseOrderFulfillmentManager")
                        .Value("PurchaseOrderFulfillmentManagerId")
                        .Label("FulfillmentManagerEmailAddress")
                        // works
                    )
                )

                .Field(new Field("OperationsInventoryItemClass.ItemClassDesc"))
                .Field(new Field("Branch.BranchDescription"))

                .LeftJoin("Branch", "Branch.BranchId", "=", "OperationsPurchaseOrder.BranchId")
                .LeftJoin("Department", "Department.DepartmentId", "=", "OperationsPurchaseOrder.DepartmentId")
                .LeftJoin("OperationsPurchaseOrderFulfillmentManager", "OperationsPurchaseOrderFulfillmentManager.PurchaseOrderFulfillmentManagerId", "=", "OperationsPurchaseOrder.PurchaseOrderFulfillmentManagerId")
                .LeftJoin("OperationsInventoryItemClass", "OperationsInventoryItemClass.ItemClassId", "=", "OperationsPurchaseOrderFulfillmentManager.ItemClassId");
// padding for display

Working code example:

                .Model<OperationsPurchaseOrder>("OperationsPurchaseOrder")
                .Model<Branch>("Branch")
                .Model<Department>("Department")
                .Model<OperationsPurchaseOrderFulfillmentManager>("OperationsPurchaseOrderFulfillmentManager")
                .Model<OperationsInventoryItemClass>("OperationsInventoryItemClass")

                .Field(new Field("OperationsPurchaseOrder.PurchaseOrderFulfillmentManagerId")
                    .Options(new Options()
                        .Table("OperationsPurchaseOrderFulfillmentManager")
                        .Value("PurchaseOrderFulfillmentManagerId")
                        .Label(new[] { "FulfillmentManagerEmailAddress", "ItemClassId", "BranchId" })
                        // works
                    )
                )

                .Field(new Field("OperationsInventoryItemClass.ItemClassDesc"))
                .Field(new Field("Branch.BranchDescription"))

                .LeftJoin("Branch", "Branch.BranchId", "=", "OperationsPurchaseOrder.BranchId")
                .LeftJoin("Department", "Department.DepartmentId", "=", "OperationsPurchaseOrder.DepartmentId")
                .LeftJoin("OperationsPurchaseOrderFulfillmentManager", "OperationsPurchaseOrderFulfillmentManager.PurchaseOrderFulfillmentManagerId", "=", "OperationsPurchaseOrder.PurchaseOrderFulfillmentManagerId")
                .LeftJoin("OperationsInventoryItemClass", "OperationsInventoryItemClass.ItemClassId", "=", "OperationsPurchaseOrderFulfillmentManager.ItemClassId");
// padding for display

Non-working code example:

                .Model<OperationsPurchaseOrder>("OperationsPurchaseOrder")
                .Model<Branch>("Branch")
                .Model<Department>("Department")
                .Model<OperationsPurchaseOrderFulfillmentManager>("OperationsPurchaseOrderFulfillmentManager")
                .Model<OperationsInventoryItemClass>("OperationsInventoryItemClass")

                .Field(new Field("OperationsPurchaseOrder.PurchaseOrderFulfillmentManagerId")
                    .Options(new Options()
                        .Table("OperationsPurchaseOrderFulfillmentManager")
                        .Value("PurchaseOrderFulfillmentManagerId")
                        .Label(new[] { "FulfillmentManagerEmailAddress", "OperationsInventoryItemClass.ItemClassDesc", "Branch.BranchDescription" })
                        // unable to bind fields
                    )
                )

                .Field(new Field("OperationsInventoryItemClass.ItemClassDesc"))
                .Field(new Field("Branch.BranchDescription"))

                .LeftJoin("Branch", "Branch.BranchId", "=", "OperationsPurchaseOrder.BranchId")
                .LeftJoin("Department", "Department.DepartmentId", "=", "OperationsPurchaseOrder.DepartmentId")
                .LeftJoin("OperationsPurchaseOrderFulfillmentManager", "OperationsPurchaseOrderFulfillmentManager.PurchaseOrderFulfillmentManagerId", "=", "OperationsPurchaseOrder.PurchaseOrderFulfillmentManagerId")
                .LeftJoin("OperationsInventoryItemClass", "OperationsInventoryItemClass.ItemClassId", "=", "OperationsPurchaseOrderFulfillmentManager.ItemClassId");
// padding for display

The code returns the error:
The multi-part identifier "OperationsInventoryItemClass.ItemClassDesc" could not be bound.
The multi-part identifier "Branch.BranchDescription" could not be bound.

Is it possible to refactor the code so that my select label can make use of field descriptions instead of just primary keys? If so, how?

allan

Hi,

The Options class, at the moment, does not yet have the ability to do a join and get data from a second table for display in the label. That is something I'm going to start work on next week as part of the Editor 2.1 development cycle.

You could hang fire until that is ready, but if you prefer to get on with it, what I would suggest is to create a VIEW that performs the join you want. Then the Options class that reference that VIEW just as if it were a table.

Allan

sbsu

Allan, thank you for the reply and thank you for planning to include this ability sometime in the future.

sbsu

Allan, did this feature get implemented in the latest Editor? Thank you.

allan

Hi,

Yes indeed. leftJoin support was implemented for Mjoin and the Options classes in Editor 2.1. Release notes are available here.

Allan