Lookup Help

(0) Share

Report

Posted on by MWads44

Hi Experts :),

Hope you can help again.

So i have created a simple app for Vehicle mileage entries,

I have a form where you select the Reg of that vehicle from a data card drop down and what i want it to do is lookup the last record of (End Mileage) for that Reg and display in a label (Label4) or (Text box(Data Card Value) even better if possible or both formulas if different 🙂 )

I have attached images to help and 1 of my attempts below to help with any formula suggestions. (Sharepoint List Column Names and App Form)

Sort(LookUp(tblVehicles,'Vehicle Reg'= DataCardValue5.Selected.Value,SortOrder.Descending).End Mileage

sharepoint list.PNG

Thank you as always

Categories:

Building Power Apps

sharepoint list.PNG

form.PNG

I have the same question (0)

All responses (14)

Answers (2)

WarrenBelz 153,051 Most Valuable Professional on at

Like (1)

Report
Hi @MWads44 ,

LookUp returns a record, so the Sort needs to be inside of this

LookUp( Sort( tblVehicles, 'End Milage', SortOrder.Descending ), 'Vehicle Reg' = DataCardValue5.Selected.Value ).'End Mileage'

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
LaurensM 12,516 Moderator on at

Like (1)

Report
Hi @MWads44,

LookUp returns the first record that matches a certain condition - Sort expects a table / array. What we can do is (1) return all records that match the selected Vehicle Reg., (2) Sort Descendingly and (3) fetch the first record:

First( Sort( Filter( tblVehicles, 'Vehicle Reg' = DataCardValue5.Selected.Value ), 'End Mileage', SortOrder.Descending ) ).'End Mileage'

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

Was this reply helpful? Yes No
MWads44 62 on at

Like (0)

Report

So i am getting an eroor but that is my fault for giving you the wrong datasource name tblVehicles in my example. It should of been tblMileageLogs which i change but have errors.

First(
Sort(
Filter(
tblMileageLogs,
'Vehicle Reg' = DataCardValue5.Selected.Value
),
'End Mileage',
SortOrder.Descending
)
).'End Mileage'

The record i am pulling is a number field as well if that makes a difference.

Thank you

Was this reply helpful? Yes No
MWads44 62 on at

Like (0)

Report

I have just tried the on the above logic using correct table and still having an error 😞

First(Sort(Filter(tblMileageLogs,'Vehicle Reg'= DataCardValue5.Selected.Value),'End Mileage',SortOrder.Descending)).'End Mileage'

Was this reply helpful? Yes No
WarrenBelz 153,051 Most Valuable Professional on at

Like (0)

Report
@MWads44 ,

As I posted earlier, the Sort needs to be first and LookUp is a better query than First(Filter())

LookUp( Sort( tblMileageLogs, 'End Mileage', SortOrder.Descending ), 'Vehicle Reg' = DataCardValue5.Selected.Value ).'End Mileage'

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
MWads44 62 on at

Like (0)

Report

Thanks for replying @WarrenBelz i did try the first one and that did not work for me, i have also copyed the revised but getting an eroor still. What am i going wrong sir?

Was this reply helpful? Yes No
MWads44 62 on at

Like (0)

Report

Is it because of this? mine is a number

Was this reply helpful? Yes No
Verified answer

LaurensM 12,516 Moderator on at

Like (1)

Report
Hi @MWads44,

I agree with @WarrenBelz that LookUp is preferred over the First & Filter. According to your errors, it looks like the DataCardValue5.Selected.Value does not match the 'Vehicle Reg' column type.

To further troubleshoot the issue, we may need more information on the 'Vehicle Reg' column. The following LookUp conditions may fix the issue:

//Single-select choice column 'Vehicle Reg'.Value = DataCardValue5.Selected.Value //LookUp column 'Vehicle Reg'.Id = DataCardValue5.Selected.Id

I hope this helps!

Was this reply helpful? Yes No
WarrenBelz 153,051 Most Valuable Professional on at

Like (1)

Report

@MWads44 ,

I cannot see any error lines in the code - what does the error say when you hover over the red X ? Also the fact that SortOrder.Descending has reverted to Descending suggests Power Apps has accepted the code as valid.

Was this reply helpful? Yes No
MWads44 62 on at

Like (0)

Report

@WarrenBelz @LaurensM Hi both, really appricate the continued support in this 🙂

The error message i am getting over the red X is incompatable types for comparrison, these types cant be compared:Record Text.

The lookup value i am trying to recieve from the sharepoint list is a number from the Mileage End Column using the Vehicle Reg as the lookup value which is a text field.

Was this reply helpful? Yes No