Wiki source code of Quellen
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | {{content/}} | ||
| 2 | |||
| 3 | {{id name="fig_data_source"/}} | ||
| 4 | {{figure image="data_source_en.png"}}Configuration screen for creating data sources: (1) list of existing data sources, (2) area for editing the selected data source, (3) settings panel of the data source, (4) servlet URL for accessing the data source.{{/figure}} | ||
| 5 | |||
| 6 | The sources or rather data sources are lodged as text in the format CSV, JSON or XML and can be worked up through the {{designer/}}/the form. | ||
| 7 | To have an access to data sources in the script area via javascript too, all data sources are converted into a JSON structure and customized in the object {{code language="none"}}XFC_METADATA.urls.datasource_[csv|json|xml]{{/code}}. The structure of this JSON structure for the different data sources is expounded in the respective section. | ||
| 8 | |||
| 9 | {{info}} | ||
| 10 | **Servlet URL** | ||
| 11 | The servlet URL of a data source will be displayed on the configuration screen (see point 4 in [[figure>>||anchor="fig_data_source"]]) and is provided in the JS object {{code language="none"}}XFC_METADATA.urls.datasource_[csv|json|xml]{{/code}}. | ||
| 12 | {{/info}} | ||
| 13 | |||
| 14 | == Creating a data source == | ||
| 15 | |||
| 16 | Open the module "Data > Sources" and click the button "New" {{ficon name="plus-circle-outline"/}} in the header of the list (see point 1 in [[figure>>||anchor="fig_data_source"]]). After that you will be prompted to either create an empty data source or import one from a file. Three types of data sources can be created: //XML//, //CSV// und //JSON//. | ||
| 17 | |||
| 18 | {{lightbox image="data_source_new_en.png" title="Data sources can be imported or created from scratch."/}} | ||
| 19 | |||
| 20 | Every data source needs: | ||
| 21 | |||
| 22 | * a unique name (in the settings panel: see point 3 in [[figure>>||anchor="fig_data_source"]]) | ||
| 23 | * the actual data in the editing area (see point 2 in [[figure>>||anchor="fig_data_source"]]) | ||
| 24 | |||
| 25 | === Import of data sources from files === | ||
| 26 | |||
| 27 | The following Data types can be used to import data sources: | ||
| 28 | |||
| 29 | * **XML**: //.xml// | ||
| 30 | * **CSV**: //.csv//, //.xls//, //.xlsx// (Excel) | ||
| 31 | * **JSON**: //.json// | ||
| 32 | |||
| 33 | {{id name="data_source_import_csv"/}} | ||
| 34 | __CSV data sources__ | ||
| 35 | |||
| 36 | CSV data sources can be imported from different file types: | ||
| 37 | |||
| 38 | * //.csv// | ||
| 39 | Opens up a dialog for configuring the import: | ||
| 40 | ** **Delimiter**: Determines which symbol is used in the file for separating data fields/ colunms (typically: comma). | ||
| 41 | ** **Text qualifier**: Determines the text qualifier. This symbol is used to support special characters inside data fields (e.g. comma in numbers with decimal places). | ||
| 42 | ** **Start at line**: Determines where to start importing the data source from. | ||
| 43 | ** **Ignore empty lines**: When activated ignores empty lines during import. | ||
| 44 | |||
| 45 | {{lightbox image="data_source_import_csv_en.png" title="Import dialog for CSV files"/}} | ||
| 46 | |||
| 47 | * //.xls//, //.xlsx// | ||
| 48 | Opens up a dialog for configuring the import: | ||
| 49 | ** **Sheets to import**: Determines which worksheets/tables should be imported from the file. | ||
| 50 | |||
| 51 | {{lightbox image="data_source_import_excel_en.png" title="Importdialog für Excel-Dateien"/}} | ||
| 52 | |||
| 53 | __Drag&Drop__ | ||
| 54 | |||
| 55 | Data sources can be imported by Drag&Drop as well. If a file is being dropped over the list (green area) than a new data source of the corresponding type will be created (for Excel files: one data source per worksheet will be created). If a file is being dropped over an existing data source than it will be updated. Changes will only take effect after the "Save" {{icon name="save"/}} button is clicked. | ||
| 56 | |||
| 57 | == CSV == | ||
| 58 | |||
| 59 | {{figure image="data_source_csv_servlet_json_comparison.png"}} | ||
| 60 | The JSON structure of a CSV data source. This JSON structure can be accessed through JavaScript via {{code language="none"}}XFC_METADATA.urls.datasource_csv{{/code}}. (1) On the left there is the JSON structure of the data source with out using the first row as a header. Instead the headers are of the form: {{code language="none"}}colX{{/code}}. (2) On the right there is the data source where the first row is being used as the header. | ||
| 61 | {{/figure}} | ||
| 62 | |||
| 63 | CSV data sources can be viewed in a source code view or in a table view. The view can be switched by clicking the appropriate button {{ficon name="table-large"/}}/{{icon name="code"/}} beneath the editing area. | ||
| 64 | |||
| 65 | //Source code view of a CSV data source//: ([[CSV file format>>https://en.wikipedia.org/wiki/Comma-separated_values||target="_blank"]]) | ||
| 66 | {{lightbox image="data_source_csv_source.png" title="Source code view of a CSV data source"/}} | ||
| 67 | //Table view of a CSV data source//: | ||
| 68 | {{lightbox image="data_source_csv_table.png" title="Table view of a CSV data source"/}} | ||
| 69 | |||
| 70 | === Editing CSV tables === | ||
| 71 | |||
| 72 | There is a row of buttons beneath the editing area for editing the CSV data table: | ||
| 73 | |||
| 74 | * {{ficon name="plus-circle"/}} **Insert**: Inserts new rows or columns relative to the selected cell. | ||
| 75 | * {{ficon name="trash-alt2"/}} **Remove**: Removes the selected row(s) or column(s) or empties the selected cell. | ||
| 76 | * {{icon name="upload"/}} **Import**: Opens up the [[dialog for importing CSV data sources>>||anchor="data_source_import_csv"]]. | ||
| 77 | * {{icon name="code"/}} **Source code view**: Switches to source code view. | ||
| 78 | |||
| 79 | === Header of CSV data sources === | ||
| 80 | |||
| 81 | There is an additional option for CSV data sources in the data source settings. The option **First row is header** determines how the values of the data sources are being accessed via its servlet ({{code language="none"}}XFC_METADATA.urls.datasource_csv{{/code}}): | ||
| 82 | |||
| 83 | * **Activated**: | ||
| 84 | The cells of the first row will be used as {{code language="none"}}keys{{/code}} for accessing the values of the respective columns (see [[example>>||anchor="data_source_csv_example"]]). | ||
| 85 | * **Deactivated**: | ||
| 86 | The {{code language="none"}}keys{{/code}} for accessing the values of individual data fields/ rows are enumerated, starting with {{code language="none"}}0{{/code}}. These keys have the following form: {{code language="none"}}colX{{/code}} where {{code language="none"}}X{{/code}} stands for the column index. | ||
| 87 | |||
| 88 | {{lightbox image="data_source_csv_settings_en.png" title="The option **first row is header** determines how the values of CSV data sources are accessed with its servlet"/}} | ||
| 89 | |||
| 90 | CSV data sources can be accessed in the {{designer case="dat"/}} via its servlet: {{code language="none"}}XFC_METADATA.urls.datasource_csv{{/code}}. | ||
| 91 | |||
| 92 | === Servlet parameters === | ||
| 93 | |||
| 94 | {{info}} | ||
| 95 | **Servlet-URL** | ||
| 96 | http(s):~/~/<server>/formcycle/datenquellecsv?mandantName=<Mandant-Name>&name=<Datenquellen-Name> | ||
| 97 | {{/info}} | ||
| 98 | |||
| 99 | Bei Zugriff aus dem {{designer case="dat"/}} verwenden Sie immer das globale Variablen-Objekt //XFC_METADATA//, siehe hierzu auch [[Globale Designer Variablen>>https://jsdocs.formcycle.eu/jsdocs/de/modules/_formcycle_v6_de_d_.html#xfc_metadata||target="_blank"]]. Beispielsweise kann wie folgt darauf zugegriffen werden: {{code language="none"}}XFC_METADATA.urls.datasource_csv{{/code}}. | ||
| 100 | |||
| 101 | Always use the global variables object {{code language="none"}}XFC_METADATA{{/code}} when accessing data sources from the {{designer/}}, see also [[global designer variables>>doc:Formcycle.FormDesigner.CodingPanel.ScriptTab.FormMetadata]]. The servlet URK can be accessed as follows: {{code language="none"}}XFC_METADATA.urls.datasource_csv{{/code}}. | ||
| 102 | |||
| 103 | The following parameters are possible: | ||
| 104 | |||
| 105 | |=name of the parameter|=description|=necessary | ||
| 106 | |name|Must match the name of the CSV data source.|Yes | ||
| 107 | |mandantName|Must match the name of the client under which the data source was created.|Yes, if //projektId// is not given. | ||
| 108 | |projektId|Must match the ID of the form. These information can be recalled via {{code language="none"}}XFC_METADATA.currentProject.id{{/code}}.|Yes, if //mandantName// is not given. | ||
| 109 | |kopfzeile|Indicates a comma seperated list with column name of the CSV table. If this is not given, the first line of the CSV is used.|No | ||
| 110 | |spaltenNamen|Indicates a comma seperated list of the column, which columns are redelivered of the //servlet// with the corresponding value. At this, according to using, the names of the columns of the parameter //headline// or the name of the first line of the //CSV// databaseHierbei are expected.|No | ||
| 111 | |csvTrennzeichen|Indicates the seperator between the single data cells. If this is not set, a comma {{code language="none"}},{{/code}} is used by default.|No | ||
| 112 | |csvQuote|The seperator with which the values of the lines are seperated. If this is not set, a inverted comma {{code language="none"}}"{{/code}} is used by default.| No | ||
| 113 | |||
| 114 | === Application example === | ||
| 115 | |||
| 116 | A CSV data source with the option activated: **First row is header**. It has the following content: | ||
| 117 | |||
| 118 | {{code language="csv"}} | ||
| 119 | title,firstName,lastName,zip,city | ||
| 120 | Herr,Max,Mustermann,1000,Musterhausen | ||
| 121 | Frau,Monika,Mustermann,2000,Musterdorf | ||
| 122 | Frau, Luise,Musterfrau,3000,Musterstadt | ||
| 123 | |||
| 124 | {{/code}} | ||
| 125 | |||
| 126 | In the {{designer/}} the following script is used in the [[script area>>doc:Formcycle.FormDesigner.CodingPanel.ScriptTab.WebHome]] for pre-filling form fields: | ||
| 127 | |||
| 128 | {{code language="javascript"}} | ||
| 129 | $.getJSON( XFC_METADATA.urls.datasource_csv + "?name=Demo&mandantName=xima.de", function(json) { | ||
| 130 | var obj = json.returnValue[0]; | ||
| 131 | |||
| 132 | $('[name=tfTitle]').val(obj.title); | ||
| 133 | $('[name=tfFirstName]').val(obj.firstName); | ||
| 134 | $('[name=tfLastName]').val(obj.lastName); | ||
| 135 | $('[name=tfZIP]').val(obj.zip); | ||
| 136 | $('[name=tfCity]').val(obj.city); | ||
| 137 | }); | ||
| 138 | {{/code}} | ||
| 139 | |||
| 140 | {{lightbox image="data_source_csv_designer_script_en.png"/}} | ||
| 141 | |||
| 142 | This form example is provided as a [[download>>attach:CSV-Demo_en.json||rel="__blank"]]. | ||
| 143 | |||
| 144 | === Exeption selection element === | ||
| 145 | |||
| 146 | {{figure image="data_source_csv_designer_en.png"}} | ||
| 147 | The connection of a data source to a selection element takes place directly in the {{designer/}} | ||
| 148 | {{/figure}} | ||
| 149 | |||
| 150 | If the //CSV// data source has to be loaded directly in a [[selection element>>doc:Formcycle.FormDesigner.FormElements.Selection]], this happens via through the properties of the selection element in the {{designer/}}. | ||
| 151 | |||
| 152 | The content of the CSV data source is processed in a [[selection element>>doc:Formcycle.FormDesigner.FormElements.Selection]]. | ||
| 153 | |||
| 154 | {{code language="none"}} | ||
| 155 | visible value, assigned value, optional value, optional value, ... | ||
| 156 | {{/code}} | ||
| 157 | |||
| 158 | Columns of the data source are use as follows for [[selection elements>>doc:Formcycle.FormDesigner.FormElements.Selection]]: | ||
| 159 | {{code language="none"}}col + column index{{/code}}. | ||
| 160 | |||
| 161 | If a data source has more than two columns than it contains optional values. These optional values can be accessed with JavaScript via the following jQuery selector: | ||
| 162 | |||
| 163 | {{code language="javascript"}} | ||
| 164 | $('[name=sel2]').find('option:selected').attr('col2'); | ||
| 165 | {{/code}} | ||
| 166 | |||
| 167 | Optinal values start at column index 2. | ||
| 168 | If the first row is marked as a header than it will be ignored for selection elements. | ||
| 169 | |||
| 170 | == XML == | ||
| 171 | |||
| 172 | The XML data source acts analogously to the //CSV// data source, but cannot be bound to a [[selection element>>doc:Formcycle.FormDesigner.FormElements.Selection]]. | ||
| 173 | |||
| 174 | {{figure image="data_source_xml_servlet_json.png" width="300"}} | ||
| 175 | The JSON structure of the XML data source. To this JSON structure can than be accessed to {{code language="none"}}XFC_METADATA.urls.datasource_xml{{/code}} via Javascript. | ||
| 176 | {{/figure}} | ||
| 177 | |||
| 178 | === Example for an XML data source === | ||
| 179 | |||
| 180 | {{code language="xml"}} | ||
| 181 | <root> | ||
| 182 | <person> | ||
| 183 | <anrede> | ||
| 184 | Herr | ||
| 185 | </anrede> | ||
| 186 | <vorname> | ||
| 187 | Max | ||
| 188 | </vorname> | ||
| 189 | <nachname> | ||
| 190 | Mustermann | ||
| 191 | </nachname> | ||
| 192 | </person> | ||
| 193 | <person> | ||
| 194 | <anrede> | ||
| 195 | Frau | ||
| 196 | </anrede> | ||
| 197 | <vorname> | ||
| 198 | Monika | ||
| 199 | </vorname> | ||
| 200 | <nachname> | ||
| 201 | Musterfrau | ||
| 202 | </nachname> | ||
| 203 | </person> | ||
| 204 | </root> | ||
| 205 | {{/code}} | ||
| 206 | |||
| 207 | |||
| 208 | === Servlet parameter === | ||
| 209 | |||
| 210 | {{info}} | ||
| 211 | **Servlet-URL** | ||
| 212 | http(s):~/~/<server>/formcycle/datenquellexml?mandantName=<Mandant-Name>&name=<Datenquellen-Name> | ||
| 213 | {{/info}} | ||
| 214 | |||
| 215 | If you access from the {{designer/}}, always use the global variable object //XFC_METADATA//, see also [[global designer variables>>doc:Formcycle.FormDesigner.CodingPanel.ScriptTab.FormMetadata]]. For example it can be accessed as follows: {{code language="javascript"}}XFC_METADATA.urls.datasource_xml{{/code}}. | ||
| 216 | |||
| 217 | The following parameter are possible: | ||
| 218 | |||
| 219 | |=Parameter name|=Description|=Neccessary | ||
| 220 | |mandantName|Must match the name of the client under which this data source was created.|Yes | ||
| 221 | |name|Must match the name of the CSV data source.|Yes | ||
| 222 | |||
| 223 | == JSON == | ||
| 224 | |||
| 225 | The //JSON// data source acts analogously to the //CSV// data source, but cannot be bound to a [[selection element>>doc:Formcycle.FormDesigner.FormElements.Selection]] directly. | ||
| 226 | |||
| 227 | {{figure image="data_source_json_servlet_json.png"}} | ||
| 228 | The JSON structure of the JSON data source. It is identically equal to the JSON data source. This JSON structure can be accessed via Javascript through {{code language="none"}}XFC_METADATA.urls.datasource_json{{/code}}. | ||
| 229 | {{/figure}} | ||
| 230 | |||
| 231 | === Example for a JSON data source === | ||
| 232 | |||
| 233 | {{code language="json"}} | ||
| 234 | {"person":[ | ||
| 235 | { | ||
| 236 | "anrede":"Herr", | ||
| 237 | "vorname":"Max", | ||
| 238 | "nachname":"Mustermann" | ||
| 239 | }, | ||
| 240 | { | ||
| 241 | "anrede":"Frau", | ||
| 242 | "vorname":"Monika", | ||
| 243 | "nachname":"Musterfrau" | ||
| 244 | } | ||
| 245 | ]} | ||
| 246 | {{/code}} | ||
| 247 | |||
| 248 | === Servlet-Parameter === | ||
| 249 | |||
| 250 | {{info}} | ||
| 251 | **Servlet-URL** | ||
| 252 | http(s):~/~/<server>/formcycle/datenquellejson?mandantName=<Mandant-Name>&name=<Datenquellen-Name> | ||
| 253 | {{/info}} | ||
| 254 | |||
| 255 | At an access from the {{designer/}} always use the global variable object //XFC_METADATA//, see also [[global designer variables>>doc:Formcycle.FormDesigner.CodingPanel.ScriptTab.FormMetadata]]. For example you can access as follows: {{code language="javascript"}}XFC_METADATA.urls.datasource_json{{/code}}. | ||
| 256 | |||
| 257 | The following parameter are possible: | ||
| 258 | |||
| 259 | |=Parametername|=Description|=Neccessary | ||
| 260 | |mandantName| Must match the name of the client under which this data source was created.|Yes | ||
| 261 | |name| Must match the name of the CSV data source.|Yes |