Pull mileage from All Employees

Options
Janchee
Janchee Expensify Customer Posts: 1 Expensify Newcomer
edited September 2019 in Integrations and API

Hey Expensify community,

We are working towards an environmentally friendly company 🌎

Part of this work is understanding the mileage we complete in company cars!

I would like to get an API running where I can pull through all mileage completed by the employees (who are on one policy) so I can pair it with vehicle data and calculate our CO2 output.

We will then use this to make future decisions on electric vehicles or cost of offsetting (i.e. planting more trees! 🌳)

Any advice on the API approach would be very useful,

Thanks in advance 👌 Jan

Flag · Accept Answer Off Topic Insightful Vote Up 2Awesome

Answers

  • Sheena Trepanier
    Sheena Trepanier Expensify Team, Approved! Accountant, Expensify Student Ambassador Posts: 1,362 Expensify Team
    Options

    @Janchee, this is great! We've recently begun this journey ourselves and have just started the first small steps towards a more environmentally friendly future.

    I'm going to research this a bit more for you, although I'm not sure the capability available to achieve this with the API. Either way, I'll circle back with you after I've had a bit more time to research.

    Talk to you soon!

    Flag · Accept Answer Off Topic Insightful Vote Up 1Awesome
  • fpettinella
    fpettinella Expensify Customer Posts: 17 Expensify Admirer
    Options

    I was really excited to read this thread because I'm looking to do the exact same thing - then I realised @Janchee and I work for the same company :)

    @Sheena Trepanier I just posted a thread on a related issue for this: https://community.expensify.com/discussion/5266/how-to-retrieve-just-expenses#latest

    Is there something that you've found internally that could help?

    Thank you!

    Flag · Accept Answer Off Topic Insightful Vote Up 1Awesome
  • fpettinella
    fpettinella Expensify Customer Posts: 17 Expensify Admirer
    Options

    Should probably mention: we have the go-ahead from our domain owner to use the admin credentials.

    However, the API seem to have insufficient read endpoints to achieve this, or maybe it's just undocumented.

    Flag · Accept Answer Off Topic Insightful Vote Up 1Awesome
  • Sheena Trepanier
    Sheena Trepanier Expensify Team, Approved! Accountant, Expensify Student Ambassador Posts: 1,362 Expensify Team
    Options

    @Janchee and @fpettinella, thanks for keeping this one alive. Via the API, you would only have the ability to export expenses if they were placed on a report, so this could work for reported employee expenses.

    There currently isn't a way for admins to export unreported expenses that belong to accounts other than their own, with the sole exception being imported company card spend.

    Whether you use the API or the spreadsheet export option from the Expenses page, employees would still need to add their expenses to a report in order to get record of them out of Expensify.

    Flag · Accept Answer Off Topic Insightful 1Vote Up Awesome
  • fpettinella
    fpettinella Expensify Customer Posts: 17 Expensify Admirer
    Options

    Hi @Sheena Trepanier thank you for your response. I started to understand this when I found examples of other customers in this forum regarding filtering reports. So for the expensify API, "retrieve all expenses" actually means "set filters on report retrieval to retrieve all reports and then retrieve the expenses in those reports; unreported expenses (card transactions aside) cannot be retrieved". These points may benefit from being explicitly made in the documentation as a one or two-liner at the top of the documentation rather than trying to cross reference the examples to understand the implicit API assumptions.

    Flag · Accept Answer Off Topic Insightful 1Vote Up Awesome
  • fpettinella
    fpettinella Expensify Customer Posts: 17 Expensify Admirer
    Options

    The key lies in getting all reports with filters, then pulling the expenses from those reports with a template: https://community.expensify.com/discussion/comment/12222

    Flag · Accept Answer Off Topic Insightful Vote Up Awesome
  • Matt Moore
    Matt Moore Expensify Customer, Expensify Success Coach - Admin, Expensify Team, Expensify Student Ambassador Posts: 132 Expensify Team
    Options

    Hi @Janchee, @fpettinella and @Sheena Trepanier!

    I've done some back-end digging and I've found that we store the mileage amounts in the expense array under:

    [comment] => {"type":"distance","units":{"count":"1234","unit":"mi","rate":"1234"},"comment":"Example Comment"}

    "count" can be used to extract the mileage amounts from the expenses on a report.

    ${expense.units.unit}
${expense.units.rate}
${expense.units.count}

    You'll want to use something like this:

    <#if expense.category?contains("Mileage")>
            <#if merchant?contains(" mi ")>
                <#assign type = "mi">
                <#if expense.units.count?has_content>
                    <#assign distance = expense.units.count>
                <#else>
                    <#assign distance = merchant?keep_before(" mi ")>
                </#if>
            <#elseif merchant?contains(" km ")>
                <#assign type = "km">
                <#if expense.units.count?has_content>
                    <#assign distance = expense.units.count>
                <#else>
                    <#assign distance = merchant?keep_before(" km ")>
                </#if>

    Let me know if you need more help here!

    Flag · Accept Answer Off Topic Insightful Vote Up Awesome

Categories