Import Records = No Relationships

[RPM]

I've got a problem with importing data and automatically establishing the correct relationships between tables.

I'm working on a solution to quantify on a daily basis the number of railcars handled by our railroad for our various customers. I have built a table, with the usual customer data in it, including a field, which is the key field. I have also built a table, and it contains the data about each rail car handled. It also includes a field which contains matching data related to the field. Any given customer may have many cars.

Unfortunately the format of the data I have to import every day (via FTP) is not user friendly in that it is old-fashioned, fixed column width data, delimited with semi-colons instead of commas. This cannot be changed. I've developed a work-around for this problem by importing each days rail car data into a single and then using a script to parse the data into the correct fields using the "Middle" function, as in

Set Field [Cars::WaybillNumber; (Middle (Cars::ImportField ; 0 ; 9 )

for example. This gets the data into the correct fields, (23 of them) but it does not establish the correct relationships. In fact, there are NO relationships established at all.

I have obviously missed something in the import and field mapping process, but I've "hit the wall" in trying to figure out how to make the relationships work in this situation.

Any help will be greatly appreciated.

Edited February 26, 2007 by Guest
removed shouting

[aldipalo]

Does the import ftp file contain the ::CustCode field data? If not how are you assigning it?

If you are having a relationship problem that MAY be it.

hth

[RPM765]

The import file contains the data, however it is not assigned to the proper field until I parse the data and map the fields using the "Middle" function as described above.

I've given this a little more thought today and I think you are on to something here. The way I'm doing it now, the new Car Records which get added during the import get added as unrelated records because of the way the data I have to work with is formatted. Is there a way to establish the relationship AFTER the new records have already been added?

[aldipalo]

I guess that depends on how many records you are importing. If we are talking >100 it can get pretty clumsy.

Have you thought about importing data into an excel spreadsheet and parsing the data there then importing the xls file into fm? A little more work but then your ::custcode will be parsed and the relationship should be matched immediately.

Just a thought.

I do that regularly with contact data I import from web sites. My companyID is a concatenated calculation of company name and zip code which I need to relate the contact name to the correct company and I find this works for me.

[LaRetta]

My companyID is a concatenated calculation of company name and zip code

You'd better hope a company doesn't move or change their name.

The question arises ... does the import data contain the CustCode anywhere? If so, place it in the CustCode field afte the import. If not, you need to determine what makes each record relate to your Customer. There must be something which you import (a RailTicketNumber?) which ties them to the customers.

Is there a way to establish the relationship AFTER the new records have already been added?

Yes. Parse the CustCode to the CustCode field. But if ALL records are going into ONE record, you have a mess. I would suggest (if you can't map the import as records) that you import into a global and parse it into records. We need more information to help you with that.

Edited February 27, 2007 by Guest
Added more

[comment]

This has become a duplicate thread.

[LaRetta]

I have merged them.

Rich, please don't double post. You are new and new people do it - no biggie. But it is highly discouraged. :wink2:

[aldipalo]

Hi LaRetta:

Unfortunately, I started collecting this data about 20 years ago in the old Ashton-Tate dBase programming language. The original developer suggested this was the "BEST" way to make sure all records were unique. When I developed the FM database I had 22,000 companies and 15,000+ contacts to import from Approach. I didn't want to take the chance of losing my relationships by trying to change to a simple serial number field. In all honesty my clients don't move that often and even if they do I leave the key field alone.

But, if I were starting from scratch I would not do it this way. Some of the issues are that many of my clients have multiple divisions and those divisions have multiple sites so by joining the company name with the zip code it allows my an easy way to identify which location the contact is in. But, as I said I recognize the frailty of the system and would love suggestions as to how I could reliably restructure the data.

Al

[RPM765]

LaRetta, my apologies for cross-posting this question to the Import/Export forum. I thought I had posted it to the wrong forum. I'll stay here with it.

I have attached a zip file with a little data in it. In layout No. 999 the "Testing Layout" the IMPORT FIELD will show you what the raw imported data looks like.

This one sure has me stumped...

P.S. It appears that I have TWO User Profiles on this forum, RPM and RPM765. I do not know how that happened...I could swear I only registered once. If you want to delete one of them, that's fine.

RevenueCars.zip

Edited February 27, 2007 by Guest

[comment]

At first glance:

1 record in Customers:

CustName = "Total Waste Logistics - LAS"

CustCode = "TWLYOU"

7 records in Cars:

Freight Bill Code = "TWLYOU______" (6 trailing spaces)

That's not a match.

[comment]

I have merged them.

Ahem...

[comment]

suggestions as to how I could reliably restructure the data.

It's not that difficult - see here.

Be sure to back up first.

[LaRetta]

In the middle of my first merge attempt, FM Forums went into buggy mode. But it said it moved it. I looked and it was still there so I merged it again. It said it did! But now it appears to have crossed into cyber heaven. Maybe it will re-appear on its own. :

Sorry folks ... I do this quite a bit and it usually works well. :crazy2:

[RPM765]

1 record in Customers:

CustName = "Total Waste Logistics - LAS"

CustCode = "TWLYOU"

7 records in Cars:

Freight Bill Code = "TWLYOU______" (6 trailing spaces)

Well...isn't THAT interesting! I had not noticed the trailing spaces in the car data. I'll look into that corner of things and see if I can fix it.

Thanks, comment.

[RPM]

[color:red]BINGO! Problem Solved!

Comment, it was those 6 blank spaces in the cars table that were the culprit. I changed the field mapping code to write only 6 characters to the Cars::Key Field and instantly the relationships were OK.

[color:blue]I cannot thank you all enough!