Skip to Content

Wiki source code of Quellen

Show last authors
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.Designer.Form.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 |jsonPath {{version major="7" minor="3"/}}|This parameter allows specifying a JSON path expression which will be applied to the returned result of the query on the server side. The example //$.returnValue[0]// returns the values of the first result of the query.|No
114
115 === Application example ===
116
117 A CSV data source with the option activated: **First row is header**. It has the following content:
118
119 {{code language="csv"}}
120 title,firstName,lastName,zip,city
121 Herr,Max,Mustermann,1000,Musterhausen
122 Frau,Monika,Mustermann,2000,Musterdorf
123 Frau, Luise,Musterfrau,3000,Musterstadt
124
125 {{/code}}
126
127 In the {{designer/}} the following script is used in the [[script area>>doc:Formcycle.Designer.Form.CodingPanel.ScriptTab.WebHome]] for pre-filling form fields:
128
129 {{code language="javascript"}}
130 $.getJSON( XFC_METADATA.urls.datasource_csv + "?name=Demo&mandantName=xima.de", function(json) {
131 var obj = json.returnValue[0];
132
133 $('[name=tfTitle]').val(obj.title);
134 $('[name=tfFirstName]').val(obj.firstName);
135 $('[name=tfLastName]').val(obj.lastName);
136 $('[name=tfZIP]').val(obj.zip);
137 $('[name=tfCity]').val(obj.city);
138 });
139 {{/code}}
140
141 {{lightbox image="data_source_csv_designer_script_en.png"/}}
142
143 This form example is provided as a [[download>>attach:CSV-Demo_en.json||rel="__blank"]].
144
145 === Exeption selection element ===
146
147 {{figure image="data_source_csv_designer_en.png"}}
148 The connection of a data source to a selection element takes place directly in the {{designer/}}
149 {{/figure}}
150
151 If the //CSV// data source has to be loaded directly in a [[selection element>>doc:Formcycle.Designer.Form.FormElements.Selection]], this happens via through the properties of the selection element in the {{designer/}}.
152
153 The content of the CSV data source is processed in a [[selection element>>doc:Formcycle.Designer.Form.FormElements.Selection]].
154
155 {{code language="none"}}
156 visible value, assigned value, optional value, optional value, ...
157 {{/code}}
158
159 Columns of the data source are use as follows for [[selection elements>>doc:Formcycle.Designer.Form.FormElements.Selection]]:
160 {{code language="none"}}col + column index{{/code}}.
161
162 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:
163
164 {{code language="javascript"}}
165 $('[name=sel2]').find('option:selected').attr('col2');
166 {{/code}}
167
168 Optinal values start at column index 2.
169 If the first row is marked as a header than it will be ignored for selection elements.
170
171 == XML ==
172
173 The XML data source acts analogously to the //CSV// data source, but cannot be bound to a [[selection element>>doc:Formcycle.Designer.Form.FormElements.Selection]].
174
175 {{figure image="data_source_xml_servlet_json.png" width="300"}}
176 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.
177 {{/figure}}
178
179 === Example for an XML data source ===
180
181 {{code language="xml"}}
182 <root>
183 <person>
184 <anrede>
185 Herr
186 </anrede>
187 <vorname>
188 Max
189 </vorname>
190 <nachname>
191 Mustermann
192 </nachname>
193 </person>
194 <person>
195 <anrede>
196 Frau
197 </anrede>
198 <vorname>
199 Monika
200 </vorname>
201 <nachname>
202 Musterfrau
203 </nachname>
204 </person>
205 </root>
206 {{/code}}
207
208
209 === Servlet parameter ===
210
211 {{info}}
212 **Servlet-URL**
213 http(s):~/~/<server>/formcycle/datenquellexml?mandantName=<Mandant-Name>&name=<Datenquellen-Name>
214 {{/info}}
215
216 If you access from the {{designer/}}, always use the global variable object //XFC_METADATA//, see also [[global designer variables>>doc:Formcycle.Designer.Form.CodingPanel.ScriptTab.FormMetadata]]. For example it can be accessed as follows: {{code language="javascript"}}XFC_METADATA.urls.datasource_xml{{/code}}.
217
218 The following parameter are possible:
219
220 |=Parameter name|=Description|=Neccessary
221 |mandantName|Must match the name of the client under which this data source was created.|Yes
222 |name|Must match the name of the CSV data source.|Yes
223 |jsonPath {{version major="7" minor="3"/}}|This parameter allows specifying a JSON path expression which will be applied to the returned result of the query on the server side. The example //$.returnValue[0]// returns the values of the first result of the query.|No
224
225 == JSON ==
226
227 The //JSON// data source acts analogously to the //CSV// data source, but cannot be bound to a [[selection element>>doc:Formcycle.Designer.Form.FormElements.Selection]] directly.
228
229 {{figure image="data_source_json_servlet_json.png"}}
230 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}}.
231 {{/figure}}
232
233 === Example for a JSON data source ===
234
235 {{code language="json"}}
236 {"person":[
237 {
238 "anrede":"Herr",
239 "vorname":"Max",
240 "nachname":"Mustermann"
241 },
242 {
243 "anrede":"Frau",
244 "vorname":"Monika",
245 "nachname":"Musterfrau"
246 }
247 ]}
248 {{/code}}
249
250 === Servlet-Parameter ===
251
252 {{info}}
253 **Servlet-URL**
254 http(s):~/~/<server>/formcycle/datenquellejson?mandantName=<Mandant-Name>&name=<Datenquellen-Name>
255 {{/info}}
256
257 At an access from the {{designer/}} always use the global variable object //XFC_METADATA//, see also [[global designer variables>>doc:Formcycle.Designer.Form.CodingPanel.ScriptTab.FormMetadata]]. For example you can access as follows: {{code language="javascript"}}XFC_METADATA.urls.datasource_json{{/code}}.
258
259 The following parameter are possible:
260
261 |=Parametername|=Description|=Neccessary
262 |mandantName| Must match the name of the client under which this data source was created.|Yes
263 |name| Must match the name of the CSV data source.|Yes
264 |jsonPath {{version major="7" minor="3"/}}|This parameter allows specifying a JSON path expression which will be applied to the returned result of the query on the server side. The example //$.returnValue[0]// returns the values of the first result of the query.|No