Search in multiple SQL tables

[Anonymous]

Hi,

I have create a gallery that shows data from different SQL tables.

Some fields are in this main table (price) that are displayed in the gallery, and some fields are lookup to other SQL-tables.

The field "Kundenr" (customer number) is in the main price table. But the customer name and "faktura kundenr" (invoice customer number) is a lookup to a customer table.

How can I create a search box to search in "customer name" (kundenavn on the picture) for example ?

[timl]

Hi @Anonymous 

The way to do this is to create a view in SQL Server that joins the price and customer tables. You can then specify a call to the Search function in the Items property of your gallery.

Search(YourSQLView, txtSearchBox.Text, "CustomerColumn")

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-filter-lookup

[Anonymous]

How would I create a SQL view? Is this done in Power apps?

The SQL tables that I’m referring to are inside an on-premise ERP-system and connects to Power platform through a gateway.

We have a few reports in Power BI based from this SQL-data and there I know that one can create queries.

[timl]

Hi @Anonymous 

You would create the view in SQL Server, rather than from Power Apps. I appreciate this may be difficult if this refers to an ERP system that someone else manages.

Without using a view, this requirement is difficult to accomplish without receiving delegation issues that may prevent you from accurately performing the search.

[Anonymous]

Could I create a collection in PowerApps that acts as a temporary table depending on what the user search for? Or would that be to slow? 

[timl]

@Anonymous 

The speed would depend on the amount of records in the tables. For example, if there were tens of thousands of records, the performance would be poor if you were to preload this data into local collections.

How are you currently displaying the customer name in your gallery? Have you added a label in the gallery that calls LookUp, or have you added the call to LookUp in the Items property of the gallery?

[Anonymous]

Yes, in the gallery I have added a label for the customer name that looksup customername from another table (actor) based on customer number.

The main table that I display which is called PrDcMat is a price discount matrix where you input customer number, product number and price discount.

In this price discount table Im trying to create some user friendly search funtions. Sometimes a user search after customer name, sometimes product number or customer number.

[timl]

@Anonymous 

Would it be acceptable for the user to be able to filter the gallery view by a single user, using a combo box control?

This would be easier to implement, compared to the ability to filter the gallery by multiple customers using a partial match that the user types into a text input control.

[Anonymous]

So to explain what Im trying to create here is to find a list of order lines based on multiple selected customer numbers AND multiple products.

A filter that:

Table source: Order lines
Criteria: one or many customer numbers AND one or many product numbers

So its OK to list order lines if I filter on ONE customer number AND ONE product, but when its many Im not sure how to use it.

I tried creating two collections:
SelectedProductCol
SelectedCustomerCol

In the filter it would be something like:
Filter(OrderLine, CustNo in SelectedCustomerCol.selCustNr).