Normal Topic Importing Problem - multi-layer database (Read 2552 times)
jfreeman
Member
*
Offline


No personal text

Posts: 23
Location: KZN, South Africa
Joined: Apr 10th, 2005
Importing Problem - multi-layer database
Nov 23rd, 2017 at 10:57am
Print Post Print Post  
I've a job I've been ducking for a while.  My Q&A Investment and Banking suite comprises five interlinked databases: Account holder, Security Name (e.g. shares), Transactions (record of buys and sells), Holding History (so I know how many shares I had at each dividend time), Income (records each dividend due) and Bank Account.  Lots and lots of reports, etc.

It has to become a Sesame Application!  This becomes a three-tier one with the Account holder at the top, the Security Name and Bank Account as subforms, with Transactions, Holding History and Income as subforms to the Security Name subform. All are Form View.

So I went ahead with a nice naturally-linked design and then realised I had no clue how to import such a hellaciously complicated data set which is 12 years old.  I can't even think how to build the hierarchical ASCII file for this!! 

I fear I may have to start again with relational linking, just to be able to manage the import!  Is there any way even to save the individual form designs so that I won't have wasted some weeks of work?

John F
  

John F
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 Problem - multi-layer database
Reply #1 - Nov 23rd, 2017 at 10:06pm
Print Post Print Post  
I am sure the quick answer to this is YES.  But more complex than I can explain now on the forum.

A few general guidelines:
1. Think of natural structures the same way you think of relational ones.
2. Create separate database in Sesame to match the ones you already have. Keep all the databases in the same application
3. After the new databases are created you can import data from old system to new databases
4. After the new databases are created you can nest them as subforms.  Create different subforms as needed for each parent form.   There can be multiple subforms on same parent form.  Same database can be a subform on more than one parent form. You can have subforms within another subform.  You can choose different fields to display on different forms for the same database.

More specific definitions needed now for more specific instructions.   Just wanted to give you hope and help you to understand the potentials.

It sounds like you already did your design.   Remember you will be importing into databases,  not into forms.  If they are structured correctly,  I think you can just import from your old databases to your  new ones.   I would do this in a top down process of database links.   As they are imported, the natural links should be created and attach the records to  the correct parents.

Can you show a tree of your databases and forms?
  



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


No personal text

Posts: 23
Location: KZN, South Africa
Joined: Apr 10th, 2005
Re: Importing Problem - multi-layer database
Reply #2 - Nov 24th, 2017 at 3:02pm
Print Post Print Post  
Thanks Bob!

Most of what you write is familiar to me.  The form designs are done.  Unfortunately I had already made the whole linked design before the difficulty of importing struck me!  The diagram is attached.  There are no separate copies of any form except the banking one, which exists as a separate application in its own right.

I think I have cooked my goose and will have to begin again.  This time I'll make each form a separate application and keep it that way until  the import is done.  Most of the forms are simple, except for the 'Transactions,' 'Income'  and 'Capital Distrib.' ones, which (because of the shifting sands of South African tax law) need 30, 14 and 17 dynamic LE's respectively.  The four lowest layer forms plus the Bank form all have to fit within a tabbed page group to make them accessible.  I think you can now see why repeating all the work is daunting me!

John F
  

Investment_Aoplication_Design.doc ( 23 KB | 120 Downloads )

John F
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 Problem - multi-layer database
Reply #3 - Nov 24th, 2017 at 9:34pm
Print Post Print Post  
Hi John.

Thanks for the attachment.  I see nothing unusual based on that.   

It would seem that you should be able to import the AccountHolder  into a Sesame AccountHolder database.  Then import the SecurityName database into the Sesame SecurityName database.

If you have already added a SecurityName subform to a form in AccountHolder, you should see immediate results.   If not, then add the subform with Natural links to get the results. (Use Tabs for subforms, even if only one now.  Will be easier to add multiples at a later date).

Then do the same with BankAccounts with a Natural linked subform on AccountHolder

Now do the same with the other four databases , making subforms for each one on your desired parent form(s) in SecurityName.

I generally make Tabs for subforms to eliminate scrolling and to have quick access to other info just by changing Tabs.

But what I suggested here is based on some experience assumptions and may not be so straightforward.  What I would really want to see is the exploded tree of the database and form structures you have created in Sesame.

You can always make a copy of your existing design and try these steps on the copy.   Good luck.
  



Bob Hansen
Sesame Database Manager Professional
Sensible Solutions Inc.
Salem, NH
603-898-8223
Skype ID = sensiblesolutions
Back to top
IP Logged
 
Ray the Reaper
Global Moderator
Members
Lantica Support
*****
Offline


The One & The Only

Posts: 2480
Joined: Aug 20th, 2003
Re: Importing Problem - multi-layer database
Reply #4 - Nov 27th, 2017 at 2:09pm
Print Post Print Post  
Even if they are already linked as Natural, you can import into each individual database the records that you need in that database. You will however need a unique identifier in each database so that you can run a reparent. The reparent will let the parent records in Sesame know which child records belong to it. Once they are parented they stay parented. So you just have to import into your parent database, child databases and grandchild databases. Then reparent each child database to it's respective parent database.

-Ray
  

Raymond Yoxall Consulting
ray.yoxall@gmail.com
ryoxall@lantica.com
Sesame Applications, Design and Support
Back to top
IP Logged
 
jfreeman
Member
*
Offline


No personal text

Posts: 23
Location: KZN, South Africa
Joined: Apr 10th, 2005
Re: Importing Problem - multi-layer database
Reply #5 - Feb 26th, 2018 at 7:51pm
Print Post Print Post  
Thank you Bob and Ray!  I know this is delayed, but I haven't died. I am a clergyman and near Christmas and for some time afterward life gets hectic, even though (at 78) I'm 'retired.'  I didn't know I could do the re-parent procedure with a naturally linked database structure. That certainly does solve my problem!
John F
  

John F
Back to top
 
IP Logged