Administration Data import export Import Field definitions - robinfeng/beashelp GitHub Wiki
Navigation: Administration > Data import/export > Import: help-zahnrad Field definitions No contents entries on this page Contents The field definitions (button FIELDS) are used to define which values the target fields contain in the target table. Fields of the source table, SQL functions or/end fixed defaults can be defined as source.
The left side shows the target fields, the right side the source fields. It is important that the primary key (key fields) contains a unique allocation as far as no fields have to be filled with invalid values.
help-ablauf New field allocation entry
- First select new entry via tool-auswahl target field
- Set allocation type (see further below)
- Select source field via tool-auswahl or enter additional information via tool-verweis or use to verify values
Field Description Target field Name of target field Type Allocation type: From table: Read value from source table SQL Instruction: Use result of the SQL instruction Default: Transfer specified value to source field Source field Type table: Field name from source table or field name in placeholder "<" and ">", e.g. . All beas standard placeholders and formatting details items are permitted.
Type SQL: select instruction. The field names of the source table may be inserted as placeholders.
Type default: The text or number specified here is inserted directly max. length Maximum number of characters the result may consist of. Longer results are truncated before insertion into the target field. For example, the field itemcode may never contain more than 20 characters. There is no check in case of 0.
help-beispielThe following are some examples to provide a better understanding
Example 1: Target field "itemname" should be filled with the content of the source table "descr1": Target field = "itemname", Type="from table", Source field ="descr"
Example 2: Target field "itemname" should be filled with contents of the source table "descr1" and "descr2" Target field = "itemname", Type="from table", Source field =" " Placeholders were used here. Everything outside of the placeholders, in this case the blank character, is adopted directly.
Example 3: Target field "u_ke" (Purchased part In-house production) should be filled with "K" (Purchased part) if the third character in the source field "dispo" is filled with "K". Target field = "u_ke", Type="SQL", Source field="select case when substring('',3,1)='K' then 'K' else 'E' end"
The SQL syntax is used here to create conditions. The possibilities for SQL syntax are unlimited. The source field value is enclosed by placeholders again. Since the type is "CHAR", the result must be enclosed by apostrophes.
Example 4: The field abm contains the dimensions "length x width mm". However, the mm specification may also be missing or a width may not be specified. The values should populate the target field "abm1" and "abm2". Decimal places are separated by a dot, and MS Windows is set to "German" where the comma is used as separator.
Target field="abm1", Type="SQL", Source= select left(left(replace(' mmx','.',','),charindex('mm',' mmx')-1),charindex('x',' mmx')-1)
Target field="abm2", Type="SQL", Source= select substring(left(replace(' mmx','.',','),charindex('mm',' mmx')-1),charindex('x',' mmx')+1,10)
The full version of MS SQL is supplied with excellent documentation (help file) about the MS SQL syntax. There is also a wealth of helpful documentation available in bookstores.
Example 5: Handling numbers Handling of numbers is rather complicated, since the decimal separator must be taken into account. In Germany, the separator is a comma, in the U.S. it is a period. But MS-SQL syntax always works with a period. beas follows the windows setting. If regional settings in Windows are set to German, the number must contain a comma as separator, but if the number is not first reformatted,a comma is also submitted with an SQL instruction . beas is equipped with a large number of formatting specifications. Formatting for SQL-capable numbers reads "num(number of positions)
Example: select <abm1,num(3)> returns the nationally/regionally independent value, i.e. the formatting specification considers the local setting and converts the transferred number so that it is correctly understood by the SQL server.
For this reason, number should always be transferred in this form only (example: Source value "width" should be entered into the dimension field "abm1"):
Target field="abm1" Type="SQL" Source="select <width,num(3)>"
Example 6: Handling dates Date transfers are even much more difficult to handle since they also depend on national settings. The direct specification of the following format is the easiest dd = Day, mm=Month, yy=Year (yy=two-digit, yyyy=four-digit) e.g. <source date,mm.dd.yyyy>
返回 主页