Hello everyone,
I have two tables Users and Resources, the user's table has a relationship type with resource table as 1:N.
From the resource table, I want to fetch the user that he belongs to a specific resource.
Once I try to filter my data using the WIth function to fetch first the user ID and compare it with another "With function" for the resource.
The functions that I used is:
With(
{
UserID: Filter(
'Timesheet Users',
'User Name' = username_text.Text
).'User ID'
},
With(
{
ResID: Filter(
Resources,
'User ID'.'User ID' = UserID
).'Res. ID'
}
)
The error is clearly saying that the variable which I defined for the Used ID is returning a table result as a value and it's failing because I am comparing it to a text.
How can I match their type to solve this error and get the right result?
Can please someone provide an example?
Any help will be highly appreciated.
Thanks in advance!
Solved! Go to Solution.
Okay, I tried to change what you made before a bit:
Set(
varResourceID,
LookUp(
Resources,
'User ID'.'User ID' = varTest
).'Res. ID'
);
This doesn't really match with what you explained in your previous post, but I'm assuming varTest is a variable based on the UserID from timesheet users.
ClearCollect(
ColProjResources,
Filter(
'Project Resources',
'Resource ID'.'Res. ID' = varResourceID
).'Project ID'
);
This is where multiple project ID's are grabbed from the third list using the one resource ID, and storing them in a local collection called ColProjResources.
Clear(
ColTimesheetProjects,
);
ForAll(
ColProjResources,
Collect(
ColTimesheetProjects,
LookUp(
'Timesheet Projects',
'Project ID' = ThisRecord.'Project ID'
)
)
)
This will iterate through each record in the new local collection. For each record, it will match the 'Project ID' from that record with the 'Project ID' from 'Timesheet Projects', and store records found in another local collection based on 'Timesheet Projects', called ColProjResources.
It's a bit of a hassle working with all this. Is it possible to add another column in the Project Resources list, where you store the resource ID, or is it possible for one entry to use multiple resource IDs?
Hello @CNT ,
As mentioned, I changed the first filter to lookup but I get another issue which is the below:
Could you please assist?
Looking forward to your response.
@Julien2 Try this,
With(
{
UserID: LookUp(
'Timesheet Users',
'User Name' = username_text.Text
).'User ID'
},
With(
{
ResID: Filter(
Resources,
'User ID'.'User ID' = UserID
).'Res. ID'
}
)
@CNT , Thanks for your prompt reply.
I tried what you mentioned it keeps on failing:
Hello @CNT ,
Thank you for your reply.
It seems the first problem was wrong spelling in Lookup as it should be with capital U.
Now the other issue I got once I try to compare a filter vs lookup result from the WIth function it failed because:
This is all the formula:
With(
{
UserID: LookUp(
'Timesheet Users',
'User Name' = username_text.Text
).'User ID'
},
With(
{
ResID: Filter(
Resources,
'User ID'.'User ID' = UserID
).'Res. ID'
},
With(
{
ProjResID: Filter(
'Project Resources',
'Resource ID'.'Res. ID' = ResID
).'Project ID'
},
Filter(
'Timesheet Projects',
'Project ID' = ProjResID
).'Project Name'
)
)
)
What should be the workaround to solve the above issue?
Thank you!
Hello, any help would be highly appreciated!
Why did you adapt CNT's formula to include another With( ?
Whenever you use Filter(, it will return a table of values.
In your second With( statement, you are asking the formula to return a table with one column ('Res.ID') and define that as ResID.
In your third With( statement, you are asking the formula to filter a text value in SharePoint with the table you just defined.
Before you said you have a 1:N relationship, but you are making the formula to support a 1:N:N relationship. Is this necessary?
Hello @KvB1 ,
From the beginning I am using the WIth function formula because the main goal of the formula is the below:
The tables are created on dataverse and between those tables I have custom relationship types.
I still have one more issue and still struggling with that which is the below:
And, this is my updated formula:
With(
{
UserID: LookUp(
'Timesheet Users',
'User Name' = username_text.Text
).'User ID'
},
With(
{
ResID: Filter(
Resources,
'User ID'.'User ID' = UserID
).'Res. ID'
},
With(
{
ProjResID: Filter(
'Project Resources',
'Resource ID'.'Res. ID' = ResID
).'Project ID'
},
Filter(
'Timesheet Projects',
'Project ID' = ProjResID
).'Project Name'
)
)
)
It would be highly appreciated if you can assist.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
User | Count |
---|---|
195 | |
67 | |
46 | |
41 | |
28 |
User | Count |
---|---|
257 | |
119 | |
86 | |
84 | |
81 |