Show Child Gallery Item if Not in Parent Gallery and patch to join parent

(0) Share

Report

Posted on by StuCundy

377

I have a child list called "Students" in Gallery1 and a parent list called "WorkingGroup" in Gallery2.

Students are filtered by a "GroupName" in dropdown1. I want to send the student Items to the "Working Group" List shown in Gallery2 using a select and patch method. I want to show only those items in students that are NOT already in the WorkingGroup to make sure they are all list.

I have filtered Gallery2 by the dropdown : Filter(WorkingGroup, GROUPTitle = Dropdown1.Selected.Title)

In Gallery 1 how can I show only those items that are not in the parent and then patch them across to the working group. In the end I do not want to see any items left in my child list.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @StuCundy ,

Do you want to only display those items that are not in the parent table?

Could you show me the structure of WorkingGroup table and the structure of Students table?

I need to know you patch what data of Students table to WorkingGroup table.

You could use this data to filter.

For example:

I assume that there's a field in WorkingGroup table named Students.

You patch a whole record of Students table to this field.

In Students table, there's a unique field named ID.

Then you could set the Students gallery's Items:

Filter(Students,Not(ID in AddColumns(WorkingGroup,"studentid",Students.ID).studentid))

Then the gallery will only show the students that id has not existed in workinggroup table.

Then patch function could be like:

Patch(WorkingGroup,Defaults(WorkingGroup),{Student:LookUp(Students,ID=studentgallery.Selected.ID)})

Best regards,

Was this reply helpful? Yes No
StuCundy 377 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-yutliu-msft
Thanks for your reply. Yes I would like to display those only not appearing in the parent table.
Structure of Student Table is ,ID, Title,Surname,Firstname
Parent Table is ID, Title, StudentID, StudentTitle
I want to patch StudentTitle and StudentID as a new item if they don't exist already in the parent.

I used your filter for the student table and it did not work as intended. I also filter the gallery by a dropdown box. It is a group name to which this set of students belong. The filter showed ID's that appeared in the parent table unfortunately.

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @StuCundy ,

The formula that I provided is based on the assumption of students is a nested table in workinggroup table.

But obviously, you do not set like this.

So the formula above will not work.

Could you tell me these fields' data type in the two tables?

I assume that they are all text type.

If so, try this:

set the Students gallery's Items:

Filter(Students,Not(ID in WorkingGroup.StudentID))

Then patch function could be like:

Patch(WorkingGroup,Defaults(WorkingGroup),{ID:..., Title:..., StudentID:childgallery.selected.ID, StudentTitlechildgallery.selected.Title}) //please fill in ID and Title value

Best regards,

Was this reply helpful? Yes No
StuCundy 377 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @v-yutliu-msft

The Fields datatypes are all text expect for the ID. However I do have a dropdown which the galleries are filtered by. When I select the group Title in the Dropdown5 the appropriate Working Group gets displayed.

Working Group Junction Gallery
Next to it I have my Student list which is also filtered by the same dropdown.
//Junction Gallery// Filter(WorkingGroup, GROUPTitle = Dropdown5.Selected.Title) //Student Gallery Filter(Students, GroupTitle = Dropdown5.Selected.Title)
So Filtered by the same dropdown I need to see who is not in the Junction Gallery
I tried to hybrid your function many times in many ways but has not worked yet.

Filter(Filter(Students, GroupTitle = Dropdown5.Selected.Title),Not(ID in JunctionGalllery.Selected.StudentID)) ??

Was this reply helpful? Yes No
StuCundy 377 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @v-yutliu-msft

I gave in a good go but just cant get this to work
I was able to have both a collection and non collection gallery pulling up records that are not in my junction gallery.
The patch direct gave me this.
Patch(Program_GroupHostJunction,Defaults(Program_GroupHostJunction),{StudentID:StudentIDTT,STUDENTTitle: StudentTitleTT,GROUPTitle: GroupTT,ID:StudentGalleryNotInJunction.Selected.ID,Title:StudentGalleryNotInJunction.Selected.Title,GroupTitle:StudentGalleryNotInJunction.Selected.GroupTitle})
Says GroupTitle does not match expected number, found text type. But that is a text type field. Actually it is a lookup on the students list but its not number/ I tried putting "Selected.'GroupTitle'" but not change.

The other Patch from Collection formula I've tried is
ForAll(RenameColumns(Filter(ColStudentNotinList, Ischosen), "ID", "EID"), Patch(Program_GroupHostJunction, LookUp(Program_GroupHostJunction, ID=EID), Defaults(Program_GroupHostJunction), {STUDENTTitle: StudentTitlelabel},{StudentID: StudentID7, GROUPTitle:Label31 })); ClearCollect(ColStudentNotinList, AddColumns(Program_GroupHostJunction, "Ischosen", false))
Am I close?

Was this reply helpful? Yes No