For Single Select++, Multi Select++ and Radio Buttons++ custom fields you can configure database synchronization of options. If you configure database synchronization option value and descriptions will be periodically imported from a selected database table or SQL select result.
It has been tested with following databases, it may also work for other databases if you add correct drivers;
Database configuration can be done by clicking on "Edit Synchronization Options from Database" link on the Custom Field Configuration Page. You can access this page by selecting "Configure" link from the menu corresponding to custom field. Note that there may be multiple field configurations applicable for different contexts, so be sure to edit the correct one.
Also on the field configuration screen, you will be able to check what is the last time synchronization is performed and how custom fields options are modified.
You will need to configure database connection information, a db table or sql select and finally map result columns to option field's value and description columns. Since description column is optional, you don't need to bind a result column to it.
You can enter connection information using either a JDBC URL or by explicitly specifying necessary fields. If you check below screenshots, on the left you see an oracle database connection specified using URL, and on the right you see a PostgreSQL database specified using fields. In either modes you need to know hostname, port and database name of your database.
Since version 1.3.0 of the add-on, you can define datasources from add-on settings. Datasource is used as a reusable connection pool inside the add-on. For example 'Database Lookup++" field uses datasource for database connection, it doesn't require you to configure connection information. Obviously Database Synchronization of Options can also use datasource, but they are not updated yet. In the next version you won't need to specify connection information for synchronization, selecting an already defined datasource will be enough. |
When you click on next button the connection information will be verified and if there is any error it will be shown for you to fix before configuring table or SQL select. If database connection is successful you will proceed to second step of configuration. In this step you can either directly enter an SQL select statement or you can select a table name. Although it requires you to manually create a select query you can create much more complex queries, you can join tables, filter for criteria none of which is available in table selection mode. In table selection mode all rows of the table are automatically fetched.
When you press the 'Next' button, entered SQL or table selection will be verified and if there is any errors, you will need to fix it before proceeding to next step. If everything is correct, you will proceed to next step to configure mapping between result columns and option field's value and description and additionally synchronization interval and merging strategy.
'Value' field corresponds to value field of option. It is the string shown to user when they are performing a selection on single/multi select or radio button custom fields. 'Description' field maps to description of the corresponding option. It is the help text shown to user when they hover on the option. You can leave description mapping empty, if you want. Depending on how often your database is updated you can select a synchronization interval between 1 minute and 1 day. Merge strategy can be one of the followings:
When you press the 'Save & Synchronize' button, configuration will be saved and an initial database synchronization will be performed. If there is any change performed you will be notified about it. Depending on the number of rows exist in your database, this operation may take a while although it should not exceed a few seconds in most situations.