Unanswered

Lookup specific line in SQL table

(0) Share

Report

Posted on by ShadowTech

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Sort by

WarrenBelz 156,384 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
ShadowTech on at

Like
a
(0)

Report
Copy link

Link copied!
@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

Was this reply helpful? Yes No
WarrenBelz 156,384 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
ShadowTech on at

Like
a
(0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
WarrenBelz 156,384 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
ShadowTech on at

Like
a
(0)

Report
Copy link

Link copied!
@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.

Was this reply helpful? Yes No
WarrenBelz 156,384 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
ShadowTech on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
WarrenBelz 156,384 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@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)

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.

Was this reply helpful? Yes No
ShadowTech on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz But the values in this column are based on a value that is manually input from a user from another table.

Basically a user inputs a number in filed "A". Field "B" (Item column) returns records based on that number.
ReturnAuthNumb=DataCardValue10.Text is a different DataCard referencing a different SQL source table, not '[dbo].[tblsoReturnAuthLine]'.

Was this reply helpful? Yes No