This proceedure is provided for archival purposes only. ShoWorks versions since 2012 expanded the 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. The instructions serve as a technique to using spreadsheets and manual intervention of records to add to or manipulate the sale. This article is for advanced users only.
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
To automate Misc. Adjustments to the sale by using a spreadsheet, do the
Once the sale is completed, run the ShoWorks Companion for Microsoft
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
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
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
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.