Inventory reports export to Excel

All Inventory Items report show the first column containing both the item code and the item name. When the report is exported to Excel, the resulting file has the cells of the first column also containing both the item code and the item name. This can generate some time-consuming work in order to manipulate the data. For example, I want to see the list in alphabetical order but this cannot be done as the code is what appears first in each cell of the first column, and the code numbering does not match the alphabetical order of the items.

Would it be possible to separate the two information (item code and item name) in two different columns? After all these two pieces of information come from two different information fields of the items.

have you tried sorting by Item name first in Manager and then exporting it?

I don’t think there is any way to sort the various columns from the report itself. It comes as in the picture. One can only select, copy and paste into Excel part of the data, or use the export function at the bottom of the report. In both cases the result in Excel is the first column having cells that contain both the item code and the item name.

This will not solve your code/name separation desire, but it looks like you are entering a date for the item codes. Maybe these are vintages, maybe purchase dates, I don’t know. But use of a item codes is intended to be for unique identifiers that make it easier to locate and track items. Yours are not doing that, because Manager sorts them alphanumerically. (Therefore, 01-04-15 comes after 01-14-17.)

You might consider putting these dates into the names themselves, after the more basic product name. For example, Santa Cristina White 2017. Or, you could put them in a custom field, although those will not show on reports. Or, you could change the item code scheme. For example, SCW 01-04-17. Then, they would sort in more conventional alphabetical order.

We do need the code as it helps us with another system that we are obliged to use for VAT purposes. And the full info on vintages etc. is indicated on the item description (which does not appear on the report). So the code is not a date, only the last 2 digits indicate the vintage.

What I have done so far is to use the “Replace” function in Excel in order to delete all numbers and the hyphens, but this takes some time and is not doing the job to its best.

I am not an expert, but I guess that changing the way the reports are created would not be a very complicated thing to do as the item codes and the item names come from two different fields and they are actually put together in one field in these reports, instead of being kept separated.

Based on your latest information, it sounds like you may be using item codes to divide inventory items into categories. That is not what item codes are designed for. As I said earlier, they are meant to be unique identifiers. Using them to assign items to categories may pose problems in the future as the program develops, because no categorization usage is going to be considered. So you may end up with bigger problems in the future.

If you describe your actual usage of the item codes for VAT purposes, there may be another way to get you what you need.

No, the codes are not per category. Each item has its own code. The first 2 digits identify the supplier (and yes you might argue that this is a category), the following two digits are a sequential number given to the item of the same supplier (for example if we start buying a new item from supplier 01 we will move from 01-01 to 01-02, 01-03, etc. The last 3 digits indicate the vintage and it is not very relevant. We used 00 for the last two digits if the item has no vintage.

The VAT system we must use when we issue invoices (we issue both a Manager invoice and official invoice from the so-called EBM system used here) requires codes and we use the first four digits of the item code (hence not the vintage).

Based on what you have said, I might abandon item codes completely. You do not seem to be using them to track, locate, or describe the products for your own purposes. Instead, you seem to be using them only to get desired information on reports from which to extract information for official filings.

Unlike item codes, item names are intended as internal nomenclature, primarily to identify inventory items on reports. If you adopted a naming scheme like the following examples, you would get the information you say you want for VAT filing purposes and have it in alphabetical order:

  • Pasta De Matteis 000100
  • Santa Cristina White 010216
  • Santa Cristina Chianti 010414

You could also leave the hyphens in place. That would have the advantage that during conversion you could simply cut from the item code field and paste into the item name field after the existing name. Thus, the conversion would go more quickly. If the two digits for vintage really aren’t relevant, you could leave them off, although from your example that would actually produce some duplicate names.

Of course, in actuality, you would want to do the conversion using Batch Update. You could use the CONCATENATE function in Excel to combine and reorder field contents. That way, you could revamp your entire inventory in just a couple of minutes.

Thank you for your suggestion but this is not correct. When we issue an invoice from Manager we can see the item codes and the item descriptions on the invoice. The item names do not appear. Thanks to the item codes on the (printed) invoice we can then go into the other official system and digit the codes for each item. This way this other system create another invoice with the codes and (automatically) the names of the items (which were previously saved in the official system).

If I use your solution, the invoices from Manager would not show the codes and it would become very difficult to issue the other official invoice.

Still I wonder: is it something very difficult that needs to be modified in Manager in order to get two separate columns in these reports?

That I do not know. Remember, I am not the developer. I was just trying to help you get what you want with current capabilities. Your extra requirement makes that more difficult.

1 Like

considering your purpose explained, you can still create custom fields for Inventory items and use it to show your item codes. these custom fields will show as a column on your sales invoice and will also help you get the report in a manner you desire.

if you create a custom field for Inventory items, it is easier to do a batch update where you just cut-paste the contents from Item code column to the new custom field in your spreadsheet program.

Perhaps in excel you can use the CONCANTENATE function to separate out the values, then use filter to sort via various data.

its very easy to separate the two in excel. Just click Data, choose Text to columns and follow the instructions

@Tinashe thank you, this was very helpful