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

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

No problem @WarrenBelz, just wanted to see if the other smart people that have helped me to this point had any advice. 

 

The Active flag is just to allow the admin user to save the questions and answers they've picked to a particular client, mainly the active one that is selected from another screen in the admin app. It could just as easily be an input control that reads from the Client table instead of the way I have it currently setup. 

 

Loaded is numeric in all occurrences currently. 

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

Thanks @krickard ,

I would add one more thing to this (you don't want this collection to carry over to the next user selection) - otherwise, does this work?

Clear(SavedAssessments);
ForAll(
   Filter(
      QuestionPool,
      Loaded=1
   ), 
   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
      }
   )
)

do the job for you?

You picked a couple of my smart colleagues with whom I regularly consult.

 

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 I wouldn't want to ever clear SavedAssessment because it holds all the records of the past saved surveys that any admin user has saved. I always want to append records to the SavedAssessment table. 

 

Also, I should have mentioned that this app is a Prototype and I'm using Excel right now to prove it out before I get a DB setup to make it more real. It seems the Clear function doesn't work on Excel. 

 

Just to see if this latest code made any difference I removed the Clear at the beginning and ran it, but it has the same issue as all the others... Expected 'Text' and getting 'Table'. If I remove all the items that reference QuestionPool, from Assesstype to A3 it stops throwing the error. Just FYI. 

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 ,

Now we are looking at a different issue - to date I have only been looking at your original request for the filter placement and assumed (as noted) the rest of your code was achieving your needs.

When you referred to the error previously, I assumed the error was the filter and also cannot see the structure of QuestionPool.

So can you please try two things. Firstly, the code below which is how I would refer to a text field in a table.

ForAll(
   Filter(
      QuestionPool,
      Loaded=1
   ), 
   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
      }
   )
)

Next if that does not work, do the collection exercise at the top

ClearCollect(
   colTest,
   Filter(
      QuestionPool,
      Loaded=1
   )
)

Then have a look at the collection (View > Collections) and see what is in the fields you are trying to collect.

 

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 ,

Update - is SavedAssessments a Collection or a Table - I just saw the significance of your comment about clearing it. If it is a Table (I know Collect should work), you might also try

ForAll(
   Filter(
      QuestionPool,
      Loaded=1
   ), 
   Patch(
      SavedAssessments, 
      Defaults(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 II
Super User II

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

@WarrenBelz is going a great job here. Apologies for the late response. Finally recovering from cold/cough and hoping to get back to a routine.

Let me know if this still doesn't work out.

Thanks,
Hardit Bhatia
https://thepoweraddict.com
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

Thanks @PowerAddict ,

Great to see you back - we have missed you.

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 

 

I'm only using Excel data tables currently. See this thread on why I'm using Collect instead of Patch - https://powerusers.microsoft.com/t5/Building-Power-Apps/Copy-one-table-to-another-not-working/td-p/4...

 

I'll also restate that the original code I posted in this thread works, in that is doesn't have any errors and it copies records from QuestionPool to SavedAssessments. The problem is that I don't want all the records from QuestionPool to go to SavedAssessments, I just want the flagged Loaded = 1 rows. What I haven't talked about yet is that I've been getting around this issue by adding the Loaded column to the SavedAssessment table and copying that column as well, then adding another command to remove all the Loaded = 0 records from SavedAssessments afterward. But that's not sustainable as the question pool will grow and many unnecessary records would be copied and removed each time.

 

This is how I got to the place where I wanted to "pre-filter" the records in QuestionPool before I copied them over to SavedAssessment. However, no matter how I've tried or the code you've so graciously provided keeps breaking the original "working" code that at least copied all records from QuestionPool to SavedAssessments.  

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

Thanks @krickard ,

The issue is that until now I have been trying to create a collection, not apply data to a list and have been wondering why nothing worked. It was not obvious from your code or comments that SavedAssessments was actually a data source, not a collection that you saved later and had a working process for. 
OK back to basics - you are actually the first person I have seen use this. Shane Young made a video on this a little while ago - I cannot presently find it, however his instructions were a simple application of a collection directly to a list with matching fields.

If you do not want to use Patch (and this will work with a filter and was suggested in the post you have referred to), we will I believe have to pre-filter the list before patching.

So I have now gone back to the start of this armed with the current information and suggest something close to the structure we originally had. 
This will limit you to 2000 loaded records unless you put these in a temporary table.

 

ClearCollect(
   ColQuestions,
   Filter(
      QuestionPool, 
      Loaded =1
   )
);
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
      }
   )
)

 

I have not changed your working code in the second part other than referring to the collection. Instead of the data source.

 

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 it creates the collection and I can see all the expected columns, but the second code still gets the error, expecting Text getting Table. It doesn't make any sense to me. Attaching image of the error. 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

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

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

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