cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidvo68
Helper II
Helper II

How to filter empty or blank items from gallery before patching

Hi all,

My code below is to patch to my SharePoint List from a collection, colSubmittedQuestion which being populated by a gallery.allitems, and it is working perfectly if I don't have the RemoveIf statement in it.  

Without removing the blank or empty records,  the patch function patches ALL items.  

My TriagedScore column is a type choice and it has 1,2,3 as choices inside a gallery and all items are being collected into a collection called colSubmittedQuestion.  Please see the image below.

davidvo68_0-1656636937880.png

My code:

ForAll(

RemoveIf(colSubmittedQuestion, IsBlank(TriagedScore) || IsEmpty(TriagedScore)),  // this is the statement I need help with

RenameColumns(colSubmittedQuestion,"ID", "SID"),
Patch('Fenced Triaged Review Checklist',
LookUp('Fenced Triaged Review Checklist',ID = SID),
{ProgramName:ProgramName, ProgramID:Value(ProgramID),
ARIWGInitialDate:ARIWGInitialDate, ARIWGCompletionDate:ARIWGCompletionDate,
PriARIWGPOC:PriARIWGPOC, AltARIWGPOC:AltARIWGPOC, Member:Member,
SIMAlignment:LookUp(Choices('Fenced Triaged Review Checklist'.SIMAlignment),Value=SIMAlignment.Text), SectionNumber:Value(SectionNumber.Text),
TriagedScore:LookUp(Choices('Fenced Triaged Review Checklist'.TriagedScore),Value=TriagedScore),
QuestionNumber:Value(QuestionNumber), Question:Quesion, ARIWGComment:ARIWGComment, BusinessOwnerComment: BusinessOwnerComment,
ResultCommunicate2POC:ResultCommunicate2POC, Resultof2Submission:Resultof2Submission,
SecondSubmissionDate:SecondSubmissionDate, OverallAssessComment:OverallAssessComment,CMB:CMB

}
));

Thank you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Alex_10
Super User
Super User

@davidvo68 

 

you can try this

ForAll( RemoveIf(RenameColumns(colSubmittedQuestion,"ID", "SID"), IsBlank(TriagedScore) || IsEmpty(TriagedScore)), 
    Patch('Fenced Triaged Review Checklist', LookUp('Fenced Triaged Review Checklist', ID = SID),
        {
        ProgramName:    ProgramName, 
        ProgramID:    Value(ProgramID),
        ARIWGInitialDate:    ARIWGInitialDate, 
        ARIWGCompletionDate:    ARIWGCompletionDate,
        PriARIWGPOC:    PriARIWGPOC, 
        AltARIWGPOC:    AltARIWGPOC, 
        Member:    Member,
        SIMAlignment:    LookUp(Choices('Fenced Triaged Review Checklist'.SIMAlignment),Value=SIMAlignment.Text), 
        SectionNumber:    Value(SectionNumber.Text),
        TriagedScore:    LookUp(Choices('Fenced Triaged Review Checklist'.TriagedScore),Value=TriagedScore),
        QuestionNumber:    Value(QuestionNumber), 
        Question:    Quesion, 
        ARIWGComment:    ARIWGComment, 
        BusinessOwnerComment:     BusinessOwnerComment,
        ResultCommunicate2POC:    ResultCommunicate2POC, 
        Resultof2Submission:    Resultof2Submission,
        SecondSubmissionDate:    SecondSubmissionDate, 
        OverallAssessComment:    OverallAssessComment,
        CMB:    CMB
        }
    )
);

View solution in original post

4 REPLIES 4
Alex_10
Super User
Super User

@davidvo68 

 

you can try this

ForAll( RemoveIf(RenameColumns(colSubmittedQuestion,"ID", "SID"), IsBlank(TriagedScore) || IsEmpty(TriagedScore)), 
    Patch('Fenced Triaged Review Checklist', LookUp('Fenced Triaged Review Checklist', ID = SID),
        {
        ProgramName:    ProgramName, 
        ProgramID:    Value(ProgramID),
        ARIWGInitialDate:    ARIWGInitialDate, 
        ARIWGCompletionDate:    ARIWGCompletionDate,
        PriARIWGPOC:    PriARIWGPOC, 
        AltARIWGPOC:    AltARIWGPOC, 
        Member:    Member,
        SIMAlignment:    LookUp(Choices('Fenced Triaged Review Checklist'.SIMAlignment),Value=SIMAlignment.Text), 
        SectionNumber:    Value(SectionNumber.Text),
        TriagedScore:    LookUp(Choices('Fenced Triaged Review Checklist'.TriagedScore),Value=TriagedScore),
        QuestionNumber:    Value(QuestionNumber), 
        Question:    Quesion, 
        ARIWGComment:    ARIWGComment, 
        BusinessOwnerComment:     BusinessOwnerComment,
        ResultCommunicate2POC:    ResultCommunicate2POC, 
        Resultof2Submission:    Resultof2Submission,
        SecondSubmissionDate:    SecondSubmissionDate, 
        OverallAssessComment:    OverallAssessComment,
        CMB:    CMB
        }
    )
);

davidvo68
Helper II
Helper II

Thank you Alex.  I tried your modified code and it did take care of the error. However, it still went thru and updated all of the gallery items including the blank/empty ones.

Any other suggestion?  I was thinking of instead doing the remove blank/empty items, just update the ones which has value of 1,2,3 in the column but not sure how to modify the code.

davidvo68
Helper II
Helper II

Hi Alex,  sorry that I spoke too soon.  It did work with your modified code.  Thank you so much.

Another question though related to this code.  How would I modify this code just to update the items which being changed or modified?

Alex_10
Super User
Super User

@davidvo68 

there is no an easy way to do that.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,482)