Xero - Bulk Edit Account of Customer Invoices with allocated payments

Hi DataDear Team,

I have 2000 paid customer invoices that are currently allocated to the wrong GL account. To edit manually in Xero would take hours and the Datadear mass edit template won’t allow the account to be edited as payment has been applied. I’d prefer not raise manual journals so that some of the built in reporting and the data is consistent through the period.

Can anyone suggest a work around?

Thanks,
Louis

Hi @WAL_Hunter

Welcome to the community!

This is not something that can be changed from DataDear as the Xero API states:

When can’t an invoice be updated?

An invoice can’t be updated if:

  • it has been fully paid
  • it has part-payments or credit notes applied to it
  • it is in a locked period

API Source

Does it make sense to delete payment for these Invoices?

Hi Neville,

Thanks for your response. It was two weeks work to apply the payments to these invoices so deleting the payments and doing them again isn’t really an option unless there’s a way of doing and then reapplying en masse.

If you or anyone has any trick to share, I’d be very grateful. Otherwise, I guess my only option is manual journals.

Thanks,

Hi Louis

The only solution I can think of is is to remove payment, apply changes and then re apply payment.

Step 1: Using DataDear get all the Invoices in a separate sheet in the Invoices & Bills Template workbook - you can use filters to only get ‘Paid’ Invoices and in the period you need. This is your backup so do not refresh or delete this page.

Step 2: Remove payments from Xero (DataDear cannot help with this one)

Step 3: In the workbook created earlier in Step 1, use the Get Data to Edit button and get all required invoices to be fixed (filters can be used).

Step 4: Change whatever is required in the accounts plus enter the payment details in the columns X to AC - you may use Excel functionality to map these records to the other sheet created in step 1 and payments will load automatically (example using vlookup). You may also do this manually but will take time for 2,000 Invoices.

Step 5: Use the Post Online (I would suggest testing this in small batches to ensure all goes well)

Hope that helps - if you need any help with building a model in Step 4, feel free to contact me with a private message