Filter SQL Based on Collection Values

[seadude]

Scenario:

• 3 SQL Tables
  • siteTable
  • dateTable
  • assetTable
• User will not create SQL Views, I need to relate the siteTable and assetTable in the app
• These two tables are joined by the dateTable

Steps:

1. Create `varSiteID` to designate which "site" the user selected from a dropdown
2. Grab the last 13 (year + 1) "reading dates" from the readingTable (thanks @Drrickryp for the FirstN tip there!)

   1. //Collect the last 13 READING DATES for selected Site
      ClearCollect(
          colRecentDates,
          Filter(
              FirstN(Sort('[dbo].[dateTable]', readingDate,Descending),13),
              siteId = varSiteID
          )
      );

3. Now I need to grab all of the assets for this particular SITE from the assetTable. Unfortunately, I'm hitting the Delgation issue and looking for some advice around it.

   1. //Collect the last all assets for the selected site
      ClearCollect(
          colAssets,
              Filter('[dbo].[assetTable]', dateId exactin colRecentDates.dateId)
      );

Thoughts on this one?

[mr-dang]

Hi @seadude,

In and Exactin are delegable for SQL only when checking if a string is in another string. Today it is not delegable when checking if a record has membership against a table.

If you only have 13 records, you could use ForAll:

//Collect the last all assets for the selected site
ForAll(RenameColumns(colRecentDates,"dateid","dateid_"),
    Collect(
        colAssets,
            Filter('[dbo].[assetTable]', dateId=dateId_)
    )
);

This means, "For each of the recent dates, collect the asset that matches each date until they're all collected."

When I use SQL Views, I also connect the original SQL tables for writing back data. You could keep them in for the relationships and use the View for all the Inner Joins you need.

[v-xida-msft]

Hi @seadude,

Could you please show more details about the Delegation issue within your app?

The exactin operator is not a Filter and LookUp delegable predicate in SQL table data source. More details about the supported Filter and LookUp delegable predicates in SQL table data source, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list#filter-and-lookup-deleg...

As an alternative solution, you could consider take a try to save your assetTable SQL table records in to a Collection firstly, then use it as a data source within your second formula that you provided. Please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following formula:

ClearCollection(assetTableCollection, '[dbo].[assetTable]')

Modify your second formula as below:

//Collect the last all assets for the selected site
ClearCollect(
    colAssets,
    Filter(assetTableCollection, dateId exactin colRecentDates.dateId)
)

then check if the issue is solved.

In addition, I also agree with @mr-dang's thought almost. More details about the Patch function, please check the following article:

Patch function

Best regards,

Kris

[seadude]

Awesome advice, thank you @v-xida-msft. The assetTable will be very large and continuously growing so I don't think collecting the entire table is workable in this case. I'm working with @mr-dang's suggestion right now and it seems to be working as intended (though I'm not 100% certain yet).

I'm also looking at building a "bridge table" between the siteTable and assetTable with something like:

ClearCollect(
    colSiteAssets,
    AddColumns(
        colRecentDates,
        "readingDate",
        LookUp(
            colRecentDates,
            dateId = dateId,
            readingDate
        )),
    AddColumns(
        colRecentDates,
        "siteID",
        LookUp(
            colRecentDates,
            siteId = varSiteID,
            siteId
        )
    )
);

This is almost like building a SQL View using PowerApps. But I can't quite get this one to work. I'll keep pecking away at it and post back the results.

Thanks again!

[seadude]

@mr-dang, I'm confused by the...

RenameColumns(colRecentDates,"dateid","dateid_")

It doesn't appear to actually rename the columns in either collection! Is this a trick to get the ForAll to parse through the colRecentDates collection? Or is it a best practice to not compare columns with the same names("dateid = dateid")?

Thanks!

[v-xida-msft]

Hi @seadude,

How many records stored in your assetsTable SQL Table?

Actually, the amount of records you could store in a Collection is based on your computer's configuration. Someone user has made a test on this already, he could store about 4000 records within a Collection.

I found that there is a column called readingDate within your colRecentDates collection already, why do you add a new readingDate column into it using AddColumns function? I think it is not necessary.

In addition, the RenameColumns function would return a new table with the transform applied, the original table isn't modified. In other words, the RenameColumns(colRecentDates,"dateid","dateid_") formula would return a new table which has changed the dateid column into the dateid_ column, the original colRecentDates collection would not be modified.

More details about the RenameColumns function, please check the following article:

RenameColumns function

Within @mr-dang's case, the functionality of the RenameColumns function is similar to Disambiguation operator (@). Please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators#disambiguation-oper...
 

Best regards,

Kris

[seadude]

Hello @v-xida-msft,

RE: Number of records stored in assetsTable:

• There are currently ~2700 records in the assetTable. Assets will be added daily by multiple people once the app goes live.

RE: readingDate column and AddColumns function:

• This is my first foray into using AddColumns in an app. I was actually trying to add readingDate and siteID to the assetTable. Looks like I don't quite have the syntax down :).
• I'll study some more.

RE: RenameColumns:

• Yeah! Whew! I'll have to really study those links on RenameColumns and @disambiguation to understand how they apply to this case.
• Takes low-code to another level there!

Thanks again! You are always VERY helpful and a huge asset to the community.

[eopara]

This helps replace the delegation warning presented when using the "IN" or "EXACTIN" operator.

Thanks @mr-dang 

[Anonymous]

Hi @seadude I know it's been awhile but how are you making out with filtering the SQL?

@Anonymous