cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sincilbanks
Level: Powered On

An error occurred on the server. Server Response: Not a valid connector Error response

So I've been banging my head against this for 3 days now...

 

My app in development used to work fine until recently. Now when I try and AddColumns to a table or collection it fails on the filter section of the code with the error message in the title.

 

I used to have a direct connection to our on-premise SQL server but I've installed the Gateway connector and configured it and all connection tests pass on the server with the gateway installed on it, not sure if this is the issue but the data is populated in the app fine when I run it, all the tables are present in the app and refresh fine.

 

I have 3 tables essentially that are relevant: Vehicles, Units and Assignment.  Units are installed on Vehicles and the connection is via the Assignment table, this has the PK of the Vehicle and Unit as a FK and each row in Assignment has it's own unique PK generated at creation. If I try and create a collection which AddColumns I get this error. It parses it ok when I type the code but at runtime in the background it creates this error and flags it up in the app checker.

 

I've tried it in several places in my app on a particular screen and I always get the same above error.

 

What is interesting if I do add the AddColumns in the "Datasource" attribute of the form and then remove the code to simply replace it with simply the original code (which is just the Assignment table name) the error persists despite showing the data and another form on the screen with the exact same data source remains unaffected.

 

I've attached a screenie of my code and screen layout, basically I want to populate the Vehicle Assignment History with all the units it's had installed and include the Unit serial number (hence the addcolumns to add the Unit serial number) and also populate the Unit Assignment History with all the Vehicles this Unit has been installed on (and therefore add the vehicle fleet number as well)

 

I've removed the connector from the app, deleted the connector, recreated it and reattached it to the app but that doesn't appear to have changed anything...

 

Any help welcome!!!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Level 10

Re: An error occurred on the server. Server Response: Not a valid connector Error response

Hi @Sincilbanks ,

 

I have made a test, and find the Filter formula you used in the code is incorrect.  Something to confrim:

1. What does UnitID = UnitID mean? This cause the error I believe. If the second UnitID is from Assignment, you should use disambiguate operator: UnitID='[dbo].[Assignment]'[@UnitID]

2. If you want to filter out ECUNumber of unit, you should use Lookup function: LookUp(Unit, logical_test, ECNumber), which return a value. However the filter function you use will return a table. For more information: Filter, LookUp function 

So you could try 

ClearCollect(AssignmentsPlus,AddColumns('[dbo].[Assignment]',"ECNum",LookUp('[dbo].[Unit]',UnitID='[dbo].[Assignment]'[@UnitID],ID)))

If this doesn't make sense, please post back the meaning of 'UnitID = UnitID' statement ?

Best regards,

Sik

 

View solution in original post

2 REPLIES 2
v-siky-msft
Level 10

Re: An error occurred on the server. Server Response: Not a valid connector Error response

Hi @Sincilbanks ,

 

I have made a test, and find the Filter formula you used in the code is incorrect.  Something to confrim:

1. What does UnitID = UnitID mean? This cause the error I believe. If the second UnitID is from Assignment, you should use disambiguate operator: UnitID='[dbo].[Assignment]'[@UnitID]

2. If you want to filter out ECUNumber of unit, you should use Lookup function: LookUp(Unit, logical_test, ECNumber), which return a value. However the filter function you use will return a table. For more information: Filter, LookUp function 

So you could try 

ClearCollect(AssignmentsPlus,AddColumns('[dbo].[Assignment]',"ECNum",LookUp('[dbo].[Unit]',UnitID='[dbo].[Assignment]'[@UnitID],ID)))

If this doesn't make sense, please post back the meaning of 'UnitID = UnitID' statement ?

Best regards,

Sik

 

View solution in original post

Sincilbanks
Level: Powered On

Re: An error occurred on the server. Server Response: Not a valid connector Error response

Thank you so much!! That works perfectly, I think what fooled me was my statement was being parsed by the error checker but failing at runtime, I usually rely on the error checker to catch all my mistakes!

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,092)