cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Help with syntax please: AddColumns and LookUp with Azure SQL DB

The following code is underlining the = sign in red:

 

AddColumns('[Order].[OrderDetail]',"MyMenuCategoryID", LookUp(LocalMenuItem, MenuItemID = '[Order].[OrderDetail]'.MenuItemID,MenuCategoryID))

I am trying to add a column to OrderDetail. OrderDetail is a SQL Azure DB table containing a field MenuItemID. I want to find the corresponding CatergoryID for the MenuItemID in a collection named LocalMenuItems. Can't seem to find the right syntax Smiley Sad

 

Can someone point out what I'm doing wrong please?

 

Thanks

P

9 REPLIES 9
Super User
Super User

Re: Help with syntax please: AddColumns and LookUp with Azure SQL DB

Which part has the red line under it, and what does the error message say?

Super User
Super User

Re: Help with syntax please: AddColumns and LookUp with Azure SQL DB

Only the = sign is underlined and the message is Invalid Argument type.

Super User
Super User

Re: Help with syntax please: AddColumns and LookUp with Azure SQL DB

Finally figured it out Smiley Happy :

 

AddColumns('[Order].[OrderDetail]',"MyMenuCategoryID", LookUp(LocalMenuItem, MenuItemID = '[Order].[OrderDetail]'[@MenuItemID],MenuCategoryID))
Super User
Super User

Re: Help with syntax please: AddColumns and LookUp with Azure SQL DB

Where have you written that piece of code, in a gallery's Items-property, or perhaps in a call to UpdateContext() in a screen's OnVisible-property?

 

It's probably just a typo, but just want to point it ouf it it's not: in the code you write LocalMenuItem, but in the explanation you write LocalMenuItems with an s on the end.

Super User
Super User

Re: Help with syntax please: AddColumns and LookUp with Azure SQL DB

Thanks @BitLord69 - it was just a typo in my explanation.

 

I'm now trying to do similar with a LookUp to an Azure SQL DB table rather than a local collection:

 

AddColumns('[Order].[OrderDetail]',"MyOrderHeaderName",LookUp('[Order].[OrderHeader]',OrderHeaderID = '[Order].[OrderDetail]'[@OrderHeaderID],OrderHeaderName))

This is 'working' but is showing as non-delegable - specifically the = and the [@OrderHeaderID] are underlined blue.

 

Any thoughts on why this would not be delegable? OrderHeaderID is an int data type and in the OrderHeader table is an identity field (1,1).

 

Thanks!

sdiaz
Level: Powered On

Re: Help with syntax please: AddColumns and LookUp with Azure SQL DB

Hi @PaulD1 any update on this issue? I'm also showing the non-delegable blue line on the '=' sign and the [@xxxxxxx] column field. 

 

Thank you

Super User
Super User

Re: Help with syntax please: AddColumns and LookUp with Azure SQL DB

Hi @sdiaz

 

Are you using Azure SQL DB as your datasource? If so, I'd advise creating a View in SQL and using that as your datasource to get around this issue.

 

Note that I believe Views are still not updatable from within PowerApps, so if you are performing updates to your data, you have to Patch updates to the individual tables and refresh your Datasource (based on the View) to see those changes.

sdiaz
Level: Powered On

Re: Help with syntax please: AddColumns and LookUp with Azure SQL DB

Hello @PaulD1 thank you for your reply. I'm using Sharepoint list as my datasource. 

 

The part of a longer formula that is giving me a headache is this one. 

 

AddColumns('Daily Shipping Table',"Core / Non-Core",LookUp(Materials, MaterialNum = 'Daily Shipping Table'[@MaterialNum],CoreNonCore)

 

In there, the equal sign and the [@MaterialNum] is giving me a delegation warning. Any thoughts with Sharepoint List?

 

Thank you

Super User
Super User

Re: Help with syntax please: AddColumns and LookUp with Azure SQL DB

Hi @sdiaz

 

You could check whether using a First(Filter combinations makes any difference instead of LookUp (I don't think it will, but worrt a try), e.g.

 

First(Filter(Materials, MaterialNum = 'Daily ShippingTable'[@MaterialNum],CoreNonCore)

 

Failing that... You could cache Materials locally in a collection and use that instead. This is a common trick to boost performance but remember you will need to add a facility to refresh (ClearCollect) the collection to keep it up to date.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Demo Extravaganza Championship Voting Open

Voting Ends: October 30, 2019!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 189 members 4,914 guests
Please welcome our newest community members: