• Registered: 01/08/2009
  • Posts: 16

Topic: Removing an item from the selection list, or changing an item code

Now that I have been using FrontAccounting for a while, I have created a small problem for my company.

We sell many products, and the list of products has grown to the point where users are missing existing products and creating new, duplicate products. As a result, I have decided to institute a product code policy that should eliminate the creation of duplicate products by making existing products easier to find. My problem now is that I can't simply rename the item codes for existing items, and I can't delete the items because they've been sold.

I am sure I  am not telling anyone anything they don't already know.

However, I am not so far gone that if I could find a clean, back-door, method of renaming item codes for existing items, I would be ok.

So my question is, can anyone help in any of these ways:

1. Has anyone written a script that will update all occurrences of an item code with a replacement item code?

2. Provide me with all the tables that contain the item codes so that I could create a script? Also any caveats in creating such a script (I envision a simple series of update table_name set item_code='newid' where item_code='oldid', but things often aren't that simple.)

3. Can someone offer me another solution? I really want to get these existing products fixed and implement the new naming system before the list gets unmanageable.

Thanks
Rob

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Removing an item from the selection list, or changing an item code

One solution would be to export all the company by company backup.

There is a freeware program called 'Replace'em, I guess it was named. Google the internet. There might be other similar free tools.

Here you can make a list of strings with old strings (item codes) and replacement strings (new item codes). This will only work if there are differences between all the original strings and the new strings. Remember also to use exact match.

Then you can run a replacement of all the values.

When you are ready and the created script looks correct, you can upload and restore the company again.

Because there are key constraints on the item_code in many places it is rather difficult to create a script to handle this.

The procedure above will work and you are sure you get all the values correct.

/Joe
BTW. Remenber to backup your company with another script before you begin, should it go wrong.

  • Registered: 01/08/2009
  • Posts: 16

Re: Removing an item from the selection list, or changing an item code

This sounds like an elegant solution. Essentially the same thing as updating the tables in the database...

Thanks!