Formulas worked yesterday, but not all parts of them today (EDITED)

[Anonymous]

EDITED: These formulas work for some students, but not all. Makes me think it's a data source issue, but I can't find anything off in my excel tables as I didn't change anything.

All my formulas were working yesterday, but not today. Any thoughts? I tried removing my data source which is excel and re-connecting. (examples of formulas below)

Ex #1 - the text box is supposed to say "Yes" when the student has been audited and "No" when they haven't. The current selected student has been audited. I checked Table_Audits and Table_Students and the student's name is in both. The combo box is linked to Table_Students. *EDIT: some students with audits it says "Yes," but others with audits it says "no"

Ex #2 - once a student is selected, the gallery should populate with all their audits. Then you can select a radio button to filter the gallery to that particular audit type. (formula below) When I preview the app, the gallery doesn't even show on the screen where here it at least shows it without any records. *EDIT: some selected students with audits will populate the gallery, others that also have audits will not

SortByColumns(
Filter(Table_Audits,Student=cboxSTUDENTrec.Selected.Student && If(!IsBlank(radio_AUDITTYPErec.Selected.Value),Audit=radio_AUDITTYPErec.Selected.Value,true)),"AuditDate",Descending)

Ex #3 - the gallery lists all students and those who have been audited say "Yes" and those that have not say "No". Mickey has not been audited, but the next student has. (formula below for Y/N text box) *Edit: Some students with audits it will say "Yes," but others with audits it will say "no"

If(ThisItem.Student in Table_Audits.Student,"Yes", "No")

[Mr-Dang-MSFT]

Hi @Anonymous ,

In the formula bar, did you see an error that said something about delegation? The issue you're encountering is that the data source is hitting a limit on how many records it can read and return because the filters are not being 'delegated' to the service. There are forum posts on this topic that you can search for with the keywords 'delegation.'

Depending on where your data is stored, the forum members who are experts on Filters and LookUps can help here.

+ @LRVinNC @RandyHayes 

[v-xida-msft]

HI @Anonymous ,

Based on the formula that you provided, I do not find any syntax error with it.

I have made a test on my side, and don't have the issue that you mentioned. Based on the issue that you mentioned, I think this issue may be related to your Excel table data source.

Please consider take a try to re-create your two Excel tables -- Table_Audits1 and Table_Students1, the copy data from your previous Excel table into the new created Excel table individually.

Then re-create a new app based on above new created Excel tables, then try your formula again, check if the issue is solved.

If the issue still exists, please consider store your data within SP list instead of your Excel table, then try it again, check if the issue still exists.

Best regards,

[Anonymous]

@v-xida-msft  Just tried recreating the tables in excel and testing in a new app. Same issue. Will work on switching to a SP list which was something I was considering due to the large volume of audit records.

[Anonymous]

@Mr-Dang-MSFT  I didn't see a delegation error, but will look up those posts to see if that may help. Thank you!

[Anonymous · Accepted solution]

@v-xida-msft  @Mr-Dang-MSFT 

I tried the Sharepoint list for the Table_Audits and received the same results of select audited students working. Also, the gallery had the added delegation warning. When the excel is used, there is not a delegation warning.

@RandyHayes UPDATE: I've increased my limit to 2000 which has fixed my problem for now. I anticipate there being over 2000 records in the near future, so what would solve that problem? I can keep the SP list for the audit table if that would make the solution easier as it can be delegated. Thanks!