ClearCollect adding columns from a table being filtered

[Johnh1]

Hello,

Trying to create an all-in-one process where a Collection is being build using ShowColumns and Filter which is working fine.  I am having trouble adding an AddColumns command to the process.  I have a table with PartNumber & Vendor (includes vendor id).  I build a collection based on the PartNumber entered by the user and all the associated Vendors for all active Vendors in the Vendors table.  I am trying to add two more columns to Collection from the Vendor table and not certain how to do it.  Here is the code that is working.  I am building the collection colPartVendor, the PartVendor is the table with the PartNumber & Vendor.  I am doing a lookup on the Vendor table to make sure that the Vendor is Active.  I only want the active vendors from the PartVendor table.  I need to add the QualityRating & ReviewDate to the colPartVendor collection.  No matter where I try to place the AddColumns() it is not working.  

//  code to build collection

ClearCollect(
colPartVendor,
    ShowColumns(
        Filter(
            PartVendor, PartNumber = txtLEPartNo_1.Text && LookUp(Vendor, VendorID = VendorID, isActive)
        ),
       "VendorName",
       "VendorID"
    )
);

Thank you in advance for your help with this.

Kind regards,

John

[poweractivate]

@Johnh1 

Do you have some examples of where you have tried using AddColumns?

[Johnh1]

Hello @poweractivat  

Here is one of my many attempts,

ClearCollect(
    colPartVendor,
    ShowColumns(
        Filter(
            AddColumns(QualityRating,
                PartVendor, PartNumber = txtLEPartNo_1.Text && LookUp(Vendor, VendorID = VendorID, isActive)
            ),"CurrentQualityRating"
        ),
       "VendorName",
       "VendorID"
    )
);

[Johnh1]

@poweractivate  also tried this.

ClearCollect(
colPartVendor,
ShowColumns(
AddColumns(QualityRating,
Filter(
PartVendor, PartNumber = txtLEPartNo_1.Text && LookUp(Vendor, VendorID = VendorID, isActive)
),"CurrentQualityRating"
),
"VendorName",
"VendorID"
)
);

[poweractivate]

@Johnh1 

Does this part work by itself?

 Filter(
            PartVendor, PartNumber = txtLEPartNo_1.Text && LookUp(Vendor, VendorID = VendorID, isActive)
        )

Does the above actually return any Records by itself when it's put, say, in the Items property of a Gallery?

If not, you may need to adjust it first before trying to solve the original issue. 

[Johnh1]

@poweractivate 

It will return records, though I did some more testing and it ignores the 'isActive' part of the filter so all records are returned.  The LookUp() has no affect on the filter statement.  It is back to the drawing board.  Any suggestions on how to process?

[poweractivate]

@Johnh1 The lookup may need to be like this:

//pseudocode
 Filter(
            PartVendor, PartNumber = txtLEPartNo_1.Text && PartVendorID=LookUp(Vendor, VendorID = VendorID, isActive).VendorID
        )

[Johnh1]

@poweractivate 

Thanks, I will give that a try.  I did split it up a bit and built a couple of collections to get the right results, though I would like to do it all in one.