Use parameters in Matillion API Query (Xero)

Matillion ETL
1

Hi - I am trying to pass some parameters to the Xero API component provided by Matillion. These are get parameters available in API. Does anyone know if I can either edit the RSD file to add these in? Or can they be added with an "Other" connection parameter? Or any other way?

 

I've tried using Other in the connection parameter with values of from Date=XXXX-XX-XX;toDate=XXXX-XX-XX.

 

https://developer.xero.com/documentation/api/accounting/reports#profit-and-loss. Trying to add some parameters to the query,

 

I keep getting the data back with current period (which is the default)

 

THANKS!

 

 

2

@JS217889​ I think there is a difference between the Xero Query component and the API Query component?

  • The Xero Query component has all the endpoints hardwired into it, and you can't directly influence the REST API calls it makes.
  • With the API Query component you create the REST API calls yourself, and it's flexible enough to use with any REST API.

So I think with the Xero Query component the only way you can change the API calls it makes is indirectly, through the SQL you provide. So in your case you might need something like this (in advanced mode)..

SELECT *

FROM ProfitAndLoss

WHERE FromDate = '2020-01-01'

AND ToDate = '2020-04-01'

LL

3

Thanks @LL​ for responding. I dont think that query is going to work as the fromDate and toDate arent exposed in the output. Maybe someone from the product team in Matillion who works with #xero​ can pick it up because the Xero Query tool is fairly useless without having the ability to pass parameters as then you can only get the default values and periods

I will pick up as you suggest and build out a custom one and try and share in the marketplace. Just the authentication side I think will be a bit tricky because it requires the browser sign in to get the auth tokens.

4

Hello @JS217889​ ,

I would definitely try the query @LL​ suggested. Matillion is using CDATA's connectors towards Xero.

By looking at the raw documentation,

https://cdn.cdata.com/help/DXF/synch/default.htm#pg_accountingtable-profitandloss

The FromDate and ToDate are Pseudo-columns that are used as filtering.

Hope this helps!