getting records from a master database

[hillj888]

i work at a nonprofit and have downloaded a filemaker database that is used to track donations. At our company there is a Master List of contacts that is updated by one person in the company. I would like to use the templates from the downloaded database for all our donation tracking information but would like to access the names and addresses from the company's Master List. the Master List has about 10,000 records each with a unique identifier number. i have created the field for the unique id in my donations database and set it up as the matching field but what i don't get is how to get my donations database to grab all 10,000 of those records, and add the new records as they are created in the Master List day by day. Do i have to type (or auto-enter) each ID number myself before i can grab all the records? I feel like it should be able to be done with lookup fields but i'm new to those so i'm not sure. Any help you can give would be greatly appreciated!! thanks!

[ESpringer]

If you have a network connection to the main database (and it's also an .fp5 file), I don't see why you'd need to "grab" all those records at all. If that existing file is a master contacts file, and yours is a donations file, you just need to track minimal fields in your file: ID#, date, amt., how-paid, etc. All of the rest of the data from the other file can be seen via related fields on your layout (address, name, etc.).

If you want to "freeze" certain info in the donations file (such as address, so that the donation record shows the old address even after the master file updates an address; and perhaps "name" just as a reality-check), then you'd want to do lookups on those fields.

You can redirect the fields in your donations template layouts to display the *related* data from the master contacts file. I'm not sure what your level of familiarity is... ask more questions as needed.

[ESpringer]

Ah, you specifically mention that you're new to lookup fields... OK.

Suppose your donation comes in and it has a name on it (but not the key ID). Then, you need to have a second relationship between the files for finding the ID based on the name.

You want to type into a last name field and a first name field, and then have FM calculate a concatenated lastfirst field (perhaps with a comma and space between: LastName&", "&FirstName ). The Master list needs to have a similar field in it for concatenated names, and you'd build your name relation on this concatenated field. (Really, it should be *not* a calc, but a real text field on the other side, for indexing purposes.) Call this relation Master_Name_Match or something like that.

THEN, you want to have a value list created in your donations file. The value list should be "IDs for this name". I say IDs plural, because you could have two people with the same name! Have the value list show RELATED values only. Display values from the ID field, but ALSO SHOW values from the Address field (or something else that will help you distinguish same-name people)

Then, after you enter last and first name fields, you can have the ID field display a popup of ID values from the RELATED record(s) in the master file. Usually there will be just one.

Now, fields in the donations file that depend on this ID, such as Address, can be defined in multiple ways. If you define it as a CALC field returning the text from the related field Master_ID_Match::Address, then it will change when the master address is updated. If you define it as a LOOKUP field and have it look for the related info across the Master_ID_Match connection, then the data will be written in and stored independently with each donation record.

Oops, must run, but will post without further editing in case that's helpful! Ask more!

[hillj888]

thanks! well, my level of familiarity is not great---i've worked mostly with access (ugh). i didn't mean literally grab the records, like, import them, if that's what you mean. i do just want my donations dbase to be able to "see" the master list. but what i'm stumped on is, in order to do that, i have to have the primary key set up right? and i'm guessing that means that i have to have one serial number in my database that matches the serial number in the master list. which means i have to auto-enter 10, 000 serial number in my database? is that right? and then, if that's the case, how do i refresh my database, every time the master database gets more records? and if we get a contact name that we want in our database, we would have to wait till it was entered in the master dbase first right, and then find out what serial number it is, enter that serial number into our donations database and then it would "see" that new record in the Master List. am i understanding this correctly? i feel like there must be some more automated way to get my database to see every record that is in the Master List, all the time. what am i missing?

[ESpringer]

No, you don't have to enter batches of serial numbers (unless you're updating an existing batch of donations, which could be done with the name-match value list technique I described above). What you want to do should be very friendly for FM. If this donations file can maintain a relationship with the other, you don't need any serial numbers in it *except* as they connect to any given donation. You don't WANT all the serial numbers, anyway. After all, some contacts never make donations, and some contacts make multiple donations. You just need to know, in the case of each actual donation, what the right serial number is for this donation, and then the data in that field serves as key. Once you know the ID for a given donation, all of the other data should be free-flowing.

Of course, the best thing would be getting the incoming donations data to show up with the ID number already on each one. But assuming the donation comes in with only a name, you need to find the corresponding ID if there is one, or generate one if the person is not already in the contacts list.

OK, on the FIRST case (the master record already exists though you don't know its serial number in advance):

I now think I was wrong to suggest that you enter text into the first AND last name fields in order to access a related value list. I think instead you should have a GLOBAL field to hold just the last name of the person who donated, and a scrollable portal into the master contacts file showing all records with that last name. Build a relationship between the global field in donations and the last name field in the contacts file. (The reason I think you should work only with the last name as a match is that variations on the first name can create a real nuissance for a name match.) Now, on your data entry layout, include the global field, and below or to the right include a portal to all the existing master contact records which match the global last name. The portal should show not only the first and last name fields for the contact, but any other info that is crucial for distinguishing similar names, AND the ID for that person. Then, you could make a button to COPY that ID value from the portal row and create a new donations record, pasting that ID into the key field (making sure that you're not overwriting an existing ID by accident). The button could say "Create new donation record for this contact" or some shorthand for that.

Then, you can access every other bit of info about that person via the relationship. There are three ways (you might choose some combination):

(a) simply display the related field from the master contacts file. (When you "specify field" on the layout, use the dropdown menu at the top to access fields from the other file.) Note, if you do this, then if you *edit* the data in these fields, you will be editing the contacts file!

(: use a calc field to "parrot" whatever the master contact file shows. (Just define a field to be equivalent to the corresponding field in the mostar contacs file.)

© use a text field to store the data for name, address, etc., right there in the donations file, but access each field with a Lookup. (After you define the field as a text field, go into the options for "auto-enter" and choose Lookup, and specify it so that it fetches data from the matching record in the master contacts file. Sorry I don't remember whether there are any significant differences for FM5; I think it'll be straightforward.)

OK, in the SECOND case (the master file does not contain a record for this individual):

You need to enable your relationship (between ID in this file and ID in the master file) to "Allow creation of related records" -- this is an option when you define the relationship. Now, if you type a last name into your global field, and the portal shows no records, or shows records only of OTHER folks with this last name, then you would just go ahead and create a new master contacts record *from* this file. There are various ways to do this:

(a) If your donations file is following option (a) above -- simply displaying the name and address fields from the related contacts file -- then you can just go ahead and *type into* these fields, and you're effectively typing into the master contacts file, creating a new record. If your master contacts file auto-assigns IDs, you'll find that FM knows how to auto-assign an ID to the new contact, AND put that ID# in the donation record (since you're telling it that you're making a new *related* record. (At least, that's the way it works in FM7).

(: If you have a global name check relationship to the master contacs file, then a portal on that relationship should allow you to enter a new row and create a record by typing into that row. However, you'd only be able to enter the new contact info to the extent that the fileds are in the portal (or by having a scripted button that takes you to the new related record, so that you can create a new contact properly). Then you'd go ahead, once the contact is set up, and get its ID onto the donation record.

© If you're doing lookups, you'd proceed much as with calc fields: either enter the new contact's info in a portal row, or have a script take you over to the master contacts file where you could create a new record.

Let us know how it goes...