This list provides an overview of all internal field names, field types and maximum field lengths that can be used in an item profile of Decos D5. (Varchar = Variable Character Field)
| Internal field name | Field type | Maximum field length | Particularity |
|---|---|---|---|
| SEQUENCE | Numeric | 6,0 | Sequence number field is automatically created (6 characters, no decimals). |
| MARK | Varchar | 250 | |
| SUBJECT1 | Varchar | 250 | |
| SUBJECT2 | Varchar | 250 | |
| DOCUMENT_DATE | Datetime | 16 | |
| RECIEVED_DATE | Datetime | 16 | |
| PROCESSED | Character | 1 | |
| COMPANY | Varchar | 250 | The link button between documents and addresses is always placed next to the COMPANY field. |
| MAILADDRESS | Varchar | 250 | COMPANY, MAILADDRESS, ZIPCODE and CITY are used as address data. ZIPCODE is the zip code field. |
| ZIPCODE | Varchar | 10 | |
| CITY | Varchar | 250 | |
| COUNTRY | Varchar | 20 | |
| PHONE1 | Varchar | 20 | |
| PHONE2 | Varchar | 20 | |
| PHONE3 | Varchar | 20 | |
| FAX1 | Varchar | 20 | |
| FAX2 | Varchar | 20 | |
| URL | Varchar | 250 | For all data starting with http://, the content of the field is recognized as an internet address. |
| SALUTATION | Varchar | 250 | |
| INITIALS | Varchar | 50 | |
| SURNAME | Varchar | 250 | |
| FIRSTNAME | Varchar | 50 | |
| PREFIX | Varchar | 50 | |
| TITLE | Varchar | 250 | |
| FUNCTION | Varchar | 250 | |
| DEPARTMENT | Varchar | 250 | |
| SEX | Varchar | 8 | |
| EMAIL1 | Varchar | 250 | If the email field contains a valid email address, a mailto link will be generated. |
| EMAIL2 | Varchar | 250 | If the email field contains a valid email address, a mailto link will be generated. |
| EMAIL3 | Varchar | 250 | If the email field contains a valid email address, a mailto link will be generated. |
| TEXT1 | Varchar | 250 | |
| TEXT2 | Varchar | 250 | TEXT2 is used by default as a file name with a document registration. The link button for folders is placed next to this field. |
| TEXT3 | Varchar | 250 | TEXT3 and TEXT5 can be used as second address, zip code and city (default calling address) in an address. TEXT4 can therefore be linked to the zip code table. |
| TEXT4 | Varchar | 250 | |
| TEXT5 | Varchar | 250 | |
| TEXT6 | Varchar | 250 | |
| TEXT7 | Varchar | 250 | |
| TEXT8 | Varchar | 250 | |
| TEXT9 | Varchar | 250 | |
| DATE1 | Datetime | 16 | |
| DATE2 | Datetime | 16 | |
| DATE3 | Datetime | 16 | |
| DATE4 | Datetime | 16 | |
| DATE5 | Datetime | 16 | |
| DATE6 | Datetime | 16 | |
| DATE7 | Datetime | 16 | |
| DATE8 | Datetime | 16 | |
| NUM1 | Numeric | 12,2 | 12 characters, 2 decimals. |
| NUM2 | Numeric | 12,2 | |
| NUM3 | Numeric | 12,2 | |
| NUM4 | Numeric | 12,2 | |
| NUM5 | Numeric | 12,2 | |
| NUM6 | Numeric | 12,2 | |
| BOL1 | Character | 1 | Character (1) fields are seen as Boolean fields (Yes/No fields) in Decos Document. |
| BOL2 | Character | 1 | |
| BOL3 | Character | 1 | |
| BOL4 | Character | 1 | |
| BOL5 | Character | 1 | |
| BOL6 | Character | 1 | |
| BOL7 | Character | 1 | |
| BOL8 | Character | 1 | |
| BOL9 | Character | 1 | |
| BOL10 | Character | 1 | |
| ARCHIVED | Character | 1 | This field is used to archive a registration. |
| MEMO | Text | NA (Blob) | Extensive Content. |
| LEGALID | Numeric | 20 | ID-field (for example: SSN). |
| CONFIDENTIALITY | Text | 250 | To mark the registration as confidential. |
The above data types are shown as they are used by Decos Document in a SQL database. Below are the corresponding data types for other database types:
| SQL server | Oracle | MySQL | MS Acces | Informix | Omschrijving |
|---|---|---|---|---|---|
| Character | Character | Character | Text | Character | Text field, fixed length |
| Varchar | Varchar | Varchar | Text | Varchar | Text field, variable length |
| Numeric | Numeric | Numeric | Number | Numeric | Numeric field |
| Datetime | Date | Datetime | Date/Time | Date | Date/time field |
| Text | Long Raw | Longblob | OLE | Text | Can contain a large amount of text (Extended Content) |
MS Access does not support fixed-length text fields.
The macros below are recognized by JOIN if you have activated the macro option in the field settings of an item profile. JOIN automatically replaces a macro by a generated value when a new registration is created, a registration (file, address, contact person) is linked, or when a registration is processed or saved.
| Macro | Description |
|---|---|
| DATE() | After creating a new registration, fills in today’s date. |
| DATE() + 5 | After creating a new registration, fills in the date five days after today. |
| FOFMON() | After creating a new registration, fills in the first day of the current month. |
| LOFMON() | After creating a new registration, fills in the last day of the current month. |
| FPRMON() | After creating a new registration, fills in the first day of the previous month. |
| LPRMON() | After creating a new registration, fills in the last day of the previous month. |
| FDAYWK() | After creating a new registration, fills in the first day of the current working week. |
| LDAYWK() | After creating a new registration, fills in the last day of the current working week. |
| FDAYPW() | After creating a new registration, fills in the first day of the previous working week. |
| LDAYPW() | After creating a new registration, fills in the last day of the previous working week. |
| NOW() | After creating a new registration, fills in the current time. |
| NOW() + 5 | After creating a new registration, fills in the time five hours after the current time. |
| FofQ() | After creating a new registration, fills in the first day of this quarter. |
| LofQ() | After creating a new registration, fills in the last day of this quarter. |
| FPrQ() | After creating a new registration, fills in the first day of the previous quarter. |
| LPrQ() | After creating a new registration, fills in the last day of the previous quarter. |
| FofY() | After creating a new registration, fills in the first day of this year. |
| LofY() | After creating a new registration, fills in the last day of this year. |
| FprY() | After creating a new registration, fills in the first day of last year. |
| LprY() | After creating a new registration, fills in last day of last year. |
| GLOBALNUM() | After creating a new registration, fills in a global serial number that is unique across all books. This macro cannot be used on the SEQUENCE field. Use GLOBALNUM([series name]) to create multiple global series, each with its own name. Enter the desired name instead of [series name]. |
| %HANDLER% | After creating a new registration, fills in the full name of the logged in user. |
| %USER% | After creating a new registration, fills in the full name of the logged in user. |
| %USERTEL% | After creating a new registration, fills in the phone number (PHONE1) of the logged in user. |
| %USERFAX% | After creating a new registration, fills in the fax number (FAX1) of the logged in user. |
| %USERLOC% | After creating a new registration, fills in the department (DEPARTMENT) of the logged in user. |
| %USER_[field name]% | After creating a new registration, fills in the value of [field name] of the current user (eg% USER_CITY%). |
The Macro YEAR ([macro]) around an existing date macro, for example YEAR(DATE()), only fills in the year. A renumbering tool is available for GLOBALNUM() to provide existing registrations that match the profile with a globally unique number.
| Macro | Description |
|---|---|
| %ADDRES_[field name]% | Value of [field name] of the chosen address |
| %FOLDER_[field name]% | Value of [field name] of the selected fodler |
| %CONTACT_[field name]% | Value of [field name] of the chosen contact |
| %FOLDER_ITEM_APPLIES_TO% | Value of the ‘Only visible for’ field |
| %ADDRES_COMPANY% | Company of the linked address |
| %ADRESS_COUNTRY% | Country of the linked address |
| %FOLDER_SUBJECT1% | Description of the linked folder |
| %FOLDER_TAB% | After selecting a file, offers the available tabs in the document registration. |
If one or more of these macros are set, the “default macros” will not be active anymore, after entering an address or a contact person:
By default, after choosing an address, the following fields are filled in: COMPANY, MAILADDRESS, ZIPCODE, CITY, COUNTRY, PHONE1, FAX1 and TEXT2 (the TEXT2 field is copied to the TEXT1 field of the document or folder).
By default, after selecting a contact person, the following fields are filled in: SURNAME, FIRSTNAME, PREFIX, INITIALS, SALUTATION, PHONE1, PHONE2, PHONE3, FAX1 and FAX2.
By default, no related fields are taken from folders.
Macros when handling a registration
The following macros are only executed when the current value of the field is still empty. Existing data is therefore not overwritten!
| Button | Description |
|---|---|
| DATE(PROCESSED) | After completing the registration, fill in the handling date. |
| DESTROY(DATE1,TEXT2) | Enter the destruction date after completing the registration. The destruction date is calculated by adding the number of years entered in the “TEXT2” field to the DATE1 field. The destruction date is the next 1 January after this. If the text ‘permanent’ is entered in the TEXT2 field, the destruction date will also be ‘permanent’. The destruction date can only be used in a text field. |
The macro YEAR([macro]) around an existing date macro, for example YEAR(DATE(PROCESSED)), only fills in the year.
| Macro | Description |
|---|---|
| SETINVISIBLE() | Runs when saving. Using in a BOL field makes the registration invisible to other users regardless of their permissions. |
| ANSWERTEMPLATE() | Will be executed upon selection. Fill in the correct template in the answer wizard, the field in which this macro is used must contain the same value as the name of the template. For example: Type of document = confirmation of receipt; if there is a template with the same ‘nicename’, it will already be entered in the wizard. |
| %ACTHANDLER_[field name]% | Will be executed upon selection. This macro fills in the desired data of the chosen practitioner on the activities or workflow tab. |
| %PREVIOUSVALUE% | When creating a new record, the value is filled with the last used value by the same values by the same user in the same book. |
| LASTCHANGEDATE() | Executed when creating registration, and then when saving. Fills in the date and time of the last modification of the record, in date and text field. |
| LASTCHANGEDBY() | Executed upon registration creation, and then upon save Enter the name of the user who made the last change to the record. |
| SIGNATURE() | Signed by: by electronic signature. This macro can only be used with the JOIN Client Signing feature (not with external signing providers). |
| SIGNDT() | Signed on date: with electronic signature. This macro can only be used with the JOIN Client Signing feature (not with external signing providers). |
| EXPDT() | Expiration date certificate: with electronic signature. This macro can only be used with the JOIN Client Signing feature (not with external signing providers). |
You can use a calculation in the default value of a field in an item profile by entering a formula that starts with the ‘=’-sign. Also check the macro option if you are using calculations. You can calculate with numbers and date values and can use all types of fields (numeric, text, date or BOL fields) for input and output. However, results are only displayed as a date if you apply the macro to a date field.
These macros are executed while saving the registration.
You can calculate with other fields in the same registration, for example:
= NUM1 + NUM2
is a valid expression that assigns the sum of the NUM1 and NUM2 fields to the target field. It is also possible to totalize fields of linked registrations in a field, for example:
= DSum (“DOCUMENT”, “NUM1”, “”)
Returns the sum of the NUM1 field in all linked document registrations.
You can use a number of search functions within Decos Document, namely:
| Macro | Description |
|---|---|
| =DSum(“”,“”,“”) | Sum of the values of the indicated field in linked records of the indicated type. |
| =DAvg(“”,“”,“”) | Average of the values of the indicated field in linked records of the indicated type. |
| =DMin(“”,“”,“”) | Minimum of the values of the indicated field in linked records of the indicated type. |
| =DMax(“”,“”,“”) | Maximum of the values of the specified field in linked records of the specified type. |
| =DCount(“”,“”,“”) | The number of filled values for the indicated field in linked records of the indicated type. Enter a field that is guaranteed not to be empty as the second parameter (for example “ITEM_KEY”) to count all linked registrations of the indicated type. |
| =Dlookup(“”,“”,“”) | Returns the field value to the referenced field. |
All lookup functions have three parameters, which must always be enclosed in double quotation marks [“”]:
The type of associated registration: “DOCUMENT”, “ADDRESS”, “FOLDER”, “CONTACT” (contact person) or “COBJECT” (object). The field in the linked registrations on which to perform the operation. An SQL condition on which the linked registrations must be filtered. Enter only two double quotation marks [“”] as the 3rd parameter to use all linked registrations of the specified type in the calculation.
Example
=DAvg(“CONTACT”,“DATE1”,“SURNAME=‘Jansen’”)
This gives the average value of the DATE1 field for associated contacts with the surname Jansen. Note the use of quotation marks: the three parameters of DAvg are all enclosed in double quotation marks, but in the SQL condition (3rd parameter) single quotation marks are used to enclose the text value.
Example 2
For the merging of field, say for a location address, the listing for the macro may differ per database. Below you will find examples of a Dlookup-macro that retrieves the merged location address (street, zip code and city) from a linked address.
SQL Server:
=Dlookup(“ADDRESS”,"MAILADDRESS + ‘,’ + ZIPCODE + ’ ’ + CITY)", “IT_PARENT_KEY = ‘DD812024E1A81E4FBC02485B0EB5EDB1’”)
Oracle:
=Dlookup(“ADDRESS”,"MAILADDRESS || ‘,’ || ZIPCODE || ’ ’ || CITY", “IT_PARENT_KEY = ‘DD812024E1A81E4FBC02485B0EB5EDB1’”)
MySQL:
=Dlookup(“ADDRESS”,“CONCAT(MAILADDRESS, ‘,’, ZIPCODE, ’ ', CITY)”, “IT_PARENT_KEY = ‘DD812024E1A81E4FBC02485B0EB5EDB1’”)
Note that only the address is retrieved from the address book with ITEM_KEY ‘DD812024E1A81E4FBC02485B0EB5EDB1’. If the address needs to be retrieved from all address books, replace ITEM_KEY “‘DD812024E1A81E4FBC02485B0EB5EDB1’” by [“”].
Calculated field values in Decos Document supports the following date functions:
| Macro | Description |
|---|---|
| Date() | The current date as number of days since 1/1/1900, JOIN will display the actual date. |
| Time() | The current time as a fractional number between 0 and 1 |
| Now() | The current date and time in one number |
| Year(d) | The year of the date value |
| Month(d) | Displays the month of the date as a number between 1 and 12 |
| Day(d) | Displays the day of the date as a number between 1 and 31 |
| Hour(t) | Displays the hour of the time value as a number between 1 and 23 |
| Minute(t) | Displays the hour of the time value as a number between 1 and 59 |
| Second(t) | Displays the hour of the time value as a number between 1 and 59 |
| DateSerial(y,m,d) | Composes a date value from three numbers, for example =DateSerial(2010,2,28) encodes the date February 28, 2010. |
| TimeSerial(h,m,s) | Composes a time value from three numbers |
You can also use variables with the DateSerial () and TimeSerial () functions, for example:
=DateSerial(Year(DATE1),Month(DATE1)+1,DAY(DATE1))
The macro will return a date that is exactly one month after the date in the DATE1-field. You could also add a number to Year(DATE1) to get an offset of a number of years, or a number to Day(DATE1) to get an offset of a number of days. To make the offset adjustable, the macro needs to be changed to something like:
=DateSerial(Year(DATE1),Month(DATE1)+NUM1,DAY(DATE1))
In this case, the user can input the offset in the NUM1-field, to return a date as many months after DATE1, as given in NUM1. Surely the NUM1-field must be active.
=IF([condition],[formula 1],[formula 2])
The result of the IF function is equal to [formula 1] if the condition is “true”, or [formula 2] if the condition is “false”. Condition can also directly enter a number or date without using an equation. If that value is 0 or empty it is considered “false” and thus [formula 2] is executed, for all other values [formula 1] is executed.
The IF-function is most commonly used to ensure that a field that should display the result of a formula remains empty until the input fields are filled. For example, we can apply it to a date formula:
=DateSerial(Year(DATE1),Month(DATE1)+3,Day(DATE1))
If DATE1 is not filled in, this formula gives the result February 29th, 2000. Better, however, is that the result is empty as long as DATE1 is not filled in. We can achieve this with the IF-function in which we use DATE1 as a condition and in case this field is empty to return the value 0 (empty date):
The advised way to make more complex macros is this:
BOL fields (“true” if checked) and NUM fields (“true” if not equal to 0) may also be used as [test field]. Also equations can be used as [condition], but more on this in the next topic.
An equation can also be used instead of directly testing for the value of a field:
=IF(NUM1>=18,“of age”,“minor”)
This formula applied to a text field displays the text “of age” if NUM1 is greater than or equal to 18, or “minor” if the value is less.
Normally, the functions discussed in “read-only” fields are used, because the result of the field is always a calculated value. However, it is also possible to use formulas in a writable field, by a trick with the IF function:
=IF([this field],[this field],[calculated value])
If [this field] is not filled in yet, the [calculated value] is entered once. If the user wishes to deviate from the calculation, this is also possible. If the field value is not empty, the result of this formula equals the current field value and is therefore retained.
For example: We specify this formula for the writable field DOCUMENT_DATE:
=IF(DOCUMENT_DATE,DOCUMENT_DATE,DATE()+1)
The result is that by default the date of tomorrow is entered, but the operator can also decide to enter a completely different date. Once a date has been set, it is retained when recalculating the formula.
| Macro | Description |
|---|---|
| Format([value],“[format]”) | Format returns a formatted text derived from the value. See also the explanation for formatting in Decos Admin. Example: =“IN/05-”+Format(IT_SEQUENCE,“0000”). For example, this formula applied to a text field returns the text “IN/05-0003” if the sequence number is 3. The format “0000” indicates that the number must be aligned with leading zeros at 4 positions. |
| Replace([text value],[text part],[replace with]) | Replace returns a text in which the specified portion of text has been replaced everywhere by the text in [replace with]. Replace is most commonly used to remove spaces from text. For example, there are many websites where you can search by zip code, but that zip code cannot contain a space. This formula can be used in the URL field: =“http://www.postcode.nl/index.php?address=”+Replace (ZIPCODE,“”,“”). For example, if the ZIPCODE field contains the value “2201 XA”, the URL-field will display the text “http://www.postcode.nl/index.php?address=2201XA”, without a space between 2201 and XA. |
| @display | The display: text format can be used to display a short text for displaying the link in the URL-field instead of a cluttered long web address. For example @display:Search zip code is added after the example formula from the previous paragraph, you will see in Decos web Search zip code instead of the full web address. |
| Like([text value],[compare to]) | The Like function returns 1 if the text pattern in [compare to] occurs in the text value, or 0 if it doesn’t. [compare to] must be preceded or followed by% to indicate which part of the text must match. Example: TEXT1 contains the value “Decos Software Engineering”. In that case, these three formulas all return the number 1: =Like(TEXT1,“Decos%”); =Like(TEXT1,“%eering”) or =Like(TEXT1,“%Software%”) |
| NotLike([text value],[compare to]) | NotLike returns the reverse result of Like. |
| Huisnummer([address field]) | The house number function returns the numerical part of a Dutch address as a number. This can be used to create zip code and house number combinations: Replace(ZIPCODE,“”,“”)+Huisnummer(MAILADDRESS). For example, this will return “1119PE30” if the address is “Boeing Avenue 30” and the zip code is “1119 PE”. |
The following additional functions are supported by the calculated fields:
| Function | Description | Note |
|---|---|---|
| + | Addition | |
| - | subtraction | |
| * | multiplication | |
| / | division | 35/4 = 8.75 |
| % | quotient of the division | 35% 4 = 3 (same as mod) |
| \ | integer division | 35 \ 4 = 8 |
| ^ | exponentiation | 3 ^ 1.8 = 7.22467405584208 |
| | | absolute value | -5 | = 5 (same as abs) |
| ! | factorial | 5! = 120 (same as fact) |
| abs(x) | absolute value | abs(-5) = 5 |
| atn(x) | inverse tangent | |
| cos(x) | cosine | parameter in radians |
| sin(x) | sinus | parameter in radians |
| exp(x) | natural exponent | exp(1) = 2.71828182845905 |
| fix(x) | integer part | rounded to smallest absolute value: fix(-3.8) = 3 |
| int(x) | integer part | rounded to smallest value: int(-3.8) = 4 |
| dec(x) | decimal part | dec(-3.8) = -0.8 |
| ln(x) | natural logarithm | parameter x> 0 |
| log(x) | logarithm with base 10 | parameter x> 0 |
| rnd(x) | random | returns a random number between 0 and x |
| sgn(x) | sign | returns 1 if x> 0; 0 if x = 0 -1 if x <0 |
| sqr(x) | square root | sqr (2) = 1.4142135623731; also 2 ^ (1/2) |
| cbr(x) | cube root | cbr (2) = 1.259921049899487; also 2 ^ (1/3) |
| tan(x) | tangent | parameter (in radians) x¹ k * p / 2 with k = ± 1, ± 2 … |
| acos(x) | inverse cosine | parameter -1 £ x £ 1 |
| asin(x) | inverse sine | parameter -1 £ x £ 1 |
| cosh(x) | hyperbolic cosine | |
| sinh(x) | hyperbolic sine | |
| tanh(x) | hyperbolic tangent | |
| acosh(x) | inverse hyperbolic cosine | parameter x ³ 1 |
| asinh(x) | inverse hyperbolic sine | |
| atanh(x) | inverse hyperbolic tangent | parameter -1 <x <1 |
| root(x,n) | nth root | parameter x ³ 0 (same as x ^ (1 / n) |
| mod(a;b) | quotient of the division | |
| fact(n) | faculty | parameter 0 £ n £ 170 |
| comb(n;k) | combinations | comb (6,3) = 20 |
| min(a;b) | minimum of 2 numbers | |
| max(a;b) | maximum of 2 numbers | |
| mcd(a;b) | greatest common denominator | mcm (4346; 174) = 2 |
| mcm(a;b) | least common multiplier | mcm (4346; 174) = 378102 |
| gcd(a;b) | greatest common denominator | Same as mcd |
| lcm(a;b) | least common multiplier | Same as mcm |
| yard(x) | error Gaussian function | parameter x³0 |
| gamma(x) | gamma | parameter 0 <x £ 171 |
| gamma(x) | logarithm gamma | parameter x> 0 |
| digamma(x) | digamma | parameter x> 0 |
| beta(x;y) | beta | parameter x> 0y> 0 |
| zeta(x) | zeta Riemman’s function | parameter x <-1 or x> 1 |
| egg(x) | exponential integral function | parameter x> 0 |
| csc(x) | cosecant | parameter (in radians) x¹ k * p with k = 0, ± 1, ± 2 … |
| sec(x) | secant | parameter (in radians) x¹ k * p / 2 with k = ± 1, ± 2 … |
| cot(x) | cotangent | parameter (in radians) x¹ k * p with k = 0, ± 1, ± 2 … |
| acsc(x) | inverse cosecant | |
| asec(x) | inverse secant | |
| acot(x) | inverse cotangent | |
| csch(x) | hyperbolic cosecant | parameter x> 0 |
| sech(x) | hyperbolic secant | parameter x> 1 |
| coth(x) | hyperbolic contangent | parameter x> 2 |
| acsch(x) | inverse hyperbloic cosecant | |
| asech | inverse hyperbolic secant | parameter 0 £ x £ 1 |
| acoth(x) | inverse hyperbolic cotangent | parameter x <-1 or x> 1 |
| rad(x) | conversion to radians | rad(90) = 1.5707963267949 |
| deg(x) | conversion to degrees | deg (pi / 4) = 45 |
| round(x;d) | rounds to d decimal places | round(1.35712; 2) = 1.36 |
| > | greater than | result 1 (true) or 0 (false) |
| >= | bigger or equal to | result 1 (true) or 0 (false) |
| < | less than | result 1 (true) or 0 (false) |
| <= | smaller or equal to | result 1 (true) or 0 (false) |
| = | equal to | result 1 (true) or 0 (false) |
| <> | Not equal to | result 1 (true) or 0 (false) |
| and | logical and | and (a; b result 1 (true) or 0 (false) |
| or | logical or | or (a; b) result 1 (true) or 0 (false) |
| not | logsiche not | not (a) = result 0 (false) if a ¹ 0; otherwise 1 |
| xor | logical exclusive-or | xor (a; b) = result 1 (true) only if a = b |
| nand | logical nand | nand (a; b) = result 1 (true) if a = 1 or b = 1 |
| nor | logical nor | nor (a; b) = result 1 (true) only if a = 0 and b = 0 |
| nxor | logical exclusive-nor | nxor (a; b) = result 1 (true) only if a = b |
You can use formatting codes in two places in Decos Admin to determine how a field content is displayed in the application:
This can be used, for example, to indicate that numbers representing monetary amounts should always be displayed with two decimal places, but also to format text or date fields.
The code is structured as follows:
The following format codes are available for numbers:
| Format code | Description |
|---|---|
| # | This sign represents significant figures. Insignificant characters (leading zeros) are not displayed in place of a #. |
| 0 | Non-significant zeros (leading zeros or zeros after the decimal point) are also displayed where there is a 0 in the format code. |
| . | The period represents the position of the decimal separator in the format code. You must always use a period in the format code. Decos uses a full stop or a comma in the output, depending on the set language. |
The format code for a number may begin with any sequence of characters that appear as a currency code for the number. Most commonly used is the euro symbol (€), but you can also use another currency symbol or a letter code such as EUR.
In Decos you cannot use the period to separate thousands. The reason is that the software allows to use both the period and comma as decimal separator for input. We recommend that you use the space as a thousands separator if desired, see the examples below:
| Separator | Description |
|---|---|
| 0 @ R € # ### ## 0.00 | In a field definition in an item profile: give the field the default value of 0, right-align the output and apply the format “€ # ### ## 0.00”. The number 1234567.8 is displayed in this format as: $ 1 234 567.80 |
| R € # ### ## 0.00 | The same format as above, applied in the column “Format” in the query definition of an overview. |
The following format codes are available for date fields:
Days, months and years
| Format code | Description |
|---|---|
| M. | Months as 1-12 |
| MM | Months like 01-12 |
| MMM | Months as Jan-Dec |
| MMMM | Months like January-December |
| d | Days as 1-31 |
| dd | Days as 01-31 |
| ddd | Days like Sun-Sat |
| dddd | Days like Sunday-Saturday |
| yy | Years like 00-99 |
| yyyy | Years like 1900-9999 |
Note: If you use ‘m’ immediately after the code ‘u’ or ‘hu’ or immediately before the code ‘ss’ or ‘s’, the minutes will be displayed instead of the month.
Hours, minutes and seconds
| Format code | Description |
|---|---|
| u | Hours as 0-23 |
| uu | Hours as 00-23 |
| m | Minutes as 0-59 |
| mm | Minutes as 00-59 |
| s | Seconds as 0-59 |
| ss | Seconds as 00-59 |
| u AM/PM | Hours like 4 AM |
| h:mm AM/PM | Hours like 4:36 PM h: mm |
| h:mm: ss A/P | Time as 4: 36: 03P |
| [h]:mm | Elapsed time in hours, for example 25:02 |
| [mm]:ss | Elapsed time in minutes |
| [ss] | Elapsed time in seconds |
| h:mm:ss.00 | Fractions of a second |
AM and PM If the format includes AM or PM, the hour is based on the 12-hour clock. “AM” or “A” indicates the time from midnight to noon and “PM” or “P” indicates the time from noon to midnight. If you do not specify this indication, the time is based on the 24-hour clock. The ‘m’ or ‘mm’ code must be immediately after the ‘h’ or ‘hu’ code or immediately before the ‘ss’ code, otherwise the month will be displayed instead of the minutes.
| Format code | Description |
|---|---|
| > | Use this character to always display the text in capital letters |
| < | Use this character to always display the text in lowercase |
For a text field in an item profile, you must always check the “Macro” option if you are using a formatting code. This is not necessary for a numeric or date field if you are not using macros in combination with the format code.
If you are using a macro with a formatting code, the formatting code must be added at the end of the macro. A format code does not change the content of the fields in the database, but only the appearance of the fields in JOIN.
Database connection
Decos D5 can use different types of databases. The application supports the following databases: Oracle Microsoft SQL Server MySQL
In order for Decos D5 to communicate with a database, you will have to set a connection string for the database. This connection string must be set in the global.asa file of Decos D5 as well as in DecosAdmin.ini of Decos Admin and should only be changed by experienced administrators!
To provide existing pieces with globally unique numbers after adding a GLOBALNUM () macro, you can use the GlobalNumberingTool. You can download this tool here: http://support.decos.nl/GlobalNumberingTool.zip Start the tool on a machine where Decos Admin is available or on the Decos web server. If the program has been started in a valid Decos environment, it will first display the message “Successfully connected to database”. Then click OK to start the renumbering process. When the process is completed, the message “Action complete” is displayed. After running the tool, the pieces that use a profile containing a GLOBALNUM macro that had not yet had a number are provided with a unique sequence number. Pieces that already had a number in this field are not included and may still have a non-unique number. If you want to be sure that all pieces that match the profile get a unique number, it is recommended to first clear the numbering field in all pieces by making a bulk change. The program saves a log file called GlobalNumbering- yyyymmdd.log (with yyyymmdd the date) in the folder where it is started. In this folder you will also find a file named GlobalNumbering- yyyymmdd-undo.sql afterwards. This sql script can be used by your database administrator to reverse the actions of the tool.
See also: Macros when creating a registration