Pulling in data fm multiple databases

[dcecil]

I have 2 separate databases and I need to create a "combined" db that would represent both dbs together. I created a database where all of the fields are pulled from the 2 separate databases, using relationships. When I add a new record in one of the separate databases I got it to add the record in the combined database, but I only got it to work once, but I never could get it to work for both databases. The combined db has approx 70 fields and each one is relational (:field). Is there something I am missing?

Thanks,

[Jake Sterling]

Hi dcecil,

I am not sure I entirely understand what you are trying to do, but it sounds to me as if the problem is which file you are using to add data. If you have a "central" file that carries data from two others, it would make sense to use a layout in that file for data input. Also, don't forget, when creating the relationships to check of the boxes that allow for "creation of related records"! Otherwise Filemaker won't let you create any. once sweated for hours before I realized that it was as simple (and dumb) as that.

I think that if you added some details about what you are trying to do with this solution and someone will be able to point you in the right direction.

Cheers

Jake Sterling

[dcecil]

Okay, let me try again. I have to keep 2 separate databases with identical fields. When I need to locate an item, I have to query 2 databases. My idea is to create 1 database which will have only 2 fields. These will represent the "match" to each of the databases in the relationship. Then I created the desired layouts, pulling in fields from the 2 databases. That way, when I want to do a query, I can query both from the one database, using the different layouts. I will still use the separate databases for everyday maintenance and only use the "combined" database for queries (which are less frequent than daily maintenance), but I need the "combined" database to stay current to match the "real" ones. In the relationship window, I have tried all settings. Like I said in my original posting, I was successful 1 time in having it create a new record in the combined database when I created a record in one of the separate databases, but could not get it to do in both. Then, I must have changed what I did, because I could never get it to happen again. I hope this explains it a little better. I could send you a stripped down version of all 3 databases and hopefully, you couldsee what I'm trying to do. Let me know if this is alright to email you the databases. They will have only 1 record apiece.

Thanks,

[SlimJim]

Post your stripped-down version on the forum please. I may have had a similar problem maintaining separate contact databases (Customers, Suppliers) [edit] I was using FMP3 at the time, and still am! in this case.

[dcecil]

I would have to include all 3 databases for anyone to completely understand my problem and I don't think that is possible in these forums.

[Steve T.]

Howdy, d! This is going in a different direction than you were trying but may work for your just the same. When we wanted to query several fields from a single input, we created a calculation field that was equal to a concatenation of all other fields to be searched. I'm not sure about your situation, but maybe you could do something similar?

Good luck!

--ST

[dcecil]

Thanks Steve. Can you possibly give me an example of how you create the calculation that is equal to a concatenation of a field? I love FileMaker and can make it do great things, but I'm sure I don't do things the way that someone who is good at scripts and things can do. You do realize that I am displaying all fields from 2 separate databases. Will this still apply?

Dave

[The Mad Jammer]

dcecil

I think Steve T. is thinking something like this. Create a calculated field that calculates this

Field1 & Field2 & field3 etc..

The fields can reside in any of the databases but the calculated field will contain the concatenated value of each field included in the calculation.

For instance, if you wanted to put a full name together you could concatenate the fld_Title, fld_FirstName and fld_LastName fields together in a calcualted field named clc_FullName. the calculation would look like this

fld_Title & " " & fld_FirstName & " " & fld_LastName.

thus if fld_Title = "Mr." and fld_FirstName = "John" and fld_LastName = "Doe" the calculated field clc_FullName = "Mr. John Doe". By using the calculated field as your find criteria you would find this record.

Likewise if you concatenated fields from other databases together. By combining the values of those fields into one calculated field you can query on the calculated field to obtain the record you want in the database where the calc'ed field is defined. This record will also have all the correct links to the other database files as a result.

The fact you have the same field name on each file is not a problem since the field names would be prefixed by the file name in the calculated field, thus thisFile::field1 & thatfile::field1 are two different fields combined into one field.

I believe this is what Steve T. is referring to but maybe not. You should probably wait for Steve T. to elucidate further.

The Mad Jammer