Lookup Table Column Value

(0) Share

Report

Posted on by mmattar

353

Hello all,

I have 2 SQL Tables, they are connected via PK,FK concept. First table has the PK and the second table has the FK. I have a gallery based on the second table, now I want to lookup a record into the first table (connected via a ORDER_ID) for a column named STATUS and if the value is "DENIED", I want this record to populate the gallery. How may i go about doing this?

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!
@mmattar

Put this code in the Items property of your gallery.

SQL_Table_1

Place this code in the Text property of a label in your gallery.

LookUp(SQL_Table_2, PrimaryKeyColumn = ThisItem.ForeignKeyColumn, Status)

Note: of course my sample table names and columns will need to be replaced by your own.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
mmattar 353 on at

Like (0)

Report
Copy link

Link copied!

Hey thanks for the reply, may i add. I want the gallery to consist of items from the second table that only show if the status of those items looked up in table 1 are "DENIED" that way my users know that these particular items need to be edited.

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!
@mmattar

Wait... did you want to show Table2 in the gallery? My bad.

Put this code in the Items property of your gallery.

Filter( AddColumns( SQL_Table_2, "myStatus", LookUp(SQL_Table_1, PrimaryKeyColumn = ThisItem.ForeignKeyColumn, Status) ), myStatus="DENIED" )

Another idea is: you could make a SQL view to accomplish this and load it into the gallery instead.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
mmattar 353 on at

Like (0)

Report
Copy link

Link copied!

So I am getting an error at ThisItem.Fk (Name isn't valid, This identifier isnt recongized and invalid use "."). Also when you "status" for the result of the lookup, is that the same as mystatus

Filter(
AddColumns(
'[dbo].[sqlTable2]',
"mystatus",
LookUp('[dbo].[sqlTable1]',PK= ThisItem.Fk, status)
),
mystatus="DENIED"
)

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@mmattar
Please show a screenshot of the errors in the formula bar. I need to see what is being highlighted 🙂

Was this reply helpful? Yes No
mmattar 353 on at

Like (0)

Report
Copy link

Link copied!

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@mmattar
Please show the whole formula. The surrounding code may be important here too.

Was this reply helpful? Yes No
mmattar 353 on at

Like (0)

Report
Copy link

Link copied!

yup no problem

Was this reply helpful? Yes No
Verified answer

mdevaney 29,991 Moderator on at

Like (1)

Report
Copy link

Link copied!
@mmattar
OK, I see what was wrong with my code. ThisItem was an invalid reference. Sometimes its tough figure these things out when we are not sitting beside one another.

Filter( AddColumns( SQL_Table_2, "myStatus", LookUp(SQL_Table_1, PrimaryKeyColumn = SQL_Table_2[@ForeignKeyColumn], Status) ), myStatus="DENIED" )

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No