Skip to main content
To homepage To homepage

RSD File Customization

Ask questions about customizing RSD files to get data from REST APIs using TimeXtender

  • 25 Topics
  • 187 Replies
25 Topics
J
Contributor
jarleContributor
asked in RSD File Customization
rsd file definition for adding tables to Cdata QuickBooks Online data source

The Cdata data source for QuickBooks Online doesn’t have all the endpoints in the API available. For instance TaxPayments is missing. (https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/taxpayment)It is possible to add rsd-files to the file folder in order to get additional tables when synchronizing data. However, there is no sample rsd-file available, and no obvious way to generate schema file. I have tried to copy a rsd file from a REST connector, but then I get the error message when trying to preview: “The operation ‘jsonproviderGet’ could not be found”

9211
Thomas Lind
Community Manager
R
Participant
Remy Kamphuis | VictaParticipant
asked in RSD File Customization
RSD file referencing field from ParentTablePath

Hi all,I have an API that uses nested XML to deliver the data. I have used a relational model to be able to retrieve this data in separate tables. Based on key fields that are created I am able to join the tables back together. All works fine, however, I want to reference a (different) field from my parenttable in a nested table. Currently, the output adds a field _id in my parenttable PerformanceInfoRow to be able to reference the underlying data back to the specific period. However, this _id contains an integer. Daily I will get three rows of data and daily I will thus get back the _ids 1, 2 and 3. So, in the end this will not be a unique key when retrieving more days of data. Therefore, I want to specify the Period that also is available in the parenttable, which is unique since it will not only show the date but also whether it is morning, afternoon or evening. This will be unique, but unfortunately I am not able to add this field to the underlying tables. I have tried to adjust my

899
Christian Hauggaard
Community Manager
J
Contributor
jarleContributor
asked in RSD File Customization
Nested rsd file with paging not working

I have issue with getting details from forms based on a list of formsList of forms: https://api.goformz.com/v2/formzForm details: https://api.goformz.com/v2/formz/formIdBoth nested query and pagination works seperately, but I struggle to get it work with both of them. I have tried the solution in the linked case below, but I get stuck on the same place as the user in that case:The query table is running for approximately correct time (which indicates it is reading the correct amount of data), but no data is going into the SQL-table.The query is this simple: SELECT fields,formId FROM REST.Formz_DataPerFormWHERE Nested_Id IN (SELECT formId FROM REST.formz_WithDateFilter)Is it possible to get this working?  

10311
J
Contributor
M
Starter
mirjam.bleuminkStarter
asked in RSD File Customization
Is it possible to use POST for a REST API?

I am using a REST API (CData source) to get source data. Since I need to include a CSR_NONCE value in the header (unique key value), the REST api is set up as a POST. Is it possible to specify that you want to do a POST rather than GET in a CData source?  

1618
Thomas Lind
Community Manager
G
Contributor
GardarContributor
asked in RSD File Customization
Pagination using "hasMore" response

I am dealing with an API that let’s me query its database. With LIMIT and OFFSET I want to fetch all the rows dynamically using an RSD file.Here’s the json response:{ "entities": [ { "Portfolio": { "id": "9999999999999999", "Name": "Test9" } }, { "Portfolio": { "id": "1111111111111111", "Name": "Test1" } } ], "paging": { "from": 2, "hasMore": true, "limit": 2 }}And here is my RSD file. I haven’t gotten it to work with pagination and I just get the errorThe attribute 'hasMore' does not exist.<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. --> <api:info title="entities" desc="Generated schema file_" xmlns:other="http://apiscript_com/ns?v1"> /> <attr

1669
Thomas Lind
Community Manager
K
Visitor
kenneth.bergVisitor
asked in RSD File Customization
REST API: Dynamic Dates, inputs and Data Selection Rules

HiThis is a somewhat similar question asked by Sturla SvendsenThe API I am using only returns data when fromDate and toDate are populated in the URL params.When setting up the Data Source (CData) I won't be able to fetch anything without these dates.I have tried creating the datasource with hard coded dates in the Edit-window in DH and altering the RSD-file the way that Thomas Lind informed Sturla about in the post linked above.However, when I delete the URL params in the Edit-window, and rely solely on the already created RSD-file, the connection won't work (401 as the url is invalid).When I try to add data selction rules and execute, it states the the input fields (that are written in the api:info-secion of the RSD-file) are unknown.I really could use an complete answer as to how to connect to an API with dynamic dates in the URL params.The guides/documentation found on this webpage is fragmented and doesn't work with the use case above.Thank you Kenneth

2889
E
Explorer
J
Contributor
jarleContributor
asked in RSD File Customization
Error when running nested query in rsd file

I am trying to get data from Business Central dimensionSetLines for generalLedgerEntry using a cdata REST connector.I have copied the functionality for nested API call from https://support.timextender.com/data%2Dsources%2D112/advanced%2Drsd%2Dfile%2Doptions%2D884?tid=884#Using+nested+calls+to+get+data+from+multiple+pages.However, I get error message: [500] Could not execute the specified command: Formatter [ file_id ] failed in the evaluation of [fileout.file_id]. The error was:The value of the attribute could not be accessed: The attribute does not exist.Can you see anything wrong about the file?timeXtender version is 20.10.26.64 <api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->  <api:info title="GetAllStops" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">    <!-- You can modify the nam

1354
J
Contributor
sierd.zuiderveld
Contributor
sierd.zuiderveldContributor
asked in RSD File Customization
SOAP API setup

Hi, I have some trouble setting up a SOAP API within the RSD file. The envelope returns data as follows : <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"> <s:Body> <getCertificateListByCompanyResponse xmlns="http://tempuri.org/"> <getCertificateListByCompanyResult xmlns:a="http://schemas.datacontract.org/2004/07/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"> <a:ObtainedCertificate> <a:CategoryName>text1</a:CategoryName> <a:CertificateCode>12345</a:CertificateCode> <a:CertificateID>12345</a:CertificateID> <a:CertificateName>text2</a:CertificateName> <a:CertificateNumber/> <a:DateAssigned>12345</a:DateAssigned> <a:DateExpire/> <a:DateObtained>12345</a:DateObtained> <a:Expired>false</a:Expired&g

3406
T
Participant
Paul McLeod Bespoke Analytics
TimeXtender Xpert
Paul McLeod Bespoke AnalyticsTimeXtender Xpert
asked in RSD File Customization
Looping REST API calls in one RSD file

Hi I have a REST API that uses an item index as part of the URI, in the format https://<URL>/LocationPerilGroup/{PerilGroupID}.So an example call might be https://<URL>/LocationPerilGroup/12.This is a small lookup table and I was hoping that i could use an enum to repeatedly call the API while iterating through the list (40 items) to return the table to the ODX.My enum does iterate, but I only get output from the first record.Can this be done? Is there something I have missed? Please help.RSD file content below: <api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->  <api:info title="locationPerilSets" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">    <!-- You can modify the name, type, and column size here. -->    <attr name="LocationPerilSetsRecord_id"    xs:type="integer

3126
Paul McLeod Bespoke Analytics
TimeXtender Xpert
J
Contributor
jacqueline.hofmeijerContributor
asked in RSD File Customization
Using a nested REST API in combination with paging

Hi, I am having some trouble in using the nested REST API in combination with paging. The extensive description on how to use the nested call in the RSD-file was very helpfull. The only thing I cannot get working is the paging at the first level.Two different cases, but I think the same underlying issue. In the first case the pageoffset is used as a parameter, in the other one it uses a pagetoken (the nextmarker in a blobstorage). In the output, I do see that paging is applied, but the problem is that it starts at the first page again. I seems that when using the input- and output files the correct information about the next page is not being picked up by the api-call.I have tried the call without nesting (so only on the first level) and without qualifiying the parameters and then the paging is working correctly, but when I switch to in and out again, I get false results.Does anybody recognize this isse and solved it somehow? Kind regards,Jacqueline Hofmeijer

111517
Christian Hauggaard
Community Manager
J
Visitor
Johnny YVisitor
asked in RSD File Customization
Using a nested REST API in combination with pagination (using the Graph API)

Hello everyone, I hope someone can help me with this.I am working on getting Azure AD Groups via the Graph API and then retrieving the members through a nested API call.I saw a post concerning a nested REST API call together with pagination in which one of the commenters (Gijs) had the exact same API which I am also using (commented by Gijs):https://support.timextender.com/rsd-file-customization-96/using-a-nested-rest-api-in-combination-with-paging-858However, the suggestion didn't work for my use-case as it only ‘expanded’ the nested values, but didn't paginate. I only got the members of the first 100 groups.I used the RSD-file from the commenter Gijs with the addition of two points:two additional rows to enable pagination; [memberout.userPrincipalName| allownull()] instead of [memberout.userPrincipalName] as I was getting a [500] error with this specific attribute.I've made them bold in the code down below.Would someone be able to give me some pointers to what I'm missing in the RSD

3712
Christian Hauggaard
Community Manager
erik.van.mastrigt
TimeXtender Xpert
erik.van.mastrigtTimeXtender Xpert
asked in RSD File Customization
Loading multiple Excel files with 1 data connection using RSD

Hi community,I have multiple Excel files on a sFTP location, all having the same structure. I would like to load these files using 1 data connection and create a RSD file for each file/sheet that needs to be loaded.When generating the RSD via TX, it does not contain the api:set attr=”URI” property. An example: On line 2 (api:info) there is a parameter other:uri=”[...]”, but modifying this does not change anything.Manually adding the api:set attr=”URI” does not change anything eitherThe data will always be loaded from the Excel file that you enter in the UI: Emptying the URI field results in an error that either URI or Excel File parameter should be set: Is there a way to load multiple Excel files/sheets with a single data connection using RSD files, same as we do with eg. REST API endpoints and CSV files? It will save us from creating ca. 15 separate connections :)Best,ErikTX: 20.10.43CData Provider for Microsoft Excel 2023, v23.0.8565.0

3217
erik.van.mastrigt
TimeXtender Xpert
mnoordam
Contributor
mnoordamContributor
asked in RSD File Customization
REST with POST method reading AppSheet Database

Yes, I’ve read the other posts about this topic But I'm rather unlucky getting it to work. Each time I press the ‘Test Connection' it fires a GET method at the URI, which results in the infamous ‘The requested resource does not support http method 'GET'’ message. RSD file:==================================<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">    <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->    <api:info title="Table1" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">        <!-- You can modify the name, type, and column size here. -->        <attr name="_RowNumber" xs:type="string" readonly="false" other:xPath="/json/_RowNumber"/>        <attr name="Assignee" xs:type="string" readonly="false" other:xPath="/json/Assignee"/>        <attr name="Date" xs:type="date" readonly="false" other:xPath="/json/Date"/&g

60016
Thomas Lind
Community Manager
T
Starter
timonStarter
asked in RSD File Customization
Pagination and recursive query

Hi,I have problem with pagination and recursive queryI have rest api endpoint:https://api.procountor.com/api/invoiceswhich returns invoice headers as"id": 6591273,"partnerId": 1208831,"type": "PURCHASE_INVOICE","status": "PAID","invoiceNumber": 16...etc.pagination works fine, I get every invoice headers (thousands) when pagination is set like this in RSD-file:<api:set attr="EnablePaging" value="true"/><api:set attr="pagenumberparam" value="page" /><api:set attr="pagesizeparam" value="size" /><api:set attr="pagesize" value="100" />But the actual details of invoice comes from endpoint like this:https://api.procountor.com/api/invoices/6591273So, I have to iterate thru one by one every invoice id that I get from https://api.procountor.com/api/invoices I have followed instructions from herehttps://legacysupport.timextender.com/hc/en-us/articles/360052383191-Creating-and-using-RSD-files-for-CData-providers#one-nested-calland I can query invoices successfully, but the

5626
Thomas Lind
Community Manager
A
Starter
a.chikshinStarter
asked in RSD File Customization
Nested fields filtering in RSD file.

Hi,I have a problem with data catching from JSON file. The file looks like this:And I would like to get values from data field but only for en_US locale.I was trying to add filter in RSD file like below but it did not help:Can that type of filtering be used there?Greets, Aleksei

2397
Thomas Lind
Community Manager
R
Starter
RutgerNijlandStarter
asked in RSD File Customization
Pagination azure AD graph API with RSD

Hello,I am trying to extract data from azure AD. I am at the point where I can get data using the microsoft Graph API, and have it loaded into Timextender.The issue I am facing has to do with pagination. Microsoft graph API returns an url containing information of the next 'page’ of data. According to documentation I should adjust my RSD file as follows:  <api:set attr="DataModel" value="DOCUMENT" />  <api:set attr="URI" value="https://graph.microsoft.com/v1.0/groups" />     <api:set attr="EnablePaging" value="true" />  <api:set attr="pageurlpath" value="/json/@odata.nextLink" />  <api:set attr="RepeatElement" value="/json/value/" />This does not however loop over the pages, but still only gives me a top 100 of groups. I have a feeling maybe I need to escape either the @ or the . in the pageurlpath. Or am I missing something more obvious? Kind regards,Rutger

4588
R
Starter
rogier.helmus
Problem Solver
rogier.helmusProblem Solver
asked in RSD File Customization
Passing multiple custom headers in a REST API RSD file

Hi all,I need to pass multiple custom headers in a RSD file:Host: xxxxx.cloud.xxxxx.com Authorization: Bearer dfdflejlflefdjeljfeoj Content-Length: 26Is this the correct way to do this? <api:set attr="Header:Name#1" value="Authorization" /> <api:set attr="Header:Value#1" value="Bearer dfdflejlflefdjeljfeoj" /> <api:set attr="Header:Name#2" value="Host" /> <api:set attr="Header:Value#2" value="xxxxx.cloud.xxxxx.com" /> <api:set attr="Header:Name#3" value="Content-Length" /> <api:set attr="Header:Value#3" value="26" />Greets, Rogier

2523
Thomas Lind
Community Manager
R
Contributor
r.depagterContributor
asked in RSD File Customization
API POST for a token to use in another API GET request

Hi! I have asked a similar question like this before. I am trying to request an API token and use that token inside an API get. I do this because a token is only valid for 23 hours. I need to give the POST a few paramters: A client ID, Client Secret, grant type and a customer ID to identify who I am.The API documentation says it uses OAUTH2.0 but I have not gotten it to work. I right now have the following code which returns the warning: The view "REST"."json" has no columns.The view is ignoredBelow my code: <api:script xmlns:api=http://apiscript.com/ns?v1 xmlns:xs=http://www.w3.org/2001/XMLSchema>  <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->  <api:info title="API" desc="Generated schema file." xmlns:other=http://apiscript.com/ns?v1>    <!-- You can modify the name, type, and column size here. -->    <attr name="categoryName"   xs:type="string"    readonly="false"      other:xPath="/json/catego

37912
Thomas Lind
Community Manager
D
Contributor
dan.beggsContributor
asked in RSD File Customization
Pass TimeXtender variable value to CData REST provider rsd file

We have a REST API that we are calling and setting up pagination on, so we are using an rsd file.  We need to pass a calculated date (milliseconds since 1970) to the API call.  I think we might need to reference a stored date of our last data extraction and then calculate the proper date to pass to the REST API.  I am thinking we might do that in TX, but I am unclear how we can then pass a TX variable value in to the rsd file so we can put it into the query string of the API call.  Any pointers that can be provided would be appreciated. Thanks!

2501
Thomas Lind
Community Manager
T
Starter
thyeStarter
asked in RSD File Customization
REST API request with paramaters (list) based on previous request

Although I'm following the steps explained on this page (https://legacysupport.timextender.com/hc/en-us/articles/360034695651-Connect-to-a-REST-API), I can't figure out what's going wrong.Situation: I have a REST API, with two endpoints: one for fetching all locations (locationId) and one for fetching all reviews per location. I need the locationId from the first request, iterate trough all locationId's and request the reviews per location.  First request is working perfectly, REST.json is loaded with all locations. For the second request, I've edited the RSD file, with an input parameter and filter (I know, the URL is mentioned twice, don't know which one is correct):Then I added a dynamic filter:However, no results. Does anyone know what I'm doing wrong? Can someone point me in the right direction? Thanks in advance!

27212
Thomas Lind
Community Manager
R
Contributor
r.depagterContributor
asked in RSD File Customization
REST API POST with custom body

Hi, To make sure my API will always function without manually changing the API token each period, I want to request the token and inject it into an API GET header. To do this, I need to know how to write the proper XML that can do the following: API URL: example.company.com/api/LoginRequired body: EMAIL: example@gmail.comPASSWORD: examplepassword123How do I write the poper XML inside a DSD file to request the token using the body parameters (not in the header), I cannot find the proper documentation. Thanks in advance!

3096
R
Contributor
J
Contributor
juriaan.hensbroekContributor
asked in RSD File Customization
Nested RSD REST API

I'm trying to get data from a REST api. The challenge is that the response is nested (JSON format).I did split up the seperate fields by editing the RSD file, adding something after the xPath...The result looks better than before but still all my answers are comma seperated in 1 field/row combination instead of 1 field 4 rows for example. It looks like this now:How can I make sure I only get 1 anwser per value per row? (So line 1, field q_position should be seperated to 4 rows with value 1,2,3 and 4)

3238
Thomas Lind
Community Manager
G
Starter
gaspard.duquesnoyStarter
asked in RSD File Customization
ODX REST api with incremental values

For a REST endpoint we need to load incremental, how can I set up incremental columns in ODX and pass this values in the RSD file?

1341
Thomas Lind
Community Manager
M
Starter
Madelon Jansen | VictaStarter
asked in RSD File Customization
Rest - HTTP Response Headers

How can I access the HTTP Response Headers when retrieving data from the CData Rest Connector? I need to read values from it to set up pagination in the .rsd file. In my case it contains the current page, records per page and total number of records.

725
Thomas Lind
Community Manager
S
Starter
sturla.winger.svendsenStarter
asked in RSD File Customization
How to connect to REST/API endpoint with dynamic date input?

Hi, I need to extract data from an API which take dates as input values, [from date] and [to date]. And I need to extract data from this API each day. Is it possible to pass dynamic dates to the API connection based on todays date? I can't figure out how to do this.   cheers, Sturla

2694
Thomas Lind
Community Manager

Badge winners

  • Innovator
    oscarclevehas earned the badge Innovator
  • Certified Solutions Architect
    leo.noharethas earned the badge Certified Solutions Architect
  • Innovator
    Bernarddbhas earned the badge Innovator
  • Certified Solutions Architect
    JeroenStahas earned the badge Certified Solutions Architect
  • Ingest Expert
    nmohas earned the badge Ingest Expert
Show all badges
Terms and ConditionsCookie settings