Show Table 1 depending on Table 2

[kevinsray]

Hi,
I am trying to figure out how to make a small filter work, but not having any luck.

I have 2 tables.

Table 1. ID and Table2. REF will have matching numbers

I want to have a gallery which shows the records from Table 1 where the SHOW column in Table 2 equals Yes.

I have tried a series of filters and addcolumns, however my skills don`t seem to meet the need.

If anyone can give some advise it would be greatly appreciated.

[eka24]

Try this:
Insert a combobox1 and set its items property to: Table2.ID
Then on your gallery put this on the items property:
Filter(Table1,ID=Combobox1.Selected.ID)

[DavesTechTips]

Hello @kevinsray ,

Please try the following formula in the Items property for the gallery.

Filter(
    AddColumns(
        Table1,
        "ShowFilterColumn",
        LookUp(
            Table2,
            ID = REF
        ).SHOW
    ),
    ShowFilterColumn = "Yes"
)

Please let me know if you don't come right.

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please like it and mark this as the answer to close the topic and make it easier to find for other people with similar questions. AND we get points for it 😉

[v-xida-msft]

Hi @kevinsray ,

Could you please share a bit more about your data source? Is it a SharePoint List, Excel table, SQL Table?

Based on the issue that you mentioned, I think the Filter function could achieve your needs.

I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the Gallery to following:

Filter(
       Table1,
       ID in Filter(Table2, SHOW = "Yes").REF
)

Best regards,

[kevinsray]

Hi Dave,

Thank you for this.

Unfortunately, it showed all records.

So I think I over simplified the issue.
Let`s change the Table 2 SHOW field to a DateTime field.
And I want to show records from Table 1, where ID and REF are the same and the Table 2 SHOW (DateTime) field is NOT NULL.

[kevinsray]

Thank you.

I tried your simple filter and it doesn`t show any results.

As I was referencing tables, I am using a SQL db.

So I think I over simplified the issue.
Let`s change the Table 2 SHOW field to a DateTime field.
And I want to show records from Table 1, where ID and REF are the same and the Table 2 SHOW (DateTime) field is NOT NULL.

[v-xida-msft]

Hi @kevinsray ,

Do you change the "SHOW" field into a Datetime field in your Table2?

I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the Gallery to following:

Filter(
       Table1,
       ID in Filter(Table2, !IsBlank(SHOW)).REF
)

or

Filter(
       AddColumns(
                  Table1,
                  "ShowValue",
                  LookUp(Table2, REF = ID, SHOW)
       ),
       !IsBlank(ShowValue)
)

Please take a try with above solution, then check if the issue is solved.

Best regards,

[DavesTechTips]

Hi @kevinsray ,

Be careful of the in operator as you might hit delegation issues on SQL. How many rows of data could potentially be in Table1 and Table2?

You can try the following after tweaking it to make provision for the null value.

Filter(
    AddColumns(
        Table1,
        "ShowFilterColumn",
        LookUp(
            Table2,
            ID = REF
        ).SHOW
    ),
    !IsBlank(ShowFilterColumn)
)

Please let me know if you don't come right.

[kevinsray]

Dave,

Thank you again.

There is currently just over 600,000 records in Table 2. Table 1 is very small, but growing at 5 or 6 records per day. Table 2 grows at 100-150 records per day.

I am baffled by the IsBlank function. I too would have thought !IsBlank would be the operator, however, when I use the! I get no results, but when I use just plain IsBlank, I get all of the records which have matching ID/REF.

This is giving me all the matching records (FINALISED_ON has both value and NULL).

Filter(
        AddColumns(
            '[dbo].[NonConformanceSub]',
            "ShowFilterColumn",
            LookUp(
                '[dbo].[RECEIPTS]',
                PONO = EXTERNAL_REF
            ).FINALISED_ON
        ),
        IsBlank(ShowFilterColumn)
    )

 FINALISED_ON is the field I am wanting to base whether the record shows or not.

If it is NULL, then don`t show the record.

Quick question, if the 2 tables were in different databases, it wouldn`t make any difference right?
PowerApps sees it as a datasource regardless of which DB it`s in, right?

[v-xida-msft · Accepted solution]

Hi @kevinsray ,

Based on the needs that you mentioned, I think the Formula I provided above could achieve your needs. Please consider take a try with the following formula:

Filter(
        '[dbo].[NonConformanceSub]',
         PONO in 
                Concat(
                         Filter('[dbo].[RECEIPTS]', FINALISED_ON <> Blank()),
                         EXTERNAL_REF & ","
                 )
)

then set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app. Please take a try with above solution, check if the issue is solved.

If you have faced Delegable Warning issue with above formula, please check and see if the following solution would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

In addition, as another solution, you could consider create a SQL View based on the PONO field and EXTERNAL_REF field against the '[dbo].[NonConformanceSub]' table and the '[dbo].[RECEIPTS]' table within your SQL Server. Then add the SQL View as data source into your app.

After that, you could apply above Filter formula to the SQL View data source rather than underlying SQL Tables. And the SQL View is not subject to Delegation Issue within PowerApps Canvas app.

More details about creating SQL View in SQL Server, please check the following article:

https://powerapps.microsoft.com/en-us/blog/using-sql-server-views-in-powerapps/

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15

Within your SQL View, it would relate your two SQL Tables based on the PONO field and EXTERNAL_REF field. Then within your canvas app, you could take a try with the following filter formula:

Filter(
        '[dbo].[TwoTableSQLView]',
        FINALISED_ON <> Blank()
)

or

Filter(
        '[dbo].[TwoTableSQLView]',
        !IsBlank(FINALISED_ON) || FINALISED_ON <> ""
)

Best regards,