Detailed Sort Overview
The SapphireOne Detailed Sort feature provides extensive additional options for sorting transaction records. This is achieved through the execution of sophisticated queries on your SapphireOne tables fields. Detailed Sort looks for records matching the criteria specified in your query and returns a selection of records for a given table query organised both alphabetically and numerically.
Detailed Sort Procedure
- From any transaction list within SapphireOne, select Detailed Sort from the Options menu.
- Select a field or multiple fields to be sorted by within the Available Fields panel.
- Using the right arrow, move the desired fields into the Ordered by Fields/Formulas panel.
- Your results will be sorted one row at a time, therefore the sort order of your fields will impact the results achieved. It is possible to drag each field into a new location in the hierarchy. Additionally the Direction (Arrow) button to the left of your chosen fields may be toggled from up to down by selecting it. The effect will be to influence….
- The Add formula options provide extensive capabilities to modify the existing sort behaviour or create a unique formula. Please refer to the following section.
- Once set up is complete select Sort. The following results relate to the example above.
Add or Modify a Formula
The Formula editor provides many shortcuts for writing formulas. You can click field names, operators and commands, as well as any project methods, to add them to the formula. When you click on an item, it is automatically displayed in the editing area where you can then modify it using standard cut/copy/paste functions. You can also enter items directly in the editing area or drag and drop them from the list of items.
The Formula editor contains the following areas:
- List of tables and fields – This area displays the fields of the table. The menu located above the list lets you set the fields to be displayed. You can use fields of the current table, those of related tables or those of all the tables.
- Note – Tables and fields with the Invisible property do not appear in the list. For more information about this property, refer to the “Attributes” section in Table properties and Field properties.
- Operators list – The operators list lets you choose the operators to be used in the formula. The operators are grouped into themes found in the menu located above the list.
- Commands list – The commands list contains the SapphireOne functions that can be used in formulas, as well as any project methods allowed by the developer. The menu located above the list lets you display the commands by theme or by alphabetical order. Refer to the SapphireOne Language Reference manual for a description of the SapphireOne commands that appear in this menu.
In principle, project methods that can be used in formulas must be declared beforehand using the SapphireOne set allowed methods command. However, by default, the Designer and Administrator of the database have complete access to the SapphireOne commands and user methods in the Formula editor. It is also possible to completely disable access control for all users. These options are set on the Security page of the Database settings.
Here is a brief description of the different operators available in the Formula editor. For a more detailed description of the possibilities provided by these operators, refer to the Operators chapter of the SapphireOne Language Reference manual.
- String Operators – A and B are character strings; N is a number.
OperatorUseDescription:= AssignmentA:=BAssigns the value B to A+ ConcatenationA+BReturns AB* RepetitionA*NRepeats the value of A N times[[ ]] Indexes[[A]]NReturns the Nth character of A”” Empty string””Inserts a pair of quotation marks - Numeric Operators – X and Y are numbers.
OperatorUseDescription:= AssignmentX:=YAssigns the value Y to X+ AdditionX+YReturns X plus Y- SubtractionX-YReturns X minus Y* MultiplicationX*YReturns X multiplied by Y/ DivisionX/YReturns X divided by Y\ Integer DivisionX\YReturns the integer division of X by Y (X and Y must be integers)% ModuloX%YDivides X by Y and returns the remainder^ ExponentiationX^YReturns X to the power of Y
Note: The modulo % operator returns significant values with numbers belonging to the long integer category (from -2^31 to +2^31 minus 1). To calculate the modulo of numbers outside of this interval, use the Mod command.
- Date Operators – D1 and D2 are dates; N is a number.
OperatorUseDescription:= AssignmentD1:=D2Assigns the value D2 to D1+ AdditionD1+NReturns D1 plus N days- DifferenceD1-D2 orReturns the number of days between D1 and D2D1-NReturns D1 minus N days!//! Blank date!00/00/00!Inserts a blank date - Time Operators – H1 and H2 are times; N is a number.
OperatorUseDescription:= AssignmentH1:=H2Assigns the value H2 to H1+ AdditionH1+H2 orReturns H1 plus H2H1+NReturns H1 plus N seconds, expressed in seconds elapsed since midnight- SubtractionH1-H2 orReturns H1 minus H2H1-NReturns H1 minus N seconds, expressed in seconds elapsed since midnight* MultiplicationH1*NReturns H1 multiplied by N, expressed in seconds elapsed since midnight/ DivisionH1/NReturns H1 divided by N, expressed in seconds elapsed since midnight\ Integer DivisionH1\NReturns the integer division of H1 by N, expressed in seconds elapsed since midnight% ModuloH1%NDivides H1 by N and returns the remainder?::? Blank hour?00:00:00?Inserts a blank hour - Comparison Operators – Z1 and Z2 can be of the string, numeric, date or time type.
OperatorUseDescription= EqualZ1=Z2Returns True if Z1 equals Z2# Not equalZ1#Z2Returns True if Z1 does not equal Z2> Greater thanZ1>DReturns True if Z1 is greater than Z2>= Greater than or equal toZ1>=Z2Returns True if Z1 is greater than or equal to Z2< Less thanZ1<Z2Returns True if Z1 is less than Z2<= Less than or equal toZ1<=Z2Returns True if Z1 is less than or equal to Z2 - Logical Operators – B1 and B2 must be Booleans (expressions that are TRUE or FALSE)
OperatorUseDescription& ANDB1 & B2Returns True if B1 is True and B2 is True| ORB1 | B2Returns True if B1 is True or B2 is True
You can review our Blog and YouTube channel for additional information and resources on SapphireOne ERP, CRM and Business Accounting software.