Filter by Xero TRACKING Category in Invoice/Credit Note line extract

XERO - we would massively decrease the amount of data we have to download from Xero if we were able to use Xero’s TRACKING CATEGORY in the data filter. Typically we use one Tracking Category as a Project identifier - without this as a filter option we have to download the whole of our Xero data - given our projects run over a year or so then that’s typically TWO years of data to refresh continually. VERY LONG WAITS while DATADEAR does the extract

Hey @OakenStone

It sounds like you’re referring to the Xero account transactions / journals table. Have you tried using the ‘Append’ functionality? This gets your workbook working like a warehouse, and just brings in the latest data, the first request going back 2 years will take a short while, but each time you have to update will just be bringing in the most recent transactions so much, much faster!! :rocket:

As the Xero API doesn’t allow for filtering by tracking category, it is only the amount of data we are loading into your Excel sheet that changes, and this process takes no time at all.

If using account transactions please use the ‘append new transactions’ button in the toolbar
If using Journals please use Refresh > Current Sheet > Append
If I’ve guessed wrong please let me know the exact feed so I can better advise.

I hope this helps!

thank you for the reply - the append function may well be useful HOWEVER can you confirm whether it will pick up existing transactions that have been revised or will it only append new transactions. We regularly have to revise the assigned XERO account column on existing entries - I have not used APPEND to date as I anticipated it would not pick up the revised entries

Hey @OakenStone,

I can confirm it will pick up any edits/alterations. How this report manages this is, it will reverse the original lines, and then leave you with final. In your situation you would have entries netting off to 0 for the wrong account that was originally applied, and left with the transaction lines of your final edit.

Hope this helps!

Testing this out we are seeing the new set of updated line items for each affected invoice BUT we DO NOT see any reversing line items - only the original set of line items - which means that in our ongoing analysis we have two lines for each original line thus apparently doubling the line item cost

Also having APPENDed data and now REFRESHing to renew entirely, the appended data is not being overwriten but instead remaining as part of the data table - surely a REFRESH should overwrite the previous data

Hi @OakenStone,

You should see the data for SourceID being replicated across all affecting lines. If you filter the column by the ID in your example it should make the reversing lines easier to spot. Can you use the ‘Report problem’ icon within the DataDear toolbar so our team can investigate the logs for you please?


thanks - yes I had spotted the ID being replicated and therefore had identified that only two lines appeared for each revision - the original line and then the revised line - every column entry appears to be exactly the same so I dont see how the item is reversed - however as the data gets loaded into ongoing analysis is does appear to be correct - i.e. the entry does not appear to be doubling as I would expect. I need to understand this before committing to APPEND :slight_smile:

1 Like