Welcome to Auto Build Assemblies, the program that automatically builds your QuickBooks assemblies and updates your item costs and prices for you. Members of the QuickBooks Community have long searched for a “Save & Build” feature in the QuickBooks User Interface to make building assemblies easier as well as an easier way to update the Cost Block and Sales Price fields. Especially important to users who work with assemblies is to provide an automatic way of making the Cost Block reflect the Total Bill of Materials Cost. This program goes one step further by building all assemblies in a batch according to a schedule that you enter beforehand in an Excel spreadsheet and providing a means for updating your costs and prices according to criteria you specify. Entering information in Excel is quick and easy and saves you from the tedium of manually entering information into QuickBooks one item at a time. If you always "build back to zero" on your assembly quantities, the setup is even easier because you only have to set up the Excel spreadsheet once and then never update it again. It can handle multi-layer assemblies (i.e. assemblies built out of other assemblies, which must be built first) an infinite number of layers deep. You can set this program to run after hours so you don’t tie up valuable computing power during the day with the processor intensive task of building assemblies. You can also configure the program to build as many assemblies as it can (i.e. as many as your inventory allows) even if it is less than what you requested. A comprehensive report is generated in Excel that summarizes the results of the build attempts as well as all modifications to cost and price. This program runs as a “hands off” utility that, when configured using the Scheduled Tasks feature in Windows, allows the program to start, connect to QuickBooks, build the assemblies, save the reports to Excel, and close down, all with no intervention from the user.
The installation process takes care of everything you need to run this program on your computer. In addition to installing the Auto Build Assemblies program itself, it will install the QuickBooks Foundation Class, which is the software link provided by Intuit that allows communication by third party programs such as this with your QuickBooks data file. Separately, you will also need a copy of QuickBooks Premier or Enterprise. You will also need a copy of Microsoft Excel. Lastly, you will need to be logged in as the “Admin” user the first time this program is executed. That is necessary because only the Admin user can authorize the connection of a new piece of software to the QuickBooks data file. Once this connection is allowed, Auto Build Assemblies can connect via a user with reduced access privileges. In a client/server environment, this program needs to be installed on the client machine (i.e. workstation).
The trial version is available for download and can be run as many times as you wish. It is limited in that it will only complete the builds for two assembly items. The trial version can be converted to a full version through the purchase of a registration key. No new software needs to be downloaded.
First Run When you start up Auto Build Assemblies for the first time, the easiest thing is if you have QuickBooks already running with a company file open. This way Auto Build Assemblies can simply connect by default to the open file. Alternatively, you can click on the "Change QuickBooks File" button near the top of the interface screen to specifically select the QuickBooks file to open. Whichever file Auto Build Assemblies connects to will be stored so that that file will become the default. You can change this default file at any time via the "Change QuickBooks File" button. After establishing the connection with your QuickBooks file, Auto Build Assemblies will see that a Schedule file has never been created and will automatically create one. After it creates the file, it will report that the file is empty.
Authorization During Initial Connection to QuickBooks When Auto Build Assemblies makes its first attempt to connect with your QuickBooks file, QuickBooks will display a screen asking if you would like to grant access to Auto Build Assemblies. You will only get this screen, incidentally, if you are logged in to QuickBooks as the Admin user. If you are any other user, Auto Build Assemblies will report that it simply failed to connect. The reason is that only the Admin user has the authority to grant access permission. The screen you will see is the one shown a few paragraphs down under the Auto Run section. You select the level of access you would like to grant to Auto Build Assemblies.
Two Pages of the Auto Build Schedule file The Auto Build Schedule file, which is automatically created on first run, is comprised of two pages. The names of these pages can be seen by looking at the tabs near the bottom of the screen. The first page is called the Build Page and it is where all the build information is entered. The second page is called the Cost and Price Page and it is where all the information to update item costs and prices is entered. You can leave either of these two pages unused if you so desire. If you leave them both unused, Auto Build Assemblies will report that the Schedule file is 'empty'. Auto Build Assemblies makes all updates entered on the Cost and Price Page before generating any builds entered on the Build Page. If you are leaving the Build Page blank and are only updating the cost and/or price, then the Pro version of QuickBooks may be used. This is true even if the company file has assemblies in it (Note: The Pro version can work with assemblies, it just can't build them). These pages are described in more detail below.
Overview The Build Page lists all of the assemblies in your company file. In order to build any assemblies, you will need to put a number in either the Quantity to BUILD column or the Quantity to HAVE column next to the name of the assembly you want built. If there is no number in either of those cells, no action will be taken for that assembly. All of the other fields - Custom Date, Custom Memo, both of the Configuration Checkboxes, and the Build Order column - can be left blank.
Quantity to BUILD Enter a number in this column if you specifically want to tell QuickBooks how many of a particular assembly you want built, regardless of how many are currently in stock.
Quantity to HAVE Enter a number in this column if you specifically want to tell QuickBooks how many of a particular assembly you want to end up with. One common way this is used is to enter all zeros into this column. Auto Build Assemblies will then instruct QuickBooks to build just enough to bring the quantity back up to zero.
Build Order Entering anything in the Build Order column is entirely optional. If it doesn’t matter which items get built first, then leave the column completely blank. Otherwise, enter any positive integer to specify the relative order of the builds. Lower numbers are built before higher numbers. Multiple items can have the same Build Order number if it doesn’t matter which items get built first within that group. Blanks are built last and gaps in the numbering are allowed. One thing to bear in mind regarding the build order is that, because of the "drill down" capability of Auto Build Assemblies, if a certain quantity of a lower level assembly is required to build a higher level assembly, and if you don't have any of those lower level assemblies in stock but you have enough component parts to build them, Auto Build Assemblies will automatically build those assemblies in the needed quantity in order to complete the higher level build. If you have also entered a quantity to build for the lower level assembly separately in the Auto Build Schedule spreadsheet, that quantity of the lower level assembly will be built in addition to those needed for the higher level build if the higher level build was performed first.
Build Date & Build Memo The default build date is ‘today’. To change this, enter the keyword 'yesterday', 'tomorrow' (without the quotes), or any other valid date in cell B3. The default build memo is a date/time stamp. To change this, enter anything else in cell B4.
Build to Build Point If you want to build up to the quantity entered in the Build Point field for each assembly item in QuickBooks, simply check the top checkbox in cell B5. With this box checked, you may leave any numbers you have in either the Quantity to Build column or the Quantity to Have column as these numbers will be ignored if building to the Build Point. Most users will have no need for this Build Point option as the Build Point has limited usefulness within QuickBooks itself and users can obtain the same functionality using the Quantity to Have column. It is included here only for those companies currently using the Build Point and wish to continue to do so.
Enable Building Fewer Than Requested If you want to enable Auto Build Assemblies to build as many as possible (i.e. as many as the current inventory levels allow), even if this number is less than the number you requested, check this box. For example, if you specify that you want 5 of a particular assembly built, via either the Quantity to Build or the Quantity to Have methods, and you have only enough materials to build 3 assemblies, if this box is unchecked, no assemblies will be built, and if it is checked, 3 assemblies will be built. In either case, the results will be recorded to the log file.
Pending Builds Auto Build Assemblies does not create pending builds.
COST AND PRICE PAGE
Overview This page allows you to modify the cost and/or price for all service, inventory, non inventory, other charge, or assembly items in your company file based on certain criteria. The top portion of the page gives you choices for criteria selection by item type. The lower portion of the page (starting at row 14), lists each of those items from your company file along with the item type, and current values for Average Cost, Cost Block, Total Bill of Material Cost, and Sales Price. They reflect the status of your company file at the time the Auto Build Schedule was generated. By default, they are updated at runtime to reflect changes made by Auto Build Assemblies. If you would like them to remain static, check the checkbox marked 'Leave these numbers static'. Regardless of whether or not you choose to make these numbers static, live data from QuickBooks will always be used if you select to make the Cost Block equal to either the Average Cost or the Total Bill of Materials Cost or make the Sales Price a markup of either the Cost Block or the current Sales Price. Excel formulas can be added in the Specified Cost and Price cells to reflect the specific needs of your company. You can also resort the rows below row 14 by any criteria you choose. The only requirements are that the Item Name remain unchanged and there are no blank rows.
Cost and Price Page (top portion)
Cost and Price Page (bottom portion)
Average Cost Precision There is an anomoly in the QuickBooks Foundation Class interface (the software link provided by Intuit that allows communication with QuickBooks) that requires an explanation. The Average Cost is one of the numbers read from QuickBooks that is listed on the Cost and Price Page of the Auto Build Schedule file. QuickBooks allows this number to be up to 5 decimal places. However, during the initial main data read, the Foundation Class software inexplicably truncates this number, and only this number, to 2 decimal places (i.e. if Average Cost = 1.23456 it would be read as 1.23). In order to get the full 5 decimal places, each item must be reread individually, which can add a significant amount of time to the creation of the Schedule file, depending on such factors as the speed of your computer and the number of items in your company file. There is no difference in performance during normal execution of Auto Build Assemblies, only during the creation of the Schedule file. Auto Build Assemblies gives you the choice of whether you want 2 decimal precision (faster) or 5 decimal precision (slower) at the Auto Build Schedule confirmation screen. The only reason Auto Build Assemblies does not automatically retrieve the number with high precision for all users is because that would slow down the schedule file creation process for all users even if the precision of this number is not important. Whichever method you choose only effects the Current Average Cost as it appears on the Cost and Price Page of the Auto Build Schedule spreadsheet. If you select to make the Cost Block equal to the Average Cost for either Inventory or Assembly items, the full 5 decimal number will be copied. The schedule file that is automatically created on first run of the program uses 2 decimal precision. In order to get 5 decimal precision, you will need to regenerate the Auto Build Schedule file, by clicking the appropriate button on the Auto Build Assemblies runtime screen, and select 5 decimal precision.
Formatting in Excel The rows in either page of this file can be rearranged, for example by using the Excel sort command, in any manner to suit the needs of your business. Standard Excel formatting methods can be applied to the cells including changing the font, adding color, adding borders, etc. to make navigating this file easier on a daily basis. Blank lines between items, however, are not permitted.
Modifying the Assembly Item List in QuickBooks If you add or rename any assembly items in your QuickBooks file, it is generally a good idea to regenerate the Auto Build Schedule spreadsheet file by selecting the appropriate button on the Auto Build Assemblies user interface screen when the program starts. If the change is small, you may also retype the item name in the Auto Build Schedule spreadsheet exactly as it appears in QuickBooks. No builds will be generated as a result of generating a new schedule file. A backup of any existing Auto Build Schedule file will be saved to the same folder under the name Auto Build Schedule_backup.xls. Auto Build Assemblies is as forgiving as possible with regard to any discrepancies it finds between the assembly item list in the Auto Build Schedule spreadsheet and the list it find in QuickBooks. For example, if you are building all items to zero (i.e. you have all zeros in the Quantity to Have column) then Auto Build Assemblies assumes that you want to do the same with any new items it finds. However, if you have entered anything in the Build Order column, the program must individually map the Build Order numbers item by item, which can add to the execution time of Auto Build Assemblies. If you are building to zero and are not using the Build Order feature, then you should not see any appreciable difference in execution time.
Log File This program will put a report on the screen as it runs and will also save an Excel report in a folder named C:\Users\Public\Public Documents\Auto Build Assemblies\Log\ (or C:\Program Files\Auto Build Assemblies\Log\ in Windows XP). The file will be named using a date/time stamp. The first file saved for the day will only get a date stamp. If more than one file is saved in the same day, then each file after the first will get a time stamp added to the filename to make it unique. The first worksheet in this log file lists the names of all assembly items along with the number that were built and the number that could not be built for each item. If there were some that could not be built, then the first (there may be more than one) missing item is listed for that assembly. If the schedule file specified changes to the cost or price, there will be additional pages in the log file identifying which items were changed as well as the old and the new values.
Setting up Auto Build Assemblies to run automatically during off hours can be the ideal way to use Auto Build Assemblies. Especially in a multi-user environment, where others are depending on QuickBooks being as responsive as possible, relegating the processor intensive task of building assemblies to off hours will render your system faster during the times when your users need the best possible performance. QuickBooks does not need to be running when Auto Build Assemblies is executed, as the program can start up QuickBooks and connect to your default company file automatically. This is helpful in that, if you have an automatic backup service that is expecting the QuickBooks company file to be closed in order to back up successfully, you can simply schedule the two operations far enough apart to ensure that the one is complete before the other starts.
To authorize Auto Build Assemblies to connect with QuickBooks even if QuickBooks is not running, select the radio button that reads "Yes, always; allow access even if QuickBooks is not running" when QuickBooks presents you with the following screen upon first connection (you will have to be logged in as Admin at this point) and then specify the login user you want Auto Build Assemblies to use on future logins. This user does not have to be the Admin user and access privileges can be minimal. You can leave the box unchecked regarding access to personal data.
If you would prefer to not give Auto Build Assemblies the authority to connect to your QuickBooks file even if QuickBooks is not running, then you can select the radio button that reads "Yes, whenever this QuickBooks company file is open". Then you just need to make sure that QuickBooks and your company file are open when you run Auto Build Assemblies.
After you have established the authorization level in QuickBooks, the next step is to schedule Auto Build Assemblies to run in your absence. This setup is performed in Windows, not in Auto Build Assemblies. To set this up, you have to open the Task Scheduler in Windows. To do this, right click on the Windows icon in the lower left of the screen, then click on Computer Management, then Task Scheduler. Go through the steps to Create Basic Task. When you select the task, you can simply select the desktop icon for Auto Build Assemblies. Bear in mind that if you schedule the program to run after midnight, the build date will default to that next day. If you want the build date to be the previous day, enter the keyword "yesterday" in the Custom Date field, cell D3, on the Settings Page of the Auto Build Schedule spreadsheet.
The company file that Auto Build Assemblies will connect to is the same file that it connected to the last time the program ran. The only way to change this file is to run Auto Build Assemblies with QuickBooks open and another company file open. This will "reload" this default connection file with the new file location.
With the QuickBooks authorization established, Auto Build Assemblies scheduled as a scheduled task, and the company filename you want Auto Build Assemblies to connect to stored as the default, the only thing left to do is to make sure the Auto Build Schedule spreadsheet is configured so Auto Build Assemblies will perform the actions you want. Then leave this spreadsheet, as well as Excel itself, closed. At the scheduled time, Auto Build Assemblies will start up, start QuickBooks, open the company file, establish the connection, access the Schedule spreadsheet, execute the builds and/or other tasks, save the build report to the log file, and shut down all with no interaction from the user. You will arrive in the morning and all the builds will be complete with a log file saved.
Help is available anytime at no charge.
Sales & Technical Help: firstname.lastname@example.org or 765-702-3031