cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

How to Filter only certain items in a collection, but not others and still write a single record

Alright... Hopefully, I can explain this well. It's stumping me atm. 

 

Currently, this statement below writes all records from QuestionPool to SavedAssessment and I only want the records marked with Loaded = 1. 

 

So how do I stuff all this code which is not currently filtering...

ForAll( QuestionPool, Collect(SavedAssessments, {SavedName: inpSavedName.Text, ClientName: LookUp(Clients,Active = 1, ClientName), AssessType: QuestionPool[@AssessType], Category: QuestionPool[@Category], Question: QuestionPool[@Question], A1: QuestionPool[@A1], A2: QuestionPool[@A2], A3: QuestionPool[@A3], CategoryCount: CountRows(Distinct(TempQuestions, Category)), QuestionCount: CountRows(TempQuestions), Timestamp: Text(Now()), User: User().FullName}));

 

 

Into a filter statement something like this code...

Filter(QuestionPool,Loaded in Filter(QuestionPool, Loaded =1).Loaded)

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: How to Filter only certain items in a collection, but not others and still write a single record

Hi @krickard ,

Makes no sense to me either, although as I have said, I have never used this function - although I found Shane's video here (starts about the 14 minute mark) and it was as I recalled, a simple unfiltered addition to a list. I also found this thread asking the same question and the solution was a Patch.

So just to get something working, try - note so many versions - make sure your original code is not changed in the below.

ClearCollect(
   colQuestions,
    Filter(
      QuestionPool,
      Loaded=1
    )
);
ForAll(
   colQuestions, 
   Patch(
      SavedAssessments, 
      Defaults(SavedAssessments),
      {
         SavedName: inpSavedName.Text, 
         ClientName: 
         LookUp(Clients,Active = 1, ClientName), 
         AssessType: colQuestions[@AssessType], 
         Category: colQuestions[@Category], 
         Question: colQuestions[@Question], 
         A1: colQuestions[@A1], 
         A2: colQuestions[@A2], 
         A3: colQuestions[@A3], 
         CategoryCount: CountRows(Distinct(TempQuestions, Category)),  
         QuestionCount: CountRows(TempQuestions), 
         Timestamp: Text(Now()), 
         User: User().FullName
      }
   )
)

 

View solution in original post

Highlighted
Helper III
Helper III

Re: How to Filter only certain items in a collection, but not others and still write a single record

So it seems this is a bug. I copied the code in the post above into the button and I get the same error as before... expecting Text, got Table. I decided to create a new canvas app from scratch and use the same Excel workbook that I've been using and build just enough of the app to enable the above code to work... and it does work... works perfectly. So I copied the button from the freshly created test app where it works to the existing problematic app, and it works. It seems the button I've been trying to use has somehow become corrupt, but only when I attempt to modify the code to include a filter. Crazy strange. 

 

Thanks @WarrenBelz for your patience and help, I'm sure I'll reach out to the community again as I enhance this POC in the coming weeks. 

View solution in original post

22 REPLIES 22
Highlighted
Super User III
Super User III

Re: How to Filter only certain items in a collection, but not others and still write a single record

Hi @krickard ,

Without looking at any of the rest of your logic, if this is a valid filter, this is where it needs to go. 

ForAll(
   Filter(
      QuestionPool,
      Loaded in Filter(
         QuestionPool, 
         Loaded =1
      ).Loaded
   ), 
   Collect(
      SavedAssessments, 
      {
         SavedName: inpSavedName.Text, 
         ClientName: LookUp(Clients,Active = 1, ClientName), 
         AssessType: QuestionPool[@AssessType], 
         Category: QuestionPool[@Category], 
         Question: QuestionPool[@Question], 
         A1: QuestionPool[@A1], 
         A2: QuestionPool[@A2], 
         A3: QuestionPool[@A3], 
         CategoryCount: CountRows(Distinct(TempQuestions, Category)), 
         QuestionCount: CountRows(TempQuestions), Timestamp: Text(Now()), 
         User: User().FullName
      }
   )
)

 

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.

Highlighted
Helper III
Helper III

Re: How to Filter only certain items in a collection, but not others and still write a single record

@WarrenBelz  Thanks for replying. This code generates an error of a type mismatch... expecting text and received table for A1.

 

I'm not sure if this helps but both of the code snippets in the original post work as is independent of each other.  

Highlighted
Super User III
Super User III

Re: How to Filter only certain items in a collection, but not others and still write a single record

OK @krickard ,

As I noted, I did not look at your code, only put the filter the only place it can go, which is around the data source.

However you might try this (again assuming your two code bits produce the results you need).

ClearCollect(
   colQuestions,
   Filter(
      QuestionPool,
      Loaded in Filter(
         QuestionPool, 
         Loaded =1
      ).Loaded
   )
);

ForAll( 
   colQuestions, 
   Collect(
      SavedAssessments, 
      {
         SavedName: inpSavedName.Text, 
         ClientName: LookUp(Clients,Active = 1, ClientName), 
         AssessType: QuestionPool[@AssessType], 
         Category: QuestionPool[@Category], 
         Question: QuestionPool[@Question], 
         A1: QuestionPool[@A1], 
         A2: QuestionPool[@A2], 
         A3: QuestionPool[@A3], 
         CategoryCount: CountRows(Distinct(TempQuestions, Category)), 
         QuestionCount: CountRows(TempQuestions), 
         Timestamp: Text(Now()), 
         User: User().FullName
      }
   )
)

 

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.

Highlighted
Helper III
Helper III

Re: How to Filter only certain items in a collection, but not others and still write a single record

@WarrenBelz same error as the last one. 

 

This code below works but doesn't filter as I mentioned before. Just need to add the filter without it complaining there is a Text to Table mismatch on A1.  As soon as I add a filter around the bolded QuestionPool source, it gets the mismatch. 

 

ForAll( QuestionPool, Collect(SavedAssessments, {SavedName: inpSavedName.Text, ClientName: LookUp(Clients,Active = 1, ClientName), AssessType: QuestionPool[@AssessType], Category: QuestionPool[@Category], Question: QuestionPool[@Question], A1: QuestionPool[@A1], A2: QuestionPool[@A2], A3: QuestionPool[@A3], CategoryCount: CountRows(Distinct(TempQuestions, Category)), QuestionCount: CountRows(TempQuestions), Timestamp: Text(Now()), User: User().FullName}));

Highlighted
Super User III
Super User III

Re: How to Filter only certain items in a collection, but not others and still write a single record

Hi @krickard ,

As mentioned, I did not look at the validity of your code - just where a valid filter needed to go to work. So two bits - first one

ClearCollect(
   colQuestions,
   Filter(
      QuestionPool,
      Loaded in Filter(
         QuestionPool, 
         Loaded =1
      ).Loaded
   )
);

Can you please explain exactly what you are trying to achieve here - it seems it should return one Field "Loaded" which will always be a 1?

Second piece

ForAll(
   QuestionPool, 
   Collect(
      SavedAssessments, 
      {
         SavedName: inpSavedName.Text,    //I assume this is a text box on the screen
         ClientName: 
         LookUp(
            Clients,
            Active = 1,            //what does it return if client is not active?
            ClientName
         ), 
         AssessType: QuestionPool[@AssessType], 
         Category: QuestionPool[@Category], 
         Question: QuestionPool[@Question], 
         A1: QuestionPool[@A1], 
         A2: QuestionPool[@A2], 
         A3: QuestionPool[@A3], 
         CategoryCount: 
         CountRows(
            Distinct(
               TempQuestions,      //where does TempQuestions come from?
               Category
            )
         ), 
         QuestionCount: CountRows(TempQuestions), 
         Timestamp: Text(Now()), 
         User: User().FullName
      }
   )
)

This would seem to some fields from all records in QuestionPool and add some additional fields.

I am also struggling to see any relationship between the two as colQuestions is not referenced in the second code. Maybe the answer to the first one may sort this.

Highlighted
Helper III
Helper III

Re: How to Filter only certain items in a collection, but not others and still write a single record

I'm building a survey app and a survey admin app. Admin users can select from a pool of questions the questions they want to appear in the survey, the selected questions are loaded into a TempQuestions table. Then the temp data is loaded into the "active" table so that they will show up in the survey app for users to take the survey.

 

The code below comes after all that, it allows admin users to save the set of questions and answers they have chosen for a given survey for later callback and usage again. As of now, this code below is just trying to copy all selected or "loaded" questions from the QuestionPool into the SavedAssessment table. It's just missing the code to limit the QuestionPool source to just the Loaded questions. 

 

Hope this helps paint a bigger picture for you. 

 

/*Write all loaded questions and answers from the question pool to a saved table by client*/ ForAll(QuestionPool, Collect(SavedAssessments, {SavedName: inpSavedName.Text, ClientName: LookUp(Clients,Active = 1, ClientName), AssessType: QuestionPool[@AssessType], Category: QuestionPool[@Category], Question: QuestionPool[@Question], A1: QuestionPool[@A1], A2: QuestionPool[@A2], A3: QuestionPool[@A3], CategoryCount: CountRows(Distinct(TempQuestions, Category)), QuestionCount: CountRows(TempQuestions), Timestamp: Text(Now()), User: User().FullName}));

 

 

Highlighted
Helper III
Helper III

Re: How to Filter only certain items in a collection, but not others and still write a single record

Anyone else want to weigh in? 

@PowerAddict @mdevaney 

Highlighted
Super User III
Super User III

Re: How to Filter only certain items in a collection, but not others and still write a single record

@krickard 

Sorry, no ideas here my friend.

Highlighted
Super User III
Super User III

Re: How to Filter only certain items in a collection, but not others and still write a single record

Hi @krickard ,

I am in a different timezone to you - Tuesday morning here. Is Loaded a Boolean true/false or a Numeric 1/0? Boolean is below for numeric you just need Loaded=1. The issue with Boolean however is that it is not Delegable.

Also wondering why the need for Active = 1 as if not true, you will get no value in field.

ForAll(
   Filter(
      QuestionPool,
      Loaded=true
   ), 
   Collect(
      SavedAssessments, 
      {
         SavedName: inpSavedName.Text, 
         ClientName: 
         LookUp(
            Clients,
            Active = 1, 
            ClientName
         ), 
         AssessType: QuestionPool[@AssessType], 
         Category: QuestionPool[@Category], 
         Question: QuestionPool[@Question], 
         A1: QuestionPool[@A1], 
         A2: QuestionPool[@A2], 
         A3: QuestionPool[@A3], 
         CategoryCount: CountRows(Distinct(TempQuestions, Category)), 
         QuestionCount: CountRows(TempQuestions), 
         Timestamp: Text(Now()), 
         User: User().FullName
      }
   )
)

 

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,422)