Normal Topic Importing records to multi levels (Read 518 times)
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Importing records to multi levels
Jan 19th, 2008 at 6:01pm
Print Post Print Post  
Before I get into many tests for the best method, maybe someone has been there already?

I have a design with about 7 levels of parent/child forms with natural linkiing.
Top level can be called level1, it has a subform - level2.  Level 2 form has a subform - level 3.  And so on, until Level 6 with subform - level7.

The multi levels are to support a situation with multiple customers.  Each Customer has multiple Sales Orders.  Each order has multiple line items.  Each Item can be delivered to multiple sites.  Each site can have multiple delivery dates.  Each receipt is broken into lots.  And each lot can have multiple inspection measurements.  So, the resulting database structure looks something like this:
Customers/SOs/Line Items/DeliveryLocation/ReleaseDate/Lots/QC Samples.  The customer is pretty committed to this structure, so that will not change.

Each of the forms also has a relational subform in addition to the natural linked subform.  Both are there to provide the most flexibility for the import process.  The relational forms will be deleted after data has been imported.

The data in the 7 levels comes from many different sources, but will ultimately end up in ASCII file formats.  Most have just one level of data, and a key value will be inserted for the parent ID link.  The single Sesame application will be replacing about three other applications and multiple spreadsheets.

The question is the best way to import data into different levels.

I thought that I could create parent records in relational forms 6 for each parent of level 7, then import data into relational subform7.  Then I would export all records and subrecords from relational form 6, and import the data back into Natural link form 6.  Then could delete the relational subform 7 with its data.  But I am not sure that I would then be able to link these to level5 unless I import them into level 6 relational instead. I will also be importing level 6 data from ascii files as was done with level 7.

I could take a similar approach at each level, working up the tree of chlld/parents.

Do I do all the work only with relational forms until I do the final export of level 2 records?  Export from relational, subform2 and do the final import to natural linked subform2?

I am open to all suggestions about the best way to import the data into the Sesame natural linked structure.

  



Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Importing records to multi levels
Reply #1 - Jan 20th, 2008 at 2:25am
Print Post Print Post  
FYI: 2.0.5 is going to include some really good tools for this kind of thing.
  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged
 
Bob_Hansen
Senior Member
Members
*****
Offline


WOW, They have the Internet
on computers now!

Posts: 1861
Location: Salem, NH
Joined: Nov 24th, 2002
Re: Importing records to multi levels
Reply #2 - Jan 20th, 2008 at 2:55am
Print Post Print Post  
I am glad I asked before putting in all the extra effort.

Wow, I can hardly wait until Monday morning!   Roll Eyes

Thanks for the good news Erika.
  



Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
IP Logged