DATABASE STRUCTURE UPDATE
This update requires database structure changes to your company database files. You will be prompted to update the database when the program is first opened after installing the 9.7.0.0 Release. There are at least three databases that will be updated with this update and possible more if you have multiple companies and / or multiple users within each company. Database restructure processing is outlined below:
•BASEGLOB - AccuBuild Global Database - Updated when the AccuBuild Program is opened for the first time by any user. This process is only done ONE time per AccuBuild Program Installation
•BASECOMP - AccuBuild Company Database - Updated the first time a company is opened by any user. This process is done ONE time per Company.
•BASEUSER - AccuBuild User Database - Updated the first time a user logs into a company. This process is done ONE time per User per Company.
Example 1: User Joe is the first user to log into the AccuBuild Program for Company “A” after the update is installed:
•BASEGLOB is updated for new structure changes (one time for global database)
•BASECOMP is updated for new structure changes (one time for Company “A” database)
•BASEUSER is updated for Joe (one time for User Joe in Company “A”)
Example 2: User Sally is the second user to log into the AccuBuild Program for Company “A”:
•BASEUSER is updated for Sally (one time for User Sally in Company “A”)
Note: At this point, Joe and Sally will never be prompted for restructure going forward when they log into Company “A”.
However, if Joe or Sally are have user access to another company in AccuBuild, they will be prompted again for that company.
IMPORTANT - Before any database restructures are done, a backup of the company databases are made to a zip file in the company folder.
ACCOUNTS PAYABLE
•CORRECTION - A/P Invoices
oWhen entering multiple invoices through Payables > Enter/Update Invoices, and the ‘Hold Payment’ option was selected on various invoices, only the first invoice in the batch was maintaining the status. This has now been corrected.
DOCUMENT ROUTING
●ENHANCEMENT - A single document can now be linked to multiple jobs when an invoice contains multiple job/phase entries.[PMID Ref No: 5036]
○On the Document Routing > Document Approval option, enter vendor, invoice number, date, invoice total (optional), choose ‘Waiting for Approval’, but do NOT select a job if your a/p entries will contain multiple job/phase combinations. Save your entry.
○On the grid, right-click on your entry and select Accounts Payable > Enter AP Invoice for Document to enter the invoice details. Save your entry.
○When the invoice is approved and posted, the document will be viewable in all related Job Document Folders.
•ENHANCEMENT - Invoice Date [PMID Ref No: 6982]
oIf the invoice date field on the Document Approval screen is modified after A/P entries have been entered on the record, then the invoice date on the associated A/P entries will be automatically updated with the new date.
INVENTORY
●ENHANCEMENT - Inventory Transfers [PMID Ref No: 4586]
○The Inventory > Inventory Transfers option will now allow inventory to be transferred to a piece of equipment.
○The screen has been enhanced with a new equipment look-up field along with a link to the Equipment Master List.
oIf the entry is linked to a piece of equipment, then the posting process will create an expense record in the equipment transaction table (EQTRANS) with a 55 source code which indicates that the transaction is an inventory transfer. The Equipment field in the general ledger transaction table (GLDETAIL) will also be updated with the equipment number.
oThis process replicates the posting process of transfers made through the Orders > Stock Requests option.
oIn addition to the screen and posting routine changes, the Inventory reports have been converted from Crystal Reports to Report Builder(MAR) and have been enhanced to show the equipment field.
▪IVXFRJNL.291 - Inventory Transfer Journal (batch report)
▪IVREP02A.714 - Historical Transfers - Job Order
▪IVREP02B.714 - Historical Transfers - Date Order
▪IVREP02C.714 - Historical Transfers - Part Number Order
▪IVREP02D.714 - Historical Transfers - GL Expense Account Order
▪IVREP02E.714 - Historical Transfers - Job Order - GL Inventory Account Order
▪IVREP02F.714 - Historical Transfers - Equipment Order
MY ACCUBUILD REPORTS
●NEW REPORT CLASS CODE [PMID Ref No: 4586] - The new report class code 291 was created for the new MAR version of the Inventory Transfer Journal which is the batch report that can be printed from the Inventory > Inventory Transfers screen.
○IVXFRJNL.291 - Inventory Transfer Journal (batch report)
●ENHANCEMENT - The following reports have been converted from Crystal Reports to Report Builder (MAR) [PMID Ref No: 4586]
○IVREP02A.714 - Historical Transfers - Job Order
○IVREP02B.714 - Historical Transfers - Date Order
○IVREP02C.714 - Historical Transfers - Part Number Order
○IVREP02D.714 - Historical Transfers - GL Expense Account Order
○IVREP02E.714 - Historical Transfers - Job Order - GL Inventory Account Order
○IVREP02F.714 - Historical Transfers - Equipment Order.
HELP SYSTEM
●CORRECTION - Help System converted to Web Help [PMID Ref No: 7455]
○Due to recent Microsoft updates, the CHM files that AB was using for the Help System were no longer operable for customers that had AB installed on their local area network. AB has converted the full Help System, including Release Notes, to Web Help. All Help documentation will now be accessing files located on the AB Cloud.
ACCUBUILD MOBILE
•NEW FEATURE [PMID Ref No: 3620] - Transform TimeCardWorksheet Entries - Night work shifts that would start on the evening of day one and finish the morning of day two, were posting into the time card records with only the date that the employee started the work. California labor law requires that the time card records reflect the individual days. The work around to this was to enter a separate Daily Report for each day with the hours as they related to the individual day but this was causing issues with Production Item entries. To make this a single step process, a new Advanced Setting has been created called ‘TransFormTCWorksheetEntriesOnImport’ that will be triggered when the Import option is selected on the Time Card Worksheet. This process will split the Hours Worked among the involved phases and update the Work Date if needed. Audit trail tables are saved in the user’s folder if needed. This feature will maintain a single time card worksheet entry (and, therefore, a single DFR entry) and will not adversely affect Production Item entries. This option only works in conjunction with time that is entered through AccuBuild Mobile with a start / stop time and Stop Date where the Stop Date is not the same as the Start Date.
oSetup - This setting must be enabled in the System Administrator > Advanced Setting screen.
●Transform TimeCardWorksheet Entries - Audit Trail
○These tables are created in the user’s directory each time that time is imported from the Time Card Worksheet and the TransFormTCWorksheetEntriesOnImport Advanced Setting is enabled. These tables are overwritten with the current import records each time that the import process is selected.
■TCW_TRANSFORM_IN - This is a copy of the original time card worksheet entries before import.
■TCW_TRANSFORM_OUT - This table holds the transformed time card worksheet entries as they will be posted to the timecard batch.
○Copies of the two tables listed above are saved in the user’s directory each time that the import time card worksheet function is selected.
■The filename will include the UserRecordID and the datetimestamp of when the import was processed.
JOB COST
•CORRECTION - Production Units by Phase [PMID Ref No: 4753]
oThe Production Units by Cost Phase screen had an issue that when you typed in a phase that ended in zero (ie. 12100.00) and you moved off the field, then the cursor would jump to the bottom of the grid instead of going to the selected phase. This behavior has been corrected in this release.
•ENHANCEMENT - Job Estimate Detail Table Audit Trail [PMID Ref No: 6946]
oThe audit trail table (TableAudit) on the Job Estimate Detail table (EstDetl) has been enhanced to include all fields (except memo field types) in the record of field changes. This was specifically upgraded to help troubleshoot changes to Production Unit Bid Item and Task Item numbers.
ORDERS
●CORRECTION - Order Invoices [PMID Ref No: 6796]
○When entering invoices through Orders > Invoices, the Invoice Subtotal, Sales Tax and Accrued Tax amounts were being cleared when the ‘Hold Payment’ option was selected, and the amounts would need to be re-entered. This has been corrected in this release.
●ENHANCEMENT - PO Line Items Screen Filter Options [PMID Ref No: 7243]
oThe columns that appear at the top of the Orders > Line Items Tab now contain filter options:
■Click on the down arrow on a column to set a filter for a specific value
■Click on the column header to change the sort order.
o.A popup grid titled ‘PO Line Items’ has been added to help you search and locate a specific line on a PO.
■Access the popup grid from the Line Item Tab Grid:
●Double click on a item
●Press the ‘D’ key on a item
●Press the Display Item Grid button
■The popup screen can remain open while you perform multiple searches.
■When you click on a line item in the popup grid, the corresponding item on the Line Items Tab will be selected.
■Double click on an item in the popup grid to close the grid. The grid will close automatically if you close the PO screen.
■Data in the popup grid is read only.
■Grid Filter Options:
●Group by dragging the column up to the top of the grid.
●Click on the down arrow on a column to set a filter for a specific value.
●Click on the column header to change the sort order.
ACCUIMPORT
●NEW FEATURE [PMID Ref No: 5559] - A new import utility is available for purchase. The new AccuImporter.exe is an enhancement of the original AccuImport.exe tool which allows A/P Invoices, General Journal Entries and Equipment Journal Entries to be imported into a user batch. The new version of the utility will eventually allow the user to import a variety of master table records into AB from a spreadsheet, but as of this release, only union codes may be imported.
○Union Code Import - This option was designed to facilitate the update of union code rates. An existing list of the current rates will be exported into a spreadsheet via an AB data view (this contains the specific layout needed) and then the union rates and effective dates can be updated in the spreadsheet. The revised spreadsheet data can then be imported into AB using the new AccuImport program. This process will behave similarly to the ‘Update Rates’ process found on the Union Screen without the need to edit and save each individual record. The current set of codes in the Unions table will be hidden from the ‘Current’ view and the imported codes will be flagged as the current codes. Refer to the AccuImport documentation for complete instructions for using this tool.
License & Training: This option requires a serial number and training before a customer can use this feature. Please send an email with a subject of:
“AccuImport: Union Codes” to steam@accubuild.com for licensing and training details.
Unions View: A Company database view that contains the current union codes in Unions.adt. This View can be accessed once an authorized accubuild database administrator has created the view in the database.
Export View to Excel:
●Once the View is installed, log into Accubuild as an Administrator
●Select: File > System Administrator > Company Inquiries
●Select View: vw_Unions_Current.
●Click Open
●Select File > Export > Excel
●Edit Excel File before Import:
●Open the spreadsheet and modify the rates and effective dates as desired
a.Delete any codes that won’t be modified
b.Set a unique effective date for rates that will be imported to the Unions table. If any are the same, the post routine will fail.
●To avoid any mapping issues, do not change any Column names in the first row in Excel! If you accidentally change a column name, start over and export the data once again using the company view to ensure the proper column names are used.
a.Save Time and Auto Map Fields: The Unions table import contains between 50 - 100 fields depending on your benefits schedule and mapping each individual field can be time consuming.
●Be sure to close the spreadsheet before you try importing the data otherwise an error will occur.
Launch AccuImport:
Dashboard > Program Links:
●Create a shortcut to the AccuImport program
○Right click on the Program Links ; choose ‘add program link’
○Browse the file system and locate AccuImporter.exe in the Accubld9 folder.
○Click OK
●Use the Dashboard > Program Links shortcut to launch the AccuImport
Using the AccuImport Utility
●Step 1: Select Company and User
●Step 2: Select Excel File
○Browse the file system and locate the Excel file. Be sure the spreadsheet is closed before attempting to import the data.
●Step 3: Select Target Table
○Choose “Import Union Codes”
■The step will also ensure that the Union_Batch file is empty before import.
●Step 4: Map Fields
○The mapping of each field from the spreadsheet into the Union Master Table has already been completed for you.
●Step 5 Export to User Batch:
○Click Export to Batch.
■The button will only be enabled if all fields are green and mapped.
AccuImport Posting Process and Audit Trail
●The posting routine includes inserting the data into a user batch and then updating the master table with the new data.
●A series of tests are performed before committing the new records into the master table.
●An audit trail of the posting process is created in a table called ‘AccuImport_PostLog’ which is located in the user directory.
●Post 1: User Batch. This takes the XLS Reader and inserts the data into a User Batch table named Union_Batch.
●Post 2: After the data is inserted, a series of tests are performed and a backup of the data is created:
a.Union_Batch Duplicates - Tests the user’s Union_Batch file for any duplicate union codes that may have been imported from the spreadsheet
i.Delete any duplicate records from the spreadsheet and then import once more.
ii.If no duplicate codes are found in the spreadsheet, then Post 2 will begin.
b.Union Batch - Counts the number of records in the imported data for comparison with the final posted data.
c.Test for Union Master Duplicate - Tests that the master Unions.adt table does not contain duplicate union codes (This is a combo of the Union code and Effective Date).
i.Call AccuBuild Support if you encounter this error.
d.Backup Data - This step creates a backup of the Unions.adt table before any changes are made to the table.
i.The audit trail logs the name of the backup file.
ii.The backup file will be saved in the user’s directory.
e.Union Batch vs Unions Type 1 - This step looks to see if the master union table (Unions.adt) already contains the same union code / effective date combo that are being imported from the batch.
i.If the combo already exists in the union table, then the posting process will fail and a comparison of the master table and the batch table will be displayed on the screen.
ii.To fix this issue, change the Effective Date on your spreadsheet records and then import once more.
f.Batch Posted Successfully - This step is logged when the batch posting does not fail on any of the prior steps. This step also contains a count of the records that were posted.
Technical Notes on Data Field Mapping:
The AccuImport.ini contains sections for each import type: Copy the Following Section for [UNION_BATCH]. This will set the field mapping and remember the next time the AccuImport is launched for the current User Database.
[UNION_BATCH]
UnionCode=UNIONCODE
UnionDescription=UNIONDESCRIPTION
Report Format#=REPORT FORMAT#
DepartmentCode=DEPARTMENTCODE
MasterUnionCode=MASTERUNIONCODE
Hourly Rate (Reg)=HOURLY RATE (REG)
Hourly Rate (@1.5)=HOURLY RATE (@1.5)
Hourly Rate (@2.0)=HOURLY RATE (@2.0)
Hourly Rate @ 3.0=HOURLY RATE @ 3.0
Vac Rate(Reg)=VAC RATE(REG)
Vac Type (REG)=VAC TYPE (REG)
Vac Rate(@1.5)=VAC RATE(@1.5)
Vac Type (@1.5)=VAC TYPE (@1.5)
Vac Rate (@2.0)=VAC RATE (@2.0)
Vac Type (@2.0)=VAC TYPE (@2.0)
Vac Rate (@3.0)=VAC RATE (@3.0)
Vac Type (@3.0)=VAC TYPE (@3.0)
Ded1 Rate (REG)=DED1 RATE (REG)
Ded1 Type (REG)=DED1 TYPE (REG)
Ded1 Rate(@1.5)=DED1 RATE(@1.5)
Ded1 Type (@1.5)=DED1 TYPE (@1.5)
Ded1 Rate(@2.0)=DED1 RATE(@2.0)
Ded1 Type (@2.0=DED1 TYPE (@2.0
Ded1 Rate (@3.0)=DED1 RATE (@3.0)
Ded1 Type (@3.0)=DED1 TYPE (@3.0)
Ded2 Rate (REG)=DED2 RATE (REG)
Ded2 Type (REG)=DED2 TYPE (REG)
Ded2 Rate (@1.5)=DED2 RATE (@1.5)
Ded2 Type (@1.5)=DED2 TYPE (@1.5)
Ded2 Rate (@2.0)=DED2 RATE (@2.0)
Ded2 Type (@2.0)=DED2 TYPE (@2.0)
Ded2 Rate (@3.0)=DED2 RATE (@3.0)
Ded2 Type (@3.0)=DED2 TYPE (@3.0)
Ded3 Rate (REG)=DED3 RATE (REG)
Ded3 Type (REG)=DED3 TYPE (REG)
Ded3 Rate (@1.5)=DED3 RATE (@1.5)
Ded3 Type (@1.5)=DED3 TYPE (@1.5)
Ded3 Rate (@2.0)=DED3 RATE (@2.0)
Ded3 Type (@2.0)=DED3 TYPE (@2.0)
Ded3 Rate (@3.0)=DED3 RATE (@3.0)
Ded3 Type (@3.0)=DED3 TYPE (@3.0)
Pay1 Rate (REG)=PAY1 RATE (REG)
Pay1 Type (REG)=PAY1 TYPE (REG)
Pay1 Rate (@1.5)=PAY1 RATE (@1.5)
Pay1 Type (@1.5)=PAY1 TYPE (@1.5)
Pay1 Rate (@2.0)=PAY1 RATE (@2.0)
Pay1 Type (@2.0)=PAY1 TYPE (@2.0)
Pay1 Rate (@3.0)=PAY1 RATE (@3.0)
Pay1 Type (@3.0)=PAY1 TYPE (@3.0)
Pay2 Rate (REG)=PAY2 RATE (REG)
Pay2 Type (REG)=PAY2 TYPE (REG)
Pay2 Rate (@1.5)=PAY2 RATE (@1.5)
Pay2 Type (@1.5)=PAY2 TYPE (@1.5)
Pay2 Rate (@2.0)=PAY2 RATE (@2.0)
Pay2 Type (@2.0)=PAY2 TYPE (@2.0)
Pay2 Rate (@3.0)=PAY2 RATE (@3.0)
Pay2 Type (@3.0)=PAY2 TYPE (@3.0)
Pay3 Rate (REG)=PAY3 RATE (REG)
Pay3 Type (REG)=PAY3 TYPE (REG)
Pay3 Rate (@1.5)=PAY3 RATE (@1.5)
Pay3 Type (@1.5)=PAY3 TYPE (@1.5)
Pay3 Rate (@2.0)=PAY3 RATE (@2.0)
Pay3 Type (@2.0)=PAY3 TYPE (@2.0)
Pay3 Rate (@3.0)=PAY3 RATE (@3.0)
Pay3 Type (@3.0)=PAY3 TYPE (@3.0)
Benef1 Rate(Reg)=BENEF1 RATE(REG)
Benef1 Type=BENEF1 TYPE
Benef1 Rate (@1.5)=BENEF1 RATE (@1.5)
Benef1 Rate (@2.0)=BENEF1 RATE (@2.0)
Benef1 Rate (@3.0)=BENEF1 RATE (@3.0)
Benef1 Description=BENEF1 DESCRIPTION
Benef1 CPR=BENEF1 CPR
Benef2 Rate(Reg)=BENEF2 RATE(REG)
Benef2 Type=BENEF2 TYPE
Benef2 Rate (@1.5)=BENEF2 RATE (@1.5)
Benef2 Rate (@2.0)=BENEF2 RATE (@2.0)
Benef2 Rate (@3.0)=BENEF2 RATE (@3.0)
Benef2 Description=BENEF2 DESCRIPTION
Benef2 CPR=BENEF2 CPR
Benef3 Rate(Reg)=BENEF3 RATE(REG)
Benef3 Type=BENEF3 TYPE
Benef3 Rate (@1.5)=BENEF3 RATE (@1.5)
Benef3 Rate (@2.0)=BENEF3 RATE (@2.0)
Benef3 Rate (@3.0)=BENEF3 RATE (@3.0)
Benef3 Description=BENEF3 DESCRIPTION
Benef3 CPR=BENEF3 CPR
Benef4 Rate(Reg)=BENEF4 RATE(REG)
Benef4 Type=BENEF4 TYPE
Benef4 Rate (@1.5)=BENEF4 RATE (@1.5)
Benef4 Rate (@2.0)=BENEF4 RATE (@2.0)
Benef4 Rate (@3.0)=BENEF4 RATE (@3.0)
Benef4 Description=BENEF4 DESCRIPTION
Benef4 CPR=BENEF4 CPR
Benef5 Rate(Reg)=BENEF5 RATE(REG)
Benef5 Type=BENEF5 TYPE
Benef5 Rate (@1.5)=BENEF5 RATE (@1.5)
Benef5 Rate (@2.0)=BENEF5 RATE (@2.0)
Benef5 Rate (@3.0)=BENEF5 RATE (@3.0)
Benef5 Description=BENEF5 DESCRIPTION
Benef5 CPR=BENEF5 CPR
Benef6 Rate(Reg)=BENEF6 RATE(REG)
Benef6 Type=BENEF6 TYPE
Benef6 Rate (@1.5)=BENEF6 RATE (@1.5)
Benef6 Rate (@2.0)=BENEF6 RATE (@2.0)
Benef6 Rate (@3.0)=BENEF6 RATE (@3.0)
Benef6 Description=BENEF6 DESCRIPTION
Benef6 CPR=BENEF6 CPR
RecType=RECTYPE
EffectiveDate=EFFECTIVEDATE