J

J. River Database Expressions
(last updated: February 21, 2006)

Overview

The J. River Media Core database engine supports Excel-style functions for use in view schemes, searches, and displayed columns.

An expression is a mixture of text, fields, and functions.

Examples:
This is simple text
This is an artist: [Artist]
The song [Name] is rated [Rating] stars
The file is FormatBoolean(IsMissing(), missing from, on) your machine

Fields

Any text between brackets [] will be replaced with the corresponding field. As an example, [Artist] would be replaced by Bob Dylan for any Bob Dylan tracks. If the text between brackets doesn't match any known fields, it will be left alone. After the field name, a comma can be placed followed by a 0 or 1 for whether the field should get formatted. So, [Duration] and [Duration, 1] will give "4:02" while [Duration, 0] will give "242".

Functions

A function allows special operations to be performed. A list of functions follows:

Field(...): Outputs the value for a given field.

Field name: the name of the field to evaluate (i.e. Artist, Album, Name, etc.)
Format for display: 0 to use raw data, 1 to use data formatted for display (optional: defaults to 1)

Notes:
You can use brackets around a field to accomplish the same thing. For example, GetField(Artist) can be replaced by [Artist] and GetField(Artist, 0) can be replaced by [Artist, 0].

Examples:
Field(Artist)
Field(Artist, 0)

If(...): Outputs different values depending on the value of the first parameter.

Test Expression: the value that gets tested
True: the value used if the test expression equals 1
False: the value used if the test expression does not equal 1

Examples:
If(1, 1, 0)
If(IsMissing(), The file is missing., The file is here.)
If(IsEqual([Artist], Abba, 1), Too embarrassing, [Artist])

IsMissing(...): Checks to see if a file exists on the system.

Filename: the filename to check (optional: defaults to this file)

Examples:
IsMissing()
IsMissing([Filename])

IsRemovable(...): Checks to see if a file resides on removable media.

Filename: the filename to check (optional: defaults to this file)

Examples:
IsRemovable()
IsRemovable([Filename])

IsEqual(...): Compares values and outputs a "1" if the values pass the test, and "0" if they don't pass the test.

Value 1: the first value
Value 2: the second value
Compare type: the mode to compare (optional: defaults to case-sensitive string compare)

0: case-sensitive string compare for equality
1: case-insensitive string compare for equality
2: numeric compare for equality
3: numeric less than
4: numeric less than or equal to
5: numeric greater than
6: numeric greater than or equal to

Examples:
IsEqual([Artist], [Album], 1)
IsEqual([Duration, 0], [Bitrate, 0], 2)

IsEmpty(...): Tests to see if a value is empty and outputs a "1" if the value is empty, and "0" if it's not empty.

Value: the value to test
Mode: the mode to test (optional: defaults to 0)

0: string style, so "" is empty
1: number style, so "" or 0 is empty

Examples:
IsEmpty([Artist])
IsEmpty([Duration], 1)

IsRange(...): Compares a value against a range and outputs a "1" if the values is inside the range, and "0" if not.

Value: the value to test
Range: the range of values (in the form: a-z or 1-100)

Examples:
IsRange([Artist], a-b)
IsRange([Bitrate], 128-192)

FilePath(...): Returns the path from a filename.

Filename: the filename to check (optional: defaults to this file)

Examples:
FilePath()
FilePath([Filename])

FileName(...): Returns the name from a filename.

Filename: the filename to check (optional: defaults to this file)

Examples:
FileName()
FileName([Filename])

FileVolume(...): Returns the volume name from a filename.

Filename: the filename to check (optional: defaults to this file)

Examples:
FileVolume()
FileVolume([Filename])

FormatNumber(...): Formats a number in a specified manner.

Value: the number to format
Number of decimal places: specifies how many decimals to use (-1 uses as many as necessary) (optional: defaults to 0)

Examples:
FormatNumber(3.123224, 0)
FormatNumber([Replay Gain, 0], 3)
FormatNumber([Duration, 0])

FormatDate(...): Formats a date value in a specified manner.

Value: the date to format
Formatting: formatting style

Year: 1997
Month: March
Day: 12
Filename: 20040521-032221
Elapsed: 3.2 days ago
Other: flexible formatting (i.e. MMMM-d)

No Date Output: the output when the date is empty (optional: defaults to nothing)

Examples:
FormatDate([Date Imported, 0], elapsed)
FormatDate([Date, 0], MMMM: d, no date)

FormatBoolean(...): Formats a boolean (true / false) value in a specified manner.

Value: the boolean to format (optional: defaults to false)
True display: string to display for true (optional: defaults to "True")
False display: string to display for false (optional: defaults to "False")

Examples:
FormatBoolean(1)
FormatBoolean(IsMissing(), File missing, File exists)

FormatDuration(...): Formats a duration in seconds to a readable string.

Value: the value to format

Examples:
FormatDuration(60)
FormatDuration([Duration, 0])

FormatFileSize(...): Formats a number of bytes as a readable string.

Value: the value to format

Examples:
FormatFileSize(1024)
FormatFileSize([File Size, 0])

FormatRange(...): Formats a value as a range.

Value: the value to format
Range size: the number of letters / numbers to put in a grouping (optional: defaults to 1)
Mode: the mode to perform the grouping (optional: defaults to 0)

0: automatically choose between number / letter grouping
1: use letter grouping
2: use number grouping

Examples:
FormatRange(Abba, 3)
FormatRange([Artist])
FormatRange([Bitrate, 0], 100, 2)

AlbumArtist(...): Returns the calculated album artist for a file.

No parameters

Examples:
AlbumArtist()

AlbumType(...): Returns the album type for a file.

No parameters

Examples:
AlbumType()

Size(...): Returns the size of a file in a media-type independent manner.

No parameters

Examples:
Size()

CustomData(...): Returns custom data stored in a file array. (used primarily for internal uses)

Field: a field name ("#" gets the array sequence number)

Examples:
CustomData(#)
CustomData(My Special List Data Field)

Clean(...): Returns a cleaned up version of a filled in template.

Value: the value to clean

Examples:
Clean(Size())
Clean([Artist] - [Album] /([Genre]/))

FixCase(...): Changes the case of a string.

Value: the value to change
Mode: case mode (optional: defaults to title case)

0: title case
1: all words
2: first word
3: all uppercase
4: all lowercase

Examples:
FixCase(MaKe ME PreTTy, 0)
FixCase([File Type], 3)

Mid(...): Retrieves specified characters from a value.

Value: the value to get characters from
Start: the character to start at (optional: defaults to 0)
Characters: the number of characters to get (-1 returns all) (optional: defaults to 1)

Examples:
Mid(Abba)
Mid(Abba, 0, 2)
Mid([Artist], 3, -1)

Notes

To use a special character (bracket, parenthesis) as regular text in a function, place a / before it. Change / to // to output an actual slash. Example: Clean([Artist] - [Album] /([Genre]/))
Case (upper vs. lower) does not matter for function names, field names, and most parameters.
Spaces are interpreted literally in all areas, except immediately after a comma in a function.
To specify a data type (for sorting, etc.), add &DataType=[...] to the end of the expression where ... is one of these values:

String: sorts as strings (with smart number handling)
List: a list of strings, separated by semicolons
Number: sorts values as numbers (decimal or integer)
Path: sorts using a smart filename compare style
Month: sorts string month names (i.e. January, February, etc.)

Examples:
FormatDate([Date, 0], Month)&DataType=[Month]