6 Replies Latest reply on Sep 16, 2009 12:35 PM by Algo

    Importing records problem



      Importing records problem


      I'm not sure how to get this working in Filemaker Pro 9, but here is what I am trying to accomplish.


      Database 1:  Group Table.  Has groupID as the primary key and a field to store an agentID.  Names, etc.

      Database 2:  Agent Table.  Has agentID as the primary key.  Names, etc.


      I'm trying to get the agentID to import into Database 1 where the names match.  I can export the names from both databases and then match the groupID with the agentID in excel and import that back in, but even still it only asks me to update/replace data.  I wiped all the data in Database 1 on my first attemp.  Luckily I backed it up!



        • 1. Re: Importing records problem

          Still haven't found an answer, hoping someone here can shed light on the issue.



          • 2. Re: Importing records problem
               I've read your post a time or two and can't quite picture what you are trying to do and why. Perhaps you could elaborate a bit?
            • 3. Re: Importing records problem

              We have 2 databases.  A group database, and an agent database.  Both have records already (I inhereted these, did not create them).  Here's what each has.


              Agent Database:  It has records of the agents we have had some contact with in the past.  I added an agentID primary key.

              Group Database:  It has a record of which groups we do business with, however it also has information on which agent we used for that group.  I added a groupID primary key.


              Now that's basically what we had, except for the primary keys I added.  Here is what I am trying to do.  I want the group database to pull some information from the agent database depending on which agent is with that group.  A group ALWAYS has an agent.  An agent MAY or may not have a group.  So I exported some agent information from both databases and compared them in excel.  I added an agentID field to the Group database and right now it is empty.  I would like to import that information back into the group database.


              I have in excel:


              groupID matched with agentID (except for a few hundred).


              once I have the agentID in the group database I can just pull from the agent database and add a button to open the agent record in the agent database.


              I hope that clarifies it some.  Basically, just need the right agentID in the group database. 

              • 4. Re: Importing records problem

                Thanks! That does clear things up. You are linking two previously unrelated tables by AgentID. You probably could have done this all with Filemaker, but since you've already done this in an Excel spreadsheet, you need to import just the AgentID value into the Group table matching the values in the Excel file up with records in Group that have the same GroupID right?


                Do this on a copy of your file:

                When you select Import Records, you can specify a "Match field" that aligns your GroupID field in Group with the GroupID column in your spreadsheet. You can then disable the import of data from any of the other columns.

                Select a layout that refers to Groups.

                Find all records.

                Select Import Records.

                Select your excel file and make the following selections in the Import Field Mapping dialog:

                Select Update Matching Records in Found Set.

                Find the column in the right hand list that contains your GroupID field. Drag it up or down to align it with the matching GroupID value in the spread sheet. Click it to change the arrow into an =.

                Click the arrows between any other fields until they show as simple lines (no arrow, no equals sign). Click Import and inspect the results to make sure that it worked.

                • 5. Re: Importing records problem

                  So, you have both the IDs in an Excel file, already matched as you want? The other data is irrelevant at that point. There are at least a couple methods you could use, one of which you tried, but likely incorrectly. 


                  But first, is it a permanent business rule that a Group has 1 and only 1 Agent? Because if you ever need multiple agents per group, now is the time to create a "join" table. To do so you would Import that Excel data, using the option to "create new table" with it (at the bottom of the target table choices). Then, on the Relationship Graph, put it between your parent tables, with a relationship from each, based on its primary key field.


                  However if 1 and only 1 agent per group, you should put the Agent ID into the Group table. The method you tried would work. You could Import the Excel data, with [x] Matching records, based on the GroupID field (on both sides). Click on its -> (or -) arrow to turn it to an equals sign. Line up the AgentID field (column) to import (->). Uncheck [ ] New records. Click the [x] Don't import first record, has names (lower left), in order to see the header row names from Excel. 


                  Alternatively you could open the Excel file with FileMaker. Then, from your main file, Group table, create a relationship between the files, based on GroupID. Then, Show All Records, put your cursor in the AgentID field in Group, and Replace, via calculation, that relationship, the AgentID field.


                  In all cases, backup first. 

                  • 6. Re: Importing records problem

                    Insurance is a weird business.  So many people getting different levels of commission.  And this is only the voluntary health insurance I am having to deal with! But yes, basically agents are given a territory based on whatever criteria someone (company, insurance carrier, etc.) assigns, that I do not know or care about.


                    Is it possible for a company to have more than one agent?  I suppose.  Different agents are always wanting to sell different products to different companies.  However I was told we have 1 agent per company/group, so that is how I have set it up.  I do feel that is unrealistic as I noticed many of the groups repeating several times in the group database.  The join is something I will look into further down the road as I just want to get this thing workin properly first.