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

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 III
Super User III

@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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (15,676)