Answered

Create collection while removing undesired content

(0) Share

Report

Posted on by tonykiefer

534

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

Sort by

Abhilash_Swain 901 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
tonykiefer 534 on at

Like (0)

Report
Copy link

Link copied!

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

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"

Was this reply helpful? Yes No
tonykiefer 534 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Abhilash_Swain 901 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
tonykiefer 534 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Abhilash_Swain 901 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
tonykiefer 534 on at

Like (0)

Report
Copy link

Link copied!

Hello @Abhilash_Swain ,

Using the latest suggestion only provides errors.

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

Was this reply helpful? Yes No
Abhilash_Swain 901 on at

Like (1)

Report
Copy link

Link copied!

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

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

Was this reply helpful? Yes No
tonykiefer 534 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Abhilash_Swain 901 on at

Like (1)

Report
Copy link

Link copied!

Hi @tonykiefer ,
I think we are missing "" for column names.

Ex:- AddColumns( IceCreamSales, "Revenue", UnitPrice * QuantitySold )

Syntax:- AddColumns(Table,"ColumnName", Formula)

Was this reply helpful? Yes No