Error when Filtering data source before Creating a Collection

(1) Share

Report

Posted on by amk1421

195

Using mydatasource to create mycollection1 before filtering mydatasource to return only specific rows

ClearCollect(mycollection1,Blank());
ForAll(mydatasource As mydatasource,
	Filter(mydatasource,mydatasource.UserId="Jonathan"),
 Collect(mycollection1,
 {
 FullNameOriginator: LookUp(mycollection2, ID = mydatasource.'User (Originator)','Person name'),
 Status:mydatasource.Status,
 'User (Originator)':mydatasource.'User (Originator)',
 Subject:mydatasource.Subject,
 'Work item instructions':mydatasource.'Work item instructions',
 WorkflowWorkItemClaimed:mydatasource.WorkflowWorkItemClaimed,
 'User (UserId)':mydatasource.'User (UserId)',
 'Due date time':mydatasource.'Due date time'
 }
 )
);

I want to create collection based only on filtered rows returned from my datasource. In above query I am trying to filter but it returns error.

Is the syntax correct?

Categories:

Dataflows

Connecting to Data

General Questions

Using Apps Using Formulas

All responses (11)

Answers (1)

poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Re: Error when Filtering data source before Creating a Collection

@akg1421

Do this carefully and revert to the previous working version if it fails, but I recommend the ForAll inside the Collect, not outside.

Set(currUserId,First(Split(User().Email,"@")).Result); Set(workitemCount,CountRows(Filter(mydatasource, 'User (UserId)'=currUserId))); //then the collection ClearCollect(mycollection1,Blank()); Collect ( mycollection1 ,ForAll ( Filter(mydatasource As mdsPreFilteredRecord,mdsPreFilteredRecord.UserId=currUserId) As mydatasourceRecord ,{ FullNameOriginator: LookUp(mycollection2, ID = mydatasourceRecord.'User (Originator)','Person name') ,Status:mydatasourceRecord.Status ,'User (Originator)':mydatasourceRecord.'User (Originator)' ,Subject:mydatasourceRecord.Subject ,'Work item instructions':mydatasourceRecord.'Work item instructions' ,WorkflowWorkItemClaimed:mydatasourceRecord.WorkflowWorkItemClaimed ,'User (UserId)':mydatasourceRecord.'User (UserId)' ,'Due date time':mydatasourceRecord.'Due date time' } ) );
If this doesn't work, just revert to the previous working version with ForAll outside.

However, in general, it is better to put ForAll inside and not outside.
The reason is because when ForAll is outside, it is performing the same step inside for each of the individual items.
When it is inside, the outer operation is performed only once.

Especially consider the example of a data source and ForAll outside Patch instead of the better way of ForAll inside Patch

Especially consider the example of a data source and ForAll outside Patch - in this case, Patch is being performed individually for every single item from ForAll. This could be a performance and efficiency problem, and it could significantly reduce the performance of the App if it is used enough times or on large enough data sets.

The better way of ForAll inside Patch - here, ForAll from inside returns a Table, that Patch operates on only once on all of the items, so only one call to the data source for all the items at the same time, not one individual call for each of the items individually. In this case, the efficiency and performance may be increased by orders of magnitude depending on the scenario.

I wanted you to consider this in case @akg1421
This example does not Patch - and it may be less important here - however it would be better to try to get in the habit of using ForAll inside

As for your issue with the Timer, do you still have the issue?
amk1421 195 on at

Like (0)

Report
Re: Error when Filtering data source before Creating a Collection

Well, it still navigates to the MainScreen, I had a Timer control before which I disabled and implemented new one with number 0. Now the temp screen just navigates to MainScreen after few seconds. It also starts showing following error, even though it eventually succeeds in loading the data (shows in gallery on mainscreen)

"The requested operation is invalid. Server Response: mydatasource failed: An error has occurred. Object reference not set to an instance of an object. clientRequestId: b5c1bef3-ed1e-47de-b838-da84ff76e833"

I have two variables calculating in TmpScreen.OnVisible

Set(currUserId,First(Split(User().Email,"@")).Result); Set(workitemCount,CountRows(Filter(mydatasource, 'User (UserId)'=currUserId))); //then the collection ClearCollect(mycollection1,Blank()); ForAll ( Filter(mydatasource As mdsPreFilteredRecord,mdsPreFilteredRecord.UserId=currUserId) As mydatasourceRecord ,Collect ( mycollection1 ,{ FullNameOriginator: LookUp(mycollection2, ID = mydatasourceRecord.'User (Originator)','Person name') ,Status:mydatasourceRecord.Status ,'User (Originator)':mydatasourceRecord.'User (Originator)' ,Subject:mydatasourceRecord.Subject ,'Work item instructions':mydatasourceRecord.'Work item instructions' ,WorkflowWorkItemClaimed:mydatasourceRecord.WorkflowWorkItemClaimed ,'User (UserId)':mydatasourceRecord.'User (UserId)' ,'Due date time':mydatasourceRecord.'Due date time' } ) );

Just in place of "Jonathan" i'm using the currently logged in user's id via User function in currUserId
Set(currUserId,First(Split(User().Email,"@")).Result);
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report

Re: Error when Filtering data source before Creating a Collection

@akg1421

Instead of 50 just use 0 - or use a smaller number like 5 or 10 does it work better that way?
Try just 0 for now, it sounds like you don't have so many records right now - so just use 0.
amk1421 195 on at

Like (1)

Report
Re: Error when Filtering data source before Creating a Collection

@poweractivate

It just loops around with timer resetting. CountRows is dynamic, it may never be greater than the number of choice. It's values range from 0 to 50

If(CountRows(myCollection)>50,Navigate(MainAppScreen))

Any solution to this?
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Re: Error when Filtering data source before Creating a Collection

@akg1421

On the first screen of your app, have a big Label that says PLEASE WAIT.
Have a Timer Control on that same first screen where the big PLEASE WAIT label is, that is repeating each second. Make the Duration be 1000 to do this
Make the AutoStart property to be true
Make the Repeat property to be true
OnTimerEnd of the Timer control, use CountRows to check how many Records are in the Collection you are trying to load into.

If it is greater than some number of your choice, use Navigate(MainAppScreen)
So for OnTimerEnd of the Timer control something like this:

If(CountRows(myCollection)>50,Navigate(MainAppScreen))

Otherwise the Timer will auto repeat and check it again in another second.

See if something like the above could help as starting point @akg1421
amk1421 195 on at

Like (1)

Report

Re: Error when Filtering data source before Creating a Collection

@poweractivate Nope, your previous reply resolved the issue.

Moreover, please guide how can I have the data fully loaded in collection before the user starts navigating through screens. Because the datasource is a "dynamics 365" entity and it takes usually 15 to 20 seconds before data is fully loaded and showing in Gallery.

I have tried App.OnStart() - Didn't work! I then tried LandingScreen.OnVisible() that also didn't work. What would be the right way to STOP user from navigating before the data is completely iterated and stored in collection.
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
Re: Error when Filtering data source before Creating a Collection

@akg1421

Maybe you want the Collection to be iterated on all items but for the final result to be Filtered - if so, then maybe you want it like this?
Filter ( ForAll ( mydatasource As mydatasourceRecord ,Collect ( mycollection1 ,{ FullNameOriginator: LookUp(mycollection2, ID = mydatasourceRecord.'User (Originator)','Person name') ,Status:mydatasourceRecord.Status ,'User (Originator)':mydatasourceRecord.'User (Originator)' ,Subject:mydatasourceRecord.Subject ,'Work item instructions':mydatasourceRecord.'Work item instructions' ,WorkflowWorkItemClaimed:mydatasourceRecord.WorkflowWorkItemClaimed ,'User (UserId)':mydatasourceRecord.'User (UserId)' ,'Due date time':mydatasourceRecord.'Due date time' } ) ) As processedRecord ,processedRecord.UserId="Jonathan" )

Does the above work better?
amk1421 195 on at

Like (1)

Report

Re: Error when Filtering data source before Creating a Collection

@poweractivate

It's working fine. Collection is created as per requirement. However, it does not seem to be Filtering data for UserId="Jonathan"
Verified answer

poweractivate 11,078 Most Valuable Professional on at

Like (1)

Report
Re: Error when Filtering data source before Creating a Collection

@akg1421

The second one is closer, but the second one is incorrect as well. As is said in the error message, there are too many arguments to ForAll. In your case, it means that the Filter itself has to be used as the iterable Table in the ForAll. In your second formula, the part where you have

mydatasource As mydatasourceRecord,

has to instead be

Filter(mydatasource As mdsPreFilteredRecord,mdsPreFilteredRecord.UserId="Jonathan") As mydatasourceRecord

So like this:

ForAll ( Filter(mydatasource As mdsPreFilteredRecord,mdsPreFilteredRecord.UserId="Jonathan") As mydatasourceRecord ,Collect ( mycollection1 ,{ FullNameOriginator: LookUp(mycollection2, ID = mydatasourceRecord.'User (Originator)','Person name') ,Status:mydatasourceRecord.Status ,'User (Originator)':mydatasourceRecord.'User (Originator)' ,Subject:mydatasourceRecord.Subject ,'Work item instructions':mydatasourceRecord.'Work item instructions' ,WorkflowWorkItemClaimed:mydatasourceRecord.WorkflowWorkItemClaimed ,'User (UserId)':mydatasourceRecord.'User (UserId)' ,'Due date time':mydatasourceRecord.'Due date time' } ) );

Check if it helps @akg1421
amk1421 195 on at

Like (1)

Report
Re: Error when Filtering data source before Creating a Collection

@poweractivate

Which of the following is correct:
First:
ForAll(mydatasource; Filter(mydatasource,ThisRecord.'User (UserId)' = "Jonathan"), Collect(mycollection1, { FullNameOriginator: LookUp(mycollection2, ID = mydatasource.'User (Originator)','Person name'), Status:mydatasource.Status, 'User (Originator)':mydatasource.'User (Originator)', Subject:mydatasource.Subject, 'Work item instructions':mydatasource.'Work item instructions', WorkflowWorkItemClaimed:mydatasource.WorkflowWorkItemClaimed, 'User (UserId)':mydatasource.'User (UserId)', 'Due date time':mydatasource.'Due date time' } ) );
gives error "Incompatible types of comparison. These types can't be compared: Text, Table." at:
LookUp(mycollection2, ID = mydatasource.'User (Originator)','Person name')

Second:
ForAll(mydatasource As mydatasourceRecord, Filter(mydatasource,mydatasourceRecord.UserId="Jonathan"), Collect(mycollection1, { FullNameOriginator: LookUp(mycollection2, ID = mydatasourceRecord.'User (Originator)','Person name'), Status:mydatasourceRecord.Status, 'User (Originator)':mydatasourceRecord.'User (Originator)', Subject:mydatasourceRecord.Subject, 'Work item instructions':mydatasourceRecord.'Work item instructions', WorkflowWorkItemClaimed:mydatasourceRecord.WorkflowWorkItemClaimed, 'User (UserId)':mydatasourceRecord.'User (UserId)', 'Due date time':mydatasourceRecord.'Due date time' } ) );
gives error "Invalid number of arguments: received 3, expected 2."