Apex Customer Portal Apex Customer Portal
Forums Pictures Articles Downloads
Advanced purchasing for Electrical, Plumbing, and HVAC contractors
Register Active Topics Members Search Bookmarks FAQ
Save Password Forgot your password?
 All Forums
 SQL Query Recipes
 Recipe: Add unknown commodity codes
 Printer Friendly Version  
Author Previous Topic Topic Next Topic  

131 Posts
Posted - December 05 2007 :  6:12:21 PM  Show Profile Send a private message to admin
Trade Service frequently changes their commodity codes. If you are receiving TRA-SER CDs you can update your commodity code list by using the Apex File / Utilities / Load Commodity Codes selection and finding a file named "Commod.txt" on the CD. Not every CD contains a Commod.txt file -- only when the commodity code list changes.

If, however, you are loading your TRA-SER updates via email or online it is unclear how you will get the new commodity codes. This recipe provides an interim solution.

The following recipe will add commodity codes that are used in your dbItem data that do not appear in your commodity code list. Since there is no way to tell what they are supposed to represent they are given a description of "(Unknown)". Once you figure out what they are used for you can update the list using the Commodity Code Maintenance selection in Apex.

Once the new commodity codes have been added you can search for them properly on the Material Search screen, and the db Items that use those codes will come up.

SQL Query Recipe

insert into CommCd (CommCd, CommDesc)
select distinct CommCd, ' (Unknown)' from PFMS
where not exists (select CommCd from CommCd where CommCd.CommCd = PFMS.CommCd)

Authorization Code: ED42

Jump To:

Set as your default homepage Add favorite Privacy   2017 Vulcan Software LLC All Rights Reserved.   Go To Top Of Page