cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tonykiefer
Super User
Super User

Create collection while removing undesired content

I'm trying to create a collection that has multiple columns.  The collection should be filtered by one specific column - "Description".  I would like to build the collection based on the "Description" column while removing all records where the "Description" does not start with "F1", "F3", or "X1".  

 

So only records where the "Desription" starts with "F1", "F3", and "X1" populate the collection.

 

Here is my code...

 

ClearCollect(CopyFromBomModelCollection, AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
First(Filter(CopyFromBomModelCollection, Description<>"F1"),
First(Filter(CopyFromBomModelCollection, Description<>"F3"),
First(Filter(CopyFromBomModelCollection, Description<>"X3")))

 

This isn't working and I'm having trouble trying to figure this out...any help would be great!

1 ACCEPTED SOLUTION

Accepted Solutions

@Abhilash_Swain ...I tried the quotes and the result is the same.  As an alternative I am going to create a SQL view with the filtered data instead.  I do appreciate you help!

 

tonykiefer_0-1654274021026.png

 

 

 

View solution in original post

12 REPLIES 12
Abhilash_Swain
Super User
Super User

Hi @tonykiefer ,
Please try this.

 

 

ClearCollect(CopyFromBomModelCollection, AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId), Filter(CopyFromBomModelCollection, Description<>"F1" , Description<>"F3",Description<>"X3"))

 

 

OR 

 

ClearCollect(CopyFromBomModelCollection, AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId), Filter(Filter(Filter(CopyFromBomModelCollection, Description<>"F1"),Description<>"F3"),Description<>"X3")) 

 

Please check the syntax if I miss ")".

 

 

 

@Abhilash_Swain Thank you for the quick reply.  I made one mistake...the column I need to filter by is the ItemId...but that was easily corrected.  Unfortunately I am still getting the same result.  The undesired records are not filtering out.

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, ItemId<>"F1" , ItemId<>"F3",ItemId<>"X3"))

 

OR

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(Filter(Filter(CopyFromBomModelCollection, ItemId<>"F1"),ItemId<>"F3"),ItemId<>"X3"))

 

Neither solutions work.

 

Also...in my original description I need to filter out the records that do NOT start with "F1", "F3", or "X1"

 

tonykiefer_0-1654180459723.png

 

I also tried this with no success...

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, ItemId, StartsWith("F1","F1"), ItemId, StartsWith("F3","F3"), ItemId, StartsWith("X1","X1")))

 

The goal is to eliminate all records where the ItemId does not begin with "F1", "F3", or "X1"

 

tonykiefer
Super User
Super User

I also tried this with no success...

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1"), StartsWith(ItemId,"F3"), StartsWith(ItemId,"X3")))

 

tonykiefer_0-1654183264420.png

 

 

Abhilash_Swain
Super User
Super User

I am sorry, We are looking for a condition with OR and we are trying a condition which is for AND . 😄 

Try this,

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1") || StartsWith(ItemId,"F3") || StartsWith(ItemId,"X3")))

 

Use !StartsWith(ItemID,"F1") if you want not starts with. 

Hello @Abhilash_Swain,

We seem to be heading in the right direction but I still get the same result set.

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId),
Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1") || StartsWith(ItemId,"F3") || StartsWith(ItemId,"X3")))

 

tonykiefer_0-1654200562958.png

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1") || StartsWith(ItemId,"F3") || StartsWith(ItemId,"X3")),FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId))

 

We were doing it wrong for AddColumns section.

tonykiefer
Super User
Super User

Hello @Abhilash_Swain ,

Using the latest suggestion only provides errors.

 

Do you have a test table you can use for testing?

 

tonykiefer_0-1654203628148.png

 

Seems the column names are not recognized. 
Please check the below screen shot.

Abhilash_Swain_0-1654266724066.png

 

Please give a like and accept as solution if I answered your query.

tonykiefer
Super User
Super User

@Abhilash_Swain I'm pretty certain the col name is recognized because it works when I add the new column.

 

ClearCollect(CopyFromBomModelCollection,
AddColumns(Filter(CopyFromBomModelCollection, StartsWith(ItemId,"F1") || StartsWith(ItemId,"F3") || StartsWith(ItemId,"X3")),FBCModelTables,"ItemColorId",ItemId & " - " & BHSInventColorId))

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 (3,598)