How to display Product fields and it's pricebookentries in a single datatable?

Question

I need to dynamically put products and the products' pricebookentries in a single row in a datatable. If I add a pricebook, a new column and it's prices should also be displayed.

I could do this for static pricebooks using the following code. But how do I change it dynamically?

for(var j = 0;j < p.length;j++){
                for(var i = 0; i < p[j].length; i++){
                    if(p[j][i].Pricebook2Id=="01s46000004XMWhAAO"){
                        tes.push(p[j][i].UnitPrice);

                    }else if(p[j][i].Pricebook2Id=="01se0000000sup8AAA"){
                        stpb.push(p[j][i].UnitPrice);
                        
                    }else if(p[j][i].Pricebook2Id=="01se0000000spgaAAA"){
                        pb1.push(p[j][i].UnitPrice);
                        
                    }else if(p[j][i].Pricebook2Id=="01se0000000spgkAAA"){
                        pb2.push(p[j][i].UnitPrice);
                        
                    }else if(p[j][i].Pricebook2Id=="01se0000000strOAAQ"){
                        pb3.push(p[j][i].UnitPrice);
                        
                    }else if(p[j][i].Pricebook2Id=="01se0000000sugVAAQ"){
                        sup.push(p[j][i].UnitPrice);
                        
                    
                    }
                }
                columnData.push({ "ProductCode" : this.allProductColumns[j].fields.ProductCode.value,
                                  "Name" : this.allProductColumns[j].fields.Name.value, 
                                  "Family" : this.allProductColumns[j].fields.Family.value,
                                  "01s46000004XMWhAAO" : tes[j],
                                  "01se0000000sup8AAA" : stpb[j],
                                  "01se0000000spgaAAA" : pb1[j],
                                  "01se0000000spgkAAA" : pb2[j],
                                  "01se0000000strOAAQ" : pb3[j],
                                  "01se0000000sugVAAQ" : sup[j]
                              });
        }
        this.allProductColumns=columnData;

this.allProductColumns goes to the data attribute in lightning-datatable

How do I change the static Id part in columnData.push dynamically?

Answer 1

There as many ways to do this. One way is to use object (literals), computed properties and some functional javascript. Note that you will need to tweak your code based on this sample code.

Looking at your code, you already have an array of products with each element containing an array of corresponding Pricebook entries. Now, you need to get a list of unique pricebook entries via apex call (or you can create an array of unique pricebook IDs within the JS code). For example, the SOQL would be something similar to the following:

select Pricebook2Id from PricebookEntry where IsActive = true group by Pricebook2Id

Ensure that the WHERE clause used in the above query doesn't drastically change from your original query used to fetch the product list, in order to avoid any additional pricebook IDs. Lets call this array as priceBookIds and should as shown in the screenshot below:

Now, you can update your JS code based on the sample below to make it dynamic (note that I've retained the outer for loops as-is so it's easier to understand this code, but they can also be converted into functional constructs):

for(var j = 0;j < p.length;j++){
    // this object will be used to store all the pricebookId-Unitprice details for a given product
    var objPrice = {};
    
    for(var i = 0; i < p[j].length; i++){       
        priceBookIds.forEach(pbID => {
            if(p[j][i].Pricebook2Id == pbID ){
                // Here we are creating an object literal with pricebook ID as the key and the unit price as value in the second parameter using object literal notation
                // Append this new object literal to objPrice using assign method
                Object.assign(objPrice, {[pbID]: p[j][i].UnitPrice});               
            }
        });
    }
    
    // Using object.assign, append all the pricebook entries for a product to an empty object and push it into the data array
    columnData.push(Object.assign({}, { 
        "ProductCode" : this.allProductColumns[j].fields.ProductCode.value,
        "Name" : this.allProductColumns[j].fields.Name.value, 
        "Family" : this.allProductColumns[j].fields.Family.value}, objPrice)
    );
}

You will also need to update the columns passed to the lightning datatable.