How can I use the Xero journals data table?

:warning: Still need to be formatted

Learn more about the Xero journals data table and how to use the parameters window to filter and extract the required journals.

DataDear gives you the option to download (GET) data from your connected organisations into your Excel worksheet. In this article, we will focus on the journals data table. There are few steps required to download the data as this can be filtered based on your needs.

Choosing the Journals data table

  1. Open Ms Excel and login to DataDear.
  2. Choose the organisation (if you have more than one connected organisation) from the first drop-down. You may first need to add an organisation - refer to how you can add an organisation in DataDear.

DataDear Organisations

  1. Click on the Journals data table drop-down list to select the data/report (data-set) you wish to download.
  2. A parameters window will be loaded with various options / filters to choose from.

Parameters window

The journals data table will help you filter the data to be downloaded into Excel.

  1. Preset filters - DataDear comes preloaded with a number of preset filters which can help you understand how the mining works. You can choose a preset by using the Preset filters drop-down. You may also save your frequently used filters as a preset by typing a name in the preset drop-down and select the Save Preset. Custom presets can also be deleted.
  2. Date Range - You need to select between Modified since (plus select a date) and The beginning of time . More details on these options further down in this article.
  3. Unique Records only - Remove any possible duplicated content such as a contact may have more than one telephone number (this may not be applicable for journals).
  4. Remove empty columns - Remove columns which do not have any data.
  5. From journal number - Choose the position / starting point from where DataDear should start downloading journals.
  6. Number of journals to download - Data can be selected in batches of 100 journals - minimum download is 100 journals and maximum is 5,000 journals.
  7. Filter your data - You may also decide if the filters will need to match all conditions or at least one of these conditions. You may apply a variety of filters based on any parameter (column) available in the selected data table. Select the Parameter (example Journal Number), the Text Filter (such as Equals … or Contains…) and a Value. You may pick up the value from another cell or sheet which is ideal when you need to refresh data based on another sheet. You can also use Excel’s function wizard in these fields. Choose the + icon to add the filter for processing. Multiple filters can be added and applied to your query. You may also edit or delete these filters until you find the right balance for your needs.

Note: This functionality works when the date range selected is The beginning of time. DataDear will start looking for the chosen filters from the selected journal number and will stop once the number of journals to download is reached.

DataDear - mine your date

Date range - The beginning of time

Since DataDear does not store any data on its servers, all the downloaded data is obtained fresh from Xero. Filters are not provided by Xero and are processed with the DataDear engine after the data has been retrieved from Xero. Hence when using filters, DataDear will only search within the journal number and range you specify - i.e. starting from the indicated journal number and will keep looking until the number of journals to download is reached. Filters only work when the beginning of time is selected.

Example:

A company setup in 2016 has accumulated 12,000 journals up till today. The journal number for the year 2017 starts from the 6,000 journal.

Parameters window:
From journal number - 0
Number of journals to download - 5,000

Result: 0 records found

Doing a search for a transaction which happened in 2017 and selecting 0 as a starting point and the 5,000 maximum range DataDear will not yield any results. You would need to change the journal range in the parameters window to start from 6,000 (and keep the maximum of 5,000) to get the results you require. Same thing happens if you are using filters.

If you plan to use journals as your base reporting, since journals never change, our recommendation is to get all journals once by carrying out the following:

  1. Download all journals starting from journal 1 and selecting the maximum number of journals to download (5,000 journals).

  2. Copy the downloaded journals in a separate (non-DataDear) sheet

  3. Back on the DataDear sheet, click Refresh > Refresh Current Sheet and change the ‘from journal number’ to 5,001 (keep number of journals to download as 5,000)

  4. Copy the downloaded journals at the bottom of the separate (non-DataDear) sheet used earlier

  5. Repeat this process until you have downloaded all journals

  6. Save these journals as the master record

Going forward there is no need to re-download the old journals as these never change hence it is a matter of topping up the new journals by specifying the new starting point using the journal number or the modified since. This data can then be filtered using normal Excel functionality such as Power Tables.

Date range - Modified since

When you select the Modified Since the following will happen:

Example

A company setup in 2016 has accumulated 12,000 journals up till today. The journal number for the year 2017 starts from the 6,000 journal.

Parameters window:
Modified since 1 Jan 2017
From journal number - disabled
Number of journals to download - disabled
Filters - disabled

Result: 5,000 records found starting from 6,000

DataDear will download 5,000 records (or less) starting from journal number 6,000 (this is when the first journal of 2017 was processed in the example) up to journal 12,000 (5,000 records).