By Mary Mays
Have you ever wanted to export some Sage 100 info quickly to Excel? Using the ALE custom feature allows you to create personalized data sets that can then be exported to Excel.
In any module, click the magnifying glass ‘lookup’ button to bring up the lookup screen. The default lookup is called Standard. We will be using AR Customer lookup to create a ‘customer balance due’ list for the examples in this article. Click the Custom button in the lower left corner (if the Custom button is not available then the lookup is universal and is not available for customization).
Give your lookup a description and if you want the new lookup to become your default lookup view, click the Default View box. This can be changed later by editing the ALE. Click Next.
You’ll see a screen to select or deselect the info that can be included in your lookup. The first item under Selected Columns that has the * next to it CANNOT be removed, but it can be modified.
Remove zip code and sort fields by highlighting the item on the right side and clicking the single arrow pointing left. These items then go back to the Available Fields list. If you scroll down the Available Fields you will see there are many items to choose from, including any user defined fields. Highlight the field you want then click the single arrow to move it over to the Selected Columns side. I will add a contact name, date of last payment, date of last activity, last payment amount, average days overdue and current balance. If you want to change the order of the fields under Selected Columns, highlight the field and click on the move up/move down arrows located at the bottom of the panel.
Use the Modify button to control what will display for the column heading information and whether the info is center, right or left justified and the numeric mask if numeric field. Choose New Field button to create your own calculated field using a selection of the Available Fields on the left.
Click Next and choose any filters. For this example I will select only active customers. Under Column, click the pull down arrow to display available choices; under Operand choose Equal to and for Value choose Active.
You can add additional filters, for example CurrentBalance Greater than 100.00. After making all filter selections click next and change the Title that will display in the upper left corner of the lookup. You can also change the Default search Column.
Click Finish and the lookup will be displayed.
Notice the Filters button displays in RED indicating you have filters selected. You can click on any of the column headers to change the sort order of the list. If you want to change the fields, click on the Custom button and click Next to go back to add or remove any fields. You can add or delete any of the filters. When satisfied with the data, click the Excel button on the lower right of the screen to export the info and open Excel.
Any of the ALE’s you create along with the default will be available and listed in the drop down next to View.
If no data displays in your lookup, it is possible the Filter is wrong. Simply click the Custom button and go back through the screens. You can delete the filter by setting the Column back to None. DO NOT click the Delete button here as it will delete the entire lookup, unless you want to start over with a new lookup.
If you are unsure what your Value should be in a Filter, create your lookup first and look at the data under the column for the correct value choice, then edit the lookup. Once you have created a Custom lookup, to create another in the same program click Create new view next to View.
Custom lookups are a useful way to create a data set that can be exported to Excel without any custom programming. If you’re a new user, start with something simple and then spread your wings with more complicated ALE’s. Contact PSI if you have any questions.
Some additional suggestions for custom lookups:
• In PR Employee maintenance create a listing for hire date.
• Payrate listing to Excel to use a calculation for a % pay increase.
• Add account type or CashFlowsType to GL Account Maintenance lookup
• Add source to Invoice History Inquiry lookup to see whether the invoice comes from A/R or S/O; in AR Customer add salesman and commission rate.
• Employee, Customer or Vendor address listings for mailings.