Hi, I have developed an app that uses stock data on a sql server however on the code drop down and on all the data tables it has the following error message 'the requested operation is invalid. server response: the key didnt match any rows in the table. inner exception: the key didnt match any rows in the table. ClientRequestID b3a0c7b5-cdfd-4f87-ac40-f964cbbb4d93'
Im using this expression to filter the data tables -
Filter('[dbo].[SQL SERVER TABLE]', Warehouse = WarehouseDropDown.Selected.Value, Code = CodeDropDown.Selected.Value)
And then for the drop downs which filter the data tables I use the expression -
Distinct(Filter('SQL SERVER TABLE', Code = CodeDropDown.Selected.Value),Name)
In my code drop down i am using the expression:
Search('SQL SERVER TABLE', If(IsBlank(SearchCode.Text), "",SearchCode.Text), "Code")
This should work but it doesn't, any help would be greatly appreciated.
First your approach is not the best. Giving you have a SQL server as a datasource, in your fact table you should have an CodeID and WarehouseID column as a reference to the foreign tables, right.
Also you should create a view on the sql side for the codes and warehouse and use these in your connections and controls, so at the sql side you clean up the data you want to return. The formula's then becomes somewhat like this:
ShowColumns( Filter('SQL SERVER VIEW' , IsBlank(SearchCode.Text) || ColumnToFilter = SearchCode.Text ) ,CodeName ,CodeID ) ShowColumns( Filter('SQL SERVER VIEW' , IsBlank(CodeDropDown.Selected.CodeID) || CodeID = CodeDropDown.Selected.CodeID) ) ,WareHouseName ,WareHouseID ) Filter('[dbo].[SQL SERVER TABLE]' , WarehouseID = WarehouseDropDown.Selected.WhareHouseID , CodeID = CodeDropDown.Selected.CodeID )
Also I would advise you to store the lookup tables in collections at start off the app or when you go to the screen where they are needed. This will limit the calls to your sql source and increase the speed off your app.
Optional you can use Flow as an interface and pull the lookup list to the collection through a call to a stored procedure. Want to know how to do this? Look at this video's I made on this topic: PowerApps-Flow-SQL
Hope this helps.
Hi Paul (@KroonOfficeSol), Thanks for the response.
Please can you explain what I need to do on the SQL side in a bit more details as I am new to it and am not sure what needs to be done. I have added the warehouse ID and itemID(CodeID) to each list however this still hasn't worked in my app its just saying 'Invalid argument type'
These are the expressions I'm using for my homepage
Warehouse Drop Down - ShowColumns(Filter('[SQL TABLE]', IsBlank(CodeDropDown.Selected.CodeID) || CodeID = CodeDropDown.Selected.CodeID),WarehouseName,WarehouseID)
Code Drop Down - ShowColumns(Filter('[SQL TABLE]', IsBlank(SearchCode.Text) || CodeID = CodeDropDown.Selected.CodeID)
Data Table - Filter('[SQL TABLE]', WarehouseID=WarehouseDropDown.Selected.WarehouseID, CodeID=CodeDropDown.Selected.CodeID)
If you are entirely new at SQL, maybe you should hire me give some advise on how to build logic structures ;-)
From your prespective you will have three tables in your Datasource. Two LookUp tables and One fact table. Both lookup tables contains minimal the following fields:
Create Table WhareHouses( ID Primary Key Identity ,[WhareHouseNo] varchar(50) -- this is a key field for the user to identity the record. This could also be an int field if you only use number as a key ,[WhareHouseName] varchar(255) -- this is a more descriptive name ,[Active] bit CONSTRAINTS YourTable_Active_DF DEFAULT 1 );
In your fact table you have a field that connect the fact table with the lookup table, so like
Create Table DataTable( ID Primary Key Identity ,[WhareHouseID] Int null -- connection WhareHouses Table ,[ItemID] varchar(255) -- connection Item Table ,.... ,.... ,.... ,....other fields.... ,[Active] bit CONSTRAINTS DataTable_Active_DF DEFAULT 1 -- or a [Status] int field instead || complementary )
You create views on the sql side to concat() fields for the lookup tables, for example a WhareHouseFullName field.
Hope this clears things a little.