Error on the Lookup - wrong data types?

(0) Share

Report

Posted on by Tomzy

Hello everyone, I'm faced with a problem and I think I'm stuck. So bit of background first

I have two SharePoint lists

ChangeRequest, where I have a custom column called "RFC_ID" , it's single line of text and type number, it just fills in a role of an ID column

EstimationsList, where I have cost estimations for the Change, I'm identifying which change the estimation refers to by providing EstimationList with the "RFC_ID" from Change Request list. This is not a lookup I'm just patching it in the app when users create estimations for a selected request.

I have a formula in the OnStart Property of the app where I get all estimations done by or assigned to currently logged in user, Since user might have multiple Estimations on a request I want to get just Distinct RFC_ID's from EstimationList

This is the formula:

ClearCollect(myCostEstimations, Distinct(Filter('EstimationList', TaskOwner.Email = properEmail), RFC_ID));

Now to my problem:

I want to get all records from ChangeRequest list where the ChangeList RFC_ID is in the myCostEstimations collection. I cannot use the "in" operator since this runs into delegation issues. What I've tried is to loop through all the items of myCostEstimations collection and collect records from ChangeRequest list where RFC_ID = Looped item RFC_ID, but this throws me an error.

This is my formula:

ClearCollect(estimationRFC,[]);

ForAll(myCostEstimations, Collect(estimationRFC, LookUp('ChangeRequest',RFC_ID = ThisRecord.RFC_ID)));

This an error I'm getting on part in red

Error when trying to retrieve data from network: Fetching items failed. Possible invalid string in filter query.

I think it might be an issue with types of data in the end, since just 2 lines below I've implemented similar formula which works but with text columns

Has any one of you faced an issue like that? Thank you in advance for all the help

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

gcmfaizan 1,022 on at

Like
a
(1)

Report
Copy link

Link copied!
@Tomzy you can achieve this task by using mention below formula I create two collection but you can use your actual data source:
// ChangeRequest List Data ClearCollect(ChangeRequestList, {RFC_ID:1001,Title:"Request1",Description:"Description for 1"},{RFC_ID:1002,Title:"Request2",Description:"Description for 2"},{RFC_ID:1003,Title:"Request3",Description:"Description for 3"}); // EstimationList Data ClearCollect(EstimationList,{RFC_ID:"1001",// Text representation TaskOwnerEmail:"myemail@example.com",EstimatedCost:500}, {RFC_ID:"1002",TaskOwnerEmail:"user2@example.com",EstimatedCost:700}, {RFC_ID:"1001",TaskOwnerEmail:"user3@example.com",EstimatedCost:300}, {RFC_ID:"1003",TaskOwnerEmail:"myemail@example.com",EstimatedCost:1000});
then I created two more collecting one to get the current login user other to get change requests of this user:
// Collect unique RFC_IDs from EstimationList for the logged-in user ClearCollect( myCostEstimations, Distinct( Filter(EstimationList, TaskOwnerEmail = "myemail@example.com"// here you can use User().Email), RFC_ID ) ); // Collect records from ChangeRequestList where RFC_ID is in myCostEstimations ClearCollect( estimationRFC, Filter( ChangeRequestList, RFC_ID in myCostEstimations ) );

If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

Thanks!

Was this reply helpful? Yes No
Tomzy 6 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @gcmfaizan ,
thanks for the reply, this should work, but I worry about the delegation warning with usage of this formula
ClearCollect( estimationRFC, Filter( ChangeRequestList, RFC_ID in myCostEstimations ) );
As far as I know the "in" statement is not delegable I think this might be a solution if nothing else comes up that allows for Filtering the ChangeRequest list directly. Thank you for your response!

Was this reply helpful? Yes No
Verified answer

gcmfaizan 1,022 on at

Like
a
(1)

Report
Copy link

Link copied!
@Tomzy yes in some cases in function is not delegable, you can use this formula I'm sure by using this there is no deligation warning:
Clear(estimationRFC); ForAll( myCostEstimations, Collect( estimationRFC, Filter( ChangeRequestList, RFC_ID = ThisRecord.RFC_ID ) ) );

If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

Thanks!

Was this reply helpful? Yes No