Canvas App - Lookup returning no results despite valid formula

[LouiseAspman]

I've got a form that I'm using to display a result from an Excel file.

The formulas I'm using are

DataSource = myTable

LookUp(myTable, ID = myId)

I've tried casting the ID and myId to Int's and Text's but each time it's brining back no results.

I've checked and myId is 20 and the Excel file has an ID of 20 so there should be a result - except the form is empty 

Why is this happening?

Thanks
Louise

[zmorek]

Is this what's on the form's Item() property?

LookUp(myTable, ID = myId)

Excel is a tough data source to work with and sometimes it can cause you issues if it's locked out/in use by another user. In the past I've seen this lock-out time to be anywhere from 5-15 minutes while the file realizes it's no longer in use.

[Drrickryp]

@LouiseAspman

Agree with @zmorek, Excel is a terrible datasource. Move your data to SharePoint or better, Dataverse. 

[LouiseAspman]

@zmorek  @Drrickryp 

Thank you, but the use of excel as a datasource is a decision out of my hands, I need a solution to my formula

Thanks

Louise

[WarrenBelz]

@LouiseAspman ,

Firstly, I support both @Drrickryp and @zmorek in their comments and you will find more issues with formulas that should work and simply do not due to Excel's very limited Delegation capabilities, however I did a test here and did the following: -

• Connected an Excel Table using the "Excel for Business" Connector.
• The ID column was formatted as a Number in this Table
• Chose this column as the Unique ID when requested during the connection (rather than letting Excel generate one)
• Set myId as a Numeric Variable

The formula you have brought back the correct record in a Form (myId is set OnChange of the Text Box at the top)

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

[LouiseAspman]

@WarrenBelz - Thank you I will look into that.

However, the data being in an excel file is outside of my control.

[Drrickryp]

@LouiseAspman 

If you need some help to convince someone that the data would be better managed in SharePoint, check out Shane Young's video https://www.youtube.com/watch?v=RtmZM3tm2Zs  but it contains the drawbacks of Excel and its limitations for PowerApps.