cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Select Top 1 Record

I'm building out an app for personal vehicle tracking, primarily related to fuel tracking. I built a SQL table that has an ID column and collects data such as date, vehicle, odometer, miles, gallons, etc.

 

I'm trying to see if there is a way to have the Miles field in my app take the text/number entered in the Odometer field and have it subtract the odometer reading from the most recent entry in the table.

 

So Miles = OdometerField - (select top 1 odometer from table where vehicle = 1)

1 ACCEPTED SOLUTION

Accepted Solutions

Yes, you should be able to do that. But in your expression:

 

First(SortByColumns(Filter('[dbo].[VehicleFuelTracking]',"Vehicle" = DataCardValue14.Selected.Value), "FuelingDate",Descending)).OdometerReading2

 

You're comparing that the value is the string "Vehicle". What you probably want is

 

First(SortByColumns(Filter('[dbo].[VehicleFuelTracking]',Vehicle = DataCardValue14.Selected.Value), "FuelingDate",Descending)).OdometerReading2

 

Where the value of the column 'Vehicle' is equal to the selected dropdown.

View solution in original post

3 REPLIES 3
Power Apps
Power Apps

Yes, you can have the SortByColumns function (with the Descending option) to get the latest entry in the table, and then use the First function (equivalent to "top 1") to take the latest record. Something along the lines of:

LabelMiles.Text:
    Value(TextInputOdometer) -
    First(
        SortByColumns(
            Filter(Table, Vehicle = 1),
            Date,
            Descending)).Odometer

Carlos,

Thanks for the info. Is there a way to have the Vehicle on the filter be = the data card value of a previous drop down? I have a drop down of vehicles that the use must choose and that is stored in the Vehicle column we are filtering on.

 

This is what I have so far. It works until I add in the Filter command:

First(SortByColumns(Filter('[dbo].[VehicleFuelTracking]',"Vehicle" = DataCardValue14.Selected.Value), "FuelingDate",Descending)).OdometerReading2

 

It's not erroring, just not pulling anything back.

 

Yes, you should be able to do that. But in your expression:

 

First(SortByColumns(Filter('[dbo].[VehicleFuelTracking]',"Vehicle" = DataCardValue14.Selected.Value), "FuelingDate",Descending)).OdometerReading2

 

You're comparing that the value is the string "Vehicle". What you probably want is

 

First(SortByColumns(Filter('[dbo].[VehicleFuelTracking]',Vehicle = DataCardValue14.Selected.Value), "FuelingDate",Descending)).OdometerReading2

 

Where the value of the column 'Vehicle' is equal to the selected dropdown.

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (12,308)