# Data export arranged as tables

**URL:** <https://community.trooptrack.com/t/data-export-arranged-as-tables/3863>\
**Category:** The TroopTrack Developer Program\
**Created:** [November 14, 2016, 10:39am UTC](https://community.trooptrack.com/t/data-export-arranged-as-tables/3863 "2016-11-14T10:39:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![peterm](https://avatars.discourse-cdn.com/v4/letter/p/a9adbd/32.png) [@peterm](https://community.trooptrack.com/u/peterm)\
**Post date:** [November 14, 2016, 10:39am UTC](https://community.trooptrack.com/t/data-export-arranged-as-tables/3863/1 "2016-11-14T10:39:20Z")

</div>

I’m interested in creating some custom reports to track how are Australian Cubs are going with their Boomerang Badges. I thought the offline data from the API would be great but it isn’t returning the data how I thought it would - rather than being a direct table dump (in my 20 year old experience of SQL databases) it seems to have lots of tables/sub tables for each achievement/person.

Is there any _easy_ way to do a table dump so I can import it into Access (or something similar) and fiddle with it?

---

<div class="post-metadata">

**Author:** ![peterm](https://avatars.discourse-cdn.com/v4/letter/p/a9adbd/32.png) [@peterm](https://community.trooptrack.com/u/peterm)\
**Post date:** [November 14, 2016, 11:14am UTC](https://community.trooptrack.com/t/data-export-arranged-as-tables/3863/2 "2016-11-14T11:14:20Z")

</div>

An example of what I find hard to process is this sort of thing:

```
{
  "file": "achieveLevel2_49_11111",
  "contents": [
    {
      "achievement_id": 11111,
      "name": "Gold Boomerang",
      "purchased": false,
      "user": "Fred Nurk",
      "award_card_front": "",
  "award_card_back": "",
  "award_type_id": 49,
  "user_id": 123456,
  "user_achievement_id": 32345214,
  "descendants": [
    {
      "user_achievement_id": 32345215,
      "achievement_id": 23489,
      "name": "These are compulsory",
      "completed_on": ""
    },
    <etc>
  ],
  "percent_complete": 23,
  "completed_on": "",
  "reported_on": "",
      "approved_on": "",
      "scoutmaster_conference_on": "0001-01-01",
      "review_board_on": "0001-01-01"
    },

```

From my point of view it would be heaps simpler to just get a list of User Ids and Achievement Ids as one set of data and the achievement detail records as another. Does that make sense?

---

<div class="post-metadata">

**Author:** ![mlsully2002](https://yyz1.discourse-cdn.com/flex027/user_avatar/community.trooptrack.com/mlsully2002/32/442_2.png) [@mlsully2002](https://community.trooptrack.com/u/mlsully2002)\
**Post date:** [November 14, 2016, 3:50pm UTC](https://community.trooptrack.com/t/data-export-arranged-as-tables/3863/3 "2016-11-14T15:50:13Z")

</div>

Hi @peterm,

Here’s what the boss had to say:

“The API is for consuming in applications and isn’t designed to be a strait import/export tool. Some of our reports provide csv as an export format (if I remember correctly). Maybe you could look and see if there is already a solution for his problem by looking through the reports.”

You may check under Manage \> Reports to see if there is a custom or preexisting report you can use.

This one has the scouts and their achievements and may be useful to you:

[https://gwscouts.trooptrack.com/achieve/badge\_book](https://gwscouts.trooptrack.com/achieve/badge_book)

---

<div class="post-metadata">

**Author:** ![peterm](https://avatars.discourse-cdn.com/v4/letter/p/a9adbd/32.png) [@peterm](https://community.trooptrack.com/u/peterm)\
**Post date:** [November 15, 2016, 2:53am UTC](https://community.trooptrack.com/t/data-export-arranged-as-tables/3863/4 "2016-11-15T02:53:59Z")

</div>

I’ve looked and looked and looked - there is no way I can find to do what we NEED to do.

I raised it in this idea post:

> [@Achievement detail Export](https://community.trooptrack.com/t/achievement-detail-export/2659):
>
> I’m trying to work out which bits each Cub hasn’t done in the Boomerang Award system (Scouts Australia). I can’t see any easy way to do it in TroopTrack - the only solution seems to be to click on eac…

I’m now trying to achieve that using your API (and failing badly).

I’ve got 24 Cubs doing Boomerang. Each one is missing different things to finish off the badge. The Bronze Boomerang award has 46 different requirements. I’m guessing Silver & Gold would have the same or more.

The only way I can work out which of the 46 bits are missing for each Cub is to go individually into their record and look at it. At that point I’d be transferring it to a paper spreadsheet to visualise it - and if I have to do that then why bother using TroopTrack? It is getting to the point where a Google Docs spreadsheet would be easier to manage for badge tracking.

---

<div class="post-metadata">

**Author:** ![peterm](https://avatars.discourse-cdn.com/v4/letter/p/a9adbd/32.png) [@peterm](https://community.trooptrack.com/u/peterm)\
**Post date:** [November 21, 2016, 10:15am UTC](https://community.trooptrack.com/t/data-export-arranged-as-tables/3863/5 "2016-11-21T10:15:55Z")

</div>

Turns out you have a report which sort of does what I want - “requirement detail report”. For some reason it is hidden away in the Members section rather than the Achieve section. It’s also way down the bottom of the list - off the bottom of my screen. Not sure why I didn’t see it before - the name and location didn’t help.