Unanswered

Filter Gallery with a list from a filter on a different datasource

(0) Share

Report

Posted on by timridolfi

I have a power app built which on the initial screen lists all Inventory Items stored in a SQL table. I have a number of fields that can be selected or entered to feed a filter the gallery items.

One of filter values is a text field where you can enter the Vendor Stock number which is FK on the Vendor Item table. Currently the filter is setup like this:

Gallery

Sort(
Filter(
'CPM.ItemMaster',
((txtSearchName_home.Text in ItemDescription) Or IsBlank(txtSearchName_home.Text))
And (GLCode = ddSearchGLCode_home.Selected.LookupCode Or IsBlank(ddSearchGLCode_home.Selected.LookupCode))
And (ddSearchBrand_home.Selected.LookupDescription = "Select..." Or Brand = ddSearchBrand_home.Selected.LookupCode)
And (ItemStatus = "A" Or Not(chkSearchActive_home.Value = true))
And (ItemCode = Value(txtSearchItemcode_home.Text) Or IsBlank(txtSearchItemcode_home.Text))
And (ItemMasterKey = Value(hiddenSearchVendorCode_home.Text) Or IsBlank(txtSearchVendorItemCode_home.Text))
),
ItemCode,
SortOrder.Ascending
)

hiddenSearchVendorCode_home.Text =

If (
IsBlank(LookUp('CPM.VendorItemMaster',VendorItemCode = txtSearchVendorItemCode_home.Text).ItemMasterKey),
0,
LookUp('CPM.VendorItemMaster',VendorItemCode = txtSearchVendorItemCode_home.Text).ItemMasterKey)

Which works but of course, using a Lookup on the hiddenSearchVendorCode_home.Text, I only get the first record.

How can I use a Filter on the CPM.VendorItemMaster table to then filter the CPM.ItemMaster table in the gallery?

Categories:

Building Power Apps

All responses (2)

Answers (0)

Sort by

Datanerd 103 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @timridolfi ,

To filter the 'CPM.ItemMaster' gallery based on the 'CPM.VendorItemMaster' table using the Vendor Stock number, you can use the Filter function with a condition that checks for the presence of the Vendor Stock number in the 'CPM.VendorItemMaster' table.

Please try this,

Gallery( Sort( Filter( 'CPM.ItemMaster', ( (txtSearchName_home.Text in ItemDescription) Or IsBlank(txtSearchName_home.Text) And (GLCode = ddSearchGLCode_home.Selected.LookupCode Or IsBlank(ddSearchGLCode_home.Selected.LookupCode)) And (ddSearchBrand_home.Selected.LookupDescription = "Select..." Or Brand = ddSearchBrand_home.Selected.LookupCode) And (ItemStatus = "A" Or Not(chkSearchActive_home.Value = true)) And (ItemCode = Value(txtSearchItemcode_home.Text) Or IsBlank(txtSearchItemcode_home.Text)) And (ItemMasterKey in Filter('CPM.VendorItemMaster', VendorItemCode = txtSearchVendorItemCode_home.Text).ItemMasterKey Or IsBlank(txtSearchVendorItemCode_home.Text)) ), ItemCode, SortOrder.Ascending ) ) )

Let me know if it works.
timridolfi 11 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks so much for your response. I have tried your suggestion, but unfortunately, I still only get the first record from the CPM.VendorItemMaster table.

in SQL the query is
Select * From DataIntegrationDev.CPM.ItemMaster Where ItemMasterKey IN (Select ItemMasterKey From DataIntegrationDev.CPM.VendorItemMaster Where VendorItemCode='3226')