Module CustomFields

Return to developer
documentation index

Return to user
documentation index

CustomFields
Numero/ID of module	8500
User doc. of module	This page
Developer doc. of module	Module_CustomFields_Dev

This documentation is about module CustomFields branch v3. For previous versions, please read the included README inside the archive, although most of the documentation here is also valid for those older versions.

You can also jump directly to Module_CustomFields_Cases wiki page for a collection of practical examples.

Informations

Module name
Editor / Editeur
Download or buy page / Page achat ou téléchargement	External module - www.dolistore.com
Status / Statut / Estado	Stable
Prerequisites / Prérequis	Dolibarr <= 3.3.* Dolibarr min version: See on Dolistore    Dolibarr max version: See on Dolistore
Last update date / Date mise à jour fiche / Fecha última actualización	2013-12-25
Note / Nota

Description

This module allows the implementer to easily create and manage custom fields, and then use them in PDF and ODT documents, or in any other way you want.

You can choose the datatype, the size, the label(s), the possible values, the value by default, and even constraints (links to other tables) and custom sql definitions and custom sql statements.

The three main goals of CustomFields are to enable you to:

• Easily create your own custom fields with your data type of choice.
• Automatically manage/create/update/delete custom fields records of datas.
• Easily use the custom fields in your ODT and PDF documents, or any other PHP code.

And the philosophy:

• Standards-compliance.
• Independence with the Dolibarr code.
• Be reusable.

CustomFields has been made with the intention of being as portable, flexible, modular and reusable as possible, so that it can be adapted to any Dolibarr's module, and to (almost) any user's need (even if something isn't implemented, you can most probably just use a custom sql statement, the rest will be managed automatically, even with custom statements.).

Understanding CustomFields

Technically speaking, this module is a simple wrapper for your sql database. It respects sql standards and manages them in a standard way.

This means that you can modify your sql database with another tool (eg: phpMyAdmin), and the changes will be reflected inside CustomFields as well.

Features overview

- Natively support the modules: Invoices, Propales and Products/Services.

- Full multilanguage support:

• multilanguage in the administration gui (french and english for now, but can be translated by anyone using langs files)
• multilanguage user-defined custom fields labels
• multilanguage custom fields values (eg: yes/no select box can be translated to any language, same for enum user defined values). You can even translate the values of your dropdown boxes or your constrained fields.

- Several natively supported fields types :

• Textbox
• Areabox
• YesNoBox
• TrueFalseBox
• DropdownBox (your own options)
• Date
• DateTime
• Integer
• Float
• Double
• Constrained (link to other tables)
• Other (custom type, defined by your own SQL command)

Note: you can add your own sql datatypes, see the related chapter.

The last one is not a native support but permits you to easily set any SQL data type that is not yet implemented, and it will be managed as best as possible by the module (by default if it's unknown it will be shown as a textbox).

- Custom sql statements to create complex fields : these custom sql statements will be executed after the creation/edition of the custom field definition.

- Fully automated management of the customfields in the database and via triggers.

- Extensible to any module, be it core module or third-party user-made module (see the related chapter).

- Auto-detection and auto management of constraints (automatically find the primary key and choose the same data type and size) and auto management of their printing and edition (show them as a dropdown box).

- Smart Value Substitution for constrained fields: tell which column(s) you care, and the module will print the remote column instead of the row id (see the related chapter).

- Can define different custom fields PER module (you can have different custom fields for each module)

- Easy to use in PDF and ODT templates (see the related chapter).

- Works on mobile phones and tablets: no ajax, no javascript code, only standard html.

- Elegant presentation in datasheet.

- Supports all classic functions of a standard field: creation, edition, cloning, etc.

- Clean and isolated: do not interfer with the normal functionning of Dolibarr, everything is separated. You can just delete the customfields and it will never do anything wrong with your Dolibarr install or your other datas.

- Develop your own modules via CustomFields: CF provides many methods to automatically manage the inputs of a user and access custom fields values from anywhere in the code (from Dolibarr's core to your own module), and even a few generic functions to query any SQL request and help to manage them, with an automatic management of errors.

- Code is very easy to edit/customize/reuse/port (fully commented and modular architecture, and no duplicates functionnalities).

- Use of strict SQL standards, so it should work for any database following the minimum standards requirement (tested on MySQL, probably works for PostGreSQL and SQLite).

- Secure: only authorized users can edit customfields (one whose rights include create and modify for the module)

- Optimized and fast: cache whenever possible and super optimized sql requests and fast php code with as few loops as possible and low number of opcodes.

Requirements

This module requires your database to support Foreign Keys for full functionalities, for example with MySQL you need to enable InnoDB, because MyISAM alone does not support foreign keys and referential integrity checks.

However, in the case you really cannot enable Foreign Keys support in your database, CustomFields can still work in compatibility mode: you will get most of the features, constraints included, but your database will be a bit less clean.

For more informations, please read the developper's documentation about the SQL Compatibility mode.

Usage

Enabling the module

To use this module, you must first enable it using an administrator account, via the menu option "Home - Setup - Modules".

Choose the tab where the module is listed. Then click on "Activate".

The module is now activated.

If a cog icon appears on module thumb or at end of the line of the module, click on it to access the setup page specific to the module.

Now you can choose the tab of the module for which you want to configure your custom fields.

If the sql table does not exist yet, the module will allow you to create it automatically by clicking the button Create table.

Note: when you update Dolibarr or the module, you should disable and re enable the module to activate the new functionnalities (particularly the support of new modules).

Creating a custom field

After enabling the module and creating the table, you can now create a custom field.

Click on the button New field on the bottom right hand corner of the page.

You will then be presented with several options (bold are required):

• Label: this option is disabled, because the label will automatically be the same as the Field Name, but you can translate it (set it) in language files (see the related chapter below).
• Field Name: sql field name. Choose any name you want, it just has to be alphanumeric without any space (you can use underscore '_'). You can also use remote sql columns names if you set a constrained field to enable Smart Value Substitution for this field (see the related chapter).
• Type: the type of the field. This is where you choose what kind of data your field will hold, and more importantly how it will be shown (eg: Textbox, DropdownBox, etc..). You can also choose Other and set your own SQL type (and size) by entering it in the Other box just below (Eg: int(11) will create an int of max size 11 bits).
• Size, or for DropdownBox the Values: if you want to set a specific size, you can set it here. By default, the module will set the right size for most uses, but if you find that it's too limited, you set any value. Also, this is required if you set an DropdownBox type, and instead of setting the size you should here set the values that will be offered in the box (eg: "firstval","secondval","thirdval" WITH the quotes and commas).
• Accept null values: if enabled, the field will accept empty values. Else if disabled, a value will be required, and if an empty value is entered, the last non empty value will be used instead.
• Default value: you can set the default value when a field has not received any value by the user yet.
• Constraint: you can link the field to another table in the database. The field will then offer as choices the records in the remote table.
• Custom SQL Definition: this is used to add more sql commands to the sql definition of the field (such as comments, meta parameters or transformations).

Any SQL command you write here will be appended at the end of the automatically generated SQL statement to create the custom field.

Eg: you create a custom field named "belowten" and want to make sure that it will never store any value above 10, you can set the following in the Custom SQL Definition (if supported by your DBMS):

CHECK belowten < 10

This will be appended to the automatically generated SQL creation statement, which would be something like this:

ALTER table ADD belowten int(11)

With the appended Custom SQL Definition:

ALTER table ADD belowten int(11) CHECK belowten < 10

You can of course add multiple statements as long as they are doable in the same statement (you are not allowed to use ";" to add another request, you should then use Custom SQL Statement to do that).

• Custom SQL Statement: any sql command can be entered here and will be issued only ONCE after the definition of the field (thus the commands won't be issued every time the field is shown, but only once when you create the field!). This is the same as using a third-party tool such as phpMyAdmin, and is just offered as a quick shortcut.

Note: in Custom SQL Statement, do NOT do

SELECT *

it will be rejected by the injection protection of Dolibarr - and anyway this is totally useless as the module won't show you the result of the request, you should better try sql commands like View, Procedure, Trigger, Check, etc..

any SQL request (not just one or two commands like above) will be issued AFTER the creation statement (not in the same statement, this is the main difference with the Custom SQL Definition field).

Eg: you are creating a custom field named "syncedfield" in "llx_invoices" and you want that everytime data is inserted into that field, the same data is also inserted in another field named "remotefield" but in another table named "llx_propal". You can use a trigger that you will create at the same time as the field, with something like that:

CREATE TRIGGER synctrigger AFTER INSERT ON llx_invoices INSERT INTO llx_propal (remotefield) VALUES (NEW.syncedfield)

In practice, when you create the custom field, it will then issue two different requests:

ALTER llx_invoices ADD syncedfield int(11);
CREATE TRIGGER synctrigger AFTER INSERT ON llx_invoices INSERT INTO llx_propal (remotefield) VALUES (NEW.syncedfield)

Notice the semicolon (;) at the end of the first request.

The field Custom SQL Statement is just a shortcut to quickly input a SQL request to be issued just after the creation of the field, but of course you can do the same by using phpmyadmin or any other interface and issuing SQL requests by yourself.

Note2: if what you want to do is to store metadata about your custom fields (extended infos), you do not necessarily have to use SQL, but you may use a relatively recent facility called ExtraOptions, which allow you to basically store any data you want about a custom field (NOT about each record, but about the custom field! Thus there is one ExtraOptions per custom field, not per record!). More info can be found here: CustomFields JSON API

---

At last, you can click on the Save button to create the field.

As soon as the field is created, the page will reload and the field will show up in the list of custom fields, and also it will be immediately available in the module's datasheet page (you can go there and try it).

Note: fields are ordered by ordinal_position (order of creation - first created at the top, last at the bottom), so if you want a specific order for your fields, you are advised to carefully think what order you want. Then delete and recreate fields in the order you want them to be.

Modifying a custom field

To edit a custom field, just click on the edit button at the right hand side of the admin page (one button per field).

The same form as when you create a custom field will appear with the parameters of the field, and you can then freely edit them, and click Save to commit the changes.

Deleting a custom field

To delete a custom field, simply click on the trash button at the right hand side (just at the right of the edit button).

WARNING: no confirmation will be asked, and once a custom field is deleted you can not get it back nor get back the values saved by user, they are lost forever! Anyway you can re-create the same field, but user's values will still be lost.

Translation of a custom field's label

Fields can easily be labeled or translated using the provided language files.

Just edit /customfields/langs/code_CODE/customfields-user.lang (where code_CODE is the ISO code of your region, eg: en_US) and add inside the Variable name of your custom field (shown in the admin panel, column Variable) and the translation (format: cf_something= My Label).

Eg: let's say your custom field is named "user_ref", the resulting variable will be "cf_user_ref". In the customfields-user.lang file just add:

cf_user_ref= The label you want. You can even write a very very very long sentence here.<br />You can even do a line return with <br />.

Testing your custom fields with the PDFTest module

An auxiliary module called CustomFieldsPDFTest is provided so that you can easily test your custom fields in your PDF outputs. This avoids the need to make your own PDF template and risking to do some mistakes in the php code.

Just enable the CustomFieldsPDFTest in Home>Setup>Modules and then generate a PDF document using any template.

A page will be appended to the end of the generated PDF with an extensive list of all the available custom fields, their values and their raw values (raw value = no beautify, no html encode and no translation).

You can then see if the custom fields fits your needs and contain all the informations you will need in your final PDF template. Disable it when finished, you will make your own PDF template after (see below).

Note: already generated pdf files won't be affected, only generated PDF documents after the PDFTest module is activated will have a list of custom fields appended, and if you disable the module and generate again the PDF document, the appended page will disappear.

Implementing in ODT templates

First, you have to know how to create an ODT template. Please refer to the related documentation: Create_an_ODT_document_template

Custom fields are automatically loaded for ODT templates.

Just use the shown variable name (Variable column in the configuration page) as a tag enclosed by two braces.

Eg: for a customfield named user_ref, you will get the Variable name cf_user_ref. In your ODT, to get the value of the field, just type

{cf_user_ref}

You can also get the raw value (without any preprocessing) by appending _raw to the variable name:

{cf_user_ref_raw}

There is also full support for constrained fields, so that if you have a constraint on this field, it will automatically fetch all the linked values of the referenced tables and you will be able to use them with tags. Eg: cf_user_ref is constrained on the llx_user table:

{cf_user_ref} = rowid
{cf_user_ref_firstname} = firstname
{cf_user_ref_user_mobile} = mobile phone
etc...

As you can see, you just need to append '_' and the name of the column you want to access to show the corresponding value.

For lines, it works just the same, you just have to put the variable name inside the table that handles the product's lines, between the tags [!-- BEGIN row.lines --] and [!-- END row.lines --]

Note: an interesting use of custom fields is to use a TrueFalseBox with a conditional substitution, eg: with a custom fields cf_enablethis:

[!-- IF {cf_enablethis_raw} --]
This is enabled and this will show.
[!-- ELSE {cf_enablethis_raw} --]
Else this will show up when disabled.
[!-- ENDIF {cf_enablethis_raw} --]

We need to use the raw value, because we need to have a 0/1 value for the conditional to work. It also works for empty/non-empty, so this can also work with empty Textbox or any other sql datatype: if there's no text, you can avoid to show anything:

[!-- IF {cf_mytextfield_raw} --]
My text field is not empty, here is its value: {cf_mytextfield}
[!-- ENDIF {cf_mytextfield_raw} --]

Implementing in PDF templates

First, before implementing the custom fields, you have to make your own PDF template. Please refer to the related documentation: Create_a_PDF_document_template

To use custom fields in your PDF template, you first need to load the custom fields datas, then you can use them wherever you want.

• To load the custom fields:

// Init and main vars for CustomFields
dol_include_once('/customfields/lib/customfields_aux.lib.php');

// Filling the $object with customfields (you can then access customfields by doing $object->customfields->cf_yourfield)
$this->customfields = customfields_fill_object($object, null, $outputlangs, null, true); // beautified values
$this->customfields_raw = customfields_fill_object($object, null, $outputlangs, 'raw', null); // raw values
$this->customfields_lines = customfields_fill_object_lines($object, null, $outputlangs, null, true); // product lines' values

Note: you can place this just after:

$pdf=pdf_getInstance($this->format);

• To access the field's value:

Beautified formatting:

$object->customfields->cf_myfield

or for the raw value:

$object->customfields->raw->cf_myfield

• To access a product's line's value:

$lineid = $object->lines[$i]->rowid;
$object->customfields->lines->$lineid>cf_myfield

Where $lineid must be replaced by the id of the line you want to fetch (rowid of the product, so it does NOT necessary start at 0).

• To print it with FPDF (the default PDF generation library):

$pdf->MultiCell(0,3, $object->customfields->cf_myfield, 0, 'L'); // printing the customfield

• And if you want to print the multilanguage label of this field :

$outputlangs->load('customfields-user@customfields');
$mylabel = $this->customfields->findLabel("cf_myfield", $outputlangs); // where $outputlangs is the language the PDF should be outputted to

or if you want to do it automatically (useful for a loop):

$outputlangs->load('customfields-user@customfields');
$keys=array_keys(get_object_vars($object->customfields));
$mylabel = $outputlangs->trans($keys[xxx]); // where xxx is a number, you can iterate foreach($keys as $key) if you prefer

• If you want to print an HTML field (Areabox), the FPDF library provides you with a mean to do so:

$pdf->writeHTML('HTML beautified printing for myfield: '.$object->customfields->cf_myfield); // printing areabox with html parsing
$pdf->writeHTMLCell(); // you can also use writeHTMLCell() to get more options with the positionning of your cell

Warning: TCPDF support for HTML is very limited and not guaranteed. If you use the writeHTML() method and it doesn't print you any text of the field in your PDF, then try to strip out the html tags by doing something like this:

$myfield = str_replace(array("\n\n","\r\n\r\n"), "\n",strip_tags($object->customfields->cf_myfield));
$pdf->MultiCell(0,3, $myfield, 0, 'L'); // printing the customfield with line returns but no HTML

Implementing in php code (dolibarr core modules or your own module)

One of the main features of the CustomFields module is that it offers a generic way to access, add, edit and view custom fields from your own code. You can easily develop your own modules accepting user's inputs based on CustomFields.

You can use a simplifier library that eases a lot the usage of custom fields in php codes:

dol_include_once('/customfields/lib/customfields_aux.lib.php'); // include the simplifier library
$customfields = customfields_fill_object($object, null, $langs); // load the custom fields values inside $object->customfields

customfields_fill_object() takes 5 parameters:

• $object: the object where the custom fields datas will be set
• $fromobject: the object from which the custom fields will be fetched (this can be used to fetch custom fields from multiple modules into one $object)
• $langs: language translation class, to localize your custom fields values
• $prefix: prefix to add to help you organize and avoid overwriting custom fields (eg: you have two modules and want to store into one $object, with $prefix='invoice' the first will store inside $object->customfields->invoice->cf_myfield, and second='propal' will store inside $object->customfields->propal->cf_myfield).
• $pdfformat: beautify the customfields values? (null = no beautify nor translation; false = beautify and translate; true = translation and pdf beautify with html entities encoding)

Note: $fromobject (or $object if $fromobject=null) can be a dummy object and contain only two properties: $fromobject->id and $fromobject->table_element (which is the module's name, or table's name where the module stores its datas).

You can then access easily to the custom fields values:

print($object->customfields->cf_myfield);

To load product's lines custom fields, you can use the customfields_fill_object_line() function which takes exactly the same parameters:

dol_include_once('/customfields/lib/customfields_aux.lib.php'); // include the simplifier library
$customfields = customfields_fill_object_lines($object, null, $langs); // load the custom fields values inside $object->customfields

Then you can access the line's custom fields by using:

$object->customfields->lines->$lineid->cf_myfield

You can also manually fetch the custom fields values (called $records below):

• First, you necessarily have to instanciate the CustomFields class:

// Init and main vars
//include_once(DOL_DOCUMENT_ROOT.'/customfields/class/customfields.class.php'); // OLD WAY
dol_include_once('/customfields/class/customfields.class.php'); // NEW WAY since Dolibarr v3.3
$customfields = new CustomFields($this->db, $currentmodule); // where $currentmodule is the current module, you can replace it by '' if you just want to use printing functions and fetchAny.

• Secondly, you have the fetch the records (this will fetch ALL custom fields for ALL records):

$records = $customfields->fetchAll();

• Thirdly, you can now print all your records this way:

if (!is_null($records)) { // verify that we have at least one result
  foreach ($records as $record) { // in our list of records we walk each record
    foreach ($record as $label => $value) { // for each record, we extract the label and the value
      print $label.' has value: '.$value; // Simple printing, with no beautify nor multilingual support
      print $customfields->findLabel($customfields->varprefix.$label).' has value: '.$customfields->simpleprintField($label, $value); // Full printing method with multilingual and beautified printing of the values. Note: We need to add the varprefix for the label to be found.  For printField, we need to provide the meta-informations of the current field to print the value from, depending on these meta-informations the function will choose the right presentation.
    }
  }
}

• Full final code:

// Init and main vars
//include_once(DOL_DOCUMENT_ROOT.'/customfields/class/customfields.class.php'); // OLD WAY
dol_include_once('/customfields/class/customfields.class.php'); // NEW WAY since Dolibarr v3.3
$customfields = new CustomFields($this->db, $currentmodule); // where $currentmodule is the current module, you can replace it by '' if you just want to use printing functions and fetchAny.
// Fetch all records
$records = $customfields->fetchAll();
// Walk and print the records
if (!is_null($records)) { // verify that we have at least one result
  foreach ($records as $record) { // in our list of records we walk each record
    foreach ($record as $label => $value) { // for each record, we extract the label and the value
      print $label.' has value: '.$value; // Simple printing, with no beautify nor multilingual support
      print $customfields->findLabel($customfields->varprefix.$label).' has value: '.$customfields->simpleprintField($label, $value); // Full printing method with multilingual and beautified printing of the values. Note: We need to add the varprefix for the label to be found.  For printField, we need to provide the meta-informations of the current field to print the value from, depending on these meta-informations the function will choose the right presentation.
    }
  }
}

• If you want to fetch only one record:

$record = $customfields->fetch($id); // Where id is of course the id of the record to fetch.

foreach ($record as $label => $value) { // for each record, we extract the label and the value
  print $label.' has value: '.$value; // Simple printing, with no beautify nor multilingual support
  print $customfields->findLabel($customfields->varprefix.$label).' has value: '.$customfields->simpleprintField($label, $value); // Full printing method with multilingual and beautified printing of the values. Note: We need to add the varprefix for the label to be found.  For printField, we need to provide the meta-informations of the current field to print the value from, depending on these meta-informations the function will choose the right presentation.
}

• You can get the errors returned by the CustomFields class (sql errors and others):

print($customfields->error); // This will print all errors concatenated in a single string returned by the CustomFields class
print($customfields->errors[0]); // To print a specific error separately from the rest (can be useful to better understand what happened)

// More complete example:
if (!empty($customfields->error)) {
  dol_htmloutput_errors($customfields->error); // Use the old Dolibarr error printing facility
  setEventMessage($customfields->error, 'errors'); // Use the new Dolibarr 3.3 error printing facility
}

Do's and don't to avoid slowdowns when implementing CustomFields

• In PDF templates, use customfields_fill_object_lines() only ONCE before the loop, else it will be very heavy on CPU and memory usage (using it once will already fetch all lines for the current object at once, no need to use it several times).

• Adding a lot of customfields or product lines won't really slow down CustomFields in any way (datasheet printing, editing field, PDF/ODT generation, etc..), thank's to caching.

• Try to avoid the number of constrained customfields: adding a lot of constrained customfields will, because CustomFields automatically lookup the remote tables for every constrained field, so that you can access any remote column in your PDF. There is a cache mechanism that tries to speed up the process, so for example if you have many different constrained customfields on the same remote table (eg: with different Smart Value Substitution prefixes), then the performance won't be tampered, but if every constrained customfields is linked to a different table, there is no way around, it will slow down. If you encounter problems, try to limit the number of constrained fields you create, or try to add SQL indexes on the remote table of the constrained field, or don't use customfields_fill_object*() functions but directly access _only_ the fields you need by using the CustomFields class or your own sql query.

• Smart value substitution for constrained fields does not hamper any performance (eg: prefix of the remote column for auto replacement of the raw value by the specified column's value).

• Try to use customfields_fill_object() and customfields_fill_object_lines() only once per script, these functions are really heavy and do all the work for you, they are optimized and cache everything they can, but the drawback is that they're heavy. An easy way is to modify directly the o