Import in a normalized structure

[Søren Dyhr]

Hi

I read a question in another forum (in my native language) ...that really made me wonder why there isn't provided for it in the tools at hand when importing new records into a solution.

You might buy a wagonload of not normalized addresses for a mailing campain, but some of the data already exists in your current system, which obviously is normalized to the marrow of the bone. How would you bring your database up to sync normalized, which just migt be adding a few more contacts to a company??

Well de-duping via a selfjoin is common practice, but how would add to a related tables data if it's a dupe?? Now here comes the new GetNthRecord( function in handy, because it also handles related data - I use in this template following calc' that perhaps requires some explanation:

Substitute ( GetNthRecord ( Extend ( CompanySelfJoin::ContactPerson ) ; Get ( CalculationRepetitionNumber ) ) ; "?" ; Case(0;0) )

...This is a repeating calc'field that plucks data from the selfjoined TO, but if the number of repeating fields defined is larger than the number of records related returns GetNth... a questionmark, which is unwanted data in the between table imports, so a Substitute( is required to replace it with void ...I do here use Case(0;0) ...which is producing voids regardless of the field type.

I've made both a Normalize script and a Denormalize script, which could have been unified in one with a pause inserted, but I think it make more sense to have i organized as logical chunks of code ...since it better illustrates the dealings.

The way it's ment to work is every time you recieve a new set of addresses should the data be denormalized just before the new records are manually imported. When it's done is the final thing to do to normalize the now larger set of non normalized data and view it as main records having a portal showing contacts.

Enjoy!!

--sd

splitting.zip

[Brudderman]

Hello, Søren!

This looks quite interesting as i have a situation now where I need to normalize lots of data and this looks like an efficient way to do it. If you happen to have a small sample file of unnormalized raw data that would be imported into the receiver table and processed by the normalized script, that would be most helpful in seeing exactly how it works.

Is this something that you have available? If not, that's OK as I can probably eventually get it figured out.

Thanks so much for submitting this!

James McAlister

[Søren Dyhr]

Ha, Ha, I'm always scratching my head to invent data a.k.a. Boilerplate, Hoosewrench and Gadget. Sorry it would be better if you dissect it guided by me.

First do I need to know what is the toughest part to grasp??

--sd

[Brudderman]

Thanks!

>>I'm always scratching my head to invent data

No need to do that. i just thought that it might exist already from the problem you were working to solve when you wrote the routine.

>>First do I need to know what is the toughest part to grasp??

The repeating field and its relation to the data structure being imported. And why are there just 5 repetitions. But let me work on it some, and I'll get back with a more detailed query.

Thanks!

jm

[Søren Dyhr]

And why are there just 5 repetitions.

Purely guesswork, if you know that you're likley to have at least 25 to be shown in your portal, then make it 25 repeaters.

--sd

[Brudderman]

Thanks. I'll dig into it a bit and get back with any other questions that arise.