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

patch as a new Item to in a list

Hi there

I have two galleries/Lists Im working with.  One is a list of students and the other a junction table into which I wish to add chosen items (student's Title and ID) as new items in the Junction list.  I would also like filter the student gallery to not show ID;s that already appear in the Junction gallery.

 

Collection is pretty simple

ClearCollect(ColStudents, AddColumns(GroupStudents, "Ischosen",false))

My Patch is as follows

ForAll(RenameColumns(Filter(ColStudents, Ischosen), "ID", "EID"), Patch(Program_GroupHostJunction, LookUp(Program_GroupHostJunction, ID=EID), {STUDENTTitle: StudentTitle.Text,StudentID: StudentID7}));
ClearCollect(ColStudents, AddColumns(Program_GroupHostJunction, "Ischosen", false))

 

Its not adding missing items.  Also i have no idea where to insert a filter to not show items in student gallery that already appear in the junction gallery.  Look forward to some wisdom 🙂

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @v-xida-msft 

I have finally got some success with this.  In the end I kept the collection and added a check box to that.  I filter the collection so items already in the destination list do not show.  The field data is taken directly from the collection and not the gallery so I can use field names instead of label names.  The only issue I have is that in the IsBlank area of the patch I could not HostID=ID successfully, it had to be HostID=Value(ID) and I wonder why that is....

ForAll(
        Filter(CollectionNOTinGallery.AllItems, 'CheckBox-AddToHostList'.Value=true), 
        Patch(
               HostSearch,
               If(
                  IsBlank(LookUp(HostSearch, HostID = Value(ID))),
                  Defaults(HostSearch),
                  (LookUp(HostSearch, HostID = ID))
               ), 
               {
                   HostTitle: Title,
                   HostID: ID,
                   GroupTitle:Dropdown4_3.SelectedText.Value,
                   Title : Dropdown4_3.SelectedText.Value&" Host Search-HostID: "&ID,
                   HostEmail: HFemail,
                   HostTypeOfStay: TypeOfStay,
                   HostOwner: Owner.Value,
                   HostAddress: HFstreet&" "&HFsuburb.Value&" "&HFcity,
                   HostVisitorNumberPref: VisitorNumberPref,
                   HostPets: Pets
                }
               ));
ClearCollect(colSearchHost, AddColumns(HOSTFAMILIESTest, "AddtoSearchList",false));
Refresh(HostSearch)

 

Anyway thank you it has helped me a lot.

View solution in original post

4 REPLIES 4
mdevaney
Super User
Super User

@StuCundy 

Are you trying to both Update records in your junction table and create new records when nothing can be found?

 

ForAll(
    RenameColumns(Filter(ColStudents, Ischosen), "ID", "EID"),
    Set(currentRecord, LookUp(Program_GroupHostJunction, ID=EID));
    If(IsBlank(currentRecord),
    
    // inserts a new record when no match is found
    Patch(Program_GroupHostJunction,
    Defaults(Program_GroupHostJunction),
    {STUDENTTitle: StudentTitle.Text,  StudentID: StudentID7}
     
    // updates an existing records
    Patch(Program_GroupHostJunction,
    currentRecord,
    {STUDENTTitle: StudentTitle.Text,  StudentID: StudentID7}
    )
);

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

v-xida-msft
Community Support
Community Support

Hi @StuCundy ,

Could you please share a bit more about your scenario?

Do you want to add new item in your junction table when the Student is not within your junction table, otherwise, update the existing records in your junction table?

Further, could you please share a screenshot your app's configuration?

 

Based on the formula that you provided, it could only be used to update existing records in your junction table rather than add new records into your junction table.

 

I assume that your First Gallery (Student Gallery) is connected to the GroupStudents List, and the second Gallery is connected to the Program_GroupHostJunction List, right?

 

If you want to filter the student gallery to not show ID;s that already appear in the Junction gallery, please set the Items property of the Student Gallery to following:

Filter(
       GroupStudents,
       Not(ID in Program_GroupHostJunction.StudentID)
)

 

If you want to patch new items into your junction table when the Student ID is not existed in your junction table, please consider modify your formula as below:

ForAll(
        RenameColumns(Filter(ColStudents, Ischosen), "ID", "EID"), 
        Patch(
               Program_GroupHostJunction,
               If(
                  IsBlank(LookUp(Program_GroupHostJunction, ID = EID)),
                  Defaults(Program_GroupHostJunction),
                  IsBlank(LookUp(Program_GroupHostJunction, ID = EID))
               ), 
               {
                  STUDENTTitle: StudentTitle.Text,
                  StudentID: StudentID7
                }
         )
);

 

In addition, if you added a Checkbox within your Student Gallery to choose these records you want to added to your junction table, I think it is not necessary to collect these records you want to patch from your Student Gallery using collection. Please try the following formula directly:

ForAll(
      Filter(StudentGallery.AllItems, Checkbox1.Value = true),
      Patch(
            Program_GroupHostJunction,
            If(
               IsBlank(LookUp(Program_GroupHostJunction, ID = Value(StudentID7.Text))),
               Defaults(Program_GroupHostJunction),
               LookUp(Program_GroupHostJunction, ID = Value(StudentID7.Text))
            ),
            {
                STUDENTTitle: StudentTitle.Text,
                StudentID: Value(StudentID7.Text)
            }
      )
);
Refresh(GroupStudents);
Refresh(Program_GroupHostJunction)

Note: I assume that the StudentID7 is a Label in your Student Gallery to display the Student ID value from your Students List.

 

Best regards,

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

Hi @v-xida-msft 

 

I used your formula to add the items.  But no success. I understand that I could just have a check box in the students list but I want to add the same student again in a different group which is why I have the check box in the collection.

I tried to use your direct method approach from the collection but although I had no errors, I had no marching ants when activating the patch

 

Here's a screen shot

screenshot.png

So the Dropdown5 showing "Miyazaki 2018" is the GroupTitle and the filter key.

1. Collection Gallery is  [colStudentNotinList] shows 3 items using the filter formula you gave me to show items NOT in the Junction table.  Works Well 🙂

2. Student in : Miyazaki Gallery is Total students in GroupStudents filtered by Dropdown5

3. Student Gallery Not in Junction show 3 items from GroupStudents using the filter formula you gave me to show items NOT in the Junction table.  I do this to check the collection that they show the same results...

4. Junction List by Dropdown Gallery is the table that I need the missing 3 items to be sent to.

 

I wish to use the checkbox in the collection to add the 3 items to Junction table.

 

If I use the both versions of the ForAll function you gave me.  (I have added the GROUPTitle field and changed the StudentTitelLabel BTW)  Here is a screen shot of them.

noerror1.pngEID1.png

I get the marching ants with both but no updates.  No added item to Junction table.

 

*Yes StudentID7 is the label name for the ID in the GroupStudents gallery Fitered by the following

 

Filter(
    ColStudentNotinList,
    GroupName = Dropdown5.Selected.Title And Not(ID in Program_GroupHostJunction.StudentID)
)

 

Look forward to your reply

Hi @v-xida-msft 

I have finally got some success with this.  In the end I kept the collection and added a check box to that.  I filter the collection so items already in the destination list do not show.  The field data is taken directly from the collection and not the gallery so I can use field names instead of label names.  The only issue I have is that in the IsBlank area of the patch I could not HostID=ID successfully, it had to be HostID=Value(ID) and I wonder why that is....

ForAll(
        Filter(CollectionNOTinGallery.AllItems, 'CheckBox-AddToHostList'.Value=true), 
        Patch(
               HostSearch,
               If(
                  IsBlank(LookUp(HostSearch, HostID = Value(ID))),
                  Defaults(HostSearch),
                  (LookUp(HostSearch, HostID = ID))
               ), 
               {
                   HostTitle: Title,
                   HostID: ID,
                   GroupTitle:Dropdown4_3.SelectedText.Value,
                   Title : Dropdown4_3.SelectedText.Value&" Host Search-HostID: "&ID,
                   HostEmail: HFemail,
                   HostTypeOfStay: TypeOfStay,
                   HostOwner: Owner.Value,
                   HostAddress: HFstreet&" "&HFsuburb.Value&" "&HFcity,
                   HostVisitorNumberPref: VisitorNumberPref,
                   HostPets: Pets
                }
               ));
ClearCollect(colSearchHost, AddColumns(HOSTFAMILIESTest, "AddtoSearchList",false));
Refresh(HostSearch)

 

Anyway thank you it has helped me a lot.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,269)