Power Platform Community Forum Thread Details

Hi Everyone,

I need assistance with a specific issue, delegation limits that is affecting my Gallery items.

Scenario

SharePoint list1 "DDH AS5369 Audit Tool Section 2" is a questionnaire that forms the Items in the Power APPS Gallery "Section Questions", when entering into each item the next screen is where answers to the question are completed including Rating, Action Item...etc

SharePoint list2 "DDH AS5369 Audit Results - Current" with greater than 2000 records, this list is the Results List of records once an answer to the Section Question is submitted.

In the "Section Questions" Gallery, I use a Template Fill property to shade the question once completed to provide a visual of Already Answered.

This achieved by: On submit of the answer the patch function to SharePoint List2, Column 'QuestionID' and 'UserID' are patched with ThisItem.ID, and User().Email.

the SharePoint List1 "Section Questions" Gallery, Template Fill Property uses a Lookup to check if QuestionID and UserID have Values and if so Shades TemplateFill, RGBA( 232,244,217,1 )

This all works fine but now where I have the issue is any record over the 2000 limit in the SharePoint List2, even though the record exists and the QuestionID and UserID columns contain the data the Delegation Limit prevents the TemplateFill from working, hence I can no longer see that the Visual Light Geen fill indicating the question is answered. The more questions answered the greater the challenge.

My question is "How do I get around the delegation Limit in SharePoint so the App can do as I need it to do and shade the template fill once a question is answered and continue to do so beyond 2000 records."

The Code for the Gallery Items includes Search, a delegable function or so I thought. I have also Indexed the SharePoint List2 'DDH AS5369 Audit Results - Current' where the answers are, columns QuestionID, UserID, Section ID, Site.

Below is the code for the gallery SharePointList1 "Section Questions":

Items:

Search(
    'DDH AS5369 Audit Tool Section 2',
    SearchBox_S3.Text,
    Section,'Sub Section Heading','Section Heading',
    Question,
    'Sub Question'
)

Template Fill:

If(
    resetGallery,
    Color.White,
    If(
        !IsBlank(
            LookUp(
                'DDH AS5369 Audit Results - Current',
                QuestionID = ThisItem.ID && UserID = User().Email && "2" in 'Section ID' && SiteDDS2.SelectedText.Value in Site.Value
            )
        ),
        RGBA(
            232,
            244,
            217,
            1
        ),
        Color.White
    )
)

Reset Questionnaire: // this button resets the questions to not answered by removing the criteria for the respective QuestionID, UserID, Section, and Site ensuring the questionnaire is ready for next use by that user.

ClearCollect(
    tempResponses,
    Filter(
        'DDH AS5369 Audit Results - Current',
        UserID = User().Email && "2" in 'Section ID' && SiteDDS2.SelectedText.Value = Site.Value
    )
);
ForAll(
    tempResponses,
    Patch(
        'DDH AS5369 Audit Results - Current',
        ThisRecord,
        {
            UserID: Blank(),
            QuestionID: Blank()
        }
    )
);
UpdateContext({resetGallery: true});
UpdateContext({resetGallery: false});
UpdateContext({resetGallery: false});
Refresh('DDH AS5369 Audit Tool Section 2');
Reset(SiteDDS2);
Reset(WardAreaDDS2);
Reset(RatingDD);
Reset('Evidence or Action input');
Reset(ActionItemCBS2);
Reset(AnnualInterimDDS2)
 
 
Next Button in Gallery to answer screen ('Rating Evidence ScreenS2'):
Set(
    VarSite,
    SiteDDS2.SelectedText
);
Set(
    varAreaWard,
    WardAreaDDS2.SelectedText
);
Set(
    varAnnualInterim,
    AnnualInterimDDS2.SelectedText
);
NewForm('Evidence Attach');
Navigate('Rating Evidence ScreenS2')
 
 
Submit Button on answer screen:
Patch(
    'DDH AS5369 Audit Results - Current',
    Defaults('DDH AS5369 Audit Results - Current'),
    {
        'DDH Primary Site Responsible Officer': 
            LookUp(
                'DDH Water Quality Responsible Officers',
                'Reprocessing Site' = VarSite.Value,
                'DDH Primary Site Responsible Officer'.Email
            ),
            
        UserID: User().Email,
        QuestionID: 'Section Questions'.Selected.ID,
        'Area of Standard': "Quality Assurance",
        'Section ID': 2,
        'Action Item': ActionItemCBS2.Value,
        Rating: RatingDD.SelectedText,
        'Evidence or Action': 'Evidence or Action input'.Text,
        'Sub Question II': 'Section Questions'.Selected.'Sub Question II',
        'Sub Question': 'Section Questions'.Selected.'Sub Question',
        Question: 'Section Questions'.Selected.Question,
        'Sub Section Heading': 'Section Questions'.Selected.'Sub Section Heading',
        'Section Heading': 'Section Questions'.Selected.'Section Heading',
        Section: 'Section Questions'.Selected.Section,
        Assessor: Assessor.Text,
        Site: VarSite,
        'Area or Ward': varAreaWard,
        'Date and Time of Assessment': varTimeTS,
        'Annual or Interim': varAnnualInterim
    },
    'Evidence Attach'.Updates
);
Reset(RatingDD);
Reset(ActionItemCBS2);
Navigate(
    'Search List',
    ScreenTransition.Fade
)
 
If you need more information please let me know. 
Thank you.

Categories:

Power Apps Pro Dev & ISV

Hi @CHQLDHealth

Firstly the TemplateFill - you need to separate out the Delegable bits

If(
   resetGallery,
   Color.White,
   With(
      {
         _Data:
         Filter(
            'DDH AS5369 Audit Results - Current',
            QuestionID = ThisItem.ID && 
            UserID = User().Email &&
            Site.Value = SiteDDS2.Selected.Value
         )
      },
      If(
         !IsBlank(
            LookUp(
               _Data,
               "2" in 'Section ID'
            )
         ),
         RGBA(
            232,
            244,
            217,
            1
         ),
         Color.White
      )
   )
)

Then also your update Patch

With(
   {
      _Data:
      Filter(
         'DDH AS5369 Audit Results - Current',
         UserID = User().Email &&
         Site.Value = SiteDDS2.Selected.Value
      )
   },
   Patch(
      'DDH AS5369 Audit Results - Current',
      ForAll(
         Filter(
            _Data,
            "2" in 'Section ID'
         ) As _D,
         Patch(
            'DDH AS5369 Audit Results - Current',
            {
               ID: _D.ID,
               UserID: Blank(),
               QuestionID: Blank()
            }
         )
      )
   )
);

BTW - where do you work in Qld Health ?

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn Buy me a coffee