Batch update inventory items from invoice?

Is there a way to batch update all the inventory items that appear on one particular purchase invoice (or purchase order)?

I use a spreadsheet application to do our pricing calculations every time we import a shipment of new stock. Because of changes in exchange rates and various other factors, the prices often change from one order to the next. Also, usually at least half of the items on the purchase invoice are items that we are ordering for the first time, so they don’t have sales pricing associated with them yet.

I select the text of the purchase order or purchase invoice, copy this and paste it into a spreadsheet, and do my calculations there. However, I haven’t found an efficient way of updating the sales prices of the items in Manager with the results of this. I usually do it by editing all the items individually, but I have more than 400 to do in our next order, and that is going to take some time.

So, what I would like to be able to do is to use some kind of filter in the Inventory Items view to limit the displayed items to only those that appear in a particular purchase invoice, so that I can use the normal batch update functionality on these. The closest that I have come is by sorting by Qty to receive, but unless the number of items I want to update exactly matches one of the options for number of items displayed per page, there could be many extra items included in the operation that I will have to remove. And because the Qty to receive value isn’t carried over in the copying from Manager (since we obviously can’t change this), I can’t use it as a sorting criteria in the spreadsheet.

Any suggestions for speeding up my price updating would be appreciated.

You seem to have focused on the filtering aspect. But I don’t think that will be fruitful, because it concentrates your attention on the wrong thing. The real challenge arises because somewhere, at some point in time, you have to enter the prices for those 400 items into some application. That might be Manager, or it might be your spreadsheet. But you are trying to find a way to push prices into Manager’s database from a purchase invoice (or order) after manipulation in a spreadsheet, while Manager operates by pulling prices from the database into transactions.

Why not flip your thinking? Focus on updating inventory prices first. Then the pricing in your transactions will be correct. That would be your single data entry action. If your sales pricing is derived from your recent purchase costs, you could update the purchase prices as the initial step. Then, you could use Batch Update to copy the inventory listing into a spreadsheet. While the list is in a spreadsheet, apply your formulas to update sales pricing before pasting back into Batch Update. It won’t matter that you are “updating” prices that haven’t changed. The important thing will be that you are updating the prices that have changed.

This avoids the need to filter and keeps everything having to do with invoices in the “pull” domain. Only after inventory prices are updated would you generate the purchase transactions.

Method using excel:

  1. Go to Purchase invoice tab and search for the existing invoice you wish to use
  2. Select batch update and export the Purchase invoice data to Excel spreadsheet
  3. Go to inventory items and use batch update to export all inventory item data to a separate spreadsheet.
  4. Go back to the Purchase Invoice data Excel spreadsheet (produced at step 2) and copy the UUID codes from the Lines.Item column (L) and paste this into the inventory item data spreadsheet (produced at step 3) in blank lines below the inventory data in the “Key” column (AB)
  5. Use Conditional formatting to highlight duplicates.
  6. Sort the inventory data using “Sort On” Cell Colour
  7. Delete all rows except those with highlighted key
  8. Change the pricing on the remaining lines and import using batch update
1 Like

Thanks @Tut. I’m not sure I fully understand what you are suggesting. Or maybe I didn’t explain my workflow clearly enough. We are purchasing from a foreign supplier, so our purchase prices are in a different currency from our sales prices. I know that the prices for inventory items in Manager are just numbers for pre-filling forms and are not connected to any specific currency, so in theory we could just use a formula that accounts for the exchange rate and our markup to calculate the sales prices from the purchase prices. But there are several other factors that we use for our pricing, including the freight-in costs, rounding, and our own discretion. So at some point there has to be line-by-line manual input, but my challenge has been to find a way to isolate only the lines (inventory items) that I am interested in updating so that I am not searching for each of the 400 items to update them individually.

Surely this still requires filtering or isolation of those items that need updating?

Thank you @AJD for your suggestion. This is similar to what I ended up doing in this case, although I didn’t think of using Batch Update on a single purchase invoice. I did the following:

  1. Copy the text from the purchase invoice and paste it into a spreadsheet
  2. Perform the necessary calculations with freight-in costs, exchange rate conversions, our markup, and VAT to arrive at an automated sales price
  3. Add another column to manually input the final sales price line-by-line, with rounding and discretionary adjustments
  4. Go to the Inventory Items tab in Manager and sort by Qty to receive, making sure that the view accommodates all the items that need updating
  5. Select Batch Update and copy the data into a new spreadsheet
  6. Sort both spreadsheets by Item code, then remove all lines from the Batch Update spreadsheet that don’t have a corresponding item in the first spreadsheet
  7. Copy the column with the new prices from the first spreadsheet and paste into the Batch Update spreadsheet, overwriting the old sales prices
  8. Copy the Batch Update spreadsheet and paste it back into Manager, and complete the Batch Update process

So, next time I will try @AJD’s suggestion of using Batch Update on the single purchase invoice, and I will learn how to use the conditional formatting and sorting in the spreadsheet to speed up that part of the process.

@GrahamvdR, you have basically done what I suggested. But you can leave out the half of Step 6. You can paste back inventory items that have not changed. They won’t be updated. And that saves a lot of work.

Thanks @Tut. I understand that there’s no harm in leaving in items that are not changing. But I did this step so that I could line up the rows from one spreadsheet with those from the other. Consider the following two tables of prices:

Current list of inventory items:

Item code Sales price
aaa 12
aab 5
bfa 24
ggh 45
maa 9
ssg 16

List of items to be updated with new sales prices:

Item code Sales price
aaa 14
ggh 40
ssg 12

In both examples above, the tables are sorted by Item code. But I can’t just copy the Sales price column from the second table and paste it over the Sales price column in the first, because there are other rows there in between those that I want to update. So either I need to first remove these extra rows (as I did), or find some way to sort the table so that they are out of the way (as @AJD did using conditional formatting and then sorting). If we use @AJD’s method, then deleting all those extra rows is only a couple of clicks. Unnecessary, yes, but not “a lot of work”.

Anyhow, it looks like the main thing that needs work here is my spreadsheet skills, and Manager has the tools available to do what I need.

But I thought you were only copying after sorting by quantity to receive. Do that in both spreadsheets and you should get the same result. Then copy and paste to overwrite the original list. Copy the entire spreadsheet and paste and you are done.

You are right, though. Either way comes down to figuring out what spreadsheet tricks work best for you.

Ah, OK. I hadn’t been doing this for the purchase invoices, but I can’t think of any reason not to. As you pointed out in your first response, the process of updating the prices doesn’t need to be connected to the purchase invoice in any way. I understand you better now. Thanks for your time spent on this.