Import
Quantities and quantity changes are tracked for each subscription line, which form the basis of the subscription lines to be invoiced. All quantity changes are always recorded to the correct date.
Quantity entries sometimes only take place once, for example if a certain purchase quantity is defined at the beginning of the Subscription Period that the customer does not change over the entire term. Sometimes they take place at long intervals, for example when licenses are purchased or terminated every few Months. However, quantities are also often entered very frequently, for example if consumption is to be tracked that changes daily or several times a day.
You can store all quantity changes in the subscription line itself or enter them in the subscription Journals and then post them. However, if you need to use external mass data to map quantity changes, it is more practical to simply import this data and automatically add it to the subscription lines. Extensive import routines are available in bill-to for this purpose.
All Functions linked to the imports can be found under the Import action in the Subscriptions Manager role center:
 |
|---|
| Ill. Actiongroup Imports |
The existing routines offer the option of loading external data from different sources into an import sheet, having it checked there for compatibility and then transferring it to a subscription Journals. sheet or optionally adding it directly to the subscription lines.
As described, the different Subscription Journal, Assignment Journal and History Journal types serve different business logics and therefore also differ in the data to be imported. Imports are available separately for each of the different Post. sheet types, i.e. for each Import Target.
Data Sources, Formats and Import Targets
Dates can be imported from three different sources:
-
Excel
-
CSV
-
XMLport
-
API
The data sources do not have to have a format prescribed by bill-to. Of course, the central fields must be filled, such as Recording Date and Quantity, but where the fields to be imported are located in their source file is flexible and can be set up by the user. This allows the user to work with many different import formats.
Any number of import formats can be defined and used for each data source via mappings. There are also three different import targets for which data can be imported: the Subscription Journal, the Assignment Journal and the History Journal. bill-to provides default mappings for each data source and for each Import Target. However, you can also easily create and use your own mappings.
Let's take a detailed look at the Functions relating to import mappings.
Excel Field Mapping
Using Excel Field Mapping, any number of different data formats can be defined for each of the three Import Targets, which can then be imported and interpreted by the app. bill-to provides a default import format for each Import Target. You can load, copy and edit these formats automatically, or define completely custom formats.
The Excel Field Mapping is defined via a data record with header/row structure.
 |
|---|
| Ill. Excel Field Mapping |
The header data contains the following fields:
| Field | Description |
|---|---|
| Code | Specifies a Code that uniquely identifies the Excel mapping. There are 20 characters available for the Code. The Code can be freely selected by the user. |
| Description | Specifies a Description that further characterizes the Excel mapping. |
| Type | Specifies whether this is a default mapping predefined by the system, a mapping created by the Users themselves or a mapping created automatically by the app during import. The Type is set by the application and cannot be overwritten by the user. |
| Import Target | Specifies the purpose for which the mapping is intended: for the Subscription Journal, the Assignment Journal or the History Journal. |
| Start at Row No. | Specifies the Excel line on which the import of the data should begin. It is common for Excel rows, for example, that the first row contains column headings, so that the import of the data may only start from the second row. |
The line data defines the actual mapping of the source data to the target fields. They contain the following fields:
| Field | Description |
|---|---|
| Field Number | Specifies the number of the field within the import line into which the source data is to be imported. |
| Field Caption | Specifies the name of the field within the import line. The name is automatically determined by the application from the Field Number and cannot be changed. It is used purely for information purposes. |
| Excel Row No. | Contains the number of the line within Excel from which the data is to be read into the import field. As a rule, this field remains empty as all data rows in the Excel file are to be read and imported. The Excel Row No. is used to map a special case. There are Excel files that map certain information that is the same for all lines in a specific field only once instead of repeating it on all lines. This could be the Subscription No., for example, if an Excel file only contains information for the lines of a specific subscription. These individual cells are usually arranged in the file above the line with the column headings. If such a field exists, it can be specified by adding the Excel Row No.. Its data information is then transferred to each Line. to be imported. |
| Excel Column No. | Contains the number of the recurring columns within Excel from which the data is transferred to the import field. The letters of the Excel columns must be transferred and stored here as integers, i.e. column A corresponds to the value 1, column B corresponds to the value 2, etc. |
| Status | Displays the Status of the Line. The value is set automatically by the application and cannot be changed. This value is initially a categorization to show the user once again what Type of Excel field it is. However, it can also indicate errors. The option values of this field are Recurring Line. Single cell Invalid definition The normal case is the Recurring Line. status. It means that an Excel Column No. but no Excel Row No. has been specified. This means that the specified column is read in every Line. and assigned to the field as an imported value. The Single cell status is given if both an Excel Column No. and an Excel Row No. have been specified. This means that a value is read that is only in a single cell of the entire Excel file. This cell is completely described with row and column numbers. Its content is added to each imported Line. The status Invalid definition is given if an Excel Row No. but no Excel Column No. has been entered. This is obviously an incorrect entry. Either a single cell was to be defined and the column no. was forgotten to be entered, or a recurring Line. was to be entered but the information was inserted in the wrong field. When interpreting the field mapping, only lines that have a Field Number and the Status Recurring Line or Single Cell are taken into account. |
The menu data includes the following actions:
| Action | Description |
|---|---|
| Copy mapping | Each mapping can be copied to another. To do this, first enter the Code of the mapping and then load the data of another mapping, which you can then edit. If you want to copy a mapping, the mapping to which you are copying must not yet have any Line. |
| Create default mapping | bill-to provides a default mapping for each of the three Import Targets. You can load this default mapping automatically. To do this, first enter the Code of the mapping and then load the default mapping. If you want to load a default mapping, the mapping into which you load it must not yet have any lines. If you load a Default mapping, the Type of mapping is set to Default by the application. This means that most header data and all Line. cannot be edited. If you want to create a customized mapping based on a default mapping, simply copy the loaded default mapping to a custom mapping using the Copy mapping action. If you have carried out the basic setup of the system using the Flash setup functionality, the default mappings are already preloaded. |
CSV Field Mapping
Using the CSV field mapping, any number of different data formats can be defined for each of the three Import Targets, which can be imported and then interpreted by the app. bill-to provides a default import format for each Import Target. You can load, copy and edit these formats automatically or define your own formats.
The CSV field mapping is defined via a data record with header/row structure.
 |
|---|
| Ill. CSV Field Mapping |
The header data contains the following fields:
| Field | Description |
|---|---|
| Code | Specifies a Code that uniquely identifies the CSV mapping. There are 20 characters available for the Code. The Code can be freely selected by the user. |
| Description | Specifies a Description that further characterizes the CSV mapping. |
| Type | Specifies whether this is a default mapping predefined by the system, a mapping created by the Users themselves or a mapping created automatically by the app during import. The Type is set by the application and cannot be overwritten by the user. |
| Import Target | Specifies the purpose for which the mapping is intended: for the Subscription Journal, the Assignment Journal or the History Journal. |
| Start at Line No. | Specifies on which CSV line the import of the data should begin. A CSV file usually only contains data lines, so that the data import starts at Line 1. |
| Field Separator | Indicates the character used to separate the fields in each CSV data line. A semicolon is usually used here, for example. |
| Field Delimiter | Indicates the character that marks the start and end of a field in the CSV data line. A quotation mark is usually used here, for example; however, it is also possible for the CSV file to work without delimiters for fields. |
The line data defines the actual mapping of the source data to the target fields. They contain the following fields:
| Field | Description |
|---|---|
| Field Number | Specifies the number of the field within the import line into which the source data is to be imported. |
| Field Caption | Specifies the name of the field within the import line. The name is automatically determined by the application from the Field Number and cannot be changed. It is used purely for information purposes. |
| Repetitive from here | CSV files have the versatile property of being able to display data lines of different lengths. The trailing, repetitive fields can represent additional lines that divide the leading, non-repetitive fields. An example will illustrate this: ABO4711;ID4711;10;01.01.2023;5;01.03.2023;8;01.11.2023 Here we see a CSV data line with the following structure: Subscription No.; Component Identifyer; Quantity; Recording Date However, as you can see from the data line, the Quantity and Recording Date fields are repeated several times. To ensure that the application interprets this correctly, you must specify which fields are repeated. In this case, it is Quantity and Recording Date. For this purpose, the first of the repeating fields is marked as Repetitive from here. The application then resolves the one data line as follows: ABO4711;ID4711;10;01.01.2023 ABO4711;ID4711;5;01.03.2023 ABO4711;ID4711;01.11.2023 Three import data records are therefore created from one imported CSV data row, with the non-repetitive fields being added to each import data record. The following rule applies to this functionality: The Repetitive fields must be at the end of the CSV record line. Each CSV data record row can have a different number of repeating fields. In the example above, the Quantity and Value fields can therefore appear once, twice, three times or any number of times in each data row. |
| Repetitive | If the Repetitive from here field is ticked, this mapping line and all subsequent mapping lines are given the Repetitive indicator. The indicator in this field is set automatically by the application and cannot be overwritten. |
The menu data includes the following actions:
| Action | Description |
|---|---|
| Copy mapping | Each mapping can be copied to another. To do this, first enter the Code of the mapping and then load the data of another mapping, which you can then edit. If you want to copy a mapping, the mapping to which you are copying must not yet have any Line. |
| Create default mapping | bill-to provides a default mapping for each of the three Import Targets. You can load this default mapping automatically. To do this, first enter the Code of the mapping and then load the default mapping. If you want to load a default mapping, the mapping into which you load it must not yet have any lines. If you load a Default mapping, the Type of mapping is set to Default by the application. This means that most header data and all Line. cannot be edited. If you want to create a customized mapping based on a default mapping, simply copy the loaded default mapping to a custom mapping using the Copy mapping action. If you have carried out the basic setup of the system using the Flash setup functionality, the default mappings are already preloaded. |
XMLport Mapping
Using XMLport mapping, any number of different data formats can be defined for each of the three Import Targets in the form of XMLports, which can be imported and then interpreted by the app. bill-to provides a default XMLport for each Import Target. You can load these XML ports automatically or define your own XML ports.
The XMLport mapping is defined via a list of XMLports.
 |
|---|
| Abb. XML Field-Mapping Card |
The XMLport mapping contains the following fields:
| Field | Description |
|---|---|
| Code | Specifies a Code that uniquely identifies the XMLport mapping. There are 20 characters available for the Code. The Code can be freely selected by the user. |
| Description | Specifies a Description that further characterizes the XMLport mapping. |
| XMLport ID | Specifies the ID of the XMLport to be used. This can be selected from the available XMLport object IDs. |
| XMLport Caption | Specifies the name associated with the XMLport Caption in the database. The name is automatically determined by the application from the object list and, unlike the Description, cannot be changed. |
| Import Target | Specifies the purpose for which the mapping is intended: for the Subscription Journal, the Assignment Journal or the History Journal. |
| Type | Specifies whether this is a Default XML port predefined by the system or an XML port written by the Users themselves. The Type is set by the application and cannot be overwritten by the user. |
The menu data includes the following actions:
| Action | Description |
|---|---|
| Create default mapping | For each of the three Import Targets, bill-to provides a Default XMLport. These are 71299575: Subscription Journals Mapping 71299576: Assignment Journals Mapping 71299577: History Journals Mapping You can assign this default mapping manually or load it automatically. To do this, first enter a mapping code and then load the Default XMLport. If you load a Default mapping, the Type of mapping is set to Default by the application. If you have carried out the basic setup of the system using the Flash setup functionality, the default mappings are already preloaded. |
Unlike Excel and CSV mapping, a new XML port cannot simply be configured by the user in a mapping Setup, but must first be written by a developer and added in a PTE (Per Tenant Extension).
Accordingly, the following notes are intended for developers.
The structure of such an XML port then reflects the structure of the external XML to be imported. There is a required structure so that the XML port can be understood and interpreted in the context of bill-to. However, this is very simple and the same for all Import Targets; they differ only in the Type and number of fields to be read.
The XML port must contain the following components:
-
a table element named ImportLine. This must be a temporary record of the type ComponentImportLineBLTAPL.
-
a text element with the name EntryType. This can contain one of three values: 1, 2 or 3. 1 stands for the Entry Type "Qty. & Value Update", 2 for the Entry Type "Qty. Update" and 3 for the Entry Type "Value Update". The values must be assigned to the "Entry Type" field of the ImportLine in the OnAfterAssignVariable trigger of the Text element. This is done using the following syntax:
case EntryType of
'1':
ImportLine. "Entry Type" := ImportLine. "Entry Type":: "Qty. & Value
Update
"; '2':
ImportLine. "Entry Type" := ImportLine. "Entry Type":: "Qty. Update
"; '3':
ImportLine. "Entry Type" := ImportLine. "Entry Type":: "Value Update
"; else
ImportLine. "Entry Type" := ImportLine. "Entry Type"::" ";
end;
-
Several fallback elements from the ImportLine, which record the data to be imported.
-
If you write an XMLport with the Import Target Assignment Journal or History Journal, you usually need an additional text element with the name AssignToDocType. For Assignment Journals this can contain the values 1 or 2, for History Journals the values 1, 2, 3 or 4. Here, 1 stands for the document type "Purchase Invoice", 2 for the document type "Purchase Credit Memo", 3 for the document type "Sales Invoice" and 4 for the document type "Sales Credit Memo". The values must be assigned to the "Assign-to Document Type" field of the ImportLine in the OnAfterAssignVariable trigger of the Text element. This is done using the following syntax (example for an Assignment Journal):
case AssignToDocType of
'1':
ImportLine. "Assign-to Document Type" := ImportLine. "Assign-to
Document Type":: "Purchase Invoice
"; '2':
ImportLine. "Assign-to Document Type" :=
ImportLine. "Assign-to Document Type":: "Purchase Credit Memo
"; end;
- a trigger OnPostXmlPort. The trigger only needs to contain the following line of code:
ImportLine.Save();
This transfers the imported data to the post-processing routine of the XMLports of bill-to. You do not need to ensure that the XMLport is written back to bill-to. If you pass the values through this simple assignment in the OnPostXmlPort trigger, this is done by bill-to's Default routines. You should not attempt to fill the ComponentImportLineBLTAPL record yourself using Code.
In order for the ComponentImportLineBLTAP record to be referenced, the corresponding PTE in the app.json must have a dependency on bill-to.
API
| Page ID | PageName | Page Caption | EntityName | EntitySetName |
|---|---|---|---|---|
| 71299838 | ComponentEntryExportBLTAPL | componentEntry | componentEntries | subscriptionreportingexport |
| 71299837 | ComponentLedgerExportBLTAPL | componentLedgerEntry | componentLedgerEntries | subscriptionreportingexport |
| 71299839 | QuotaEntryExportBLTAPL | quotaEntry | quotaEntries | subscriptionreportingexport |
| 71299858 | SubscrFcstEntryExportBLTAPL | subscriptionForecastEntry | subscriptionForecastEntries | subscriptionreportingexport |
| 71299836 | SubscriptionLedgerExportBLTAPL | subscriptionLedgerEntry | subscriptionLedgerEntries | subscriptionreportingexport |
| 71299834 | SubscrComponentImportBLTAPL | subscriptionComponent | subscriptionComponents | subscriptioncomponentimport |
following API pages are available to make evaluations possible and to Import components.
 |
|---|
| Ill. API Import Setup |
There are two fields in the Import Setup that are populated for the API. These are the fields:
| Field | Description |
|---|---|
| Transmission type | Specifies how Subscriptions components that were imported via API are to be post-processed. They can be transferred to the import or already validated there in one step or assigned to a subscription. |
| After Transferred | Specifies whether subscription components that have already been transferred to the import should be marked accordingly or deleted immediately. |
Mapping of individual import fields
Excel and CSV imports can always react to project-specific fields from PTEs or other ISV products.
For example, if a field is to be imported and passed through to the subscription line, it can be added to the Import line, the Subscription book. sheet line and the Subscription line. This field can then also be called up and taken into account in Excel or CSV field mapping. Individual fields with IDs between 50,000 and 1,999,999,999 are accepted.
Import Setup
So far, we have described how the mappings for the data formats to be imported are set up and for which Import Targets data can be imported. The actual import takes place via import cards with header and line structure. We will focus on this in the following chapter.
In the current chapter, we will first take a look at the import Setup. Individual decisions can be made for each import:
-
Which file format the data to be imported comes from (Excel, CSV, XML)
-
which Mapping Code should be used
-
For which Import Target the data is loaded (Subscription, Assignment or History Journal)
If you always import with the same parameters - for example, always or mostly an Excel file with the Default Mapping Code into a Subscription Journal - then it makes sense to store these settings in the Import Setup, because these fields are then pre-assigned for every import from the Setup.
Even if the import parameters are pre-assigned from the Setup, they can be changed manually for each individual import. This is not a restrictive default setting, but merely an input aid that saves you having to make a selection.
Let's take a look at the fields of the Import Setup in detail.
These are essentially used to preset default settings when importing files. If your business logic is very different for each import and None of the import paths dominate, it may be better not to make a default setting, but to select the file formats, Import Targets and procedure individually for each import.
General
There are three fields on the General tab of the Import Setup.
 |
|---|
| Ill. Import Setup General Tab |
All three fields are option fields. The fields have the following meaning:
| Field | Description |
|---|---|
| Import Source Type Dflt. | Selects the default for the data format to be imported. The available options are: Excel CSV XMLport |
| Load Import Type Dflt. | Selects the default for the import path. The available options are: Load into import sheet Load & transfer to Journals Load & assign subscription Each import from a file is first loaded into the import sheet. It can then be transferred to a Journals sheet and assigned to a subscription via the posting from the Assignment Journal. This process can be carried out in one or more stages. The data can initially only be loaded into an import sheet or loaded directly and transferred to a Journals sheet or loaded and assigned to a subscription. Any import of data from external systems may contain incompatibilities with the data in your Subscriptions. It is therefore recommended that you initially only load this data into the import sheet and only process it further after checking it. |
| Import Target Dflt. | Selects the default for the Import Target. The available options are: Subscription Journal Assignment Journal History Journal |
Numbering
Das Tab Nummerierung weist das einzige Feld der Import Einrichtung, in das ein Eintrag erfolgen muss, wenn Sie mit Importen arbeiten möchten.
 |
|---|
| Ill. Import Einrichtung Tab Nummerierung |
| Field | Description |
|---|---|
| Import Nos. | Specifies the No. Series for the Import sheets into which external data is imported. |
Import mapping
The Mapping tab contains nine fields in which the default mappings for all combinations of file format and Import Target are stored.
 |
|---|
| Ill. Import Setup Tab Import mapping |
Please remember that there are nine different mappings in this example. The ABO mapping, for example, is defined in the master data once as an Excel mapping, once as a CSV mapping and once as an XMLport mapping. These are three different data identities whose names are based on the respective Import Target. You could also proceed differently and consider the data source and Import Target together in the Mapping Code, e.g: EXCEL FOR ABO, CSV FOR ABO, XMLPORT FOR ABO. The naming of the mapping codes is entirely up to the user. Choose the name that seems most transparent to you.
The fields have the following meaning:
| Field | Description |
|---|---|
| Excel Subscription Mapping Dflt. | Specifies the default mapping for the import from an Excel data source with the Import Target Subscription Dflt.. |
| Excel Assignment Mapping Dflt. | Specifies the default mapping for the import from an Excel data source with the Import Target Assignment Journals. |
| Excel History Mapping Dflt. | Specifies the default mapping for the import from an Excel data source with the Import Target History Journals. sheet. |
| CSV Subscription Mapping Dflt. | Specifies the default mapping for the import from a CSV data source with the Import Target Subscription Dflt.. |
| CSV Assignment Mapping Dflt. | Specifies the default mapping for the import from a CSV data source with the Import Target Assignment Journals. sheet. |
| CSV History Mapping Dflt. | Specifies the default mapping for the import from a CSV data source with the Import Target History Journals. sheet. |
| XMLport Subscription Mapping Dflt. | Specifies the default mapping for the import from an XML data source with the Import Target Subscription Journal. |
| XMLport Assignment Mapping Dflt. | Specifies the default mapping for the import from an XML data source with the Import Target Assignment Journal. |
| XMLport History Mapping Dflt. | Specifies the default mapping for the import from an XML data source with the Import Target History Journal. |
Import Journals
The Import Journals tab contains six fields. The specifications for the Journals are stored here, into which imported data from the import sheets are transferred.
 |
|---|
| Ill. Import Setup Tab Import Journals |
Please remember that the three Journal Batch Name STANDARD shown in the example above are not the same data set. They are three different Journal Batch Names, as they are assigned to different Journal Batch Templates. The Primary Key of this entity is - completely analogous to the Book.sheets in the Default of Microsoft Dynamics 365 Business Central - composed of the Book.sheet template and the Journal Batch Name. You could also proceed differently and choose the following Journal Batch Names, for example: SUBSCRIPTION DEFAULT, ASSIGNMENT DEFAULT, HISTORY DEFAULT. The naming of the Book. sheet templates and Journal Batch Name is up to the user. Name them in the way that seems most transparent to you.
The fields have the following meaning:
| Field | Description |
|---|---|
| Subscription Book. sheet template | Specifies the default for Subscription Jnl. Templates of Type Subscription. |
| Subscription Jnl. Batch Name | Specifies the default for Journal Batch Names of the Subscriptions type. Subscription Jnl. Batch Names of the Subscription type are used to transfer data imports with the Import Target Subscription Journals. Journal Batch Names are assigned to their respective Journal Batch Name templates. |
| Assignment book. sheet template | Specifies the default for book. sheet templates of Type Assignment. |
| Assignment Jnl. Batch Name | Specifies the default for Assignment Jnl. Batch Name. Data imports with the Import Target Assignment Journals are transferred to Assignment Jnl. Batch Names. Journal Batch Names are assigned to their respective Journal Batch Name templates. |
| History Book. sheet template | Specifies the default for book. sheet templates of Type History. |
| History Jnl. Batch Name | Specifies the default for History Jnl. Batch Name. Data imports with the Import Target History Journals are transferred to History Jnl. Batch Names of the Type History. Journal Batch Names are assigned to their respective Journal Batch Name templates. |
Importing
Import
The actual imports are carried out via the import sheets, which can be called up from the role center via the Import - Import action.
 |
|---|
| Ill. Import Card |
The import sheet created via an import list using the New action contains data with a header/row structure. The header specifies which data source is to be loaded with which mapping for which Import Target.
In the example above, these data are entered manually. You can also proceed differently and call up the import directly via the Import action. In this case, the data is requested via a dialog whose fields are pre-assigned with the default values from the Import Setup.
Before we get to the possible process sequences, let's first take a closer look at the header data fields of the import card.
Import Card Header Data
The import card has the following header data fields:
| Field | Description |
|---|---|
| No. | Specifies the No. of the import card. The No. is automatically taken from the No. Series that has been stored in the Import Nos. field in the Import Setup. |
| Description | Specifies a Description of the import that can be freely selected by the user. |
| File Name | Specifies the name of the file from which the data was imported. The Name is requested via a file dialog and written back to the import card. It cannot be entered manually by the user. |
| Sheet Name | Specifies the Sheet Name of the Excel file from which the data has been imported. The Name is requested via a file dialog and written back to the import card. It cannot be entered manually by the user.This field is only available if the Import Source Type is Excel. |
| Import Source Type | Specifies the data source. The following options are available: Excel CSV XMLport The Import Source Type can be specified in the card. It is then transferred from the card to the import dialog, which is called up via the Import action. If the import dialog is called up without the Import Source Type being filled, it must be entered in the import dialog and is then written back to the import card from there. |
| Field Mapping Code | Specifies the mapping code that is used to interpret the external data source and transfer it to the lines of the import map. All options that have been created in the Excel Field Mapping, the CSV Field Mapping or the XMLport Mapping depending on the Import Source Type are available. The Field Mapping Code can be specified in the map. It is then transferred from the card to the import dialog, which is called up via the Import action. If the import dialog is called up without the Field Mapping Code being filled, it must be entered in the import dialog and is then written back from there to the import card. The Field Mapping Code must be selected taking the Import Target into account, as there are usually different mapping codes for different Import Targets. |
| Import Target | Specifies the Import Target for which the data is loaded into the import card. The available options are: Subscription Journal Assignment Journal History Journal The Import Target can be specified in the map. It is then transferred from the card to the import dialog, which is called up via the Import action. If the import dialog is called up without the Import Target being filled, it must be entered in the import dialog and is then written back to the import card from there. |
| Turn off Auto-Complete | It is possible to have imported data completed automatically. For example, if only basic data such as Component Ident., Quantity and Recording Date are transmitted via an external data source, bill-to can already establish a clear reference to the subscription line and automatically add further data such as Subscription No., Item No., Description or Unit of Measure Code that can be determined from this. The Turn off Auto-Complete field specifies whether the data import is automatically completed after importing the data or whether the data should only be inserted exactly as it was entered in the external source. For day-to-day work, it is recommended that you work with auto completion. Switch off auto completion if you want to analyze the import or correct data in the external file that has not been read or interpreted as expected. |
| Auto-Create Mapping | Specifies whether you want to automatically create a field mapping when importing data. A new field mapping is only created if you do not specify an existing field mapping for the import. The field mapping is created with the number of the import card as the Primary Key; if such a field mapping already exists, it is overwritten. The functionality to Create a new field mapping is only available for the Import Source Type CSV. Here, bill-to analyzes the data source and automatically creates the field mapping on this basis. |
| Import Date | Specifies the date on which the data source was imported. |
| No. of Issues | Displays the number of Errors and Warnings that were determined when analyzing the import using the Check import action. The field is calculated automatically by the application and cannot be influenced by the Users. No. of Issues is displayed immediately in a separate page by clicking on the drill-down functionality. |
| Status | Specifies the overall Status of the import. The available options are: Unchecked Warning Error Verified Transferred to Journals Subscriptions assigned The Status is determined by the application and cannot be changed by the Users. The status field also exists on the import lines. In the header, the field follows the principle of ropagating the worst status. This means that if there is even one Line. with the status Error, then the entire import has the status Error. |
Import Card Actions
After the header fields of the import card, let's take a look at its actions.
| Action | Description |
|---|---|
| Import | Triggers the actual import of the external data source. |
| Re-import | Deletes import results and imports a new file. |
| Check import | Checks all imported rows for possible errors or warnings. Whenever data is imported from external sources, it is possible that it may not be compatible with the data or structures within the application into which it is being imported. This is one reason why data is not usually transferred immediately to operational tables, but is first stored temporarily in pure import tables. Here, it can be analysed before post-processing. Check Import triggers the analysis of the imported raw data. The method finds out if there are any problems with the imported data and lists them accordingly. Check Import is triggered implicitly every time an import takes place, but can also be triggered manually by the user via the action. |
| Show problems | Displays warnings and errors found during the import check. |
| Assign subscriptions | Assigns imported data directly to the respective subscription lines without first writing it to a book sheet. This is also done in the background via ledger sheet routines, as is customary in Microsoft Dynamics 365 Business Central, so that the same error messages would occur as when posting data that has been persisted in a ledger sheet. |
| Transfer to ledger sheet | Transfers imported data to the corresponding ledger sheet, from where it can then be posted to the respective subscription lines. sheet, from where they can then be posted to the respective subscription lines. |
| Cancel assignments | Cancels the assignment of import lines to component items or book sheet lines. |
| Subscriptions | Displays all subscriptions that are linked via import lines. |
| Assigned subscriptions | Displays all subscriptions that are assigned in import lines. |
| Invoice batch | Displays the invoice batch to which this import is linked. |
| Statistics | Displays statistics for this import, with information on the amounts in orders, invoices, credit notes, posted and unposted amounts, as well as broken down by customer. |
| Release subscriptions | Releases the subscriptions associated with the import for invoicing. |
| Create invoices | Creates invoices from assigned subscriptions. |
| Post invoices | Posts invoices from assigned subscriptions. |
Import Process
Before we look at the Line. of the import card, let's run through the import process.
The import process consists of three phases:
-
Import of external data
-
Checking the imported data
-
Transfer of the checked data to Subscriptions
The import of external data is triggered via the Import action. A dialog is first displayed in which all the parameters required for the import must be entered:
 |
|---|
| Ill. Import Process - Import load |
The dialog contains the following fields:
| Field | Description |
|---|---|
| Import Source Type | Specifies the data source. The following options are available: Excel CSV XMLport |
| Field Mapping Code | Specifies the mapping code that is used to interpret the external data source and transfer it to the lines of the import map. All options that have been created in the Excel Field Mapping, the CSV Field Mapping or the XMLport Mapping depending on the Import Source Type are available. |
| Import Target | Specifies the Import Target for which the data is loaded into the import card. The available options are: Subscription Journal Assignment Journal History Journal |
If these fields are already prefilled on the import card, they are transferred to the dialog m. If they are not yet filled in on the import card, the values from the import Setup are pre-assigned. In any case, all values in the dialog can also be overwritten again. The values finally entered in the dialog are also written back to the import card.
Once all the required fields have been filled in and the dialog has been confirmed with OK, the system prompts you to select a corresponding file for import:
 |
|---|
| Ill. Import Process - load Excel-File |
After selecting the Excel file from a file dialog, the source data is imported, written to the lines of the import map and immediately analyzed. The entire import card with its Line. now looks like this, for example:
 |
|---|
| Ill. Import Process - Import Card |
We can see that previously unfilled fields in the header of the import card now have values. The File Name and the Name of the Excel sheet have been transferred, the Import Date has been set automatically, as has the Status. No. of Issues with the imported data is also displayed.
Now the lines of the import card are also filled. In this example, the Excel file had two rows of data. These have been imported, analyzed and saved. The first Line. has the Status Error. If we look at the fields in the Line., it becomes clear at first glance what the error is. The Unit of Measure Code field has the value STCK. Obviously, a non-existent Unit of Measure Code has been entered in the external data; the correct Unit of Measure Code should be STCK. If this is possible, because the issues are not too complex, the application immediately indicates data errors by highlighting them in red so that it is easy for the user to identify the issue.
No. of Errors field shows that there is exactly one error on this imported data line. The color coding of an error is only for quick identification. You can also display the errors in a detailed Description. This can be done by clicking on the number in the No. of Issues field, which opens a list of the errors in this Line.
 |
|---|
| Ill. Import Process - Failure |
The error list contains three fields, which are described in more detail below:
| Field | Description |
|---|---|
| Status | Indicates the Status of the imported data row. The available options are: Error Warning |
| Issue Type | Classifies the analyzed Issue. |
| Issue Description | Shows a detailed Issue Description from which the user can clearly see what the Issue is. |
Once all errors have been corrected, the third step of the Import process can begin: transferring the data to a Journals sheet or directly to the subscription line that is to be updated in terms of quantity or value using the imported data. The Transfer to Journals and Assign Subscriptions actions are available for this purpose.
Import Lines
In the import process, we have already learned how the import lines are filled and what they mean. Let's now take a closer look at the fields in the import line.
| Field | Description |
|---|---|
| Status | Indicates the Status of the imported data row. The available options are: Unchecked Warning Error Verified Transferred to Journals Subscriptions assigned The status is set by the application via the Check Import action or the subsequent data transfer via the Transfer to Journals or Assign Subscriptions actions and cannot be changed by the Users. |
| Process | Indicates whether an import line can be processed further. One Line. can only be processed further if it has the status "Verified". If the status is "Unchecked", an error check must be carried out first. If the status is "Warning" or "Error", there is an Issue that must first be resolved. Further processing has already taken place for the statuses "Transferred to Subscription" and "Subscription assigned". |
| Entry Type | This field is filled by the import. It can only be filled for the Import Targets Assignment Journal and History Journal. The available options are: Quantity and value update Quantity update Value update In the external data sources, the Quantity and value update option is represented by a 1, Quantity update by a 2 and Value update by a 3. |
| Subscription No. | This field is filled by the import. It specifies the number of the Subscriptions to which the imported data record relates. |
| Item No. | This field is filled by the import. It specifies the number of the Subscription Item of the subscription line to which the imported data record refers. |
| Description | This field is filled by the import. It specifies the Description of the Subscription Item of the subscription line to which the imported data record refers. |
| Component Identifyer | This field is filled by the import. It specifies an indicator of up to 20 characters that can be used to uniquely identify the subscription line to which the imported data record refers. |
| Unit of Measure Code | This field is filled by the import. It specifies the sales unit of the Subscription Item of the subscription line to which the imported data record refers. |
| Recording Date | This field is filled by the import. It specifies the date on which the quantity and/or value changes triggered by the imported data record become effective. |
| Quantity | This field is filled by the import. It specifies the Quantity by which the subscription line to which the imported data record refers is increased or decreased. |
| Amount | This field is filled by the import. It indicates the change in value of the subscription line to which the imported data record refers. The field can only be filled if the Import Target is History Journal. |
| Assign-to Document Type | This field is filled by the import. It specifies the Document Type that is linked to a Quantity or Value change. The following options are available: Purchase invoice (external: 1) Purchase credit (external: 2) Sales Invoices (external: 3) Sales Credit Memos (external: 4) The field can only be filled for the Import Targets Assignment Journal and History Journal. The Sales Invoice and Sales Credit Memos option values are only available for the History Journals import target. |
| Assign-to Document No. | This field is filled by the import. Depending on the Assign-to Document Type field, it contains the document number of one of the four posted Documents. The field can only be filled for the Import Targets Assignment Journals and History Journal. |
| Assign-to Document Line No. | This field is filled by the import. Depending on the Assign-to Document Type and Assign-to Document No. fields, it contains the line number of the posted document. The field can only be filled for the Import Targets Assignment Journals and History Journal. |
| No. of Issues | The field is a field calculated by the application that cannot be changed by the Users. It shows the No. of Issues analyzed for the import line. By clicking on the number, the errors are displayed in a separate page using the DrillDown functionality. |
It is not necessary for all fields of the import line to be filled during import in order to achieve a valid Quantity or Value change of a subscription line. These are the minimum requirements for fields to be filled depending on the respective Import Target:
| Field | Description |
|---|---|
| Import Target | Minimum import fields to be filled |
| Subscription Journal | Component Identifyer Recording Date Quantity |
| Assignment Journal | Entry Type Component Identifyer Assign-to Document Type Assign-to Document No. Assign-to Document Line No. |
| History Journal | Entry Type Component Identifyer Quantity + Amount or Assign-to Document Type + Document No. + Document Line No. |
Individual import fields and events
Individual fields can be added to the import line. On the one hand, these can be used to be passed on to the subscription line via the subscription book. sheet line. On the other hand, they can be used to make information that is imported in a non-usable format usable.
The Recording Date is one of the mandatory fields that must be filled in for an import to be interpreted correctly. Let's assume that no date is offered from an Excel or CSV file for import, but only a Text such as "November 2023". This information can be read into a newly added text field and then parsed during import validation and converted into the Date 30.11.2023, for example, which is then used to fill the Recording Date.
Numerous events are available to external routines for editing during import. These are
- OnAfterTransferExceBufferCellToImportLine
- OnBeforeInsertImportLine
- OnAfterTransferCSVBufferCellToImportLine
- OnBeforeInsertImportLine
- OnAfterInitializeSubscriptionJournalLine
|
|---|
| Personal support www.ckl-software.de/ |