Pull mileage from All Employees

JancheeJanchee 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

Answers

  • Sheena TrepanierSheena Trepanier Expensify Success Coach - Admin Posts: 1,320 Expensify Team

    @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!

  • fpettinellafpettinella Expensify Customer Posts: 17 Expensify Newcomer

    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!

  • fpettinellafpettinella Expensify Customer Posts: 17 Expensify Newcomer

    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.

  • Sheena TrepanierSheena Trepanier Expensify Success Coach - Admin Posts: 1,320 Expensify Team

    @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.

  • fpettinellafpettinella Expensify Customer Posts: 17 Expensify Newcomer

    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.

  • fpettinellafpettinella Expensify Customer Posts: 17 Expensify Newcomer

    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

  • Matt MooreMatt Moore Expensify Customer, Expensify Success Coach - Admin, Expensify Team Posts: 109 Expensify Team

    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!

Sign In or Register to comment.