Lookup/Filter not working properly inside ForAll formula

[Anonymous]

Hi,

I am facing a very strange issue where the same formula is acting weirdly under different scenarios.

I have written the following formula in 'On Visible' property of my screen. Here the Filter is nested inside a ForAll formula. No matter what the actual case is, IsEmpty() part is always returning a true value.

For testing the output I wrote the same formula in the label. Here the output is correct.

Why is this happening? How can I fix it? 

Regards,

Aakash

[WarrenBelz]

Hi @Anonymous ,

The first thing I can see is they are two different tests. You are looping through ResourceTasks and putting the test in the middle of it, so it relates to ResourceTasks. I suggest this if it fits your logic.

If(
   IsEmpty(
      Filter(
         EffortHoursDatabase,
         Activity= Calibration Activity, 
         TaskID= "T-0267", 
         MonthNumber = Value(Text(Today(),"[$-en-US]m"))
      )
   ),
   ForAll(
      ResourceTasks,
      ResourceName=ProfileDisplayName,
      Patch(
         EffortHoursDatabase,
         Defaults(EffortHoursDatabase), 
         { 
            Activity :Calibration_Activity,
            TaskID:Result,
            ResourceName: ProfileDisplayName,
            MonthNumber:Value(Text(Today(),"[$-en-US]m")), 
            Resource0365ID:0ffice365Users.MyProfile().Id
         }
      )
   )
)

I also tend to use this test

If(
   IsBlank(
      Lookup(
         EffortHoursDatabase,
         Activity= Calibration Activity, 
         TaskID= "T-0267", 
         MonthNumber = Value(Text(Today(),"[$-en-US]m"))
      ),Activity          //use whatever field you like here
   ),
   Do whatever

Also please supply code as Text in text boxes (the </> icon above). I OCR'd this so there may be spelling mistakes.

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.

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

Could you please share a bit more about the ProfileDisplayName? Is it a variable?

Based on the formula that you mentioned, I found that the Filter formula within your ForAll function would return an fixed result, I mean the result the Filter formula returned would not be changed when you do each Loop action within the ForAll function, it would make no sense.

According to the issue that you mentioned, I think this issue may be related to Intelligence Prompt in PowerApps Studio. Based on the needs that you mentioned, I think you want to execute the IsEmpty(Filter(....)) formula for each Task Id within the ResourceTasks collection, is it right?

Please consider modify your formula as below:

ClearCollect(ResourceTasks, Distinct(Filter(ResourceTaskTable.AllItems, TaskStatus = "Work in Process"), TaskID));
UpdateContext({Calibration_Activity: "Calibration " & Text(Today(), "[$-en-US]mmm")});
// Mofify your formula as below
ForAll(
       ResourceTasks,
       If(
          IsEmpty(
                  Filter(
                          EffortHoursDatabase,
                          Activity = Calibration_Activity,
                          TaskID = ResourceTasks[@Result],    // Modify formula here
                          ResourceName = ProfileDisplayName,
                          MonthNumber = Value(Text(Today(), "[$-en-US]m"))
                  )
          ),
          Patch(
                EffortHoursDatabase,
                Defaults(EffortHoursDatabase),
                {
                  Activity: Calibration_Activity,
                  TaskID: ResourceTasks[@Result],
                  ResourceName: ProfileDisplayName,
                  MonthNumber: Value(Text(Today(), "[$-en-US]m")),
                  ResourceO365ID: Office365Users.MyProfile().Id
                }
          )
       )
)

Please consider modify your formula as above, then try it again, check if the issue is solved.

Note: Currently, there is some known issue with the Intelligence Prompt in PowerApps Studio, the execution result would not be affected by the Intelligence Prompt result

Best regards,

[Anonymous]

@WarrenBelz I forgot to mention that TaskID= "T-0267" was just for demonstration. I guess you proposed your solution based on that.  My apologies for causing the confusion and thank you so much for your reply.

As assumed correctly by @v-xida-msft the If(IsEmpty(Filter(...))) logic needs to run for each task id within the ResourceTasks collection. @v-xida-msft , I made the changes you asked me to do. Instead of TaskID: ResourceTasks[@Result] I used TaskID: Result because the former was showing a delegation warning. Now it worked absolutely fine. And yes, ProfileDisplayName is a variable I define during the app 'OnStart' property.

The strange this is, this is what the original code was and it was not giving the correct output yesterday. For some reason, each time the IsEmpty() function was returning 'true' as a result the 'If' statement was executing the 'true' expression. But today it worked fine. Is it related to the Intelligence Prompt issue or am I still missing something? 

[Anonymous]

@v-xida-msft While my original issue is resolved, I am facing another issue with the similar Lookup formula. Here are some more details.

On visible property of the screen, I looked up if a particular activity (Calibration_activity) exists in the EffortHoursdatabase or not and accordingly created one entry in the same database. Your inputs helped me to achieve this. 

Now, I have added an input slider in my gallery which on change will add hours to Calibration_activity in the Efforthours database. For this, I have written a patch formula which is as follows.

Patch(EffortHoursDatabase,
    LookUp(
        EffortHoursDatabase,
        TaskID = ThisItem.TaskID &&
        ResourceName=ProfileDisplayName &&
        Activity = Calibration_Activity
    ),
    {Hours:Hours_InputSlider_2.Value}
)

And this is what I have written in the default property of the slider. Also, beside the slider there is a text box that displays the hours and uses the same formula.

LookUp(EffortHoursDatabase,
    TaskID = ThisItem.TaskID &&
    ResourceName=ProfileDisplayName &&
    Activity = Calibration_Activity
).Hours

Now I tried to change the slider and it did update the value both in this gallery and in the backend EffortHoursdatabase but when I open this screen again, something weird is happening. The value is getting reset to 0 even in the backend database. And I don't know how but the values in the other tasks are getting changed as well (I updated the slider to 43 and the same value got updated for all the other tasks). And it seems like some infinite loop is running. Following is the screen video. Observe the MTD Calibration column and the progress below the address bar

  Aakash

[v-xida-msft]

Hi @Anonymous ,

Have your previous issue been solved?

Which data source does the Gallery connect to? ResourceTasks collection or EffortHoursDatabase?

Firstly, if you have solved your previous issue, and my solution is helpful in your scenario, please consider g ahead to click "Accept as Solution" to identify this my reply as Solution.

If you have any issue within your scenario, you could consider open another thread, and describe your issue within the new opened thread, I would help there.

Based on your second issue that you mentioned, I think this issue may be related to the OnChange property of the Slider control in your Gallery.

When you load your Gallery, the Initialization of your Slider control would also fire the OnChange property of the Slider control, so it seems that infinite loop is running.

As an fixed solution, please consider take a try with the following workaround:

Set the OnChange property of the Slider control to following:

If(
    LookUp(EffortHoursDatabase,TaskID = ThisItem.TaskID &&ResourceName=ProfileDisplayName && Activity = Calibration_Activity).Hours <> Hours_InputSlider_2.Value,  // Check if the Slider is changed manually
    Patch(
           EffortHoursDatabase,
           LookUp(
                   EffortHoursDatabase,
                   TaskID = ThisItem.TaskID &&
                   ResourceName=ProfileDisplayName &&
                   Activity = Calibration_Activity
           ),
           {Hours:Hours_InputSlider_2.Value}
    )
)

Set the Default property of the Slider control to following:

LookUp(EffortHoursDatabase,
    TaskID = ThisItem.TaskID &&
    ResourceName=ProfileDisplayName &&
    Activity = Calibration_Activity
).Hours

Please consider take a try with above solution, then check if the issue is solved. Please also consider go ahead to click "Accept as Solution" to identify this original issue has been solved.

Best regards,

[Anonymous]

Hi @v-xida-msft ,

The infinite loop is still running. Following is the video

Following is the On Visible property of the screen (it also has other formulas)

UpdateContext({Visibility_SelectRole:false,Calibration:false,Highlight:false});
//---------------------------------------------------------------------------------------------
Clear(MyAssignmentsTemp);

//Filter thr latest tasks assigned to you in the Resource Database
ClearCollect(
        MyAssignmentsTemp,Filter(SortByColumns(Resource_database,"Created",Descending),
        Resource_TO = ProfileDisplayName || Resource_TO2 = ProfileDisplayName ||
        Resource_TO3 = ProfileDisplayName || Resource_TO4 = ProfileDisplayName ||
        Resource_TO5 = ProfileDisplayName ||
        Resource_BML = ProfileDisplayName || Resource_BML2 = ProfileDisplayName ||
        Resource_BML3 = ProfileDisplayName || Resource_BML4 = ProfileDisplayName ||
        Resource_BML5 = ProfileDisplayName ||
        Resource_PKG = ProfileDisplayName || Resource_PKG2 = ProfileDisplayName ||
        Resource_PKG3 = ProfileDisplayName || Resource_PKG4 = ProfileDisplayName ||
        Resource_PKG5 = ProfileDisplayName ||
        Resource_RA = ProfileDisplayName || Resource_RA2 = ProfileDisplayName || 
        Resource_RA3 = ProfileDisplayName || Resource_RA4 = ProfileDisplayName || 
        Resource_RA5 = ProfileDisplayName ||
        Resource_AQE = ProfileDisplayName || Resource_AQE2 = ProfileDisplayName ||
        Resource_AQE3 = ProfileDisplayName || Resource_AQE4 = ProfileDisplayName ||
        Resource_AQE5 = ProfileDisplayName ||
        Resource_Others = ProfileDisplayName || Resource_Others2 = ProfileDisplayName ||
        Resource_Others3 = ProfileDisplayName || Resource_Others4 = ProfileDisplayName ||
        Resource_Others5 = ProfileDisplayName
        ).TaskID
    );

//Filter above tasks in TaskPlanner Database to populate Resource tasks gallery (ResourceTaskTable)
Clear(MyAssignments);
ClearCollect(MyAssignments,Filter(TaskPlannerDatabase,TaskID in MyAssignmentsTemp.TaskID ));
//---------------------------------------------------------------------------------------------
//Collection to be used for populating the total hours spent by the resource on the all tasks
ClearCollect(TotalHours,Filter(EffortHoursDatabase,ResourceName = ProfileDisplayName)); 

//Collection to be used for populating the MTD hours spent by the resource on the all tasks
ClearCollect(MTDHours,Filter(EffortHoursDatabase,MonthNumber=Month(Today()),ResourceName=ProfileDisplayName));
//---------------------------------------------------------------------------------------------

//Filter "WIP" Tasks from the ResourceTaskTable
ClearCollect(ResourceTasks,Distinct(Filter(ResourceTaskTable.AllItems,TaskStatus="Work in Process"),TaskID));

//For all tasks with status "WIP" check if a calibration activity for the current month exists and create one accordingly
UpdateContext({Calibration_Activity: "Calibration " & Text(Today(),"[$-en-US]mmm")});
ForAll(
    ResourceTasks,
    If(
        IsEmpty(
            Filter(
                EffortHoursDatabase,
                Activity= Calibration_Activity, 
                TaskID= Result, 
                ResourceName=ProfileDisplayName,
                MonthNumber = Value(Text(Today(),"[$-en-US]m"))
            )
        ),
            
    Patch(EffortHoursDatabase, Defaults(EffortHoursDatabase),{
        Activity:Calibration_Activity, 
        TaskID:Result, 
        ResourceName:ProfileDisplayName, 
        MonthNumber:Value(Text(Today(),"[$-en-US]m")),
        ResourceO365ID:Office365Users.MyProfile().Id
        })
    )
);

Answer to your question: Gallery source is 'MyAssignments'.

Following is the OnChange property of the Slider 

If(
    LookUp(
        EffortHoursDatabase,
        TaskID = ThisItem.TaskID &&
        ResourceName=ProfileDisplayName && 
        Activity = Calibration_Activity
        ).Hours <> Hours_InputSlider_2.Value,  // Check if the Slider is changed manually
    Patch(
           EffortHoursDatabase,
           LookUp(
                   EffortHoursDatabase,
                   TaskID = ThisItem.TaskID &&
                   ResourceName=ProfileDisplayName &&
                   Activity = Calibration_Activity
           ),
           {Hours:Hours_InputSlider_2.Value}
    )
)

I think the issue is here because when I emptying this property for testing, the infinite loop stops running.

LookUp(EffortHoursDatabase,
    TaskID = ThisItem.TaskID &&
    ResourceName=ProfileDisplayName &&
    Activity = Calibration_Activity
).Hours

Regards,

Aakash

[Anonymous]

Issue Resolved

I replaced the ResourceName column with ResourceID column as both can be used to filter by resource.

LookUp(EffortHoursDatabase,
    TaskID = ThisItem.TaskID &&
    ResourceId=ProfileID &&
    Activity = Calibration_Activity
).Hours

Thanks for all your help. @v-xida-msft I have marked your answer on the primary issue as 'accepted solution'.

Aakash