TX Rest Dynamic value Endpoint query

Hi ​@juriaan.hensbroek 

Does it behave differently if it is without a where clause?

​@Thomas Lind No it's the same (No such table error). I tried several things like:

FROM [Trigion_01_RespondentList]

FROM [Trigion_01_RespondentList].[Trigion_01_RespondentList]

FROM [REST].[Trigion_01_RespondentList]

All with and without square brackets...

Hi ​@juriaan.hensbroek 

I tried it out. I did not use a endpoint with a complicated name it is just one sentence.

I use Graph API and my Users endpoint to pull the id field.

This worked, the table in the store is also called Users, so it may be part of it.

Does the name have blank spaces instead of _ in any of the versions that exist of this table?

You are using table flattening as well, right?

Hi ​@juriaan.hensbroek do you have an update to Thomas’ questions above?

Hi Thomas,

Yes I use table flattening. The (flattened) table in which I retrieve the id's is called Trigion_01_RespondentList. I could select them from the dropdown menu. Also if I use a shorter name for the respondentlist table, without underscores, I get the same error.

Regards,

Juriaan

Hi ​@juriaan.hensbroek 

Do you still use the where clause, or is it only select id from Trigion_01_RespondentList that is your query?

Also I don’t use any new line commands it is just one row. I don’t know if it has issues with that.

It may read it as select idfrom Trigion_01_RespondentList if that is the case.

No where clause, no empty space after the query. Putting it on 1 line doesn't make a difference.

​@juriaan.hensbroek i had the same issue, but when i use the ‘From Endpoint Table’ option, it works as intended:
 

In this data source i use it to get members from Azure AD Groups

Hi ​@JogchumSiR , this works for me as well. This was my base version. In my improved version I'd like to filter upfront the userlist. For your example it could be all AD users from the Sales department. Just for example. That is why I'm trying to use the query function.

In my case the userlist is a list of all recipients we did sent an survey to. This same list has a boolean flag which shows which users responded. In my second API call I need to loop through all recipient id's which have responded to the survey.

Hi ​@juriaan.hensbroek 

In my setup I use the following endpoint path.

groups/{id}/members/microsoft.graph.user

I found that it would only return users and not other types with this added.

Perhaps that could be a workaround.

​@Thomas Lind this wouldn't work for me. The “responses” endpoint call seems correct, I only wish to filter the id's from endpoint 1 “respondentlist” upfront before making the “responses” endpoint call.

Hi!
Just wanted to add to this discussion. Does this work with pagination on the dynamic endpoint and if so, how are you supposed to set that up?

I have the “header” endpoint set up like this, with table flattening and all that, but I also have to use pagination to not drag the whole API down when reading data from it:
 

Then I use this endpoint in an Endpoint query like this:
 

Technically this works but when I synchronized the Metadata I noticed that then number of cache files that had been created was suspiciously even. The data from the first endpoint contains about 39k Id:s but only 5000 had been fetched for the dynamic query.
 

This number equals the number of results only from the first page from the Lifecare_hsl_orders endpoint as can be seen in the “limit” parameter in the topmost picture (you don’t see the pagination settings in that picture, but those are on the connector level). I thought that all the data from the “source” endpoint would be read first before it started to get processed on the second endpoint, but maybe that is not the case. How do I solve this?
 

Hi ​@juriaan.hensbroek 

We have new versions of the REST provider have you tried this for one of those and does it still behave odd when using the Endpoint Query option?

Hi ​@Thomas Lind 

I've updated to the latest version of the connector, 10.2.0.0, but it still gives the same response. 

My query is: SELECT id FROM Trigion_01_RespondentList

Response is: no such table (as in the earlier screenshot)

Hi ​@juriaan.hensbroek 

The flattening name and the name of the endpoint is the same, right?

Can you try to not use flattening and point at the main table, equal to (None)

Does that make a difference?

If not, redo the whole endpoint so it is named Trigon01RespondentList in the source.

I directly added a new endpoint without table flattening. Result is the same: no such table error (see below).

I've added a screenshot of my endpoint settings as png.

Failed to execute endpoint 'Trigion_01_ResponsesTest': SQL logic error no such table: Trigion_01_RespondentList code = Error (1), message = System.Data.SQLite.SQLiteException (0x87AF001F): SQL logic error no such table: Trigion_01_RespondentList at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, SQLiteCommand command, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) at System.Data.SQLite.SQLiteCommand.BuildNextCommand() at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at TimeXtender.RestEngine.SqliteInstance.ExecuteQuery(String query) at TimeXtender.RestEngine.RestEngine.GetDynamicValuesDictionaries(ExecutionConfigurationContainer configContainer) at TimeXtender.RestEngine.RestEngine.ExecuteDynamicValuesLoop(ExecutionConfigurationContainer configContainer) at TimeXtender.RestEngine.RestEngine.ExecuteEndpoint(ExecutionConfigurationContainer configContainer) at TimeXtender.RestEngine.RestEngine.<>c__DisplayClass30_0.<CreateExecutionTask>b__0()

Good to mention, currently you will still get an error in the portal when you try the “test connection” for your endpoint using Endpoint query. In TDI the import metadata could be successful though. So you should ignore the error you get in the portal and try if import metadata works.

We have the same error that the table cannot be found. However, we do not get the second error for the other endpoint. Could there be another reason for this error?

Sorry, the problem has been resolved. We had forgotten to add the source endpoints first.

Hi ​@vgsupdate 

It doesn’t matter what error you get, it will not work if any of the tables get one.

It is good you found the reason, as it could hit others struggling getting this to work.

We overlooked this button. As a suggestion for improvement, maybe the selection of the first source endpoint could appear directly when you select “Endpoint query.”