Solved: Re: How to Filter only certain items in a collecti...

krickard · ‎03-11-2020

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)

WarrenBelz · ‎03-17-2020

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

krickard · ‎03-17-2020

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

WarrenBelz · ‎03-11-2020

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.

krickard · ‎03-12-2020

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

WarrenBelz · ‎03-12-2020

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.

krickard · ‎03-15-2020

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

WarrenBelz · ‎03-15-2020

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.

krickard · ‎03-15-2020

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}));

krickard · ‎03-16-2020

Anyone else want to weigh in?

@PowerAddict @mdevaney

mdevaney · ‎03-16-2020

@krickard

Sorry, no ideas here my friend.

WarrenBelz · ‎03-16-2020

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.