Solved

Error when running nested query in rsd file

  • 13 February 2024
  • 4 replies
  • 60 views

Badge

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 name, type, and column size here. -->
    <attr name="code"                   xs:type="string"  readonly="false"              other:xPath="/json/value/code"                   />
    <attr name="id"                     xs:type="string"  readonly="false"              other:xPath="/json/value/id"                     />
    <attr name="parentId"               xs:type="string"  readonly="false"              other:xPath="/json/value/parentId"               />
    <attr name="valueCode"              xs:type="string"  readonly="false"              other:xPath="/json/value/valueCode"              />
    <attr name="valueId"                xs:type="string"  readonly="false"              other:xPath="/json/value/valueId"                />
  </api:info>

  <api:set attr="urlbase" value="https://api.businesscentral.dynamics.com/v2.0/{tenantId}/production/api/v2.0/companies({companyId})" />
  <api:set attr="filein.DataModel" value="DOCUMENT" />
  <api:set attr="filein.JSONPath" value="$.value" />
  <api:set attr="filein.URITemplate" value="[urlbase]/generalLedgerEntries?$Select=Id"/>
  <api:set attr="filein.ElementMapPath#" value="/json/value/id" />
  <api:set attr="filein.ElementMapName#" value="file_id" />
  <api:set attr="stopin.DataModel" value="DOCUMENT" />
  <api:set attr="stopin.EnablePaging" value="TRUE" />
  <api:set attr="stopin.JSONPath" value="$.value" />
  <api:set attr="stopin.URITemplate" value="[urlbase]/generalLedgerEntries({file_id})/dimensionSetLines" />
  <!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:set attr="filein.URI" value="[filein.URITemplate]" />
    <api:call op="jsonproviderGet" in="filein" out="fileout">
      <api:set attr="filein.file_id" value="[fileout.file_id]" />
      <api:set attr="stopin.URI" value="[stopin.URITemplate | replace('{file_id}', [filein.file_id])]" />
      <api:call op="jsonproviderGet" in="stopin" out="stopout">
        <api:set attr="out.code" value="[stopout.code | allownull()]" />
        <api:set attr="out.id" value="[stopout.id | allownull()]" />
        <api:set attr="out.parentId" value="[stopout.parentId | allownull()]" />
        <api:set attr="out.valueCode" value="[stopout.valueCode | allownull()]" />
        <api:set attr="out.valueId" value="[stopout.valueId | allownull()]" />
        <api:push item="out"/>
      </api:call>
    </api:call>
  </api:script>
</api:script>

icon

Best answer by jarle 16 February 2024, 09:58

View original

4 replies

Userlevel 6
Badge +5

Hi @jarle 

I think you are missing this.

  <api:set attr="filein.EnablePaging" value="TRUE" />

Badge

Thanks @Thomas Lind . I have tried that now, but I still get the same error message. It wasn’t in the original instruction neither.

Userlevel 6
Badge +5

Hi @jarle 

Whenever something does not work for RSD files, I turn on logging on the data source.

If I set the verbosity high enough, it will usually show what is returned from the calls. This can help explain why the field is not set.

My guess, based on the error message, is that the file_id mapping is not entirely correct somehow.

Can you with certainty confirm that it its XPath is json/value/id and not something else?

Badge

Thanks @Thomas Lind . I ended up using the Business Central adapter instead, which anyway turned out a better solution. I just had to upgrade timeXtender in order to get it to work with Service to Service Authentication.

Reply