Jump to content

Import Records = No Relationships


RPM
 Share

This topic is 5699 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Guest
removed shouting
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. :wink2:

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 by Guest
Added more
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

[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!

Link to comment
Share on other sites

This topic is 5699 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.