|
          |
 |
|
 |
 |
|
|||
 |
|
Sunday, February 17, 2008
Tip: Creating a secure "signed" check formLast Tested with Sage Master Builder Version 13.2.0.3With our larger clients, there can be hundreds of checks generated at a time for both payroll and accounts payable. Naturally, people want to create a digitally signed check form to avoid having to physically sign the checks, but there are a few technical challenges to overcome to create a signed check form that is reasonably secure. Like physical signature stamps, your signed check form can be “locked” away to help protect it from unauthorized use. With a little bit of set up, it is easy to add a signature to a check format that only the appropriate people can print. Creating the Check Form First, to get a good digital image of a signature, create the signature with a black felt tipped pen – a fine point Sharpie is perfect for this – on plain white paper. Sign a little larger than would be normal. A digital signature will maintain its quality much better if it is reduced from its original size when used on a form than if it is expanded from its original size. Scan the signature page with any reasonable scanner. Using a simple graphics software tool – Microsoft Paint works perfectly well – open the scanned image and crop it so that there no extra white space around the signature. Save in “Monochrome Bitmap (*.bmp; *.dib) format. Now create your SMB check forms. To use the signature bit map (signature.bmp in this example) in your check forms, you will need to copy the file to four locations: X:\MB7\Report Forms X:\MB7\BMP C:\MB7\Report Forms C:\MB7\BMP where X:\ is the network drive where your Master Builder data resides. (See tip on Image Files in Report Designer.) Modify or create the new check forms using the signature file as normal in Master Builder’s form designer. Make sure to force the signature image to be proportional with the “proportion” icon in the form designer. To Secure the Check Form With Master Builder, the only thing we need to secure is the actual digital image of the signature. This is done using the workstation and network level file security. Have your network administrator set rights on the specific “signature.bmp” file so that only the people that should be able to print checks can see or access the file in the following locations on the network. X:\MB7\Report Forms\signature.bmp X:\MB7\BMP\signature.bmp Secondly, set up local users for the people that should be able to print checks on the workstations where the checks will be printed and set the security on the file “signature.bmp” again so only those that should be able to print the signed checks can see or access the files. C:\MB7\Report Forms\signature.bmp C:\MB7\BMP\signature.bmp If a user that does not have access to these files tries to print the signed check form from Master Builder, the form will print, but the signature will not appear. Important Things to Note Using this method to secure a check form, there are a few things to remember. - The secure check forms can only be edited on workstations (computers) that have the digital signature copied to their local drives. - Only someone that has network and local workstation rights to see the digital signature will be able to edit the check forms. - Only someone that has network and local workstation rights to see the digital signature will be able to print signed checks. (law)
posted by Web Master
at
11:16 AM
0 Comments
Saturday, January 19, 2008
Tip: Printing Large Quantities of W2 FormsWe have quite a few clients that print hundreds of W2’s for their employees. Using Adobe to print the W2’s makes the process work better and avoids the hassle of collating hundreds of forms. 2) Print the W2’s to the screen and tie-out the totals page with your 941’s. 3) When ready, print the W2’s to a PDF file. You can use the Master Builder PDF printer, but I like the full Adobe Acrobat product better. Check the final page and make sure it matches the values you used in step 2. 4) Print the W2’s from Adobe Acrobat or the Adobe Acrobat Reader (a free download from Adobe). When you print, print six copies (the number of parts in each set of W2’s) without collating. The result you want is page 1 (the first two employees) to be printed six times, then page 2 printed six times (employees three and four). Be careful here as some printers have their own collating capabilities. Check the printer properties and turn this feature off if it exists before you print. This process has the advantage of retaining a complete exact electronic copy of the forms you sent out. Also, it is much easier to re-print a single or group of employees from this document should you need to do so by selecting specific pages to print. (law) Labels: master builder tip W2 printing payroll
posted by Web Master
at
10:50 AM
0 Comments
Thursday, January 17, 2008
Bug: Accounts Receivable Invoice Listing FailsBug: Last tested with Master Builder version 13.0.2.3Here is one to file under bizarre and cool. We ran into this one yesterday at a client that just upgraded to Master Builder version 13. This bug seems to have been introduced with version 13 of Master Builder and, believe me, it was not easy to find! In some circumstances, the accounts receivable reports 3-1-2-21, 22, etc. will not print a complete list of invoices for a job or a group of jobs. If you print the report for just a single job, you can get the error message “The page number you entered to print does not exist for this report.” When we encountered this situation where an AR invoice listing mysteriously stopped without completing, we did all of our standard testing. We reindexed the files (7-4); we replaced all of the .cdx files and reindexed again; we rebuilt the data tables; we ran our own program that can test for many other issues we know can exist in a Master Builder data set. We checked for the know issues with special characters (Sage KB1725) and damaged report files. Nothing worked. Finally, we discovered that the problem was related to the long name of the job as defined in the job screen (3-5). Out testing revealed that the AR list would stop on the page just prior to the page that a specific job would have printed on. If we printed just the identified problem job, we got the “The page number you entered to print does not exist…” error. Once we identified the problem was a specific job, we could narrow our search for the problem. It turned out that the problem was in the name. By changing the long name of the job in the job set up screen (3-5), we found we could get the report to print. This effect was repeatable. Use the original name – the report fails. Use a different name – the report works. This is the kind of problem that drives users (and consultants) batty. Once we discovered the source of the problem, we fixed it for the client and went on with our business. Still – we had to know! What about the job name caused the problem? So, we experimented (later) and we found the pattern. If the characters “IF (“ appear (without the quotes) in the long name of the job, we can crash the report. Why would that be you might ask. Well, we cannot say for sure, but we can speculate. The Master Builder report writer has the ability to interpret formulas. One of the allowed formulas is an “IF” statement which appears like this “IF (a=b) THEN (c).” The characters “IF (“ must be literally entered into the formula. It is possible that the SMB report writer is trying to treat a portion of the job name as a formula. The contents of the formula will not be valid as a job name, so the report writer tries to read an invalid formula and fails. Thus, the report fails. Try it yourself. It’s fun! Just put the characters “IF (“ in the long name of a job and try to print a list of invoices (3-1-2-21 ) associated with the job you modified. If you are using version 13, you are likely to get a failed report. Remove the characters “IF (“ and the report will work. (law) Labels: bug master builder accounts receivable ar
posted by Web Master
at
9:40 PM
0 Comments
Tuesday, January 15, 2008
A Method to Generate Complex ReportsDo you – as a Master Builder user - have to spend a lot of money to automate very complex reports such as certified payroll reports? Well, the answer is “maybe not.” Here is an alternative to expensive custom reporting that your organization may be able to do with in-house talent.Report Writers There are, basically, three ways to generate reports from data in Sage Master Builder (SMB). The first and easiest option is to use a report writer. SMB has a built-in report writer (linked to the form designer) that is reasonably serviceable and is used to generate most of the pre-created reports in SMB. While not terribly sophisticated in formatting capabilities, the built-in SMB report writer has the very strong advantage of having many of the data relationships pre-defined. For example, if you are printing a list of accounts payable invoices, you don’t have to know which table (actpay.dbf) has the vendor names and which table has the detail lines of the invoice (acpinv.dbf). You can just point and click through the linked tables in the report writer wizard or calculation builder and you are set. The drawbacks to the SMB report writer are primarily two. First, it is a fussy bit of software prone to crashing inexplicitly and the deeper functions of the report writer are not well documented. I have experienced what I call the dreaded “crash and corrupt” where a report with a minor modification will simply shut down never to run again – often requiring a complete rebuild of the report. Secondly - and much more limiting for complex reporting - is that the SMB report writer can only report on a single level of detail. A report cannot be created based on a query on a subset of data. For example, the SMB report writer will not allow the user to query the accounts receivable database for all open invoices with a balance greater than 10% of the invoice total and then give a detail report of those invoices. This functionality can be simulated by using filters and queries, but this method is limited to simple reporting problems and it is very slow on large data sets. A more comprehensive report writer such as Crystal Reports (http://www.businessobjects.com/) can be used as well. Crystal is another report writer that you purchase and install on your computer as an independent program. It can then be set up to read SMB files through the ODBC drivers in Windows. (Note – If I lost you in that last sentence, you are not likely to enjoy learning how to use Crystal.) Crystal has terrific formatting capabilities, reporting on sub-queries, and great speed. We use it extensively for reporting ourselves. The downside of Crystal is that it has a much greater learning curve and there are no predefined relationships for Sage Master Builder data. The user must know which of the 400+ tables in a Master Builder database are linked together, how they are linked, and with which key fields to write reports effectively. This is essentially programmer level knowledge that the average user will not and probably doesn’t want to know. Custom Reports Custom reports that use more of a programming language such as Microsoft Access, Microsoft Visual Fox Pro (currently our favorite tool for this type of work), Visual Basic, and many others can be used to build complex reports. There is really no limit to what can be done with these tools because they have complete access to the SMB data and can manipulate it in any imaginable method. The only limit is time and money. Custom reports tend to be quite time consuming and expensive. Sometimes a custom report is justified - we actually write many of them. However, it is usually a report that is used frequently and is critical to business success to be economically justified. Using Queries to Populate Spreadsheets There is a third way to create reports that is not fully automated, but has several advantages over report writers without the investment of custom reports. This is the method of using the SMB queries to populate pre-built Microsoft Excel spreadsheets. The concept is very easy and can be explained in a few steps. - Create one or more queries in Master Builder to extract the data you want. For a few tips on how to use the SMB queries, see our recent blog entry on this issue. - Create a pre-formatted spreadsheet with worksheets dedicated to pasting the data from the queries. The data should be in one or more dedicated worksheets and all of the analysis should be in worksheets other than the data. This will make it easy to keep the data that is updated isolated from the rest of the formatted report. - Create a process to move the data from the query to the spreadsheet. If the results of the queries are typically small (say less than 1000 lines), a simple cut and paste is fine. If the data is very large, then it may be necessary to save the query results to an intermediate file and then import into the Excel spreadsheet. While this is not a perfect method or 100% automated, with a well-designed spreadsheet and a little practice it can be remarkably efficient. The other advantage is that the formatting of an Excel spreadsheet can be very flexible allowing for small adjustments as needed. Attached is a simple example of this technique with a few of my favorite Excel tricks included and functions included. The analyzed data can be filtered with the Excel auto-filter as well as sorted by any of the analysis columns. This is a simple and useful report as it is, but if you care to, there are many other opportunities to analyze just these two simple queries extended the work that has already been completed. As an example of taking this technique to the extreme – I have attached a replica of a certified payroll report required by state of Connecticut created using this method. There are only two queries required from Master Builder to generate the data needed. This would be a very challenging report to create in any report writer. Because of the complexity of this spreadsheet, I have chosen only to show the results. (law) Example Report from Simple Excel Spreadsheet Simple Excel Spreadsheet Integrated with Queries Example Complex Certified Payroll Report Labels: master builder report writer queries excel
posted by Web Master
at
9:04 AM
0 Comments
Wednesday, December 19, 2007
Bug: When posting SR Invoices - Insufficient Stock ErrorsDecember 19, 2007Updated 2/17/08 Bug: Last tested with Master Builder version 13.1.0.2 Update Note: This bug was documented in the Sage Knowledgebase on 2/6/08 with KB13441. The Service Receivables module (screen 11-2) is designed to handle the sale of parts from an inventory. Here is an interesting little bug that could drive you crazy if you didn't know how to deal with it. With a Service Receivable invoice, a part entered in the invoice details can be charged to the control inventory account. In this case, the part quantity on hand is reduced by the amount sold on the invoice. Master Builder checks the quantity in stock and will only allow the invoice to post if there is sufficient quantity to cover all of the parts listed on the invoice. If the invoice calls for more parts than are currently in inventory, an error indicating "insufficient stock" is presented to the user and the invoice is not posted. However, we have encountered a case where we get the "insufficient stock" error and we were not taking the parts from the inventory. This was a perplexing problem to say the least. It turned out that if the same part number is listed multiple times on the invoice - even if the items are not being taken from stock - the "insufficient stock" error comes up. Note in this example rows 1 and 4 both have the same part number listed.  When there is an attempt to post this record as an invoice, Master Builder will return the error "insufficient stock." We determined two solutions to this issue. The quick solution is to delete all of the part numbers from invoice details tab and then post. This works if you are not doing any reporting of sales by part number. Even then, you can use the alpha part number for reporting.  The second solution is more tedious and takes more time if there are a lot of invoice lines, but it conserves the part numbers in the invoice. First, sort the invoice details tab by the part number (This is a great little trick that can be done in a number of Master Builder grids. To sort by a column, left-click on the Part # column heading to highlight the column, then right-click in the highlighted area. Select on "Sort by Part # Column) Next combine quantities of lines with the same part number into a single line. Delete the duplicate lines. Post without an error!  (law) Labels: bug master builder service receivables sr inventory
posted by Web Master
at
8:28 AM
0 Comments
Thursday, December 13, 2007
Tip: Quirky QueriesPut this under "stuff you don't know why it happens, you just want to fix it."Queries are one of the best features of Master Builder. In case you don't know, queries are found on most screens under either "File | Find" or - alternatively - by clicking on the binoculars icon. Queries are a way to get a list of records according to criteria you select - for example a list of AP invoices from the 4-2 screen. This information is then presented in a grid which can be drilled into for details or copied and pasted to a spreadsheet for further analysis. However, there are a couple of quirks with this process. Fortunately, these are easy to fix. Beginner: To create a query, go to a data entry screen such as 4-2 (AP Invoices) and click on "File | Find". Enter a new name (the name of a new query) in the blank field, and click [New]. This screen will take you through a series of five screens that allow you to identify the information you want to display. The screens - in order of appearance - are: Fields To Display - these are the fields that will appear on the grid after the search. Fields to Group By - these are the fields that will provide the first level of sort for the results. Each group has a subtotal. You are allowed a total of 3 fields for grouping. Fields to Sort By - these are the fields that provide the second level of sorting of the results (after the groupings). Fields to Select By - these are the fields with which you can control the search. For instance, if you want to be able to select AP invoices with an invoice date of 12/31/07, you would need to include the field "acpinv.invdte" in the Fields to Select By list. Selection Criteria - for each field in your Fields to Select list, you can pre-define your default values. A good example of this is to filter out all void AP invoices when you search for invoices. To do this, you would need to include "acpinv.status" in your Fields to Select list, and then in the Selection Criteria you would select "Between" for the comparison, and then put a default of 1 to 4 (Open, Review, Disputed, Paid invoices). The Issue - Intermediate: So here is the rub. You may be irritated by fact that after you spend the time to enter your default selection criteria in a new query, when you run the query - the defaults don't appear. Bummer. To fix this, immediately after you create a new query - before you run it - single click the new query name in the query list and click [Edit]. Then click [Next] through each of the five set up screens. Don't change anything. Viola! Next time you use the query, the selection criteria defaults will be where they belong. Related to this problem is the issue where queries that used to work suddenly and inexplicity crash. This takes you out of Master Builder in a particularly inelegant way. Usually, this condition is caused by someone starting to edit a query, deciding they don't want to, and cancelling out of the edit screen. Some people curse the system and try to re-create the query. No need. Just like above, single click the crashing query in the query list, click [Edit] and click [Next] through each of the five set up screens. This will fix the problem. Advanced: Queries are stored in the table "Qrylst.dbf" under each specific company directory. This means that if you have a set of really cool and useful queries that you have built up over the years and you start a brand new company (or get a new client) you must laboriously re-create each query manually. A simple solution is to copy the files qrylst.dbf, qrylst.cdx, qrylst.fpt from the company data directory that has the queries you want to the new company data directory. All of the queries you had in the original company will now be in the new company. Beware - if you already created any queries in your new company, they will be overwritten. It is also possible with a good tool such as Microsoft Visual Fox Pro to copy individual queries from one company to another in a selective way. Each record in qrylst.dbf represents a single query. The field "tblnme" will indicate which data screen the query will appear in. To move a specific query, copy the record in qrylst.dbf from the source company data directory to qrylst.dbf in the target company data directory. (law) Labels: master builder query crash
posted by Web Master
at
9:30 PM
0 Comments
Wednesday, December 12, 2007
Tip: Those Pesky Image Files in Report DesignerCertain problems occur in using Master Builder where I don't have time or the interest in finding out why the problem exists or the most elegant solution, I just find a way to work around it. This is one of those problems.Anyone who had done any extensive work with the form designer probably has experienced the problem where a picture (image file) they use in a form design either doesn't appear in the form designer when imported into a form or, alternatively, does appear in the form designer but does not print. Worse yet, it will print on some user machines, but not others. A search of the Sage knowledge base (http://www.sagemasterbuilder.com/support/index.asp) provides an article, KB13027, but this article only deals with one potential cause of this issue and does not resolve the erratic behavior of the form designer with respect to image files. I haven't taken the time to figure out why this happens, but here is a simple way to consistently avoid the problem. This works regardless of the type of image file used - .bmp, .jpg, or .gif files. For any image file you use in a Master Builder form design or report form, copy the exact same file into the following four directories: X:\MB7\Report Forms X:\MB7\BMP C:\MB7\Report Forms C:\MB7\BMP where X:\ is the network drive where your Master Builder data resides. Yes, this does mean that sometimes you will need to copy the picture files to multiple user local machines and is a little bit of a pain to maintain, but it always works. (law) Labels: report writer form design bitmap pictures master builder
posted by Web Master
at
2:44 PM
0 Comments
|
||||
|
|
|||
 |
|||
|
|||
|
|
