Nested filters from different tables based on some conditions on the data

[Julien2]

Hello everyone,

I have two tables that contain data about projects that are related to a user similar to the below:

Table1:

Table2:

The relationship in dataverse between Table1 and Table2 is one-to-many.

The table1 data is about the project name, the user related, its expiry date and if it is enabled for advanced scheduling or not (I will explain the purpose of it in table2).

Table2- has line items based on the project name in table 1 and that project will only have multiple lines in this table if it is enabled for scheduling in table1.

Real-example and scenario:

Table1:
User A assigned to Project A from 11/29/2021 until 11/30/2021 and enabled for advanced schedule.

In table2 the line items that are related to that project are the below:

• Proj A on day 11/29/2021 he should visit the customer from 8:00 AM - 9:00 AM.
• Proj A same-day on 11/29/2021 should again visit the customer from 12:00 PM - 1:00 PM.

What I want to achieve is the below:

For table1:
I want to filter the list of projects that is related to a specific user and not expired by date comparing it to today's date.

Before I filter table2 some conditions should be checked according to the data returned from table1:

1. If the project is already expired by date I should not query table2.
2. If the project is not enabled for advanced scheduling that means eq to false (Yes/No) field I should not query table2.

If it passed the above conditions based on the data from table1 it requires to query the second table and the filter should be as below:

• Filter the data from table2 where project resource id is equal to project resource id from table1 And active by comparing it to today's date+time.
  Example: If proj A is from 11/29/2021 8:00 AM to 9:00 AM and the current time is 9:05 AM the project should not show in the gallery.

The formula that I tried:

//Get the current user ResourceID
Set(
    varResourcesID,
    LookUp(
        TimeSheetResources,
        'User Name'.'Timesheet User' = varuserID
    ).'Resource Identification'
);

//Get the list of projects based on the User Resource ID and are active
ClearCollect(
    ColProjResources,
    Filter(
        'Project Resources',
        'Res. Name'.'Resource Identification' = varResourcesID,
         'Start Date' <= Today() && 'End Date' >= Today()
        
    ).'Project Name'
);

//Still need to check the projects returned inside the above collection if they are any project enabled for schedule yes or no
//If Yes -> Query timesheet_calendar, If no do nothing
// logical name of the field to check yes/no: 'Enable Schedule' = 'Enable Schedule (Project Resources)'.Yes

//Here I still have an issue while trying to compare the resourceID with the collection resourceID

Clear(ColCalen);
ForAll(
    RenameColumns(ColProjResources,"cr884_ProjectID","newProjID"),
    Collect(
        ColCalen,
        LookUp(
            timesheet_Calendars,
            'Project Resource ID'.'Proj. Res. ID' = newProjID And 'Start Date' <= Today() && 'End Date' >= Today()
        )
    )
);

Onvisible of screen

Can please someone advise and provide an example of what could be the updated formulas based on the above conditions and logic?

I hope that I provided all the details, otherwise please let me know if you need any clarifications.

Thank you!

[RandyHayes]

@Julien2 

If you are using this for a Gallery, then there is no need for all the collections.  Plus your ForAll is backward in this case.  ForAll is a function that returns a Table, not a For/Loop.

The concept of trying to exclude looking up into the table 2 under certain conditions will be a waste.  It will take you much more of a formula to do all of that and it will gain you nothing.  That concept is good in a programming world, but PowerApps is not programming or development, it is formula design that you are doing.

My suggestion would be the following formula:

With({_resID: LookUp(TimeSheetResources,
                  'User Name'.'Timesheet User' = varuserID,
                  'Resource Identification'
              )
     },
    ForAll(
        Filter('Project Resources',
            'Res. Name'.'Resource Identification' = _resID,
            'Start Date' <= Today() && 'End Date' >= Today()
        
        ) As _items,
        LookUp(timesheet_Calendars,
            'Project Resource ID'.'Proj. Res. ID' = _items.cr884_ProjectID &&
            'Start Date' <= Today() && 'End Date' >= Today()
        )
    )
        
)

This will do the lookup as you need based on the filter of the first table.

Now...your original issue was with a type comparison.  It is saying that the item on the left side ('Project Resource ID'.'Proj. Res. ID') is Text and you are trying to compare to a record (cr884_ProjectID).  So you would need to get those two to compare text to text or number to number (you cannot compare record to record).

I hope this is helpful for you.

[Julien2]

Dear @RandyHayes ,

Thank you for your clarifications and I think that possibly might cause some performance issues if I want to add some conditions before quiring the other table.

I need to use that formula on the gallery "Items property" instead of onVisible property of the screen.

Does the formula that you provided will find the multiple line items related to the first table because I think The lookup will retrieve only one record knowing that I should find all the line items related to the projects returned from table1.

Regarding the filter comparison, the cr884_ProjectID is an auto number column as a data type and it is the primary key of the first table (Project Resource).

I used the formula that you mentioned without an error but the labels are not showing the data as below:


The expected outcome that should be displayed from table2 in Powerapps is the below-highlighted data:

Please let me know if you need any clarification based on the above.
What I am doing wrong? Could you please advise what should be changed in the formula?

Looking forward to your response.

Best regards,

[Julien2]

Hello @RandyHayes ,

Any updates on the above?
I look forward to hearing back from you.

Thank you!

[Julien2]

Hello @RandyHayes ,

After I did an investigation, I found that in the LookUp function I am comparing the wrong Project Resource ID.

However, it seems that the LookUp is returning only one record instead of 2 or multiple records for that project.

I have also tried to change the LookUp to Filter but the problem is not fetching the columns in the gallery to be able to use "ThisItem.ColumnName".

With(
    {
        //User Resource
        _resID: LookUp(
            TimeSheetResources,
            'User Name'.'Timesheet User' = varuserID,
            'Resource Identification'
        )
    },
    ForAll(
        Filter(
            'Project Resources',
            'Res. Name'.'Resource Identification' = _resID,
            'Start Date' <= Today() && 'End Date' >= Today()
        ) As _items,
        Filter(
            [@timesheet_Calendars],
            'Project Resource ID'.'Proj. Res. ID' = _items.'Proj. Res. ID'
        )

    )
)

Expected Outcome to be displayed in the gallery:

What would be the solution to resolve that issue?

I look forward to your response.

Thank you!

[RandyHayes · Accepted solution]

@Julien2 

Sorry for the delay and thanks for keeping on top of me to respond.  I get backlogged sometimes.

So the reason you are seeing only IsSelected and Value for your options is because you introduced a Filter function into your ForAll and provided no record structure for your ForAll results.  Before, with the LookUp, it was returning a record structure and you had all the columns to choose from.

When you provide no record structure, PowerApps will create a default column called Value.  So, the results of your Filter (a table) are going into the Value column of each record of the ForAll.

The easiest way to do this is to alter the formula to:

Ungroup(
   With({_resID: LookUp(TimeSheetResources,
                  'User Name'.'Timesheet User' = varuserID,
                  'Resource Identification'
              )
     },
    ForAll(
        Filter('Project Resources',
            'Res. Name'.'Resource Identification' = _resID,
            'Start Date' <= Today() && 'End Date' >= Today()
        
        ) As _items,
        Filter(timesheet_Calendars,
            'Project Resource ID'.'Proj. Res. ID' = _items.cr884_ProjectID &&
            'Start Date' <= Today() && 'End Date' >= Today()
        )
    )
        
   ),
   "Value"
)

This will remove the Value column and raise the table records to the outer level.

[Julien2]

Hello @RandyHayes ,

No problem, thank you for getting back to me.
Thanks for the explanation I was not aware of that behavior.

I have tried the mentioned updated formula and now it is displaying the right data based on our filter logic.
However, I still have an issue with displaying the project name in that gallery based on the records retrieved as I have tried the below two formulas and the project name is not showing, knowing that there is no issue highlighted under both formulas that I used:

Any idea why the project name is not showing in the text label?

I look forward to your response.

Best regards,
Julien

[RandyHayes]

@Julien2 

What is the real name of that column in your list definition?  I would start with that and see what happens if you put that real name in instead.

[Julien2]

Hello @RandyHayes ,

The real name of that field is cr884_projectid which is a lookup to a different table as N:1 in the timesheet_calendar table.
I have also tried to use its original name but it is showing an error because it can't be found as an option.
It is showing only the logical name of the field.
 
In addition, for any relational column field that I want to display in the gallery, it is not showing it's value.

What might be the problem?

Best regards,
Julien

[RandyHayes]

@Julien2 

So, I don't work with Dataverse all that much to be definitive.  But, I believe you need to do the lookup then on the lookup table and specify the associated ID to get back the correct record and then display the column you want.

I hope that makes sense as I'd need to set up a testing example here to validate it.