cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StuCundy
Post Patron
Post Patron

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

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.

 

 

5 REPLIES 5
v-yutliu-msft
Community Support
Community Support

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

 

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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)) ??

 

 

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})

Patcherror1.png

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?

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (51,300)