Unanswered

Building Collection Error

(0) Share

Report

Posted on by MtryDoc

I am trying to build a collection to use in my app and can not figure out why I am getting the red lines. It has to do something with the data type I think. I am just tired and need a little help,

I create a Collection called records which is pulling all the info from SPO List. From that collection I am trying to make few others.

The TotalRecordsCategory Collection is the one that is not working. category from the records collection is a table(Choice Column from SPO List). I want to make a collection with two columns. Number count and Category, need to know how many of each category.

Once I get this working then i can apply same coding to another collection that will be looking at status and getting number of each also. This will be used in a graph later in the app.

//Create Collection from SPO List
ClearCollect(Records, HelpDesk);

//Create Collection Ticket number asigned
ClearCollect(Ticketype, 
{Type:"Total Tickets"},{Type:"Assigned to me"});

//Create Collection Category of Tickets Type
ClearCollect(TicketCategory,Distinct(Records,category));

//Create Collection Total number of each Category
ClearCollect(TotalRecordsCategory,ShowColumns(
 AddColumns(
 GroupBy("NUM","category"),
 "NUM",
 CountRows(category)
 ),
 "NUM",
 "Category"
 ));

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

WarrenBelz 153,117 Most Valuable Professional on at

Like (0)

Report

Hi @MtruyDoc ,

All of AddColumns, ShowColumns, GroupBy and CountRows are not Delegable, so unless you start adding some other Delegable Filters (where you can "pre-filter"), you will be limited to your Data Row Limit.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
MtryDoc 55 on at

Like (0)

Report

Understood, this should not be a problem. There should only be about 10-15 Categories total. Just want to be able to count how many of each category

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (0)

Report
Hi @MtruyDoc ,

You are missing the SP List Name

ClearCollect( TotalRecordsCategory, ShowColumns( AddColumns( GroupBy( SPLIstName, "NUM", "category" ), "NUM", CountRows(category) ), "NUM", "Category" ) );

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
MtryDoc 55 on at

Like (0)

Report
Well that was missing... Thanks but still a no go.
//Create Collection Total number of each Category ClearCollect(TotalRecordsCategory,ShowColumns( AddColumns( GroupBy(Records,"category","NUM"), "NUM", CountRows(category) ), "NUM", "Category" ));

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (0)

Report
@MtruyDoc ,

You do not need quotes anymore, you had a different case for category (Category) at the bottom and you need the added column to have a different name

ClearCollect( TotalRecordsCategory, ShowColumns( AddColumns( GroupBy( Records, NUM, category ), Number, CountRows(category) ), NUM, Number category ) );

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like (0)

Report
@MtruyDoc

Jumping in here as well because I like these challenges - apologies @WarrenBelz 🙂

I got it to work using this method

//Create Collection Category of Tickets Type ClearCollect(TicketCategory, Distinct(Records,category.Value)); //Create Collection Total number of each Category ClearCollect( TotalRecordsCategory, AddColumns( RenameColumns(TicketCategory, Value, category), NUM, CountRows(Filter( Records, category.Value = category)) ) );

Note: I changed the code for TicketCategory from ... Distinct(Records,category) ... to ... Distinct(Records,category.Value) - this may / may not be what you need though?

Was this reply helpful? Yes No
MtryDoc 55 on at

Like (0)

Report
.Value throws error ~ Name isn't valid 'value' isn't recognized. when added to ticketcategory.

I changed code to this

//Create Collection Total number of each Category ClearCollect( TotalRecordsCategory, AddColumns( RenameColumns(TicketCategory, Value, category), NUM, CountRows(Filter( Records, category.Value = "IT")) ) );

and I get the group of three records but they have the num value of 2 since IT is value 2 times. It I leave = category I get a error on the equals that it is looking for a number or text

Was this reply helpful? Yes No
MtryDoc 55 on at

Like (0)

Report
//Create Collection Total number of each Category ClearCollect( TotalRecordsCategory, AddColumns( RenameColumns(TicketCategory, Value, category), NUM, CountRows(Filter( Records, category.Value = category.Value)) ) );
is returning
number should be 2 1 2

Was this reply helpful? Yes No
MtryDoc 55 on at

Like (0)

Report

I found your https://www.practicalpowerapps.com/helpful-hints/choicecolumnsmanage/ and believe this would help in many ways. could you explain a little more how I would make a collection where it breaks out the choice values. I am very new to all this and learning all I can .. Thank you

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like (0)

Report
@MtruyDoc

You didn't copy my code correctly, did you try this?

//Create Collection Total number of each Category ClearCollect( TotalRecordsCategory, AddColumns( RenameColumns(TicketCategory, Value, category), NUM, CountRows(Filter( Records, category.Value = category)) // .Value removed here ) );

If so, what field type is category?

The key to my code is that the collection called TicketCategory creates a field called 'Value' which holds type TEXT data - not complex field data, as in a CHOICE type column

Was this reply helpful? Yes No