Answered

Find a value in a collection if another collection column has data

(0) Share

Report

Posted on by RomeroCastroK

Hi, I have 2 collections and I want to validate that the columns in one collection have values on the other one, the collections look like this, any help is appreciate it

Collection One

Tittle	Description
Area 1	Training 1
Area 1	Training 2
Area 1	Training 3
Area 1	Training 4
Area 1	Training 5

Collection 2

Employee #	Training Name
1234	Training 1
1234	Training 5
5555	Training 1
5555	Training 2
5555	Training 3
9999	Training 1
9999	Training 2
9999	Training 3
9999	Training 4

Resulting Collection

Employee #	Training 1	Training 2	Training 3	Training 4	Training 5
1234	Yes	No	No	No	Yes
5555	Yes	Yes	Yes	No	No
9999	Yes	Yes	Yes	Yes	No

Categories:

Building Power Apps

I have the same question (0)

All responses (18)

Answers (1)

madlad 2,637 Moderator on at

Like (1)

Report
You could do something like:

ClearCollect( Results, ForAll( GroupBy(Collection 2, "Employee #", "IDGroup") As C2, { 'Employee #': C2.'Employee #', Checking: ForAll( Collection1 As C1, { 'Description': C1.'Description', Done: If( C1.'Description' in C2.IDGroup.'Training Name', true, false ) } ) } ) )

It's not exactly, like the picture, but for all intents and purposes you should be able to use it the same way - every Employee will have X amount of training completion values with either true or false associated, depending on whether they're in collection 2 or not.

Hope this helps!

Was this reply helpful? Yes No
RomeroCastroK 45 on at

Like (0)

Report

Thanks for the help

I get the following error on the IF line

If(
C1.Description in C2.GroupByUsersDisplayName.'Training Course',
true,
false
)
"Can't convert this data type. Powerapps cant convert this Text to a Record

The 'Training Course' column is a record containing "Name" and "ID"

how can i compare to Name only?

regards

Was this reply helpful? Yes No
madlad 2,637 Moderator on at

Like (0)

Report

Are you able to use C2.GroupByUsersDisplayName.'Training Course'.Name? If not we will have to try remove ID earlier in the code.

Was this reply helpful? Yes No
RomeroCastroK 45 on at

Like (0)

Report

Tried that but it doesnt work

Was this reply helpful? Yes No
madlad 2,637 Moderator on at

Like (0)

Report
Unfortunate; We will have to change the filter a bit.
Maybe try something like:

ClearCollect( Results, ForAll( GroupBy( ForAll( Collection 2, {ID: ThisRecord.ID, Name: ThisRecord.'Training Course'.Name} ), "Employee #", "GroupByUsersDisplayName" ) As C2, { 'Employee #': C2.'Employee #', Checking: ForAll( Collection1 As C1, { 'Description': C1.'Description', Done: If( C1.'Description' in C2.GroupByUsersDisplayName.Name, true, false ) } ) } ) )

As this will make replace 'Training Course' with just the name

Was this reply helpful? Yes No
RomeroCastroK 45 on at

Like (0)

Report

this is what I have but it gives me errors, I had to group the and add columns to get the user DisplayName because the column a "Group Person" column in Sharepoint

ClearCollect(
Results,
ForAll(
ForAll(
DatabyArea,
{ID: ThisRecord.ID, Name: ThisRecord.'Training Course'.Value}
);
GroupBy(AddColumns(DatabyArea,"UserDisplayName", 'Employee Name'.DisplayName), "UserDisplayName", "GroupByUsersDisplayName") As C2,
{
'UserDisplayName':
C2.'UserDisplayName',
Checking:
ForAll(
DataTraArea As C1,
{
Description:
C1.Description,
Done:
If(
C1.Description in C2.GroupByUsersDisplayName.Name,
true,
false
)
}
)
}
)
)

Was this reply helpful? Yes No
madlad 2,637 Moderator on at

Like (0)

Report

What's the error it's giving you, and where is it hightlighted over?

Was this reply helpful? Yes No
RomeroCastroK 45 on at

Like (0)

Report

the red part, says Function Group By has invalid argument and Employee Name isn't recognized

ClearCollect(
Results,
ForAll(
GroupBy(
ForAll(
DatabyArea,
{ID: ThisRecord.ID, Name: ThisRecord.'Training Course'.Value}
),
'Employee Name'.DisplayName,"GroupByUsersDisplayName") As C2,
{
Name:
C2.Name,
Checking:
ForAll(
DataTraArea As C1,
{
Description:
C1.Description,
Done:
If(
C1.Description in C2.GroupByUsersDisplayName.Name,
true,
false
)
}
)
}
)
)

Was this reply helpful? Yes No
madlad 2,637 Moderator on at

Like (0)

Report
Ahh I see. Try replacing the highlighted with:

GroupBy( AddColumns( DatabyArea, "Name", ThisRecord.'Training Course'.Value, ), 'Employee Name'.DisplayName, "GroupByUsersDisplayName" ) As C2

You'll notice I changed the ForAll to AddColumns. I did this mainly because it maintains the original structure of the table, instead of my old forall, which was completely making a new table. This likely would have been the better option to begin with to be honest i should have thought of it sooner.

Hope this helps!

Was this reply helpful? Yes No
RomeroCastroK 45 on at

Like (0)

Report

thanks, but Im still getting errors on the red part

ClearCollect(
Results,
ForAll(
GroupBy(
AddColumns(
DatabyArea,
"Name",
ThisRecord.'Training Course'.Value
),
'Employee Name'.DisplayName,
"GroupByUsersDisplayName"
) As C2
{
Name:
C2.Name,
Checking:
ForAll(
DataTraArea As C1,
{
Description:
C1.Description,
Done:
If(
C1.Description in C2.GroupByUsersDisplayName.Name,
true,
false
)
}
)
}
)
)

Was this reply helpful? Yes No