ViewDBFWin MJ Library Functions
Introduction, Usage and Operating System/DKapture Support Functions
MJ Library Functions are some special functions which allow the
expression engine a little more flexibility. You can use
these functions inside an expression, mix them freely in Advantage Expressions, and nest them
as deeply as you like. These functions can be used almost anywhere except for building
indices. For example, they can be used in
Filters, and
Pivot Table expressions. They can also be embedded into
Report Templates, used in
Statistical Analysis, and used for
Setting Fields, amongst many other things. You can test
DKapture multi-codes with the MJCODETEST function. The following
functions glean information from the OS and current DKapture survey (if applicable) :-
MJEMPTYDB()
Returns .T. or .F. depending on whether there are any visible records in the current database.
MJCURDIR()
Gives the current working drive and directory, including the trailing backslash.
MJLASTERROR()
Gives the error message associated with the last operation performed as reported by the
operating system.
MJSERNO()
Gives the current interview's serial number, or one (1) if there is none.
MJSTATNO()
Gives the current interviewer's station number directly from the STN environment variable.
MJINTNO()
Gives the current interviewer number, or zero if they don't have one.
MJIPADDRESS()
Gives the current IP address for the machine.
MJMACADDRESS()
Gives the current first MAC address (unique network card number) for the machine.
MJUSERNAME()
Gives the currently logged in user name or blank if it cannot be derived.
MJDNSNAME()
Gives the PC's Domain Name Server Name or blank if it cannot be derived.
MJDBNAME()
Gives the full filename of the currently open database, or blank if nothing is open.
MJGETDKFLD(database field,question label)
Gets the data for the DKapture question question label stored in the multi-line memo
database field, or blank if there is none or the label does not exist. The format for
the data in database field should the same as that for the
DATARECORD field in the sample system.
For example, MJGETDKFLD(DATARECORD,QA1)
Arguments and the Use of Quotes in MJ Library Functions
MJ Library functions take comma-separated arguments. If an argument itself contains commas that
are not enclosed in quotes or parentheses (ie. the argument is a string containing
commas, rather than as part of an expression, for example), then that argument needs to be
enclosed in quotes. Literal string arguments to Advantage expression functions always
require quotes (single quotes, double quotes, or square brackets). Most arguments to MJ
library functions will allow you to use literal strings unquoted, as long as the literal string
is not an evaluatable expression and doesn't contain a comma. However, in the functions
MJSTATS, MJLOOKUP, MJSENDMAIL, and MJSENDFAX, you must use quotes to enclose literal string
arguments, or prepend the literal string with a % sign. An alternative to using
either single or double quotes, is to use square brackets ([]) to delimit the string, as
in [howdy] which is the same as 'howdy'. For example,
MJSTRTRAN(UPPER(CLIENTNAME),IF(MJRANDOM(2)==1,'E','N'),'O')
MJCHUNK("aaa,bbb,ccc,ddd,eee",",",4) which gives 'ddd'
MJCHUNK([aaa,bbb,ccc,ddd,eee],[,],2) which gives 'bbb'
MJCHUNK('aaa,bbb,ccc,ddd,eee',',',3) which gives 'ccc'
MJCHUNK("aaa,bbb,ccc,ddd,eee",",",6) which gives line-feed or mjchr(10) since there is no 6th chunk
Optional arguments can be omitted, just make sure the comma placement is correct if there are
arguments to the right of an omitted optional argument.
MJ Library Function Reference
MJADDREC([DBName],Pipe Delimited Field Set Expressions,[RecordNo])
Adds a record to the database specified by the expression DBName. If DBName is
not a valid expression, it is taken as a literal string. If DBName does not exist, an
error is returned. If DBName is blank, the current database is used. The fields are
populated from the fieldnames and expressions held in the string Pipe Delimited Field Set
Expressions. The delimiter character is | (pipe, or Shift+Backslash). Each Pipe
Delimited Field Set Expression should be of the form 'fieldname=expression', as used in
the SET macro command. If the optional RecordNo
parameter is specified, and it is a number greater than zero, then MJADDREC will move to that
record number and populate the fields on that record. The database is opened in
shared mode and a single record appended to it if
RecordNo is not specified, or, if it is, the record at RecordNo is locked. The
fields are then filled with the evaluated expressions in the second argument. If the field
being set is a raw, binary or image type field, then the expression should yield a filename, from whose
contents the field will be set. If the file does not exist, then the raw, binary or image type
field will be blanked out. If any pipe-delimited expression is not valid, it is taken as a
literal string. If DBName is specified, the database is auto-closed after 20 seconds of
inactivity and re-opened when necessary. For example, if you have a database called EXPERI
with a structure as follows :-
NAME,C,20
ADD1,C,30
ADD2,C,30
ADD3,C,30
ADD4,C,30
POSTCODE,C,8
SPECIALCOD,N,9,0
then MJADDREC(%EXPERI,name=Marco Polo|add1=27 Main Street|add2=Bamford|add3=Wrexham|add4=United Kingdom|postcode=WX3 5TJ|specialcod=47091026)
would add a new record to the EXPERI database in the current directory, and populate the fields
with the data specified. Also see the MJADDREC-based example macro.
Another example with the database DEST.DBF structure :-
FYEAR,N,4,0
FTIME,C,5
FNAMATT,C,200
HALFSIZE,N,10,0
and the current database with a structure as follows :-
FILENAME,CHARACTER,160
FILEDATE,DATE,8
FILETIME,CHARACTER,12
FILESIZE,NUMERIC,10,0
ATTRIBUTES,CHARACTER,5
then MJADDREC(%DEST,fyear=LEFT(DTOS(FILEDATE),4)|ftime=LEFT(FILETIME,5)|fnamatt=RTRIM(FILENAME)+' : '+ATTRIBUTES|halfsize=FILESIZE/2)
MJALPHANUM(Expression)
Returns the string resulting from evaluating Expression, with only A-Z, a-z, and 0-9
alphanumeric characters in it. It takes into account your locale, so that accented characters
will be allowed if you are in, say, France. It is a way of "cleaning" strings so that they
contain no control characters or punctuation. For example,
MJALPHANUM(5%h yu*i92!\4d) would give the string 5hyui924d
MJASCII(Expression)
Returns the ASCII value of the first character in the string Expression, or zero if Expression
is an empty string. For example, MJASCII(MJLF()) would give the number 13. This is the opposite of
the function MJCHR.
MJASK(Caption,Prompt,InitialValue)
This prompts the user for input. The input box has a title specified by Caption and a prompt
specified by Prompt. The entry box is filled with InitialValue to begin with, which the user
can overtype. The return value is the user input string, or blank if the dialog was cancelled.
If any of the arguments are valid expressions, they are evaluated first, otherwise they are
taken as is. An example macro using this might be :-
DEFINE cutoff=MJASK(Record Verifier Macro,Please Input the Cut-Off Date :-,DATE()-14)
FILTER CALLDATE>=CTOD('#cutoff#')
MJBAND(Numeric Expression,Band Definition)
This returns a string denoting which range of numbers Numeric Expression falls into.
Numeric Expression is the integer you wish to "band".
Band Definition are the banding ranges into which integer will go. If this is a number,
then bands are divided into ranges from 1 to number, then from number+1 to number*2, and so
on. 0 is always in a band by itself. If it is a string of comma-separated ranges, then the
range which contains the number is returned, and if it is not in any of the specified ranges, a
blank string is returned. A range without a closing number goes to infinity. If MJBAND is used
in Pivot Table and the Band Definition consists of separate,
comma-delimited ranges, then a row or column is output in the table for each range, whether or
not there are records with values in those bands. This can be overidden with the DROPCODES
keyword in a macro command, or by using the appropriate checkbox on the pivot table parameters
screen. For example :-
MJBAND(3,"1-2,3-4,5-7,8-") would give 3-4
MJBAND(39,"1-2,3-4,5-7,8-") would give 8-
MJBAND(1209,100) would give 1201 - 1300
MJCASE(Test1:Expr1;Test2:Expr2;Test3:Expr3;...;[default:]Exprn)
This evaluates Test1 and if it is true, returns Expr1 evaluated (if it is a legal expression) or
as is (if it is not a legal expression). If Test1 is false, it will try Test2 and so on, until
it finds one that evaluates to true. If it does not find one, it will use the default
expression (Exprn), or return blank if there is no default. If an expression contains an
unenclosed : or ; (by brackets or quotes), then enclose it in double quotes. For example :-
MJCASE(TIME()>='23:00':Go to bed now.;TIME()>='22:00':Don your slippers.;TIME()>='12:00':"Afternoon;tea-time!";Anything else.)
would give "Go to bed now." after 11pm, "Don your slippers." after 10pm,
"Afternoon;tea-time!" after noon, and "Anything else." at any other time.
MJCHOOSEMANY(Caption,Prompt,Items[,CheckedItems][,Delimiter])
This allows a user to choose several items from a list. The window that appears will have a
caption of Caption and will prompt the user above the list of items with Prompt.
Items is a comma-separated list of strings to choose from. CheckedItems is a
comma-separated list of items that should be checked before the window appears to the user (a
method of initialising what is checked in the listbox that appears). Delimiter is what
is used to join the items that have been picked by the user when they OK the dialog. It
defaults to a comma. If they cancel the dialog or choose nothing, then an empty string is
returned. Please note that the fields specified in CheckedItems should usually be
wrapped in quotes, even when a single field is specified. For example :-
MJCHOOSEMANY(Window Caption,Textual Prompt :-,mjdbfields(),'station,outcome',mjlf())
would produce a window with a caption "Window Caption", a prompt "Textual Prompt :-", a list of
fields in the currently open database with checkboxes to multi-choose from, and with the
fields STATION and OUTCOME already checked when the dialog appears. If the user OKs the dialog
as is, then
STATION
OUTCOME
would be returned by this function call (the delimiter is the new line pair CR-LF).
MJCHOOSEONE(Caption,Prompt,Items)
This allows a user to choose a single item from a list. The window that appears will have a
caption of Caption and will prompt the user above the list of items with Prompt.
Items is a comma-separated list of strings to choose from. The item they choose is
returned as a string or a blank string if they cancel the dialog.
MJCHR(Expression)
Returns the character corresponding to the ASCII code Expression evaluates to. MJCHR(13)+MJCHR(10)
is the equivalent of MJLF().
MJCHUNK(Expression,Delimiter,Element Expression)
This function chops up the Expression using the character Delimiter (a single character), and
returns the "Element Expression"th element of the chopped up string. Avoid using brackets and
quotes as the Delimiter character.
Expression is what you want to chop up. Delimiter can be a control character
by specifying it using the MJCHR function. Element Expression is a numeric expression
or literal number that denotes which section of the chopped up string to return. If the
element is beyond the end of the string, it returns line-feed (MJCHR(10)), so as to
discriminate between this condition and an empty element. To detect this condition in a macro,
use the following technique :-
MODE SHARED
DEFINE stf=MJCHOOSEMANY(Field Blanker,Check the Fields You Want to Blank Out :-,\
MJDBFIELDS(),'CONTACT,TELNO,OTHDETS,NOTES,DATARECORD,NAME,TEL,TELE',&)
IF '@#stf#'=='@' THEN GOTO end
DEFINE ii=1
:top
DEFINE seg=MJCHUNK(#stf#,&,#ii#)
IF MJASCII('#seg#')==10 THEN GOTO end
SET #seg#=''
DEFINE ii=#ii#+1
GOTO top
:end
Note the use of IF MJASCII('#seg#')==10 to detect when there are no more "chunks" left
in the string, and the use of IF '@#stf#'=='@' to detect a blank return value from an MJ
library function. Another example :-
MJCHUNK(55 57 67 69 121 388, ,60/12) gives 121
MJCODETEST(TestCodes,AllowableCodes,Allin)
This function returns true or false, and can be used in filters on DKapture database data
files. All the arguments are evaluated first if they are legal expressions. If the codes
parameter contains commas, it must be enclosed in double quotes.
TestCodes are the comma-separated ranges or codes to test.
AllowableCodes are the comma-separated ranges or codes that are to be tested for.
Allin is .T. or .F. (true or false) as to whether this is an ALLIN test or not. If it is
true, then all the codes in TestCodes must be present in AllowableCodes for the function to
return true. If it is omitted or false, then any of the codes in TestCodes can be present in
Allowable codes for the function to return true. For example :-
MJCODETEST('3-6','5-9') gives .T.
MJCODETEST('3-6','5-9',.T.) gives .F.
MJCODETEST(%3-6,%5-9,true) gives .F.
MJCODETEST('3-6,8,12',5-9,.f.) gives .F. (because 5-9 evaluates to -4 which doesn't match any of the test codes)
MJCODETEST('3-6,8,12',%5-9,.f.) gives .T.
MJCODETEST('2,6-9,11','1-12',.t.) gives .T.
MJCODETEST('2,6-9,11','1-10',.t.) gives .F.
MJCONCAT(Expression1[,Expression2][,Expression3][,Expression4][,Expression5])
Concantenates the strings held in the Expression parameters (from left to right) and returns
the resulting long string. This can be used where the Advantage
String concatenation operator '+' fails when the
strings being concatenated are too long (>~15KBytes).
MJCONTSFROMURL(Expression)
Expression yields a URL and this returns the textual contents of that URL. Requires Internet access.
MJCOPYFILE(SourceFile,TargetFile)
Copies SourceFile to TargetFile, overwriting TargetFile if it already existed. If either argument
is a valid expression it is evaluated beforehand to yield the filename. Returns .T. if successful
and .F. otherwise. Use MJLASTERROR() to see what went wrong when .F. is returned.
MJCSVFIELD(Expression)
Gives the string returned by evaluating Expression, in a form that can be used in a CSV file.
If the string contains both a comma and double quotes, the double quotes are changed to single
quotes. If it contains a comma, it is surrounded in double quotes to properly delimit the
comma contained therein. If it contains CR/LF pairs, these are changed to LF so Excel will
import the CSV file properly.
MJDATETIME(Expression[,Format])
Converts Expression into a string representing the date and time in the current locale's long
formats, or, if Format is specified, formatted as in the table below. It will convert from a
variety of date/time formats, including UTC format (used in email and newsgroup message headers
- for example ddd, d mmm yyyy hh:nn:ss +/-aaaa). If it cannot convert Expression, it returns a
blank string. If Expression or Format are valid expressions, they are evaluated first,
otherwise they are taken as literal strings. Formats table :-
| Format String | Displays |
|---|
| c | Displays the date using Short Date Format, followed by the time using the Long Time Format. |
| d | Displays the day as a number without a leading zero (1-31). |
| dd | Displays the day as a number with a leading zero (01-31). |
| ddd | Displays the day as an abbreviation (Sun-Sat). |
| dddd | Displays the day as a full name (Sunday-Saturday). |
| ddddd | Displays the date using the Short Date Format. |
| dddddd | Displays the date using the Long Date Format. |
| m | Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed. |
| mm | Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed. |
| mmm | Displays the month as an abbreviation (Jan-Dec). |
| mmmm | Displays the month as a full name (January-December). |
| yy | Displays the year as a two-digit number (00-99). |
| yyyy | Displays the year as a four-digit number (0000-9999). |
| h | Displays the hour without a leading zero (0-23). |
| hh | Displays the hour with a leading zero (00-23). |
| n | Displays the minute without a leading zero (0-59). |
| nn | Displays the minute with a leading zero (00-59). |
| s | Displays the second without a leading zero (0-59). |
| ss | Displays the second with a leading zero (00-59). |
| t | Displays the time using the Short Time Format. |
| tt | Displays the time using the Long Time Format. |
| am/pm | Uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. The am/pm specifier can use lower, upper, or mixed case, and the result is displayed accordingly. |
| a/p | Uses the 12-hour clock for the preceding h or hh specifier, and displays 'a' for any hour before noon, and 'p' for any hour after noon. The a/p specifier can use lower, upper, or mixed case, and the result is displayed accordingly. |
| ampm | Uses the 12-hour clock for the preceding h or hh specifier, and adds the relevant AM/PM string. |
| / | Displays the date separator character given by the current locale. |
| : | Displays the time separator character given by the current locale. |
| 'xx'/"xx" | Characters enclosed in single or double quotes are displayed as is, and do not affect formatting. |
MJDBFIELDS([FieldMasks][,FieldType])
Returns a comma-separated list of field names from the currently open database. You can use the
optional FieldMasks argument to limit the fields returned to a specific subset of those
discovered in the database. You can additionally use the second optional argument FieldType
to limit the fields to one particular type. The types allowed are :-
- Logical Field Type
- Date or Timestamp Field Type
- Any Type of Numeric Field
- Any Character, Memo, Binary, Image or Raw Field Type
If there is no database open, then a blank string is returned.
FieldMasks should be a semicolon-delimited list of possibly wildcarded field identifiers.
It is case insensitive and can use the mjre: prefix to denote a
regular expression match. If you prefix a field mask with a minus sign (-) then it will exclude
those fields that match that mask. For example :-
MJDBFIELDS(Q9G*;mjre:^DKDATA\d+$;mjre:^S\d+$)
would produce a comma-separated list of fields from the current database that start with Q9G,
or match DKDATAx or Sx where x is a number and there is at least one digit (^ means match the
start of the field name and $ means match the end of the field name).
MJDBHTMEDIT([Filename][,AllowedFields,Action[|Filter[|Picklists]],colour1,colour2])
MJDBHTMFILTER([Filename][,AllowedFields,colour1,colour2])
The MJDBHTMFILTER function returns a string holding HTML table rows that define the
body of a <TABLE> for filtering the records in the database specified by Filename,
or the currently open database if Filename is not specified. The MJDBHTMEDIT
function returns a string holding HTML table rows that define the body of a <TABLE> for
browsing, selecting, editing, deleting and adding records in the database specified by
Filename, or the currently open database if Filename is not specified. There
should be an HTML template file into which the returned string is substituted to make a web
page that can filter the database. Any HTML template using this table should include the
scripts dkwsafuncs.js and datepicker.js which are found in the \DKFILES directory on the
DKapture installation drive. For MJDBHTMFILTER, the Javascript function getxp() can be
used in the HTML template to return a valid expression to filter the database with ViewDBFWin.
colour1 and colour2 provide the table row alternation colours and default to
white. AllowedFields is an optional comma-separated list of fields you want in the
table. If omitted, all fields will be put in the table.
For MJDBHTMEDIT, the optional Action field can be "view", "browse" or "multi"
for a record viewer, selector or multi-record selector, where no editing can take place. If
omitted, the default Action is "edit". If the Action is "view", then you can
prefix any of the AllowedFields with a + (plus) to mean sum that column or
a ~ (tilde) to mean average that column. If you follow the Action with
| (pipe) and a filter expression Filter, for example, "browse|RECNO()>90 AND
RECNO()<100", then only records satisfying the filter expression will be put in the HTML
table. In our example, only records between number 91 and 99 inclusive will be shown. You can
specify that certain fields have drop-down Picklists by following the optional filter
with another | (pipe) and the Picklists in the following format :-
Fieldname1=Option1,Option2,Option3...¬Fieldname2=Option1,Option2,Option3... and so on ...
If you prefix an option with an asterisk and Action is "edit", then that option is the
default selected option on the new record line in the table. If an option consists of
"value~description" then the value for that option is "value" and what is displayed in the
picklist is "value description". For example, you could have an Action argument that
looks like
edit|RECDATE>=DATE()-7|JOBNUM=3007,3008,3009¬CENTRE=R,*Q,W
which would present an "Add Rec." line with JOBNUM on 3007 and CENTRE on Q. To have the line
come up with a blank JOBNUM but still with a picklist, use
edit|RECDATE>=DATE()-7|JOBNUM=,3007~Iceland,3008~Waitrose,3009~Discount Direct¬CENTRE=R,*Q,W
where the starting comma on the JOBNUM options sets the "Add Rec." line with JOBNUM as initially
blank and the options are presented with shop names next to them in the pull-down list.
Please note that MJDBHTMEDIT should be used cautiously on large databases as the
output will consist of multi-megabyte strings that can slow the system down. The input fields
in the table are named "rn000000xyz_FIELDNAME" where xyz is a 9-digit-wide zero-padded record
number and FIELDNAME is the name of the field in the database. The select checkboxes (Action
is "multi") or radiobuttons (Action is "browse") are called "Select_qq" and will
have the value(s) of the record number(s) selected when the form is submitted. The delete
checkboxes (Action is omitted or "edit") are called "Delete_qq" and will have the value
(s) of the record number(s) marked for deletion when the form is submitted. For example, the
following macro allows the user to choose some fields from the currently open database, and put
them onto a web page for browsing and selecting multiple records from. Only records between
number 91 and 99 inclusive will be shown. It uses an HTML template file with mnemonics _HOST_
for the current host and _TBLEDIT_ for the rows and cells of the table itself :-
define flds=mjdbfields()
define chz=mjchoosemany(Pick Some Fields,Select :-,[#flds#])
define tbl=MJDBHTMEDIT(,[#chz#],multi|RECNO()>90 AND RECNO()<100)
define tpl=mjstrtran(mjreadfile(C:\myhtmltemplates\dbbrowseedit.htm),_HOST_,localhost)
define tpl=mjstrtran([#tpl#],_TBLEDIT_,[#tbl#])
define res=mjwritefile(c:\temp\mjtest.htm,[#tpl#])
start c:\temp\mjtest.htm
MJDECRYPT(Expression)
Returns the decrypted evaluated Expression. Expression should yield a string consisting of the
digits 0 to 9, in order for this to work properly. It should be the return value of a call to
MJENCRYPT.
MJDELETEFILE(Filename)
Deletes the file specified by Filename. If the argument is a valid expression it is evaluated
beforehand to yield the filename. Returns .T. if successful and .F. otherwise. Use MJLASTERROR()
to see what went wrong when .F. is returned.
MJDHMS(NumberofSeconds[,TimeFormat])
Returns a string expressing the evaluated NumberofSeconds in days, hours, minutes and seconds
(ie. as a duration), or as a time (hh:mm:ss) if TimeFormat evaluates as .T. (true).
NumberofSeconds should be a number of seconds duration. The returned string will be in the
format 5d 14h 21m 32s, for example, or 17:43:25 if TimeFormat is true. In duration format, if
the duration does not exceed one day, the day part is omitted. Similarly for hours and minutes.
The seconds part is always included. For example :-
MJDHMS(3600) gives 1h 0s
MJDHMS(11) gives 11s
MJDHMS(10215,(4>2)) gives 02:50:15
MJDHMS(671562) gives 7d 18h 32m 42s
MJDHMS(-7) gives -7s
MJDHMS(0.5) gives 0s
MJENCRYPT(Expression)
Returns the encrypted evaluated Expression. The resultant string consists of the digits 0 to 9.
MJEVAL(Expression)
Expression should be a string, which itself is an expression, for example, "1+2*3-4". MJEVAL
evaluates the string and returns the result. The example would return 3. Another example :-
MJEVAL(MJSTRTRAN('11,12,13',',','+')) gives 36
Given a string field FIELD1 containing the text "57 + 81 + 03"
MJFLOOR(MJEVAL("(9/7+5/6)*("+FIELD1+")")) gives 298
MJEXPRTYPE(Expression)
Returns a number denoting the type of Expression. The number is one of :-
1 - Logical Expression
2 - Date Expression
3 - Numerical Expression
4 - String Expression
0 - Illegal Expression
Expression is not effected while ascertaining its type. So, if Expression is MJSENDMAIL(...),
no email is sent out by calling it inside of MJEXPRTYPE. For example :-
MJEXPRTYPE(CTOD('11/11/2012')>DATE()) gives 1
MJEXPRTYPE(CTOD('11/11/2012')) gives 2
MJEXPRTYPE(11/11/2012) gives 3
MJEXPRTYPE('11/11/2012') gives 4
MJEXPRTYPE(DATE()-7) gives 2
MJEXPRTYPE(7-DATE()) gives 0
MJEXTFROMCONTS(Expression)
Expression yields a filename and this returns the expected extension of that file from the
first 16 bytes of the file itself. The return value does not include the period character.
MJFILELIST(Path,Patterns[,DateRange,SizeRanges,Full[/Dirpatterns]])
Returns a CR-LF delimited list of files matching Patterns in the directory Path.
Patterns can consist of semicolon-delimited lists of file masks, for example, "*.exe;
*.com". If you prefix a file mask with a minus sign (-) then it will exclude those files.
DateRange is an optional date and time range string of the form "Date1 Time1 - Date2
Time2" where either side of the hyphen can be omitted if no lower or higher limit is required.
You can also use a single asterisk (*) as the DateRange to mean return the most recent
file, rather than a list of files. SizeRanges is a comma-separated string of numerical
ranges that determines the allowable sizes of the files to be returned in the list (or the
single file if "*" is used for DateRange). Full is optionally used to denote that
the size, date and time of each file is appended to the filename in the returned list.
Optional Dirpatterns can tell MJFILELIST to recurse into Path and only consider
subdirectories found therein that match the patterns specified by Dirpatterns. You can
use *.* for Dirpatterns to mean search all subdirectories of Path for
files matching Patterns. The syntax for specifying Dirpatterns is exactly the
same as that used for Patterns. If Dirpatterns is specified, the filenames
returned contain the path portion, and subdirectories of Path are searched. If
Dirpatterns is not specified, the filenames returned do not contain the
path portion, and subdirectories of Path are not searched. For example :-
MJFILELIST(c:\temp,*.wav)
gives a list of WAV files in the TEMP directory on the C: drive.
MJFILELIST(f:\jobs\Proj1,*.doc;*.xls;*.csv;*.ppt;*.rtf;*.mdb,DATE()-14,1000000-)
gives a list of MS Office files in f:\jobs\Proj1 up to 2 weeks old larger than 999999 bytes.
MJFILELIST(f:\jobs\Proj1,*.doc;*.xls;*.csv;*.ppt;*.rtf;*.mdb,'-'+DTOC(DATE()-90))
gives a list of MS Office files in f:\jobs\Proj1 more than 89 days old.
MJFILELIST(f:\jobs\Proj1,*.doc;*.xls;*.csv;*.ppt;*.rtf;*.mdb,*,1000000-)
gives the name of the most recent MS Office file in f:\jobs\Proj1 larger than 999999 bytes.
MJFILELIST(c:\,*.*,*,,/lib)
finds the most recent file in any directory on the c: drive called lib.
MJFILELIST(c:\,*.*;-vcl*.*,*,,/lib;obj)
finds the most recent file not starting with "vcl" in any directory on the c: drive called lib or obj.
MJFILELIST(f:\jobs,*_sn107*.wav,'-'+DTOC(DATE()-90),,Full/*.*)
finds all files older than 89 days old, matching *_sn107*.wav in any directory in f:\jobs, returning lines as follows :-
f:\jobs\3403w\Recordings\3403w_spl_SN107_IN94_ST9_2015.wav | 15/04/2010 10:06:46 | 7350046
f:\jobs\3403w\Recordings\Luton\3587q_spl_SN1071_IN9_ST12_3512.wav | 15/02/2010 14:58:46 | 1644046
f:\jobs\3646w\Recordings\3646w_spl_SN107_IN280_ST17_1012.wav | 06/04/2010 19:12:06 | 166046
f:\jobs\3646w\Recordings\3646w_spl_SN107_IN280_ST17_1014.wav | 06/04/2010 19:12:16 | 80046
MJFILESIZE(Filename)
Returns the size of Filename in bytes or 0 if the file does not exist. If Filename is not a
valid expression, it is taken as a literal string.
MJFILETIME(Filename)
Returns the date and time of when Filename was last modified or an empty string if the file
does not exist. If Filename is not a valid expression, it is taken as a literal string.
MJFINDTEXT(Expression,Search Text Expression)
This enables you to search for text containing wildcards inside any text field including memo
type fields.
Expression is what you want to search. Search Text Expression is the text you
want to locate. It can contain wildcards * and ?, or you can use a
regular expression by prefixing the search string with MJRE:. It can also use the
keyword "OR" to signify that you want one or the other eg. "cat OR dog OR fish". The
keyword "OR" has to be in capitals or it will be treated as part of the search string.
The matching is done case insensitively. It returns the text that is found. If no match is
found, the return value is blank. For example :-
[]!=MJFINDTEXT(NOTES,angry OR unhappy OR dissatisfied)
would return .T. (true) if NOTES contained "angry", "unhappy", or "dissatisfied" and .F. (false) otherwise.
SET FOUNDTEXT=MJFINDTEXT(NOTES,mjre:\b[QXJqxj]\w*\b)
This would find any word starting with q, x, or j in the field NOTES and store it in the field
FOUNDTEXT.
See Regular Expression Syntax for more help on regular expressions.
For searches in index expressions, use the $ (substring of character field) or
CONTAINS (substring of memo field) functions
instead.
MJFTPGET(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPPUT(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPDEL(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPREN(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPAPPEND(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPMKD(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPRMD(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPDIR(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPLIST(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPRESTGET(Host,UserID/Password,Local File,Remote File[,Options])
MJFTPRESTPUT(Host,UserID/Password,Local File,Remote File[,Options])
Host is the host name or dotted IP address for the FTP server you are communicating with.
UserID/Password is the username and password (separated by a forward slash /).
Local File is the full pathname to the file on the local machine (may be omitted in
MJFTPDEL, MJFTPMKD, and MJFTPRMD). In MJFTPREN, this should be the new pathless filename on the
remote server.
Remote File is the full pathname to the file or directory on the remote FTP server.
Options controls whether the connection is binary or ascii, and if it is passive or
active. Default (when Options not specified) are binary and passive. Use 'ascii' and 'active'
to override these defaults.
If an argument is a valid expression, it is evaluated to yield the parameter that will be used.
If you are using MJFTPMKD or MJFTPRMD commands, then Remote File should be the name of the directory.
MJFTPGET retrieves Remote File and stores it in Local File.
MJFTPPUT uploads Local File to Remote File.
MJFTPDEL erases Remote File.
MJFTPREN renames Remote File to Local File (not really Local in this instance!).
MJFTPAPPEND appends Local File to the end of Remote File.
MJFTPMKD creates the directory specified by Remote File.
MJFTPRMD removes the empty directory specified by Remote File.
MJFTPDIR stores a complete directory listing of the directory Remote File, into Local File.
MJFTPLIST stores a list of filenames and directories found in the directory Remote File, into Local File.
MJFTPRESTGET retrieves Remote File and stores it in Local File, restoring from where it
left off if the file already exists on the local machine.
MJFTPRESTPUT uploads Local File to Remote File, restoring from where it left off if the
file already exists on the remote machine.
These functions return .T. (true) if it worked, and .F. (false) otherwise. For example :-
MJFTPGET(ftp.microsoft.com,bgates/secret,c:\downloads\windows7.iso,bin/images/private/windows7_x86_64bit.iso)
would retrieve the 64-bit x86 windows 7 iso image from the Microsoft server and store it in the
local machine's downloads directory, calling it windows7.iso.
MJGETENV(Environment Variable)
Returns the string associated with the environment variable specified by the expression
Environment Variable, or blank if Environment Variable does not exist. For example, under XP
MJGETENV(HOMEDRIVE)+MJGETENV(HOMEPATH)+'\My Documents\' would give the full path to your
documents directory including the trailing backslash.
MJINDEXCOND(Indexname)
Returns the expression for the condition part of an index named Indexname, or blank if
Indexname does not exist or if there is no condition associated with the index. If Indexname
is not a valid expression, it is taken as a literal string.
MJINDEXDEF(Indexname)
Returns the expression for the ordering part of an index named Indexname, or blank if Indexname
does not exist. If Indexname is not a valid expression, it is taken as a literal string. For
example, if we had indexed a field called STATUS, then MJINDEXDEF(%STATUS) would give
the string STATUS.
MJISDIR(Pathname)
Returns .T. or .F. (true or false) depending on whether the path Pathname is a directory or
not. If Pathname is not a valid expression, it is taken as a literal string.
MJISFILE(Filename)
Returns .T. or .F. (true or false) depending on whether the file Filename exists or not. It
returns false if Filename specifies an existing directory. If Filename is not a valid
expression, it is taken as a literal string. If Filename starts with the >
(greater than) character, then this signifies to start up the file (launch its associated
application) if the file exists.
MJISNULL(Fieldname)
This is used to test whether a field has ever been populated with data, and differs from the
function EMPTY() in that numeric fields with 0 in them because they have never had a value set,
are deemed NULL and EMPTY(), but numerics set to zero are deemed to be EMPTY() but not
NULL.
Fieldname is the name of the field you wish to test for ever being populated.
MJLENGTH(Fieldname)
This gives the length of the data held in the field Fieldname. It works on all data types
including raw, binary and image BLOB fields, and returns the number of bytes required to hold
the field's current contents, without trailing spaces. If Fieldname is not a field, it is
evaluated and the length of the resultant string is returned. If it is not an expression, the
length of the literal string specified by Fieldname is returned.
MJLF()
Returns 'mjchr(13)+mjchr(10)' which is a newline (carriage return - line feed) pair.
MJLOOKUP(Look-up File,Look-up Index,Expr to Find,Expr to Return[,Stats Type[,Weight Expr]])
This allows you to cross reference something in another database. If what you search for cannot
be found then "**NOT FOUND**" is returned, unless you are setting a field, in which case, the
field is left unchanged. If there is a problem with the lookup parameters, then "**ERROR** "
followed by an explanation of what is wrong, is returned.
Look-up File is the name of the database file you want to perform look-ups in. It is
opened in Readonly access mode, so other people will be able to perform look-ups with it,
whilst you are doing yours. It is auto-closed after 20 seconds of inactivity and re-opened when
necessary. Look-up File can be a string expression. If it is not an expression, it is taken as
a literal string.
Look-up Index is the look-up search index order name. It is taken as a literal string.
Expression to Find is the expression evaluated in the main database that you want to look up in
the look-up database.
Expression to Return is the expression evaluated in the look-up database, and whose
instances are optionally statistically analysed, and returned as the result of this function
call.
Stats Type (optional - default 0) can be used to specify the type of statistic required
from the Expression to Return, when this is numeric, and where more than one record matches
the Expression to Find. If the Expression to Return is not a string or number, then Stats Type
is ignored, and the first matching record is used to determine the return value. If the
Expression to Return is a string and Stats Type is not zero, then all matching records
concatenate their result to the end of the resultant return value - you get a long string for
each lookup.
Use one of the following values for Stats Type to control the type of
statistical analysis required for the matching records :-
0 - value of the first record
1 - weighted sum
2 - count
3 - weighted average
4 - maximum unweighted value
5 - minimum unweighted value
6 - weighted standard deviation (zero if less than 2 records)
(this is a measure of how wide spread most of the data is about the mean)
7 - weighted coefficient of skewness (zero if less than 2 records)
(this is a measure of how far from the centre of a normal distribution the peak frequency for
the data is, with negative values denoting to the left of centre and positive to the right)
8 - weighted coefficient of kurtosis (zero if less than 2 records)
(this is a measure of how much higher or lower than the height of a normal distribution the
peak frequency for the data is, with negative values denoting lower than a normal distribution
peak and positive higher)
9 - standard error of the mean (zero if less than 2 records)
(this is a measure of how spread out the probability is over the set of possible values and
is the weighted standard deviation divided by the square root of the number of matching records)
10 - weighted median value
Weight Expression (optional - default 1) can be used to do weighted statistical
analysis. The Weight expression should be a numeric expression that is evaluated against each
matching record in the Lookup Database, and used as a weighting for the numeric return
expression.
Since the arguments for MJLOOKUP are separated by commas, if any of its expression arguments
have got commas in them, they should be enclosed in double quotes. If you need to use quotes
in any of the expression arguments, you should use single quotes and not double
quotes. If the look-up cannot be found, the field which is being set, remains unchanged.
For example, you could determine the totals sales broken down by region, given a database of
regions. Say that the region is held in a field called REGION, and the lookup database contains
individual records for sales, which also contains a field called REGION, and a field called
VALUE containing the amount for the sale. Assuming that this lookup database is indexed on the
REGION field, we can use the following expression to return the sales for any given region
database record :-
MJLOOKUP(SALES,REGION,REGION,VALUE,1)
If VALUE is a numeric field with 2 decimal places, and because of the high precision floating
point library rounding errors, we need to format the look of this expression if we are not
storing it in a numeric field. If the result is going to a string field, then we need to use
the STR and, optionally, the LTRIM functions to format the result to 2 decimal places.
See Help on String Functions for more details on
the use of these and other string formatting functions. This is how the expression would look
if we were to store the result back into a string field :-
LTRIM(STR(MJLOOKUP(SALES,REGION,REGION,VALUE,1),15,2))
You may nest any of these functions as deeply as you like, and you can mix their results
with any of the string-handling functions.
Very complex expressions can built up, for example :-
MJLOOKUP(LT68,VALUE,MJLOOKUP(LT68,LREF,LREF+'2000',VALUE,1),DATE)
is used to determine the date ranking in value of the sum of the year 2000 transactions.
MJMEMOLINES(String Expression[,Start Line,No.of Lines,Delimiter String])
This is used to extract lines from a multi-line string.
String Expression is any expression yielding a string, Start Line is the number
of the first line we wish to extract (default 1), and No.of Lines is the number of lines
to join up and return (default 1). Lines are joined to each other by Delimiter String,
or CR-LF if this is not specified. Delimiter String can contain control characters by
specifying them using the MJCHR function. If the line(s) are outside of the range of lines in
the string, a blank string is returned. If the start line number is negative, then -1 means the
last line, -2 means the line before the last, -3 means 2 lines before the last... If there are
fewer lines than the negative number specifies, then the first line is used.
For example, you could select the first line of the field MEMOTEXT with the expression
MJMEMOLINES(MEMOTEXT).
MJNUMRECS([Database Filename])
Returns the number of records in the string expression Database Filename, or in the current
database if Database Filename is not specified. If the string expression Database Filename is
invalid, then it is taken as a literal string. If Database Filename does not exist, zero is
returned. For example, with f:\jobs\q2746\q2746_spl.dbf open,
MJNUMRECS(%Q2746_DAT)
would give the number of records in the current data file for this project
(f:\jobs\q2746\q2746_dat.dbf).
MJPASS(Caption)
This prompts the user for a password. The input box has a title specified by Caption and hides
characters as they are typed, with an asterisk.
MJPHONESTR(Expression)
This is used to take the string returned by evaluating Expression (less any hyphens and
spaces), and make it into a UK phone number string. It will prepend a leading zero if
necessary. It preserves international numbers as they are (ones starting with '00'). For
example,
MJPHONESTR(1162589037) would give the string 0116 258 9037
MJPHONESTR('01617892345') would give the string 0161 789 2345
MJPHONESTR(%2079117713xn709) would give the string 020 7911 7713xn709
MJPHONESTR(08457959722) would give the string 08457 959722
MJPHONESTR('00738911327389') would give the string 00738911327389
MJPOS(Inwhat,Tofind[,Case])
MJLASTPOS(Inwhat,Tofind[,Case])
These functions return the character position of the first (MJPOS) or last (MJLASTPOS)
occurrence of Tofind within Inwhat. They can handle very long strings indeed (multi-megabyte)
and can be used as a replacement for the Advantage expression engine functions
AT and RAT when using very long strings. If Tofind
cannot be located within Inwhat, then a value of zero is returned. These functions are
case-insensitive, unlike AT and RAT, unless you use the optional boolean Case argument - .T.
means use case-sensitive matching. The arguments are the opposite way round to AT and RAT. For
example, MJPOS(This is really silly,is) gives 3 and MJLASTPOS(This is really
silly,iS) gives 6. MJPOS(This is really silly,Is,3>2) gives 0.
MJPOS(This is really silly,Is,3<2) gives 3.
MJPROCESSES()
Returns a list of all running processes and their dependencies. Useful for seeing if something
is running. For example,
'myprog.exe' $ LOWER(MJPROCESSES()) would give true or false as to whether myprog.exe was
running on the PC at the moment.
MJPROPERCASE(Expression)
This is used to capitalise words in an expression.
Expression is what you want to capitalise. For example, say the field INT_NAME contains
the data "MR. PAUL HODGSON", then MJPROPERCASE(INT_NAME) gives the string "Mr. Paul Hodgson".
MJRANDOM(Expression)
This is a numeric function, which is used to generate random whole numbers. The numbers range
from 0 to 1 less than the value of expression, inclusive.
Expression is evaluated to yield the highest number plus one that will be generated. If
it evaluates to something which is not a number, then 0 is returned. For example, to simulate
a dice throw, we would use MJRANDOM(6)+1
MJRANDSTR(Expression)
This is a string function, which is used to generate random strings of a length specified by
Expression. The strings consist of the characters A-Z. For example MJRANDSTR(5) may give 'EPOLG'.
MJRANK(Expression,Delimiter,Rank Expression[,Element,Strings])
This function chops up the Expression using any character in Delimiter, and
returns the position of the chunk that is numerically or alphabetically ranked Rank
Expression, or the Rank Expressionth element, if the optional parameter
Element evaluates to true (.T.). Expression is what you want to chop up. This
would typically be numbers separated by a delimiter. Delimiter can contain control
characters by specifying them using the MJCHR function (eg. MJCHR(44) for comma). Rank
Expression is a numeric expression or literal number that denotes the rank desired. For
example, 1 would mean the highest number or first string, and the return value would be the
element position where this highest number is, or this first element. If this number exceeds
the number of chopped elements, the last one is taken, or it returns the number of chopped
sections. Otherwise, the number of the chopped section that corresponds to the rank desired, is
the number returned by this function, or the element at that position if the Element
parameter evaluates to true. If optional Strings is specified, then the list is sorted
in ascending case-insensitive alphabetical order, rather than in descending numerical order.
For example :-
MJRANK(255 157 67 369 121 388, ,48/12) would give 2
157 is the 4th highest
number in descending sort order with the highest at the top
MJRANK(255 157 67 369 121 388, ,48/12,true) would give 157
157 is the 4th highest
number in descending sort order with the highest at the top
MJRANK(255 157 67 369 121 388, ,48/12,true,true) would give 369
369 is the 4th highest
string in ascending alphabetical order (121 157 255 369 388 67)
MJRANK(255 157 67 369 121 388, ,48/12,false,true) would give 4
369 is the 4th highest
string in ascending alphabetical order and occurs at the 4th position in the list
MJREADFILE(Filename)
This returns the contents of Filename as a string.
Filename evaluates to the name of the file you wish to read from. If it does not exist,
a blank string is returned. For example, you could build an immense list of postcode sectors
into a text file called PCODES.TXT, and then use an expression to check whether this record is
in this list, such as :-
SET REGION=IF(MJPOS(MJREADFILE(PCODES.TXT),LEFT(POSTCODE,3))>0,'NORTH','SOUTH')
MJRENAMEFILE(SourceFile,TargetFile)
Renames SourceFile to TargetFile. If either argument is a valid expression it is evaluated
beforehand to yield the filename. Returns .T. if successful and .F. otherwise. Use MJLASTERROR()
to see what went wrong when .F. is returned.
MJREVERSE(Expression)
Expression yields a string and this function reverses the characters in that string. This is
the same as the REVERSE function but can handle very
long strings (multi-megabyte strings for example).
MJSECSDIFF(DateTime1,DateTime2)
Returns the number of seconds between the date/time string yielded by evaluating DateTime1, and
that yielded by evaluating DateTime2. The string should be in a format where the date comes
first, then a space and then the time. You can omit the date part and it will default to
today's date. For example, the following gives the number of seconds into the current minute we are :-
MJSECSDIFF(DTOC(DATE())+' '+LEFT(TIME(),5),DTOC(DATE())+' '+TIME())
or, more simply,
MJSECSDIFF(LEFT(TIME(),5),TIME())
and the next, how many days between now and the field SESSDATE :-
MJSECSDIFF(SESSDATE,DATE())/86400
which is precisely equivalent to DATE()-SESSDATE because there are 86,400 seconds in a day.
(See Date Operators).
MJSENDMAIL(To,From,Host,UserID,Subject,Body[,Attachments,Delay])
Returns a string which is the result of sending an email.
To validate the email address(es), use MJVALIDEMAIL.
To is the address the email is going to. You can specify a name as well as an email
address here if you use the syntax "name <email address>". Multiple entries can be
specified by separating them from each other using the ; (semicolon) plus a space character.
You can also specify Cc and Bcc entries in this parameter by using the slash (/) delimiter to
separate them from each other. In that case, To should comprise of
To-email-addresses/Cc-email-addresses/Bcc-email-addresses.
From should be an email address from which it is valid to send emails on this Host
. It can be optionally followed by the slash (/) delimiter and a valid email address to
which replies can be sent. Both these elements can be specified in the "name <email
address>" format. If you prefix the either of these elements with an equals ('=') sign, then
a read receipt is automatically requested for that element. For example,
root@myurl.com/=fred76@aol.com would use root@myurl.com as the From address,
fred76@aol.com as the Reply-To address, and request a read receipt for fred76@aol.com.
Host is the host of the mail server that will be sending the email.
UserID is the username you use to access the mail server specified by Host. You
can optionally follow this by the slash (/) delimiter and a password to authenticate the logon
if the SMTP server specified by Host requires authentication.
Subject is the title of the email.
Body is the multi-line body of the email.
Attachments is an optional semi-colon delimited list of files you wish to send with the
email as attachments. If you wish to embed images in HTML emails, then specify a list of email
images as a semi-colon delimited list of image files separated from the attachments by a slash
(/), as in the To parameter description above. In that case the Attachments
parameter should comprise attachments/images. You can then use the cid: prefix and the list of
email image files. The HTML source should contain references like <IMG SRC="cid:IMAGE1">
which means embed the first image from the list of email images etc.
Delay is the time in seconds you wish to wait after the email has been sent. This
can be used when sending many emails one after the other. It defaults to 0 (no pause
afterwards).
For example,
mjsendmail('Burt Bacharach <bj76@aol.com>; Jilly Cooper <jc412@aol.com>','Bill Gates <b.gates@microsoft.com>',
'smtp.freenet.com','billg','Important Message','Line 1'+mjlf()+'Line 2'+mjlf()+'Line 3')
If the file c:\temp\mjtestem.htm contains the text :-
<HTML>
<HEAD>
<TITLE>MJ Test HTML Email</TITLE>
</HEAD>
<BODY BGCOLOR="#6DA082">
<H2>MJ Test HTML Email</H2>
I am hoping that you will see an image below this text :-<BR><BR>
<IMG SRC="cid:IMAGE1">
<HR>
</BODY>
</HTML>
then you can use the follwing to embed the image in an HTML email :-
mjsendmail('jim@aol.com','john@microsoft.co.uk','mssmtp02.msemail.co.uk','johnny','MJ and Bianca',
mjreadfile(c:\temp\mjtestem.htm),'/f:\jobs\mj\bianca.jpg')
Possible returned responses are :-
ERROR MJSendMail: Open Aborted
ERROR MJSendMail: Email Aborted
ERROR MJSendMail: some error message
EMail Sent OK plus some details of the submission
Each of these is followed by the date and time the event occurred, and the server response.
MJSENDFAX(File to Send,Fax Number,Recipient Name,From Name[,Subject,Cover Page,Note])
Returns a string which is the result of queuing a fax. The PC running this must have the
Windows fax server software installed on it (use Add/Remove Windows Components).
File to Send is the document you wish to fax out. If you only want to fax a cover sheet,
use a blank string here.
Fax Number is the telephone number to which the fax is being sent.
Recipient Name is the name of the person or company to receive this fax.
From Name is the name of the person or company sending the fax.
Subject is the title on the cover page for the fax. If not specified, it defaults to
blank.
Cover Page is the cover page template to use. You can specify a fully-qualified filename
here, to use a local .COV file, or you can just specify the name of a template in the common
cover pages library. If you do not specify anything here, it will use the "generic" cover page
that is installed with the fax components on the fax server PC.
Note is the text on the cover page which is the main message. If not specified, it
defaults to blank.
All the above are expressions that are evaluated, so you can embed mjlf() calls in the note.
For example,
mjsendfax('C:\utils\dk1prepwin.htm','0207 783 5738','Bill Jenkins, Manager Co-operative Society','Mark Testing','This is the topic of discussion','','A note to tell you'+mjlf()+'about certain issues.')
Possible returned responses are :-
ERROR MJSendFax: Could not load WinFax DLL
ERROR MJSendFax: Could not Find File File to Send
ERROR MJSendFax: Cannot Connect to FAX Server
ERROR MJSendFax: Could Not Allocate Job
ERROR MJSendFax: Fax was not Queued - some error message
Fax Queued Job ID job id number
The last 2 of these is followed by the date and time the event occurred.
MJSHORTDATE(Date Expression)
Returns a string in the form d mmm yyyy, where d is the date as one or two digits, mmm is
the 3-character abbreviated month name, and yyyy is the 4 digit year. For example,
MJSHORTDATE(DATE()+7) would yield one week past today in the form 9 Nov 2005
MJSOUNDEX(String Expression)
Returns a string which is a phonetically-simplified version of the original. This is useful
when comparing strings that have been manually entered into a field, so that slight spelling
mistakes are ignored in the comparison. For example,
MJSOUNDEX('smith') gives 'SM0'
MJSOUNDEX(smythe) also gives 'SM0'
MJSOUNDEX(%smithson) gives 'SM0SN'
MJSTATS([Database Expr],Stats Expr,[Weight Expr],[Filter Expr],Stats Type[,Keep])
This allows you to perform a statistical analysis of records in the file specified by optional
Database Expr parameter, or the current database if omitted. It will sweep through the database,
performing the analysis requested. If evaluated with the same arguments (except for Stats
Type), on the same underlying database on successive records, and the optional Keep
keyword is used, the analysis is done on the first record and subsequent records use the
analysis derived from the first record's sweep.
Database Expr (optional) is used to specify the name of the database on which the
analysis is to be conducted. The expression is evaluated to yield a filename. If it is not a
valid expression, it is taken as a literal string. If omitted, the current database is used,
otherwise Database Expr is auto-closed after 20 seconds of inactivity and re-opened when
necessary. If the database does not exist, MJSTATS always returns an error.
Stats Expr is the expression to analyse. If it is numeric or logical, a statistical
analysis is done (logical gives 1 for true and 0 for false), otherwise a long string is built
up from the return values of Stats Expr for each record, and the Stats Type parameter is
ignored.
Weight Expr (optional - default 1) is the expression used to weight the Stats Expr
return values.
Filter Expr (optional - default .T.) is the expression that determines if a particular
record is counted towards the statistics return result. If it evaluates to false, this record
is not counted towards the statistics. If this expression contains any commas, enclose it in
double quotes.
Stats Type can be used to specify the type of statistic required - see
Stats Type Description for an explanation of this parameter. It should
be noted that type 0 (first matching record) is not a valid value for this parameter, unless a
concatenated string is being formed (ie. where the Stats Expr is not numeric or logical).
Keep (optional) signifies that if the parameters for database, stats expr, weight expr,
and filter expr, are exactly the same as for the previous call to this function, then the
sweep of the database is not conducted, and the previous statistical results are used.
MJSTRIPTAGS(Expression[,Delimiters])
Expression yields a string and MJSTRIPTAGS drops all text contained in angular brackets
(<...>), or HTML tags as they are sometimes known, including the tags themselves, and
returns the result. If you specify the optional Delimiters argument, then it will strip any
text contained in those delimiters from the input argument, rather than those enclosed in
angular brackets. Delimiters can contain control characters by specifying them using the MJCHR
function (eg. MJCHR(91)+MJCHR(93) for square brackets '[]'). This can result in a lot of
whitespace in the return result. For example,
MJSTRIPTAGS('Hello [everybody ]and [how ]are we',%5B%5D)
MJSTRIPTAGS('Hello [everybody ]and [how ]are we',[])
MJSTRIPTAGS('Hello (everybody )and (how )are we',())
all give 'Hello and are we'
MJSTRIPTAGS(MJCONTSFROMURL(http://www.dkcomputing.co.uk/index.htm)) gives:-
Trinit‚ NT Webspace
This domain name has
been registered for a customer.
Either the site is temporarily unavailable
for maintenance or is not yet active.
MJSTRLKUP(LkupFile,FindString[,Delim1,Delim2])
This is a lookup function which uses a file containing a long string (LkupFile) in which to do
the lookup. LkupFile is a file which contains the text we want to search inside of. If it is
an expression, it is evaluated first. FindString is what we want to find. If it is an
expression, it will be evaluated before looking for it in LkupFile. Delim1 is what separates
the FindString from the entity we want to return from the look up, and it defaults to
')' close bracket. Delim2 is what separates each lookup pair from the next in the
LkupFile, and it defaults to ',' comma. Either delimiter can contain control
characters by specifying them using the MJCHR function. If the FindString cannot be located in
LkupFile, then a blank string is returned. White space is trimmed from the return value. For
example,
If file mytext.txt contains the string "34)Pigeons,87)Raccoons,7)Elephants,4)Wildebeest,107)Antelopes,63)Apes"
then MJSTRLKUP(mytext.txt,7) gives 'Elephants'
An example macro which measures how long various methods of setting a field take :-
define st=time()
set sparec=%
define t2=time()
set sparec=mjlookup('\dkfiles\dkusers',int_number,interviewr,int_name)
define t3=time()
set noteslkup=mjstrlkup(\dkfiles\mjtest.txt,interviewr)
display "BLANK="+MJTRIM(mjsecsdiff(#st#,#t2#))+mjlf()+\
"MJLOOKUP="+MJTRIM(mjsecsdiff(#t2#,#t3#))+mjlf()+\
"MJSTRLKUP="+MJTRIM(mjsecsdiff(#t3#,time()))
MJSTRTRAN(Expression,FindString[,ReplaceString])
This is used to "clean up" fields, for example, by removing all commas from a field before
exporting a selective CSV file using the report generator.
FindString is an expression which yields the text we wish to locate in the result of
evaluating Expression
ReplaceString is an expression which yields what we want to replace the found
text with. If it is not specified or blank, an empty replace string is assumed.
For example, MJSTRTRAN(20901,0,7) returns '27971'.
MJSUBSTR(Expression,Start[,Length])
Expression yields a string and this function returns the portion of the string starting at
position Start for Length characters. If Length is omitted, then all characters starting at
position Start up to the end of the string are returned. This is the same as the
SUBSTR function but can handle very long strings
(multi-megabyte strings for example). For example, MJSUBSTR(apples and pears,5,6) gives
'es and' and MJSUBSTR(apples and pears,12) gives 'pears'.
MJTEXTTOHTML(Expression)
Expression yields a string whose characters are translated so as to display correctly as part
of an HTML page. This involves the following translations :-
CR-LF --> <BR>CR-LF
< --> <
> --> >
& --> &
%hexcharcode --> char (for example %22 becomes ")
%% --> %
space space --> space (where space is the space character).
MJTRIM(Expression)
MJLTRIM(Expression)
MJRTRIM(Expression)
MJSPTRIM(Expression[,from])
Expression yields a string and MJxxxTRIM trims off leading and/or trailing spaces. If not using
MJSPTRIM, carriage returns, line feeds, tabs and control characters (codes 0-32) are also
removed from the string. MJLTRIM trims off leading white space. MJRTRIM trims off trailing
white space. MJTRIM removes both leading and trailing whitespace. MJSPTRIM removes only spaces
from the start/end of the string. The "from" argument should be 0 to trim from the left, 2 to
trim from the right, or 1 (the default if nothing specified) if both are required. These
functions can handle very long strings indeed (multi-megabyte) and can be used as a replacement
for the Advantage expression engine functions ALLTRIM,
LTRIM and RTRIM when using very long strings. For example, LEN(MJTRIM(MJLF()+'Well,
hello there!'+CHR(9)+CHR(32))) gives 18.
MJUPPER(Expression)
MJLOWER(Expression)
These functions evaluate Expression and uppercase or lowercase the result. They can handle very
long strings indeed (multi-megabyte) and can be used as a replacement for the Advantage
expression engine functions UPPER and LOWER when
using very long strings.
MJVALIDEMAIL(EmailString)
EmailString is an email address whose MX records will be checked, and if valid, this returns
true, otherwise it returns false. The domain of the email address is checked against known
mailbox domains and the user name is not verified (since most email servers nowadays do not
allow verification of usernames as a measure to combat spam). EmailString can have multiple
email addresses separated by semicolons, slashes and can have optional names in the usual
syntax, for example :-
Burt Bacharach <bj76@aol.com>;Jilly Cooper <jc412@aol.com>;djrakim@microsoft.com;icet@gmail.com
MJWRITEFILE(File Name Expression,Expression)
This can be used to write an evaluated Expression to a file. If File Name Expression evaluates
to a string which starts with a plus sign, then the file name after the plus sign will be
appended to, otherwise it is overwritten. This would be useful when populating a field and
simultaneously forming a pick-list file for that field, for example, or just running a one-off
macro to a file. Return value is that of the evaluated Expression argument.
Maths Functions
See also Expression Engine Numerical Operators.
MJPI() Mathematical Constant PI (3.14159265358979323846)
MJE() Mathematical Constant E (2.71828182845904523536)
MJSIN(Numeric Expression) Sine with argument in Radians
MJCOS(Numeric Expression) Cosine with argument in Radians
MJTAN(Numeric Expression) Tangent with argument in Radians
MJASIN(Numeric Expression) Arc Sine with result in Radians
MJACOS(Numeric Expression) Arc Cosine with result in Radians
MJATAN(Numeric Expression) Arc Tangent with result in Radians
MJSINH(Numeric Expression) Hyperbolic Sine
MJCOSH(Numeric Expression) Hyperbolic Cosine
MJTANH(Numeric Expression) Hyperbolic Tangent
MJASINH(Numeric Expression) Hyperbolic Arc Sine
MJACOSH(Numeric Expression) Hyperbolic Arc Cosine
MJATANH(Numeric Expression) Hyperbolic Arc Tangent
MJLOG(Numeric Expression) Natural Log
MJLOG10(Numeric Expression) Log to base 10
MJEXP(Numeric Expression) Exponential (E to the power of)
MJSQRT(Numeric Expression) Square root
MJFLOOR(Numeric Expression) Returns the nearest whole number that is less than the
argument (see also ROUND)
MJFRAC(Numeric Expression) Returns the fractional part of the argument
MJHYPOT(Numeric Expression1,Numeric Expression2) Returns the length of the hypotenuse
of a right-angled triangle whose other sides' lengths are specified by Numeric Expression1 and
Numeric Expression2.
MJCONV(Expression,Numeric Expression) Returns the decimal number equal to the string
Expression to the base of Numeric Expression (radix), if radix is positive. Returns the string
representation of Expression to the base of -Numeric Expression (radix), if radix is negative.
The absolute value of radix should be between 2 and 36 inclusive.
For example, MJCONV('FE43',16) would give 65091, and MJCONV(5432176859017,-16) would give
4F0C6F8D789
You can use all these functions anywhere in Macros. The only
exception is that these functions cannot be used in index expressions.
Back to the Top
Back to DKapture Help Index
© DK Computing