cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShadowTech
Post Prodigy
Post Prodigy

Lookup specific line in SQL table

Hello,

 

I am pre-populating fields in a form. What I want to do is lookup a column in a SQL table but choose which line to perform the lookup. Basically multiple lines can be associated with a single referenced number. Can a lookup specify a line or does it need to be a filter?

 

I'm performing a lookup of a SQL table and then gets submitted to a SharePoint List. My formula works just need to specify a certain line in the SQL table.

 

Thanks in advance.

10 REPLIES 10
WarrenBelz
Super User
Super User

@ShadowTech ,

You can have a LookUp with a condition

If(
   x,
   Lookup( a . . ),
   y,
   Lookup( b .  .),
   Lookup( c .  .)
)

 

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.

@WarrenBelz This is what I'm using right now. I need to add to the formula to check for a specific line.

 

If(Form1.Mode=FormMode.New, LookUp('[dbo].[tblsoReturnAuthLine]',ReturnAuthNumb=DataCardValue10.Text,Item),ThisItem.'Part Number')

 

Thanks 

@ShadowTech ,

You need to have the whole lookup in there - not sure if the logic is what yo are after, but the syntax would be like this.

If(
   Form1.Mode=FormMode.New, 
   LookUp(
      '[dbo].[tblsoReturnAuthLine]',
      ReturnAuthNumb=DataCardValue10.Text,
      Item
   ),
   LookUp(
      '[dbo].[tblsoReturnAuthLine]',
      ReturnAuthNumb=ThisItem.'Part Number',
      Item
   )
)

 

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.

@WarrenBelz In my formula I'm accessing the first line for the "Item" column which works just fine. I need to access the second line in the "Item" column.

@ShadowTech ,

OK I understand now - a LookUp will get the first matching value. To get the second matching value, you would have to do something like this

If(
   Form1.Mode=FormMode.New, 
   LookUp(
      '[dbo].[tblsoReturnAuthLine]',
      ReturnAuthNumb=DataCardValue10.Text,
      Item
   ),
   With(
   {
      wItem: FirstN(
         Filter(
            '[dbo].[tblsoReturnAuthLine]',
            ReturnAuthNumb=DataCardValue10.Text
         ),
         2
      )
   },
   Last(wItem).Item
)

I am simply using your filter to grab the first two matching items and then taking the last one.

 

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.

@WarrenBelz 

 

It's actually pulling the same line.

 

ThisItem.'Part Number'

 

 

 

 

If(
   Form1.Mode=FormMode.New, 
   LookUp(
      '[dbo].[tblsoReturnAuthLine]',
      ReturnAuthNumb=DataCardValue10.Text,
      Item
   ),
   With(
   {
      wItem: FirstN(
         Filter(
            '[dbo].[tblsoReturnAuthLine]',
            ReturnAuthNumb=DataCardValue10.Text
         ),
         2
      )
   },
   Last(wItem).Item
))

 

 

 

 

Thanks in advance for your help.

@ShadowTech ,

The data field in the second bit is referenced in the With() statement - as mentioned I have simply gathered the first two matching records, then specified the last one of these.

This is a workaround for an unusual requirement.

 

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.

 

@WarrenBelz  Any recommendations? As of right now it pulls the same value in row one as my original lookup formula.

@ShadowTech ,

I have tested it here and got the second line (as I mentioned, it is pulling the first two and then taking the last one of them)

SecondItemChoice.png

 

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,776)