Importing Multiple file

[JB2]

Hello,

I'm a Filemaker newbie but trying to learn as much as possible. I'm currently working with FM9 Advanced.

I'm looking for some help/instructions with importing multiple files into my DB. I'm trying to create a database for a project I'm working on. There are a lot of different excel sheets needed for this project (each group involved has their own with specific fields). Some of the fields are the same (phone number, user ID etc). I'm trying to use FM to import all the different files and use relationships to pull the data together to show update, changes and info needed for different parts of the project.

Here's an basic outline of the project. All help/suggestions are welcome as I'm very new at this but think FM will be the best solution given the number of people/files involved.

I will be the only one accessing the FM files, all other user work off of spreadsheets which is getting very cumbersome.

I have a DB with a list of about 800 people. All these people have specific phone number and a unique identifier (they are supposed to be unique but I'm finding some are not).

I need to import all the user information, then use the files from other people to update information on a given user.

I'm trying to use a few tables, one for each spreadsheet but some of the spreadsheets contain the same data (user names, phone number etc).

The users each need to have their info updated from each spreadsheet daily.

How can I import each of the spreadsheets and use relationships to show the necessary information for each user? I've tried using PK & FK for User & phone info but when I import into each table I can't view the updated info (for phone number/status info). It seems the 2 tables are still working independently even though I have the PK & FK's setup even using portals.

Table 1 - USER INFO (name, email, ID)

Table 2 - Phone/Status info (phone number's, ID, Status)

-Relationship - fk_user_info = pk_phone_info - One user may have multiple phones

After importing I can't view the information from the Phone/Status table through the relationship even in a portal.

If anyone is willing to take the time to help with this through IM or Email It would be very much appreciated.

Hopefully this makes sense? Any help/examples are very much appreciated.

Edited July 21, 2007 by Guest

[Fenton]

Short answer, the relationship is ID to ID. So they have the same values in either side. It's difficult to say from what you've written what your relationship is.

Short answer2: Everyone should use FileMaker. Users think it's easier to use spreadsheets, then let someone else sort out the mess they've created. That is because no one has designed a proper database for them. These kinds of distributed data systems (if they can even be called that) are a PITA.

"they are supposed to be unique but I'm finding some are not"

enough said :-|

[JB2]

Fenton,

Thank you for the answer and all the support.

How do I make sure they fK_phone_info pulls the info from pk_user_info? I've tried allowing "Creation and delete" through the relationships and a bunch of other things I though would help but still nothing.

When I import the Phone info after the User info is imported it doesn't recognize the relationship. If I put the fk_user_info on the Phone page its always empty so the tables don't seem to relate.

Is there a function/setting that I should use after both imports?

I agreed on the spreadsheets, it gets way too messy. Thats why I'm trying to use FM.

Thanks for the help!

[Fenton]

I can't really understand what you're asking. Basically you need to import the same unique ID into the User table and into the Phone table, then create a relationship between those 2 values, which would be the same. This is just basic relational logic. Look at them, in either FileMaker or Excel. Are any of the ID values the same in both tables?

You cannot "pull" or see info across a relationship unless the relationship is valid first. There is no magic.

[JB2]

The ID's they've given are the same for the most part (some have duplicate issues). So I wanted to use something like a serial number to generate a truly unique ID for the user on the import. I've done this on the USER info table, but not sure what needs to be done on the Phone info table? As it stands right now if I import the PHONES info into the table the FK from the USER table won't match as the PK is generated on the import of USER Info, not phones. Hopefully this makes sense? Sorry if this is very simple and I'm just making it difficult.

Basically I don't have anything that is truly unique to the USERS so I would need to generate this. When I import the PHONES info I need to be able to see the USER info in a portal - Should the relationship be based off the phone number? Then I would import the info into the separate tables but still have pk_users (serial, auto enter) & pk_phones (serial, auto enter) on the correct tables..

Maybe I'm confusing how I should use serial numbers because of I'm importing...

Edited July 21, 2007 by Guest

[Fenton]

OK, now we're getting somewhere. So, their User# is not really so good, so you want to create and use your own FileMaker auto-entered UserID (I reserve the "ID" suffix for real IDs; I'll use # in this case for their unreliable "ID"). This is commendable.

What you've got is a fairly typical problem, when people don't use a uniform system to gather information. Often all you've got are the people's name, which not reliable either.

What you can do is to match up on what you have the best you can, populate the user IDs in Phones the best you can, then review the results and fix problem records.

But, you have a serious problem. From what you've said so far, the User table has no phone#, and the Phone table has no user info, other than an unreliable ID# and a phone#. So, if you matched on the User#, how would you know if the match is correct or not? I don't see how you could, without at least user name info in Phones, which you did say anything about; you shoud say if you have it!

So what I'd do first is Import the Users. Create a UserID field, auto-enter serial number, [x] Allow calculations during import (2nd dialog). Then analyze what you've got. Do a Find for duplicates in the User name (put together the First & Last names in a calculation field if they are separate). Use the "!" character to Find duplicates. Sort by name. Look at the User#'s. The same person should have the same User#, but maybe they don't. You cannot delete anything at this point, because you don't know if they have phones linked by that ID.

I say just give the dupes the same FileMaker auto-enter UserID for now. At least they're tied.

Now Import the Phones. I don't really know if they need their own serial ID field, but go ahead if you want.

Link the Phones to Users on their User# (yeah, the old one). Show all records. Put you cursor in the UserID foreign key (which is empty at this point). Use the Replace Field Contents command (Records menu), by calculation, User table by User# relationship::UserID.

That will bring in the new real UserID from Users. You can now delete duplicate Users in the User table.

Back in Phones, do a Find for blank UserIDs, with "=". These phone#'s are orphans, useless unless you have user info to attempt to match them to someone.

And the Status in the Phone table. What's up with that?

[JB2]

Appreciate all the help!

This is beginning to make sense. I should have clarified this, all excel sheets I will be importing will have phone numbers. With this in mind I'm thinking I can base the relationship on the phone numbers then use portals to view the related data on each table as needed?

I think this will still show if a user has 2 numbers by the user showing up with 2 records if they have 2 numbers.

[Fenton]

When you say "all will have phone#s", you imply that you'll have multiple rows (records) for a single User, with possibly different Phone#'s. So, yes, you can use the phone#s in a relationship to attempt a match. But you really want to use these things as only temporary relationships, to try and get a real auto-entered serial UserID, from a single User entry in Users, into a user's Phone records. That's the goal.

[JB2]

Ok - I'm testing it out using your suggestions. I think I need to use the phone number initially, then once I've imported the data into different tables I think I can use "Replace Field Contents" for the PK's as needed. Once this is done I can change it back so the PK's can't be modified during data entry.

If what I'm thinking is correct this would give me the matching PK & FK's as needed then I could base the relationship of the P&FK's and this should do exactly what I want, I think?

I believe this is what you're saying when you mention only using these only as temporary relationships?

[Fenton]

Yes, pretty much. But the only real PK is the UserID in the User table. It can be Replaced into the Phone table, into a UserID foreign key, using a phone# temporary relationship. But do this only AFTER Finding duplicate Users, and manually giving them all the 1st one's UserID (unless there's really a lot, in which case ask). When you're all done you can then delete the duplicates. Ask again before you do that, as there are routines.