How to Create a Custom Lookup View in Sage 100

” A Custom Lookup View in Sage 100 is a powerful feature that allows users to create their own views of data.”

Sage is one of the most popular platforms when it comes to dealing with Accounting and Finances. This website has been able to offer simple and swift solutions when it comes to effective bookkeeping solutions. This goes a long way in improving productivity and enhancing profits in the long run for small to mid-sized organizations. Also, as it is not possible to hire a professional for all companies, due to various factors like budget restraints among others; hence opting for a reliable online platform is of great accessibility. 

Sage has grown immensely in its popularity due to the features it offers: Invoicing, VAT returns, Cash flow management, Inventory management, third-party software integration and much more. Among these, the ‘Sage Lookups’ is one of the most ‘helpful features’ offered in the platform. So, what is the Sage 100 Lookup all about?

What is Sage Lookup?

The ‘lookup’ feature is used for the ‘search and Select records’ within the Sage platform. This feature helps the users to perform a ‘quick search’ as and when required to ‘pull out’ a certain specific topic one needs to check out. For this, the user needs to:

• Select a ‘Cell’ 
• Enter a certain word they need to ‘lookup
• Click press F5/Shift +.
• This will activate the lookup window
• The relevant displays will be shown according to the availability in the database.

As for the database used by Sage 100; the premium edition utilizes Microsoft SQL. Also, the Sage 100 ERP Standard and Advanced editions utilize the ‘Providex’ tables which is a proprietary database. So let us now take a look at How to Customize Sage 100 Lookups:

Read More-: How to Setup Linked Accounts in Sage 50

How to Customize Sage 100 Lookups

Lookup is an essential part of the Sage 100 that allows the user to gain access to certain information retained in their database. This is all the more important as the default onscreen lookups do not necessarily display all that you are searching for. Hence the importance of the ‘Lookup’ feature is necessary for a certain platform. Also for the same reasons it is important to customize the lookup for our use:

The Custom Lookup Views

This is denoted as the ‘Magnifying glass icon’ and it launches into the lookup screen. This is of great help when you are searching for vendors, accounts, customers, or any other relevant data concerning your requirements. However, there are times when innumerable records will be displayed by default; thus, compelling you to spend more time searching and scrolling. But there are ways to customize the fields, search and filters to simplify the process and quicken things up:

Step1: Go to any lookup window and select the Custom option that launches the ‘Lookup Customization Wizard’. 

Step 2: From within the ‘View’ field within the Customisation Wizard. Now you need to select ‘Create New View’ and offer it a name within the description field. The user can also select an existing view to modify. 

Step 3: Next, you need to click the ‘Default View’ checkbox, for this to be the new default lookup. Now click Next:

Step 4: The next screen will emerge where the user can rearrange, modify, remove and add the order of the columns that are displayed in the lookup. This is the first column that is denoted by the asterisk (*) and cannot be removed, but it can be modified

Step 5: At this step, the user can click Finish if they have found the new customized view or click Next to launch the third options screen in order to define additional settings that will help modify the filters and further refine the data that will display within the lookup. 

Step 6: When the user clicks Next in step 5, you will need to use the third ‘Lookup Customization Wizard page. This is used to add, delete, or modify the filters. In order to continue defining additional features you will need to click ‘Next’ or click ‘Finish’.

Step 7: Within the last Customization Wizard screen the user can make any final changes and display options. Next, click on Finish to create the new customised lookup view. 

Once when the new lookup screen has been launched, you will find a ‘title bar’ at the top left where the new description is along the asterisk, in order to indicate the custom view.

How to Add Field to Sage 100 Using a Lookup Table?

This feature allows the user to be able to add a field that allows a selected value from our Custom lookup list. Here the best part is that no coding is required:

Creating the Table-UDT

1. First one needs to create a list of territories to select from: 

• Launch Customs Office
• Go to Main 
• Go to User Defined Field and Table Maintenance
• Next, you need to Highlight Accounts Receivable
• Click on Add User Defined Table” button (highlighted below)

2. Next, you need to enter the description, table name, and key field name (name of your new field), and ensure to give your key field length enough characters to accommodate all the required items within your list.

3. Click OK once done. 

4. Now, the table has been created. One needs to add a link in Sage 100 in order to access it. This allows for the easy maintenance of the territories list. 

5. Click on the ‘Add UDT to Desktop” button and click OK when prompted5. The user is required to give themselves access to this new option in Sage. This can be done by:

• Master>Main>Role Maintenance
• Here you need to select the role and enlarge the Custom Office > Miscellaneous Tasks menu
• Check the box next to the new UDT and click Accept when done

6. Next launch the table using the Custom Office> UDT Maintenance

• Next, you need to enter the selection options that you want to make available for populating your new field. In our example, we will add our list of territories:
• Part 2: Add a New Field (UDF) to Sage
  • Go back to the User Defined Field and Table Maintenance and select the table to which you’ll be adding your field.
  • Next, Highlight the table and click the “Edit Fields” button.
  • Click the “+” button
  • Enter a name for your new field
  • Keep the “Manual Entry” radio button selected and click “OK

7. The Next Screen will be displayed where you need to enter the same field length as you did for the UDT “Key” in step number 2 under the section “Creating A Table (UDT).” Then, click the “Validation” tab

8. Now select the radio option next to the “User-Defined Table.”

9. If the key and UDF are of the same length, you should see your table available for selection. Select the table and click OK. 

10. Click OK again in order to update the database 

11. Close to Return to the Main Menu

• Part 3: Add Your New Field to the Screen
  • Launch the screen where you want to add your new field
  • Right-click on the screen until you see a pop-up menu with the “Panel Settings” option
  • Click on Customizer 
  • Note: the user will require role security permissions to access “Customizer.”
  • The next Screen will show you an option to create a new custom screen (aka “Panel”) or modify an existing one (if any has previously been created).
• Note: the custom panels may be created for all users and all companies, or specific users and/or specific companies
  • Next, click the “Add Field” button and select your new UDF from the list that appears. 
  • Finally, you have to close the Customizer screen 
  • Save the changes when prompted
  • And you are done.

Also Read-: Sage 100 2019 Updates

Conclusion

Lookup is an essential aspect of Sage 100, where the user can gain access to a piece of information they are searching for. Hence, it is important that one should know about the various steps involved regarding How to Customize Sage 100 Lookups. However, if you continue to face problems, be sure to reach out to our team of experts at +1800-964-3096 or drop a mail at [email protected] or join our LIVE sessions at www.accountingadvice.co for detailed assistance.

💠Frequently Asked Questions💠