Custom Inquiry Overview
SapphireOne’s standard Inquiry displays a fixed number of columns containing data in a list. We additionally have the option of using a custom Inquiry which allows the user to extensively customise the data columns.
Allowing User Access
To do this do to Utilities > Controls > User Access Inquiry. The Custom Inquiry checkbox must be selected in the General Access area. SapphireOne will then display an additional Swap to Custom Inquiry button in the lower left hand corner of all the list screens in SapphireOne.
Limitations
The user should note that the Show Invoice Lines button is not available from the custom Inquiry screen. However, switching between the Standard Inquiry and the Custom Inquiry is achieved by simply selecting the Swap to Standard Inquiry button. To go back to the standard inquiry all that the user has to do is select the Swap to Standard Inquiry button.
It should be noted that when using the custom inquiry screen the @ symbols ? when used as a wildcard search operate in exactly the same was as when the Standard Inquiry is used.
Standard Transaction List Inquiry Screen
The first screen is the Standard Transaction Inquiry screen. This is the default screen shown when selecting a Transaction Inquiry, and lists the below columns and has been in SapphireOne since the begining.
- ID – This is the Client ID or Vendor ID (depending on the functionality of the Inquiry).
- Date – Date of the transaction (automatically set as current date when a new transaction is entered, however can be manually changed).
- Internal Ref – The sequential number automatically generated by SapphireOne (this can be manually changed and a Prefix/Suffix can also be added).
- External Ref – The sequential number supplied by the Client or Vendor (can be manually changed a Prefix/Suffix can also be added)
- Sequence – Automatically generated by SapphireOne (cannot be changed manually by the user).
- Period – Accounting Period automatically generated by SapphireOne based on the date of the transaction.
- FX – Type of foreign currency (e.g. USD, EUR, INR).
- Total – Total value before tax.
- Type – Code of type of transaction (e.g. (VI) Vendor Invoice, (VP) Vendor Payment).
- Status – Status of transaction (e.g. 1 (Posted), -2 (Entered), -1 (Errored), 0 (Not yet processed).
- Tag – A tag that can be custom created by the user.
- # – Number of attached documents.
Custom Inquiry Transaction List Screen
When navigating to the bottom left corner of the Standard Transaction Inquiry screen, you have the ability to select the ‘Swap to Custom Inquiry’ button. SapphireOne will then take you to a custom defined Transaction Inquiry screen. From this custom screen you are able to select for display virtually any field in the table in any order that you want. A default setting for the fields and columns is initially displayed. The user may then customise this default setting as they require. Once the customised list has been set up as the user requires, they may then save the customised setup on a user by user basis.
The default columns listed in the Custom Transaction Inquiry screen are:
- Common ID – This is the Client ID or Vendor ID (depending on the functionality of the Inquiry).
- Date In – Date of the transaction (automatically set as current date when a new transaction is entered, however can be manually changed).
- Internal Ref – Sequential number automatically generated by SapphireOne (This can be manually changed and a Prefix/Suffix can also be added).
- External Ref – Sequential number supplied by the Client or Vendor (can be manually changed a Prefix/Suffix can also be added).
- Sequence – Is a unique number automatically generated by SapphireOne (cannot be changed manually by the user).
- Period – Accounting Period automatically generated by SapphireOne based on the date of the transaction.
- EntFX Type – Type of foreign currency (e.g. USD, EUR, INR etc).
- Org Total – Total value of the transaction before tax.
- Type – This displays the short code applied to the transaction to identify it. (e.g. (VI) Vendor Invoice, (VP) Vendor Payment etc).
- Status – Status of transaction (e.g. 1 (Posted), -2 (Entered), -1 (Error), 0 (Not yet processed
Setting up a Custom Inquiry
The user should be aware that the major problem involved with adding in additional items or columns in a custom inquiry, will be the amount of screen or monitor real estate that the user has available. (The Resolution). Although at the time of writing this document there are now 5K monitors for sale which are very very wide.
- Favorite Sets – Once you have the columns set up the way you like them select the Save to Set menu. A pop up will be displayed asking for the Set Name.
- Reloading Sets – To reload your set just select the Save To Set drop down again and a list of Saved sets will be displayed.
- Altering Sets – To alter your Set, you will have reload them again and then set your columns up as you want them proceed to save with them the same name. The set will then be re-saved using the original name.
- Row Height – The row height or distance between rows on screen may be adjusted by increasing the default number of 20 pixels.
- Show Related Checkbox – By default SapphireOne will only display the data fields from the default table with just the main data field from related tables. When this check box is selected, the related tables main heading will display all data fields for the related table underneath.
- Reset to Default – If required for any reason the selection of the
button will tell SapphireOne to return all settings in this Custom Inquiry back to SapphireOne’s original default values.
- Default Settings – SapphireOne commences with a default setting for each Custom Inquiry in SapphireOne. The user may then modify what is displayed in each column by selecting the
button.
- Columns – Each column seen in the screenshot above, represents a field in the your data file. Virtually any field in any table may set up as a column of data in a Custom Inquiry.
- Column Widths – These may be adjusted by moving the mouse over the junction between column headings. Once the 2 sided arrow is displayed, click and drag the column to the desired width.
- Column Moving – To move a column to the left or right, click on the column heading and drag as required.
- Right Click Options – When the user right clicks anywhere in a column the following options are available.
- Delete Column – Selecting this option will delete the selected column.
- Move Left – Selecting this option will move the column one place to the left.
- Move Right – Selecting this option will move the column one place to the right.
- Adding Columns – To add a data field as a column double click on it. The user should note that it will be added as the very last column on the screen. The screen may have to be made wider or scrolled to to see it. The select it and use the procedures documented above to move it to the desired place on screen.
Summing a Column in a Custom Inquiry list
This procedure will enable the user to quickly an number of different calculations on any column in any list screen within SapphireOne. These are Sum, Average, Minimum and Maximum.
The user will note that there are 2 drop down menus at the bottom of the screen and they are linked together. You could sum the list in its entirety but we will perform a simple find for ARCRES. So from the two list we will select Org_Total and Sum.
The result of the calculation will be displayed next to the Setup and Export buttons. Any column or cell may be selected for a calculation although many will display odd results because of the data that they contain.
Exporting From a Custom Inquiry
When the user selects the 
button SapphireOne will perform a blind export of all of the rows of data that is currently on screen. At the same time, it will access the default application in the operating system to display the exported data, either Mac or PC. If Excel is the default spreadsheet program on the local machine SapphireOne will start it and display the file that was exported in CSV format.
You can review our Blog and YouTube channel for additional information and resources on SapphireOne ERP, CRM and Business Accounting software.