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:
- If the project is already expired by date I should not query table2.
- 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!
