Forum Discussion
error on retrieving data from sharepoint lists using a form in power app
Hi, We are trying to update data based on user input in power app and then trying to retrieve the same data through it. Some data is being retrieved from 1 Sharepoint list, while other data is being retrieved through 2nd list. The error seems to be related with the 2nd list which is getting data from the same form through user input. On retrieving the data, it shows 'Possible invalid string in filter query'. There are some dropdowns (Region, Process, Month, Year) where user needs to select values and then there is a Load button, when user clicks on load button then the below error is shown. Have tried adding another field which is alphanumeric to overcome this error but its not getting resolved. We would appreciate any advice that would help resolve it. The calculation for Load button is given at the bottom.
Thanks
Clear(colKPIInput);
// Step 1: Load KPIs and check for existing monthly data
ClearCollect(
colKPIInput,
ForAll(
Filter(
Master_KPIs,
Region = ddFilterregion.Selected.Value &&
Process = ddFilterprocess.Selected.Value
),
{
ID: ID,
KPI_id: KPI_id,
KPI_Name: KPI_Name,
Target: Proposed_Targets,
Region: Region,
BU: Process,
Manager: Manager,
ExistingData: LookUp(
'KPI_Monthly_data',
KPI_id = KPI_id &&
Month = ddFilterMonth.Selected.Value
),
InputNumerator: Text(
LookUp(
'KPI_Monthly_data',
KPI_id = KPI_id &&
Month = ddFilterMonth.Selected.Value
).Numerator
),
InputDenominator: Text(
LookUp(
'KPI_Monthly_data',
KPI_id = KPI_id &&
Month = ddFilterMonth.Selected.Value
).Denominator
),
InputComments: LookUp(
'KPI_Monthly_data',
KPI_id = KPI_id &&
Month = ddFilterMonth.Selected.Value
).Comments,
CalculatedSLA: LookUp(
'KPI_Monthly_data',
KPI_id = KPI_id &&
Month = ddFilterMonth.Selected.Value
).SLA_Percentage,
MonthlyDataRecordID: LookUp(
'KPI_Monthly_data',
KPI_id = KPI_id &&
Month = ddFilterMonth.Selected.Value
).ID,
IsEditMode: !IsBlank(
LookUp(
'KPI_Monthly_data',
KPI_id = Text(KPI_id) &&
Month = ddFilterMonth.Selected.Value
)
)
}
)
);
Set(varDataLoaded, true);
If(
CountRows(Filter(colKPIInput, IsEditMode)) > 0,
Notify(
CountRows(Filter(colKPIInput, IsEditMode)) & " KPIs have existing data - Edit mode",
NotificationType.Warning
),
Notify(
CountRows(colKPIInput) & " KPIs loaded - New entries",
NotificationType.Success
)
)
Hi, the issue is resolved.
thanks
6 Replies
You can change KPI_id = ThisRecord.KPI_id in Lookup.
If this solved your issue, please mark it as the accepted solution. ✅
thanks - this didn't work
Hi kbhalll
Thank you for reaching out to the Microsoft Fabric community forum.This error occurs because using LookUp() within ForAll() creates an invalid filter query when Power Apps communicates with SharePoint. The issue is typically caused by ambiguous column references or mismatched data types, leading to the "Possible invalid string in filter query" warning.
To avoid this, it's better to use AddColumns() to pull in related data from the second list. This method reduces ambiguity, enhances performance, and eliminates filter query errors.
For example:Clear(colKPIInput); ClearCollect( colKPIInput, AddColumns( Filter( Master_KPIs, Region = ddFilterregion.Selected.Value && Process = ddFilterprocess.Selected.Value ), "MonthlyRecord", LookUp( 'KPI_Monthly_data', KPI_id = Master_KPIs[@KPI_id] && Month = ddFilterMonth.Selected.Value ), "InputNumerator", MonthlyRecord.Numerator, "InputDenominator", MonthlyRecord.Denominator, "InputComments", MonthlyRecord.Comments, "CalculatedSLA", MonthlyRecord.SLA_Percentage, "MonthlyDataRecordID", MonthlyRecord.ID, "IsEditMode", !IsBlank(MonthlyRecord) ) ); Set(varDataLoaded, true);If you have any further questions, feel free to reach out and we'll be glad to assist.
Regards,
Microsoft Fabric Community Support Team.
Hi kbhalll
I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.
Thank You.Hi, the issue is resolved.
thanks
Hi kbhalll
Thank you for the update. If you have any further questions, feel free to reach out and we'll be glad to assist.Regards,
Microsoft Fabric Community Support Team
