Import data with parent child relationships

rocketman · Post by **rocketman** » Wed Feb 12, 2014 5:27 pm

Knotty little problem.

I'm trying to create demo copy of my App which will self load key data on initialisation. One of the key BO's is a Chart of accounts which - because I use trees in the finance module has a parent child relationship like:

8000 - Subscriptions
8010 - Full Membership
8020 - Family Membership
8030 - Associate Membership

Can't import ID's because - being a demo these will always be imported into fresh empty business spaces, so the ID's need to be new and in step with all the other BSV's

Can import the file OK so I have a flat file with the complete chart of accounts (all 152 of them) What I can't figure out is how to isolate all the instances in the range 8001 to 8999 and then insert them all into the reference attribute (ChartOfAccounts.Subsections) of instance 8000

Can run a process after the import but can't seem to sort out my THIS and THAT to get everything in the right place

Any ideas?

tford · Post by **tford** » Wed Feb 12, 2014 7:44 pm

Don't fully understand your challenge from this description.

Shot in the dark ... can you include info in your import file that helps direct where a record needs to be inserted?

rocketman · Post by **rocketman** » Wed Feb 12, 2014 10:25 pm

Hi Tom,

The problem is I can't use Import Relationships because I can't export the ID's

So I can import the flat file, I can create a process which finds all the outer levels (all instances that have a BO.Level='master' The outer levels are usually numbered 1000, 2000, 3000 etc . All instances numbered 1001 - 1099, 2001 - 2099 etc etc should then go into BO.subsections as referenced items

So for example I FIND 8000 so that's one ChartOfAccounts instance in context. I then FIND all ChartOfAccounts between 8001 and 8099. So the problem seems to be I end up with - say 10 ChartOfAccounts instances in context, 9 of which have to be placed as referenced items in the First instance.

So finding the right instances isn't the problem, it's how to place 9 into 1

Hope this is more helpful.

rocketman · Post by **rocketman** » Wed Feb 12, 2014 10:31 pm

Meant to add.... The end result is a tree which shows 8000 - subscriptions, 8100 - some other income heading as the nodes on a tree.

Clicking 8000 reveals the subsides 8010,8020,8030 etc

Clicking on the sub nodes reveals the sales transactions for that analysis code.

All works very well in the live app where I started from scratch and built up the tree manually. It's how best to export them so,they can be imported automatically and the tree rebuilt in a demo app for distribution to potential customers.

nlarson · Post by **nlarson** » Thu Feb 13, 2014 12:11 am

This might be what Tom is suggesting, but Can you create an indexed field which you control and add rules to populate it in parent and child then used that as the import child grouping? We often had to do something similar in CRM data conversions.

tford · Post by **tford** » Thu Feb 13, 2014 12:58 am

Rocketman,

What I've sometimes done is to export the ID attribute, then re-label it as an attribute say OriginalID in the file to be later imported.

Then that attribute can be used to help give direction to the import process. OriginalID will NOT be used to populate the ID field during the import process.

rocketman · Post by **rocketman** » Thu Feb 13, 2014 1:00 am

Yes can do that, and I'm figuring if I just export the masters to one file and the others to a second file, with an extra attribute added to hold the master account code (UpLink), I can then import the Masters, tweak the column headers in the second file and then import the second file ....

Thanks for helping me think it through guys. It's 1:00am in the UK. I'll try it in the morning and let you know how I get on.

rocketman · Post by **rocketman** » Fri Feb 14, 2014 12:32 pm

Got there in the end guys. In the end I decided to go with creating an UpLevel ID that matched the actual Account code in the top level account (to be used by other processes later) although I'm sure the renaming of the original ID to oldID would have worked.

Importing was a snap requiring two processes.

Process one - import the Chart of Account "Masters Levels", Import the Subsections which contained an extra column holding the Master Account Code
FIND all ChartofAccounts where level = master
Call process 2

Process 2
Stuff the master account code into a NPBO
FIND Chart of Accounts WHERE CofA.Uplevel=NPBO.IntegerAttribute
INSERT ChartOfAccounts INTO ThisChartOfAccount.Subsections

All is once again well with the world (If you ignore the wind, the rain, the flooding, the disruption to travel, the fallen trees and the leaking conservatory roof!!)