Import with IDs

dwilson837

I would like to move some existing data to a new business space. I haven't changed anything about the BOs. I do an export, including IDs and relationships. When I import that file into the new business space, the IDs aren't maintained (the newly created objects get new IDs) and my relationship files are useless because they are using the original IDs. What am I doing wrong ? How can I accomplish this without pain ?

Thanks,
Dylan

Powerm

I use Navicat ( navigate.com ) to import/export into Mysql, if you want to keep the ID's there is an option there to re-populate datas instead of append datas.

aware_support

You have to make sure that the value in the BAS_IDGEN table is correct. It has to be the max number of all ID's used in the data. The new ID for any new object is taken from this value which is automatically incremented. So you need to make sure that this value is not less than existing value, otherwise the new object will have the ID of some existing object that will screw up the system.

You can manually change this value - it won't do it any harm

dwilson837

I imagined and that and tried it but it didn't appear to work. Will try again This is the BASIDGEN table or something like that ?

dwilson837

I mildly misread your last note. It doesn't keep the IDs in the file. That is the problem.
It doesn't matter at this point where BAS_IDGEN is sitting because it drops my IDs and losing all references. I moved it past all my current IDs and it still doesn't appear to work.

5.2 Build 1513

Thanks,
Dylan

Powerm

I had this problem with an other application, now I prefer to use my own ID's when I build a web app i.e. " AccountID" as an incremented number.

nlarson

Powerm wrote
I had this problem with an other application, now I prefer to use my own ID's when I build a web app i.e. " AccountID" as an incremented number.

Having come from the CRM world where complex relational legacy data migrations are an involved in most every project I have found that ID and relationships I can control are always good to have. Often overkill, but never harmful.

The worst conversion I ever did was an old access database of just 2200 contacts for Nordea Investments - the guy with the data said "how much?!?!? how hard could it be? I built that database and I'm no programmer!" my best SQL dev just said "exactly" and smiled.... good thing we we're on a conf call :lol:

tford

now I prefer to use my own ID's when I build a web app i.e. " AccountID" as an incremented number.

In one situation, when moving selected data to a new server, I basically used AwareIM's ID attribute as my own ID. I created another attribute in the new DB called OldID and imported it. That allowed me to later know exactly where info came from in the old system.

aware_support

1) Whether you use your own ID's is irrelevant. Aware IM DEPENDS ON CORRECT values in the BAS_IDGEN table. Without it NOTHING WILL WORK whether you use your own id's or not

2)
I mildly misread your last note. It doesn't keep the IDs in the file. That is the problem.
It doesn't matter at this point where BAS_IDGEN is sitting because it drops my IDs and losing all references. I moved it past all my current IDs and it still doesn't appear to work.

What do you mean that "it doesn't keep the IDs in the file"? What file? And what do you mean by "drops my IDs and losing all references"?

I think you misunderstood my message above. The BAS_IDGEN table has only one column which has only one value. This value is EXTREMELY IMPORTANT.

At all times this value should be greater than the maximum value of all ID's columns in all the tables of the corresponding database.

If you move some data from some other Aware IM database which has different ID's then you can have the following situations:

1) You move only some tables into your database, so the resulting database is a mixture of existing tables and those that you have moved from elsewhere. This can be potentially fatal unless you know what you are doing.
a) It is possible that the tables you move have records with the same ID's as records of the existing tables, in which case nothing will work no matter what you do. You should avoid this scenario at all costs.
b) The tables you moved have ID's that are all less than the ID's of the current tables. This should just work without any changes
c) The tables you moved have ID's that are all greater than the ID's of the current tables. This will not work unless you manually change the value of the BAS_IDGEN table to be the greatest of all ID values in the tables that you moved.

2) You move ALL the data tables from one database to another without moving the BAS_IDGEN table.
In this scenario YOU MUST either copy the BAS_IDGEN table as well or set the value in this table to be the same as in the database you moved.

Powerm

In all cases I do a daily backup of the entire database and once a week I do an sql dump to an external hdd. Migrations are nightmares and you do better have more than (working...) backup 🙂

ACDC

Trying to use the ID will not work as you have no control of the ID attribute as it is system generated using BAS IDGEN.

it sounds like you have to become more familiar on how to import data and the use of a "*" in the column header of the file to be imported. The * indicates that it is the key for the relationship setup. So Toms suggestion of using OldId as the key would do well for the process

Alternatively, if you are just moving the database to a new location, then you don't have to worry about importing the data, just copy the database by way of a sql dump and sql restore to the new location. That's of course after you have created the new environment with the new system tables intact ( don't restore the system tables from the old database)

dwilson837

I understand the ID table. I have one simple question. If I use the built-in AwareIM export functionality and then use the built-in import functionality, is it possible to import multiple BOs with relationships and have those relationships preserved in a new business space? That doesn't appear to work.

I understand I can do it manually on the backend.

aware_support

Using export/import to transfer data may not be a good idea unless you manually change the exported CSV files to private primary keys for relationships instead of ID's (there IS a way to indicate a primary key in the import file).

rocketman

Following Tom's post,

When using AwareIM's export/Import. It is essential to have an ID in the referenced BO which the import from a csv can match to irrespective of what the system generated ID is:

Example you have an invoice file which has an invoice number, and an invoice line items file which also contains a reference to the invoice to which the line item refers. You also need to store the invoice number locally in the line items file

Export the Invoice file and the line items file as two separate csv's DO NOT EXPORT the relationships

You then need to massage the csv's to put a row of column headers on line one (I use excel to do the massaging) that match the attributes in the file for example invoice.customer, invoice. invoiceNumber invoice.invoiceDate etc etc

Here's the key, in the line items csv file you will have a column which contains the invoice numbers. You need to ensure the column heading for that column contains the reference field so that AwareIm will try to rebuild the link it would in our example be LineItems.InvoiceRef.InvoiceNumber

AwareIM will first create a line items record, then look for a single instance in the invoice file for whatever the invoice number is in the above column, and then create the reference link.

One word of caution - don't rely on shortcuts - when last I tried this, AwareIM did not export shortcuts (It would be REALLY helpful if it did Support 🙂 ) 😃 If you don't currently have matching attributes in each BO, you'll need to write a process to create them