"ID" in alle Tabellen

[Al]

Hallo,

I'm new to SQL and real rational data bases. Have played around with Access etc. I have a basic question. (I am using EMS Interbase/Firebird Manager together with excell.csv exports or directly form access.mdb)

If I have an existing database which already has customer data and customer ID's allocated, can I import these ID's and let AvERP generate new ones in the same series? With the many BKUNDE and related tables, how are these tables linked? In access the unique key was generated by accass when a new customer was entered into the system. I notice all AvERP tables have an ID field (DOMAIN D_ID), but they seem to be unique for each transaction rather than to the customer ID. On importing, I only have the customer ID in the old rather flat file of access which I must use to be able to split up the data to various tables in AvERP. I have about 5000 customers, so a shortcut is essential. There are also around 10000 parts incl. sub parts lists (Stücklisten), where the existing part numbers are essential, and must be kept.

Any help will be wonderful.


Al

[SYN14]

Hi,

in the comming version of AvERP it is possible to define templates for generating customer IDs. You can have a look at this feature by downloading the alpha version of the database (http://www.synerpy.de/AvERP/Download/Av ... .alpha.zip). The ID field is used to link the different tables, e.g. there is an ID of the table BADR in the table BKUNDE. In AvERP you only see the adress number.

The same concept is used within the entire database. So don't worry about your sub parts list. If you look at the structure of this table you won't find a field where the part number is used. The part numbers are referrenced by the ID (D_ID) of table BSA.

mfg SYN14

[Al]

Hi,

in the comming version of AvERP it is possible to define templates for generating customer IDs. You can have a look at this feature by downloading the alpha version of the database (http://www.synerpy.de/AvERP/Download/Av ... .alpha.zip). The ID field is used to link the different tables, e.g. there is an ID of the table BADR in the table BKUNDE. In AvERP you only see the adress number.

Thanks for the info. I got the database. OK, Kundennummer is the label "Id." in the Views. I can see it when I start the Kundenstamm View. I have tried to figure out what table it is in. I can't see it in BKUNDE. In BKUNDEFILE I see BKUNDE_ID_LINKKEY. Is that the field the Kundennummer has to go into when I import to the tables with these fields? It will be helpful if the table and field names appear in the status bar as in the main menu. It only displays the Status, Sort and Field label in the views.

I have a flat file with:
Knd_ID, Knd_Name, Knd_Land, Knd_PLZ, Knd_Ort, Knd_Strasse, Knd_Tel, Knd_Fax, Knd_Entfernung, Knd_Code, Knd_Bemerk, Knd_USt_ID, Knd_Lieferantnr, Username, Lastupdate. (The Knd_ID is the Primary key).

I usually use a spreadsheet to export and manipulate the data for imports. All I need to know is what are the corresponding fields (in which Tables). Almost all are found with A_TABELLEN. Just looking for the Kundennummer.

The same concept is used within the entire database. So don't worry about your sub parts list. If you look at the structure of this table you won't find a field where the part number is used. The part numbers are referrenced by the ID (D_ID) of table BSA.

mfg SYN14

With the parts numbers, I take it you mean the numbers themselves are not used as reference keys, but referenced by ID (D_ID). I'll try that later when I got the KUNDEN sorted out (and by then maybe learned enough to then be able to do the parts; ... but Stücklisten import ?).


Al

[Gast]

Hi,

it is possible to display the list of fields, their names and values by clicking on Hilfe (help)--> Query-Info --> Feldinhalt (contents of field). Or if the field is visible on the current window (not Username, Lastupdate or ID except the PK) you can click into the field you want to know the name of and then on Hilfe -->FeldRechte (Users with full field access). Then you can see the name of the field in the title bar.

The corresponding fields to your ones are:

Knd_ID = ID in table BKUNDE (Customers), it's used as PK too
Knd_Name = NAME in table BADR (Customers->Address)
Knd_Land = LAND in table BLAND (Customers->Address->Postal Codes->Country)
Knd_PLZ = PLZ in table BPLZ (Customers->Address->Postal Codes)
Knd_Ort = ORT in table BPLZ Knd_Strasse = STRASSE in table BADR (Customers -> Address)
Knd_Tel = TELVOR and TELANSCH etc. in table BADR
Knd_Fax = FAXVOR and FAXANSCH in table BADR
Knd_Entfernung = not included
Knd_Code = ? explanation please
Knd_Bemerk = BEMERK1 and BEMERK2 in table BKUNDE
Knd_USt_ID = UST_ID_NR in table BKUNDE
Knd_Lieferantnr = not included
Username = BENUTZER in table BKUNDE (Customers)
Lastupdate = GESPEICHERT in table BKUNDE (Customers)

[Al]

Hi SYN15,

Thanx for the hint. I have now tried an import to BKUNDE with EMS. There are some errors:

0 - fmTableView_1.frmData.DataD.InsertQuery:
can't format message 13:375 -- message file C:\Programme\EMS\firebird.msg not found. validation error for column BADR_ID_ADRNR, value "*** null ***".

I gather from that, that a certain 'Reihenfolge' should be used when importing. This BADR_ID_ADRNR has to be present first, it may not be empty. In my database there is only one address, but AvERP allows more addresses per Kunde. Does each Address have an ID (D_ID) in BADR irrespective of customer, or is it per BKUNDE? How are the tables related? I filled in a value of "1" in all fields as a test. It was accepted.

Next error:

0 - fmTableView_1.frmData.DataD.InsertQuery:
can't format message 13:375 -- message file C:\Programme\EMS\firebird.msg not found. validation error for column ERFDATUM, value "*** null ***".

I have to enter the date. Added the creation date column. OK. Next error

0 - fmTableView_1.frmData.DataD.InsertQuery:
can't format message 13:375 -- message file C:\Programme\EMS\firebird.msg not found. validation error for column BKUGRU_ID_PREISGR, value "*** null ***".

have to get the Preisgruppe ID's, etc., etc.

So I have to know the sequence of the tables and what has to be entered into each at import. In the BKUNDE there are 16 constraints, linked to 11 other tables. In each of these tables there will also be constraints. Which is the first table where the data can be imported to and then which is the next, etc. I use Excel, where I can create defaults in required fields/columns. The old database has no ID's like these required in the AvERP database. They will have to be generated. Do I need the purchasable Dokumentation to get the info for a migration process? I can go into each table and search for them, but it is like pulling teeth (zum Mäuse melken).

• Is there a better way to get to the info?
  Has anyone migrated a database sucessfully yet?
  Where do I start at best with a migration import?

Al

PS. What does the error: "C:\Programme\EMS\firebird.msg not found" mean?

.

[SYN14]

Hi,

I gather from that, that a certain 'Reihenfolge' should be used when importing

of course there is an order the data must be importet. It is the same as in AvERP. Before you can add a customer you need an adress. Before you can add an adress, you need a postal code and a language, and so on.

So I have to know the sequence of the tables

This means that the sequence is equal to the order in AvERP.

Do I need the purchasable Dokumentation to get the info for a migration process?

The documentation doesn't include a migration guide.

What does the error: "C:\Programme\EMS\firebird.msg not found" mean

Firebird is using a file where all messages can be translatet. Your firebird service can't find this file. A reinstallation of firebird should fix the problem.

mfg SYN14