PowerApps on Mobile not recognising SQL query

[seanbrogan]

I have a problem I'm hoping somebody might be able to help me with.

So I have an SQL database holding the data for my app. In the database I have a "Job_Card_tbl" which holds the jobs for the next month. In the SQL DB I have a query/view wich just shows the jobs for todays date, "All_Jobs_Today_qry"

In PowerApps when a user logs in, their employee ID is set to the variable "UID". I then have
ClearCollect(My_Jobs_Today,Filter('[dbo].[All_Jobs_Today_qry]',Employee_ID = UID))

And from there I can get the ID and Reg of the Users van by doing...
Set(Veh_ID,Max(My_Jobs_Today,Vehicle_ID)) ; //Use max here since 1 = not in a van
Set(Veh_Reg,LookUp(My_Jobs_Today,Vehicle_ID = Veh_ID,Vehicle_Reg)) 


The difficulty is that this runs perfectly when testing on the web browser. I can hit view collections to view the table My_Jobs_Today and the variables Veh_ID and Veh_Reg.

When I run it on a mobile phone any variables that look to the SQL query have no value in their result. My Veh_ID IsBlank and so is my Veh_Reg.

I have tried deleting the datasources in powerapps and reconnecting to no avail. Is there something I am missing here between the web browser and the app?

[seadude]

Hi @seanbrogan. MANY OTHERS, are seeing similar issue: App works fine in IDE Preview Mode and Browser Player on laptop but does not work consistently on mobile. In nearly all cases I've read on the forums, the issue is with ClearCollect'ing a SQL View.

Did you find a resolution?

Thanks

[seanbrogan]

@seadude I'm afraid not no. Decided not to include the view in this app. I am hoping it will be corrected or I can find a workaround before the next app.

[seadude · Accepted solution]

Hi @seanbrogan. Working with a colleague, we figured this one out last night...
We had to move our ClearCollect from the OnSelect Property of a Button on Screen1 to the OnVisible Property of Screen2. For some reason this works!
Issue:

• User would click a button in Gallery on Screen1
• ClearCollects and Set(vars) (shown below) occur
• User is navigated to Screen2
• When Screen2 appears, the two Text boxes with their Text Properties set to varPreviousRecord and varAverage would not reset. They maintained their previous values until the user navigated BACK to Screen1 and clicked the button in the Gallery again.
• NOTE: '[dbo].[vwThingReading]' is the SQL View

Example:

• Not Working: Screen1, OnSelect Property of Button1

/*======================================================================
Start validation. Create a collection of the last 3 records.
======================================================================*/
ClearCollect(
    colLast3Records,
        FirstN(
            Filter(
                Sort('[dbo].[vwThingReading]', recordId, Descending),
                thingId = thingGallery.Selected.thingId,
                recordDate_string <> varRecordDate
            ),
        3)
);

/*======================================================================
Set a var for previous record.
======================================================================*/

Set(varPreviousRecord,
    Round(
        LookUp( 
            Sort(
                colLast3Records,recordDate, Descending
            ),
            thingId = thingGallery.Selected.thingId, currentRecord
        )
    ,2)
);

/*======================================================================
Set a var for previous Usage.
======================================================================*/
Set(varPreviousUsage,
    LookUp(
        Sort(
            colLast3Records,recordDate, Descending
        ),
        thingId = thingGallery.Selected.thingId, currentUsage
    )
);

/*======================================================================
Calculate the average of the last 3 records to compare against current reading.
======================================================================*/
Set(varAverage,
    Round(
        Average(
            colLast3Records, current
        )
    ,2)
);
Navigate(Screen2,Cover)

• When this code was moved to Screen2 OnVisible, varPreviousRecord and varAverage show up correctly each time. (There is a slight lag, but its better than the vars not being set at all!)

Hope this helps!