1) COMMAND
a. Required.
b. This controls what the import should do with the data. Each supported command has some special rules, as given here.
c. Can take the literal values of:
i. Insert
1. Will insert new records into both the
HIERARCHIAL_LOOKUP_GROUP and
HIERARCHICAL_LOOKUP_GROUP_ENTRY tables.
2. If the ENTRY-related fields are blank, the import simply does not insert an entry record (no error message).
3. If the GROUP-related fields are blank, the import will insert the ENTRY fields as members of whatever group was identified by the previous data row. If this happens on the first row of data, the import fail with an error. If this happens following a row where the group errors (such that no record can be identified), the import will fail with error.
4. If the GROUP-related key field identifies a record that already exists, the import will not insert a duplicate. Only the new entry field will be imported as a member of that group. Also, in the case of an insert, UPDATE logic will not be executed against the group fields; in this use case all data-fields for the group entries should be blank, or identical to the prior or (if the
identifying field is also identical). If the user tries to alter
GROUP-related data in an INSERT command, the import will fail with error.
5. If the ENTRY-related key field “client code” identifies a record that already exists in the current group, the import will fail with error.
6. If the ENTRY-related key field “ID is not blank, the import will fail with an error.
7. Note that the field REPLACEMENT_GROUP_NAME and REPLACEMENT_ENTRY_CLIENT_CODE should NOT be used in
an INSERT statement. If this field is included, the value in the
field will be used in place of the GROUP_NAME or ENTRY_CLIENT_CODE values.
ii. Update
1. Used to update records in both tables.
2. If the ENTRY-Related fields are blank, the import simply does not update a record in the entry table. This is the same for the group fields.
3. If the UPDATE-related fields are blank, the import simply does not update a record in the relevant table.
4. Note, it is NOT POSSIBLE to update an entry to be a member of a different group. If the user attempts this, by identifying a group that does not match the group of the identified entry, the import will fail with error.
a. In this special case, if the group key field is blank (so the identified group is a roll-down from the previous row) then the rule is not enforced. The group is ignored altogether and keeps the present entry in its current group while updating the other given values without erroring.
5. If the identified record in either table does not exist, the import will fail with an error.
6. An update for an entry should be identified by either the
ENTRY_CLIENT_CODE OR the ENTRY_ID. Both columns should NOT be included on the import document. One or the other should be used.
iii. Delete
1. Used to delete records in both tables.
2. Only values in the key fields are processed; values in any other column are ignored.
3. If the key field is blank for either table, the import won’t delete anything for that table.
4. If the key field is given for both a group and an entry, the entry must be a member of that group. If that check fails, the import will fail with an error.
5. A delete of an entry should be identified by either the
ENTRY_CLIENT_CODE OR the ENTRY_ID. Both columns should NOT be included on the import document. One or the other should be used.
6. It is important to note that entries related to a group must be deleted prior to deleting the parent group. iv. Auto
1. Will decide whether to insert new data, or update existing data, based on what is given in the key fields. If the records don’t exist, the import will insert them, if they do exist, the import will update them.
2. If no entry-related fields are given, the import simply ignores the entry and only processes group.
3. If no group-related fields are given, the import will use the previously-identified group if the entry-related fields evaluate to “insert”.
4. If both a group and an entry record are identified, the import will error if the entry is not a member of the identified group. The user CANNOT update an existing entry and insert a new group in the same row. The user CANNOT change an entry’s group.
5. An auto transaction for an entry should be identified by either the ENTRY_CLIENT_CODE OR the ENTRY_ID. Both columns should NOT be included on the import document. One or the other should be used.
2) GROUP_NAME
a. Required if any values are given in any of the group-related fields.
b. This is the key field for HIERARCHICAL_LOOKUP_GROUP.
c. Identifies which group will be updated or deleted or supplies the name of a group to be inserted.
3) REPLACEMENT_GROUP_NAME
a. Optional.
b. Used only when the user intends to update a group and change its group name.
c. Note that the field REPLACEMENT_GROUP_NAME should NOT be used in an INSERT statement. If this field is included, the value in the field will be used in place of the GROUP_NAME.
4) PARENT_GROUP_NAME
a. Optional.
b. The value the user enters is the NAME of the desired parent group. The code will look up the ID and use that for processing. If the NAME does not exist, the import will error.
c. The user CANNOT use this to change the parent group if the present group already has any child entries in the database. If that is attempted, the import row will fail with error (but only if this is actually a change; if the user is setting it to what it already is then it will not fail with error).
5) GROUP_INACTIVE
a. Optional.
b. Accepts values “yes”,”no”, “true”,”false”,”eqtrue”,”eqfals” (and of course <blank>), and translates them to EQTRUE/EQFALS as appropriate.
6) ENTRY_ID --OR-- ENTRY_CLIENT_CODE
a. This column can accept one of two values in the HEADER row, to determine the function of the column. So the column in the header row should be either of:
i. ENTRY_ID
ii. ENTRY_CLIENT_CODE
b. This is the key field for the entry record. It is used to identify which entry record to update. If the column header is specified as ENTRY_ID, then data value for the field should be the integer ID of the
HIERARCHICAL_LOOKUP_GROUP_ENTRY record to update. If the column header is specified as ENTRY_CLIENT_CODE, then the text value of the client code should be given instead.
c. When specified as ENTRY_ID:
i. The field should not be used for INSERT commands. The import should fail with an error in any case where this field is not blank as part of an INSERT command.
ii. Since this can be used to identify an ENTRY record by its ID, it is possible to use this without any GROUP fields. However, all the necessary import rules apply (no moving an entry from one group to another, no editing entries where there is a mismatch with the company id, etc).
d. When specified as ENTRY_CLIENT_CODE
i. The field can optionally be used for insert commands. If there is a value here, that will be the value inserted into the CLIENT_CODE field
of the new record. If, in that case, a value is ALSO given for the REPLACEMENT_ENTRY_CLIENT_CODE field, then those two values must exactly match (otherwise the import row will fail with an error).
ii. A blank value here does not identify a single record with a blank client code. Client codes are either used or they are not, so identifying a record by its client code requires that both the record, and this column in the import, have a non-blank value.
e. A transaction for an entry should be identified by either the
ENTRY_CLIENT_CODE OR the ENTRY_ID. Both columns should NOT be included on the import document. One or the other should be used.
7) REPLACEMENT_ENTRY_CLIENT_CODE
a. Optional.
b. Used when the user intends to update an entry and change its client code. This is logically necessary as a separate column in the import since users might choose to use the ENTRY_CLIENT_CODE field to identify the record.
c. This field works the same way regardless of which field is used in the prior column to identify an entry. It just updates the client code, and that’s it.
d. If an INSERT command includes both an ENTRY_CLIENT_CODE and a REPLACEMENT_ENTRY_CLIENT_CODE, and the values given for each field are different, fail with an error. But if they match just accept that as the code to use for the insert.
8) ENTRY_DISPLAY_VALUE
a. Required for new entries being inserted. Rejects the value <BLANK> with an error in all cases.
2) PARENT_ENTRY_ID --OR-- PARENT_ENTRY_CLIENT_CODE
a. Optional.
b. This column can accept one of two values in the HEADER row, to determine the function of the column. So the column in the header row should be either of:
i. PARENT_ENTRY_ID
ii. PARENT_ENTRY_CLIENT_CODE
c. This column updates the
PARENT_HIERARCHICAL_LOOKUP_GROUP_ENTRY_ID field in the database.
Its purpose is to associate an entry with its parent entry in its parent group.
d. The value the user enters depends on the column name the user has chosen. When the column header contains PARENT_ENTRY_ID, then the data row should contain the integer ID value of the desired record. When the column header contains PARENT_ENTRY_CLIENT_CODE, then the data row should contain the text value of the CLIENT_CODE of the desired entry.
e. Note, the entry’s current group is known through configuration, and the entry’s parent group is also already know through configuration, so if CLIENT_CODE is being used, it must identify a record in the appropriate group.
f. Blank cannot be used to identify a record in the parent group with a blank CLIENT_CODE.
g. All rules apply, such as, the entry picked must be valid by group and company rules, etc.
h. A row for an entry should be identified by either the
PARENT_ENTRY_CLIENT_CODE OR the PARENT_ENTRY_ID. Both columns should NOT be included on the import document. One or the other should be used.
3) ENTRY_SEQUENCE
a. Optional
b. Accepts integer values.
9) ENTRY_INACTIVE
a. Optional.
b. Accepts values “yes”,”no”, “true”,”false”,”eqtrue”,”eqfals” (and of course <blank>), and translates them to EQTRUE/EQFALS as appropriate.