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:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (1)

poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
@akg1421
@akg1421 wrote:

// ForAll(mydatasource As mydatasource,....

You have assigned your record scope mydatasource to be identical to the data source scope mydatasource - you gave them the exact same name! I do not recommend it.

I don't recommend to use As with the identical name as the data source

Do it this way.

I don't know if it will fix it, but it could be problematic to make the As name identical, so it may be better not to:

ClearCollect(mycollection1,Blank()); 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' } ) );

See if this happened to help resolve it.

Was this reply helpful? Yes No
amk1421 195 on at

Like (1)

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

Was this reply helpful? Yes No
Verified answer

poweractivate 11,078 Most Valuable Professional on at

Like (1)

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

Was this reply helpful? Yes No
amk1421 195 on at

Like (1)

Report

@poweractivate

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

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report
@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?

Was this reply helpful? Yes No
amk1421 195 on at

Like (1)

Report

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

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like (0)

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

Was this reply helpful? Yes No
amk1421 195 on at

Like (1)

Report
@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?

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No
amk1421 195 on at

Like (0)

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

Was this reply helpful? Yes No