cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnh1
Frequent Visitor

ClearCollect adding columns from a table being filtered

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

 

 

7 REPLIES 7

@Johnh1 

 

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

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

@poweractivate  also tried this.

 

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

 

@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. 

@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?

@Johnh1 The lookup may need to be like this:

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

@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.

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,330)