7 Replies Latest reply on Dec 5, 2013 9:39 AM by philmodjunk

    How do I combine a similar field from two separate databases in a third?

    kleink

      Title

      How do I combine a similar field from two separate databases in a third?

      Post

           Hi,

           I have three databases that I would like to share the same information in one particular field, but I don't know the proper calculation.  The databases are People, Company, and an overall Master database.  There is a drop down field, "status," that is a text field in the People and Company database and I want that information to appear in the Master database in the same titled field, "status."  The value of status might be different between People and Company so that's why I'm thinking a calculation field is best because it could include a comma or something.

           At first I just wanted to copy the information from one database to the other with a look up but the value wouldn't appear so then I tried a calculation to include the information from the third similar to- Master Database, Status= If (Type=People, Status) "," (Type = Company, status)) but that of course didn't work.

           I'm usually good with scripts and this kind of thing but everything I try I get a "too few parameters" or similar error code.  Any help would be greatly appreciated!

            

        • 1. Re: How do I combine a similar field from two separate databases in a third?
          philmodjunk

               Three databases means three tables or three files?

               What is the relationship that  links master to the people and company tables? What are the match fields that you use?

          • 2. Re: How do I combine a similar field from two separate databases in a third?
            kleink

                 They are three files and are connected by a unique serial number assigned to each record and the type of record (person or company).

            • 3. Re: How do I combine a similar field from two separate databases in a third?
              philmodjunk

                   But which table gets the unique serial number that the other two match to?

                   Are these one to one relationships (One and only one record in one table matches to one and only one record in the other table) or are some one to many?

                   And exactly which table links to which? Do Company and Persons both link to Master?

              • 4. Re: How do I combine a similar field from two separate databases in a third?
                kleink

                     the Master is the one that generates the unique serial number. Yes, the serial number is unique to one record only so when I am in Master database and click new company a new serial number is generated with the type "company" that also appears in the Company database. There are three because the Master only needs pertinent information to my team while the other two carry specific information about the Person or Company. I want the status field to appear on the Master database but don't want two separate fields.

                • 5. Re: How do I combine a similar field from two separate databases in a third?
                  philmodjunk

                       That doesn't really answer all of my questions. Making some educated guesses, it would appear that you have these relationships.

                       People>------Master-----<Company

                       Master::__pkMasterID = People::_fkMasterID
                       Master::__pkMasterID = Company::_fkMasterID

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                       Please confirm whether or not that is correct. Chances are that the names are different, but please pay careful attention to the function of the match fields I am specifying and those you have.

                       If this is correct, there is a major issue with how you expect a status field in Master to evaluate. The problem, is that you could have many records in People and Many records in Company that all link to the same record in Master. Thus you could have 5 records in People with different values in their status field and 2 records in Company also with different values in their status field. But you want to somehow combine the data from all of these possible records into a single field and there is more than one way to combine that data...

                  • 6. Re: How do I combine a similar field from two separate databases in a third?
                    kleink

                         Sorry, I'm trying to answer your questions as best as possible.  I think I understand your notations and believe you are correct in the relationships but I'm not understanding the problem- they do not link to the same record in Masters.  The record number never repeats so record 001 is a Person and only appear in the People Database and Masters as a person, record 002 is a Company and appears in the Company database and Masters as a company, etc.  Record 002 does not appear in the People database- the type restricts that from happening.  So if I'm displaying record 001, a person, in the Master database, I want the value of status field from the People database but when I click over to the next record, 002, a company, I need that status from the Company database to populate the field.  I hope that makes sense.

                         I figured there were several ways to do this but the easiest please.

                    • 7. Re: How do I combine a similar field from two separate databases in a third?
                      philmodjunk

                           I'm sorry but I don't understand your description.

                           If you have a record with __pkMasterID = 1 in Master, you could, theoretically, have 500 records in people all with _fkMasterID = 1. That's because the value is uniquely generated in Master but copied to People every time you link a record in People to Master. This is also the case for the relationship that I guessed at for Master to company.

                           I don't know if this is the case for your tables and relationships or not.

                           Two ways you can help clarify:

                           Upload a screen shot of Manage | Database | Relationships cropped to just the Tutorial: What are Table Occurrences? for these three tables.

                           Write up a 2 paragraph or so "narrative" explaining how each of these tables are supposed function in terms of what you see on the screen. Explaining the purpose of this "status" field and the values that might be entered into it could be very helpful.