cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seanbrogan
Level 8

PowerApps on Mobile not recognising SQL query

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
seadude
Level 10

Re: PowerApps on Mobile not recognising SQL query

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!

View solution in original post

3 REPLIES 3
seadude
Level 10

Re: PowerApps on Mobile not recognising SQL query

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
Level 8

Re: PowerApps on Mobile not recognising SQL query

@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.
Highlighted
seadude
Level 10

Re: PowerApps on Mobile not recognising SQL query

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!

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,857)