PowerApps: Filter SQL datarows by non existence in Sharepoint List

Question

Filter(
  ClearCollect(
    tblSQL,
    ShowColumns(
      WRH_Artikelmaster,
      "Materialnummer",
      "BME",
      "Einheit_KME",
      "Status",
      "Materialart",
      "Verpackung",
      "Materialtext"
    )
  ),
  Not Materialnummer in Materialstammdaten.MATNR
)

Hi, for a PowerApps application I need to have a gallery displaying all those entries of a SQL table which do NOT have counterparts in the corresponding SharePoint list. I tried to do this with the 'in' and the 'not' opertor. 'Materialnummer' is a decimal from SQL and 'Materialstammdaten.MATNR' a number from the sharepoint list. Do I need to convert these fields into text strings for this to work properly?

Answer 1

If the tables are large or ever-growing, you likely want to rethink a different solution. These can be rather resource intensive types of Filters.

This works:

• Note the Not() syntax.

//Get the Sharepoint list
ClearCollect(colMaterialnummer, Materialnummer);

//Get the SQL table
ClearCollect(
    colTblSQL,
    ShowColumns(
      WRH_Artikelmaster,
      "Materialnummer",
      "BME",
      "Einheit_KME",
      "Status",
      "Materialart",
      "Verpackung",
      "Materialtext"
    )
);

//Create a collection of the filtered results
ClearCollect(colFilterResults,
  Filter(colTblSQL, 
    Not(Materialnummer in colMaterialnummer.MATNR)
  )
)

Illustration:

• This is with an actual Sharepoint list with MATNR Number-type column AND an Azure SQL table with the Materialnummer column as a DECIMAL(4,1).