Converting 4D View plug-in formulas

Home

4D v19

Overview

As stated in the Converting 4D View documents page, most of the 4D View plug-in document contents and properties can be converted in 4D View Pro documents.

Formulas are also converted. However, the formula languages of 4D View and 4D View Pro are somewhat different and 4D View Pro implements default security features that control access to database data. As a result, some adaptations can sometimes be necessary regarding operators, constants and functions, as well as references to methods and database fields.

Three compatibility cases can occur:

a 4D View feature (operator, constant, function) is exactly the same in 4D View Pro: in this case, the conversion is transparent.
a 4D View feature or a 4D command is supported in 4D View Pro through a different function or operator: in this case, an automatic mapping is performed
a 4D View feature is partially or not supported in 4D View Pro: in this case, it will be necessary to adapt your converted formulas to make them work as expected. This is the case for references to methods, variables, or fields.

The following sections list 4D View formula features and their correspondances in 4D View Pro.

Operators

	4D View	4D View Pro
Numeric operators
Addition	+	+
Substraction	-	-
Multiplication	*	*
Division	/	/
Remainder	\	MOD
Integer division	÷	TRUNC(a/b)
Exponent	^	^
Percentage	%	%

Boolean operators
AND	&	AND
OR	\|	OR
Not	~	NOT

String operators
concatenation	+	&
destruction	-	SUBSTITUTE, ex: "Down Trend"-"Down" is replaced by SUBSTITUTE("Down Trend","Down","")
position	\	FIND (case sensitive) or SEARCH (case insensitive)

Date operators
date+days->date	+	+
date+time->date+time of day	+	+
date-days->date	-	-
date-date->number of days	-	-

Duration operators
addition	+	+
substraction	-	-
multiplication	*	*
division	/	/

Comparison operators
equality	=	=
difference	#	<>
greater than	>	>
less than	<	<
greater than or equal to	>=	>=
less than or equal to	<=	<=

Functions and 4D commands

In the table below, 4D commands are shown in italics. 4D View functions are displayed in normal font.

4D & 4D View	4D View Pro	Comment
Abs	ABS
Add to date, AddToDate	DATE	AddToDate(date;years;months;days) is replaced by DATE(YEAR(date)+years,MONTH(date)+months,DAY(date)+days).
date+time	TIME	DATE(date) + TIME(time)
And	AND
ArcCos	ACOS
ArcSin	ASIN
Arctan, ArcTan	ATAN
Area	-	n/a (no plug-in area)
Average	AVERAGE
Cell	INDIRECT
Char	Char
Character code	CODE
Column	COLUMNLETTER	COLUMN returns a number (not a letter)
Cos	COS
Count	COUNTA
Current date, CurrentDate	TODAY
Current time	RUNTIME_CURRENT_TIME
CurrentTime	NOW
CVCompound	PV	CVCompound(1%;5;1000) is replaced by PV(1%,5,-1000)
CVSimple	PV	CVSimple(1%;5;5*1000) is replaced by PV(1%,5,,-1000) -- note the two consecutive commas
Date, Date	RUNTIME_DATE
Day of	DAY
Dec	MOD
Empty	ISBLANK
Eval4D	-	Currently not available
Exp	EXP
False, False	FALSE
Find	LOOKUP
FindCell	FINDCELL
FVCompound	FV	FVCompound(1%;35;35*1000) is replaced by FV(1%,35,-1000)
FVSimple	FV	FVSimple(12%;35;351000) is replaced by FV(12%,35,,-351000) -- note the two consecutive commas
If	IF
Insert string	REPLACE
Int	INT
Length, Length	LEN
Log, Log	LN
Lowercase	LOWER
Max	MAX
Min	MIN
Mod, Mod	MOD
MonthlyValue	PMT	MonthlyValue(10.5%/12,48,6500) is replaced by PMT(10.5%/12,48,-6500)
Month of	MONTH
Not	NOT
Num	VALUE	Warning: decimal separator
Or	OR
PeriodNumber1	NPER	PeriodNumber1(10.5%/12;166.42;6500) is replaced by NPER(10.5%/12,-166.42,6500)
PeriodNumber2	NPER	PeriodNumber2(10.5%/12,5000,3000) is replaced by NPER(10.5%/12,,3000,-5000) -- note the two consecutive commas
Pi	PI
Position	SEARCH	Only the first two parameters are taken into account
Random, Random	RAND	Random (0->32767) is replaced by RAND (0->1)
Range	INDIRECT	Range("A1";"A3") is replaced by INDIRECT("A1:A3") -- note the colon character between A1 and A3
Rate1	RATE	Rate1(5;1000;3000) is replaced by RATE(5,-1000,3000)
Rate2	RATE	Rate2(5,6000,2800) is replaced by RATE(5,,2800,-6000) -- note the two consecutive commas
Replace string	SUBSTITUTE
Round, Rounding	ROUND
Row	ROW
Sin, Sin	SIN
SquareRoot	SQRT
StdDeviation	STDEV.P
String	RUNTIME_STRING
String	RUNTIME_VIEW_STRING
Substring, SubString	MID
Sum	SUM
Tan	TAN
Time	RUNTIME_TIME
True, True	TRUE
Trunc	TRUNC
Type, Type	TYPE	Returned types in 4D View Pro are different from 4D View
Uppercase	UPPER
Variance	VAR.P
Year of	YEAR

Notes about 4D commands:

If a 4D command is not part of the above list of authorized commands, it is converted to: UNSUPPORTED_4DCOMMAND(<command name>,param1,...,paramN).
Expression parameters of a 4D command called from a 4D View formula are converted to the equivalent SpreadJS syntax.

Project methods

When a 4D View document is converted, calls to 4D project methods in formulas are converted to calls to 4D View Pro user functions with the same name and parameters. Note that parenthesis are mandatory in 4D View Pro to call functions. Also, parameters are separated by commas (,).

For example, in 4D View:

=myMethod
=myMethod(1;5)

will be converted in 4D View Pro:

=MYMETHOD()
=MYMETHOD(1,5)

Project method names must comply with JavaScript Identifier Grammar (see ECMA Script standard). Note in particular that space characters are not allowed. Any non compliant method name is converted to UNSUPPORTED_4DMETHOD_NAME("<method name>",param1,...paramN).

Note: If a 4D project method in a formula has the same name as a SpreadJS function, 4D View Pro will use the function and the project method will not be called.

Once converted, project methods must comply with 4D View Pro security and availability Requirements for methods.

For more information about method calls in 4D View Pro formulas, please refer to the Project method references page.

Fields

When a 4D View document is converted, calls to 4D database fields in formulas are converted to calls to 4D View Pro user functions named "TABLETITLE_FIELDTITLE()".

For example, in 4D View:

=[myTable]MyField

will be converted in 4D View Pro:

=MYTABLE_MYFIELD()

Virtual structure
4D View Pro converts field names in 4D View formulas that come from the "virtual" structure (structure defined through calls to the SET TABLE TITLES and/or SET FIELD TITLES commands) or from the database structure (if no virtual structure was defined).
However, for security reasons only fields declared in the virtual structure will be taken into account by 4D View Pro (see Requirements for the Field references). It means that you must call the SET TABLE TITLES and/or SET FIELD TITLES commands in your database if you want 4D View Pro to use field references. It is recommended to declare a virtual structure before converting the document, thus you can select fields and tables to embed as functions in the 4D View Pro document.

Note: Converted structure fields that are not declared in a virtual structure will generate ?NAME errors in the cells when the converted document will be opened.

Conversion requirements

During the conversion, the original structure must be available. Otherwise, field and table references will only be converted with their numbers (for example, Table_6_Field_5) and need additional processing.
Table and field names must comply with JavaScript Identifier Grammar (see ECMA Script standard). Otherwise, table or field name is converted to a string with this format: "UNSUPPORTED_TABLE_FIELD_TITLE(<virtual structure name>)"

Note: If a field in a formula has the same name as an allowed 4D project method, 4D View Pro will use the field reference and the project method will not be called.

For more information about field references in 4D View Pro formulas, please refer to the Field references page.