Meaning of "expense.transactionID" in export_report_template

GetawayHouse
GetawayHouse Expensify Customer Posts: 7
edited October 2019 in Integrations and API


I'm trying to get a list of expenses reported, using the api with { type: 'file' } 

 json = {
   type: 'file',
   credentials: {
    partnerUserID: ENV['GETAWAY_EXPENSIFY_PARTNER_USER_ID'],
    partnerUserSecret: ENV['GETAWAY_EXPENSIFY_PARTNER_USER_SECRET']
   },
   onReceive: {
    immediateResponse: ["returnRandomFileName"]
   },
   inputSettings: {
    filters: {
     startDate: self.month.beginning_of_month.strftime('%Y-%m-%d'),
     endDate: self.month.end_of_month.strftime('%Y-%m-%d'),
     reportState: ["OPEN", "SUBMITTED", "APPROVED", "REIMBURSED", "ARCHIVED"]
    },
    type: 'combinedReportData',
   },
   outputSettings: {
    fileExtension: 'csv'
   },
  }

And my template looks like:

<#if addHeader == true>
  Amount,Sales date,Tag,Category,Comment,Report status,External ID,Name,Email<#lt>
</#if>
<#list reports as report>
  <#-- Report level -->
  <#list report.transactionList as expense>
    <#if expense.modifiedAmount?has_content>
      <#assign amount = expense.modifiedAmount/100>
    <#else>
      <#assign amount = expense.amount/100>
    </#if>
    <#if expense.modifiedCreated?has_content>
      <#assign created = expense.modifiedCreated>
    <#else>
      <#assign created = expense.created>
    </#if>
${amount},${created},${expense.tag},${expense.category},"${expense.comment?replace("\"", "'")}",${report.status},${expense.transactionID},${report.submitter.fullName},${expense.attendees.get(0).displayName}
  </#list>
</#list>

The problem is: I'm getting repeated ids for this field, for example: For the id 35387923473891400 I have 21 expenses with different values and comments, shouldn't every expense have it's own unique id? If I go to the dashboard, I can find a report with those 21 expenses...

If expense.transactionID isn't unique for each expense, is there a field that is?

Answers

  • Ted Harris
    Ted Harris Expensify Success Coach - Admin, Expensify Team, Expensify Student Ambassador Posts: 359 Expensify Team

    Hiya @GetawayHouse - thanks for posting this. Would you mind posting the list of Report ID's you used for testing as well and I'll do my best to dig into this for you. (Don't worry, report ID's are unique and only your authenticated users can access them).


    It's likely this is linked to a similar issue in the Expensify Community here - so hopefully if I can resolve this for you, it'll be the same fix over there too!

  • GetawayHouse
    GetawayHouse Expensify Customer Posts: 7

    Hey Ted!

    Thanks for your reply.

    I've created a report without sensitive data with these params:

    requestJobDescription={

     "type": "file",

     "credentials": {

      "partnerUserID": "secret",

      "partnerUserSecret": "secret"

     },

     "onReceive": {

      "immediateResponse": [

       "returnRandomFileName"

      ]

     },

     "inputSettings": {

      "filters": {

       "startDate": "2019-08-01",

       "endDate": "2019-08-31",

       "reportState": [

        "OPEN",

        "SUBMITTED",

        "APPROVED",

        "REIMBURSED",

        "ARCHIVED"

       ]

      },

      "type": "combinedReportData"

     },

     "outputSettings": {

      "fileExtension": "csv"

     }

    }

    and this template

    <#list reports as report>

      <#-- Report level -->

      report.reportID: ${report.reportID}

      <#if addHeader == true>

       expense.created, expense.transactionID

      </#if>

      <#list report.transactionList as expense>

        <#if expense.modifiedAmount?has_content>

          <#assign amount = expense.modifiedAmount/100>

        <#else>

          <#assign amount = expense.amount/100>

        </#if>

        <#if expense.modifiedCreated?has_content>

          <#assign created = expense.modifiedCreated>

        <#else>

          <#assign created = expense.created>

        </#if>

    ${created},${expense.transactionID}

      </#list>

    </#list>


    Yielding the attached report

    Hope it helps, please keep me posted about updates on this.

    Notice it has on line with a report id, then a list with transactions "created" and "transactionID", notice that all transactionIDs are the same given a report.

  • Nicole Trepanier
    Nicole Trepanier Expensify Team Posts: 498 Expensify Team

    @GetawayHouse Looking at the file you sent, each transaction does have a unique ID. Can you send me a screenshot of where you are seeing duplicates?


  • SBP1
    SBP1 Expensify Customer Posts: 6

    This is exactly the same field I'd like to export in my custom csv file...what is the formula for getting the transaction ID and the external id?