ShoWorks Help Articles
How to add many Adjustments to Sale items at once (check-offs, percentages, etc.)ArticleID: 110228
Date Added: 8/2/2006
Date Last Updated: 12/23/2011
Note: This proceedure only applies to ShoWorks versions 2009 and older. ShoWorks 2012 expanded the existing tool "Adjust Sale" (Sale Adjustment Wizard) located under the "Tools" menu, which allows users to adjust any sale item by %, flat rates, weights, and more.
Oftentimes, miscellaneous deductions other than commissions (such as swine check-offs, market adjustments, etc.) need to be taken out of an exhibitors check for their sale item. Note, that standard commissions that are based on the percentage of the item sold are already automated in the Divisions menu under the Sale tab. These extra deductions (non-commissions) are often based on a percentage of the weight or sale item, or perhaps a flat fee. In any case, a work-around to add these in is to use Microsoft Access and Excel.
Note: This procedure requires the use of Microsoft Excel and Microsoft Access. The versions of each depend on the version of ShoWorks that you are using. For clarification on version compatibilities of Microsoft Access (and Excel), please see the article: Understanding versions of ShoWorks and Microsoft Access
To automate Misc. Adjustments to the sale by using a spreadsheet, do the following:
- Once the sale is completed, run the ShoWorks Companion for Microsoft Access.
- Close the Companion and reopen so that the toolbars are displayed.
- In the Companion, click the Queries tab.
- Highlight Query "130" and click the button "Build a Query over this Query".
- In the top menu bar, select View>>Totals.
- In the query grid in the row marked "Field" select the field "Invoice Number" for the first column. You can then choose any fields in the remaining columns, such as "Exhibitor Name", "Sale Weight", "Show Weight", "Unit Price", etc. Just do not choose the fields "Is Add-on" NOR "Sale Transaction ID" NOR any fields that begin with "Buyer…". Once exception that you may choose is "Buyer Amount", but if you select this, you must change the "Group by" below it to "Sum".
- Run the query by selecting Query>>Run in the top toolbar.
- In the top menu bar, select Tools>>Office Links>>Analyze it with Microsoft Office Excel.
Your goal now is to arrange the presented spreadsheet in the following layout
(you are now in Excel):
- Column A row 1 should have the word "Invoice Number" exactly as spelled. Subsequent rows 2 through … should have invoice numbers in them.
- Insert a blank column for Column B and make the heading (row 1) read: "Sale Adjustment Name" exactly as spelled. You can now type (and copy/paste) in subsequent rows 2 through … the name of your adjustment. For example: "Swine Check-off fee".
- Insert a blank column for Column C and make the heading (row 1) read: "Sale Adjustment Amount" exactly as spelled. You can now create a formula in subsequent rows 2 through … for the value that you need. For example, if you wanted an amount that was 1.5% of the total weight of the animal, and the weight was in column E, then you would type =E2*0.015 and copy this formula down the remaining rows. Important! Use a negative number if the amount is going to be charged to the person. Otherwise, a positive number means that the fair is giving the amount to the person. So in our example, the formula should be =E2*-0.015 if we are taking the amount away.
- Insert a blank column for Column D and make the heading (row 1) read: "To Buyer" exactly as spelled. You can now type (and copy/paste) in subsequent rows 2 through … either "Yes" or "No". Use "Yes" if the amount is going to be applied to the buyer's bill and "No" if the amount is going to be applied to the exhibitor.
- You should now have a spreadsheet with the first four columns reading "Invoice Number", "Sale Adjustment Name", "Sale Adjustment Amount", and "To Buyer". The remaining columns can be anything you want.
- Highlight only the cells A1 through Dxxx where xxx is the total number of rows. For example, if you had 300 rows, then this would be A1 through D300.
- In the top menu bar, select Edit>>Copy
- In the Companion, on the Queries tab, click the "Build a New Query" button.
- While the "Design View" item is selected, click the "OK" button.
- Click the "Preferences" box that is located in the grid. Then select "Edit>>Delete" in the top menu bar to clear this.
- In the top menu bar, select Query>>Show Table.
- Scroll to locate the table "Sale Adjustments". Highlight and click the "Add" button, then click the "Close" button to close the dialog box.
- In the first column and first row of the grid, select Sale Adjustments.* This is the first item in the list. Note, this has a "dot" and an asterisk.
- In the top menu bar, select Query>>Run.
- Paste your records by selecting in the top menu bar, Edit>>Paste Append.
- You should now see the resulting records and may close the Companion and note that records have now been added for the adjustments.
Question not answered in this article?
Click to Contact GladStone support for more information.