silverhawk said:

Does anyone know of a way to import more inventory data fields with the import records function

Hi Andrew,

If you set up one inventory item with the additional fields filled in as you need them, and export it, the exported text file will have every field that can be imported. 

If the fields you need aren't there, they can't be imported using the built-in functionality. 

What I would do then is:

 - With the company data in Multi-User Mode, connect to the tInvent and tInvUDF tables using Microsoft Access.

 - Create a query joining tInvent.LID to tInvUdf.lInventID, showing the part number and description fields from tInvent, and all the fields from tInvUDF.  

 - Paste the additional information by column from the Excel sheet (it must be sorted in the same way that the inventory list is sorted in Excel).

If I had to do this a lot of times, I would probably buy a third-party product that automates it.

I hope that helps, please post back!

Randy

Thanks for the advice, Randy! 

This sounds like it might work well. I will be entering warehouse location codes in the "Additional Info" tab in the inventory records and this does not export into the text file as you mentioned (I tested). I will only need to be doing this one time for a single company, so I don't think there will be a need to purchase third-party software to automate. It's around 850 inventory items.

I am looking into getting Access right now and will begin testing. I will report back!

Cheers,

Andrew

Hi Andrew,

We do something similar, an added the field to a Sales Order form for the parts people to use as a pick list.

This SQL query will let you select out inactive or non-inventory (Service or Activity)

) items:

SELECT  tinvent.lId,
 tinvent.sPartCode,
 tinvent.sName,
 tinvent.bInactive,
 tInvent.bService,
 tinvudf.sUsrDfnd1,
 tinvudf.sUsrDfnd2,
 tinvudf.sUsrDfnd3

FROM tinvent LEFT JOIN tinvudf ON tinvent.lId = tinvudf.lInventId;

Hope that helps, please post back!

Missed a comma after bService.

So I never did end up getting Microsoft Access to test this. I was going to just import all the fields that Sage 50 could natively import and then manually enter some of the additional information. A bit of time involved but as this is a one-time event I couldn't convince management to purchase new software.

I have hit a major snag in my plan. I am not able to enter Opening Quantity amount for any of the imported inventory items! Yikes! 

I can confirm that the inventory module is still in history mode as I am able to enter Opening Quantities when I create a new Inventory Item. But all of the Inventory items that I import using a .csv file show the Opening Quantity as greyed out. 

Any ideas on how to get around this?

Thanks for your help!

silverhawk said:

as this is a one-time event I couldn't convince management to purchase new software.

For one time use I would get around that by either using LibreOffice or an Office 365 account, or even a trial version. Access has capabilities far beyond that one time, some things that are difficult or even impossible in Excel can be done in an Access query.

That said, there's a long, long learning curve.

silverhawk said:

I am not able to enter Opening Quantity amount for any of the imported inventory items! Yikes! 

I just tested that with version 2018.1 and, while it crashed 'out of memory' at 1,431 items I could add opening quantities.  

Are you sure the items are importing as 'Inventory Items' and not 'Service Items'?

I am downloading LibreOffice right now and I will have a look...database management software can be confusing, so we will see if the learning curve is too steep. 

I just checked and they are definitely all Inventory Items. I am testing this with version 2016.1. Would that cause a problem?=

silverhawk said:

I am testing this with version 2016.1. Would that cause a problem?=

Not any that I know of, unless there is a license issue.

Some more 'troubleshooting' tests that might highlight a cause:

Are you using multiple 'locations'?   If so, you may have to pick one before adding beginning quantity / values, you can't edit a calculated sum of all locations

Will the software allow you to add opening balances to an imported item after modifying it?

Does it work after changing the linked accounts? or changing them, saving, and changing back?

Can you still create new additional inventory items and edit opening quantities?

Can you export one item, remove it, then import that one item again?

Is anything else different between tries?  Login ID, single vs multi-user mode?

Okay, I tested the inventory import in version 2018 and everything imported fine. Not sure what was happening earlier. 

Now that I got that to work, I have two questions that have arisen.

1. I'm hoping someone could help me navigate using LibreOffice to add quantity, value, category and 'field 1' to the imported inventory items. Is that possible with LibreOffice. I couldn't even figure out how to connect to the Sage 50 database so I am definitely in need of help.
2. In the inventory item list in Sage 50, it is listed in alphabetical order with the 'Item Number" added to the end of the Item Description. Like this; Y-Stainer SS ¾'' STA-YST-001 with the 'STA-YST-001' being the Item Number. I end up with a very long list of items that are hard to navigate. Does anyone have any tips on naming conventions that can help organize my data more efficiently?  

I know those are both big questions! Any advice is very much appreciated...even if it is pointing me to other resources to investigate. 

Thanks for any help you can offer,

Andrew

silverhawk said:

Like this; Y-Stainer SS ¾'' STA-YST-001 with the 'STA-YST-001' being the Item Number. I end up with a very long list of items that are hard to navigate.

Global setting for all users for sorting by item Number vs. Description is under Setup | Settings | Invcentory & Services | Options.

silverhawk said:

Does anyone have any tips on naming conventions that can help organize my data more efficiently?  

Use a short abbreviation (three or four letters) for each manufacturer, and the same length of abbreviation for generic standard hardware, and your own company abbreviation for items you fabricate / brand.

Keep a shared list (excel, some other table) of all the abbreviations and the associated vendors.  Quantum has a built-in vendor <-> inventory reference table.

Sage 50 items are case-sensitive, so Ven STA-YST-001 is different from VEN STA-YST-001.  I've used this to keep track of the vendor for unknown / irrelevant manufacturers.

silverhawk said:

I couldn't even figure out how to connect to the Sage 50 database so I am definitely in need of help.

Entering numerical data through the database is a difficult thing to do, if you aren't fairly familiar with it I wouldn't recommend it.  There are Sage consultants who can do this for you if the list is prohibitively long.