error on retrieving data from sharepoint lists using a form in power app

[kbhalll]

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

    )

)

[v-karpurapud]

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.

[kbhalll · Accepted solution]

Hi, the issue is resolved.

thanks

[v-karpurapud]

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

[v-karpurapud]

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.

[anilelmastasi]

You can change KPI_id = ThisRecord.KPI_id in Lookup.

If this solved your issue, please mark it as the accepted solution. ✅

[kbhalll]

thanks - this didn't work