10 Replies Latest reply on Apr 27, 2010 4:23 PM by philmodjunk

    Creating records from multiple databases

    ekloeselvp88

      Title

      Creating records from multiple databases

      Post

      I currently have one database called an event list  Within this database there are the fields..Summary, Date Start, Date End, Details Event Id.  Then I have several other databases...One being called Interviews(Intv00001), one being called Venue Shows(Ven0001) id's in parenthesis.  Each of these have the same fields except with the id being interview id and Show Id...

       

      What I am wanting is when I create an event in Interviews or Venue Shows that event gets added to The event list.  I know how to do it if I have one relationship in a database using id auto look up...but I can't figure out how to get it to auto enter if I have two different Databases using the same relationship.

       

      In Summary basically what I want is that if I add and event in Interviews it will automatically add all pertinent information into the events database in a new record, but at the same time if I add an event in the venue shows database I want it to add all that pertinent information into the events database in a new record as well

       

       

      I am not sure if I explained well

        • 1. Re: Creating records from multiple databases
          philmodjunk

          On your Events layout, you could add a portal to interviews and a portal to venue shows. Then you'd create a new event record and use the next blank row in your portal to create and complete the info pertinant to that interview or venue show.

           

          However, given that "Each of these have the same fields except with the id being interview id and Show Id", it's very likely that you could use just one table for both of these and that may simplify a few things--including the structure of some of your reports.

          • 2. Re: Creating records from multiple databases
            ekloeselvp88

            Is it possible to do it as above?  The only reason I ask is this.  I am using a calender.

             

            Currently I have it importing based on an event id....when it selects this event id it then auto enters information such as date to start, date to end, a summary of the event...and a couple of other things.

             

            The only way I can think of to have it tell if they are different events is to have them go into an event database with all the information i need.....or if i could find a way to have the calender import each of the events when created in their respective database and pull in all information neccessary for each of those in the events database within the calender that would be perfect.

             

            Am I being too confusing here?

            • 3. Re: Creating records from multiple databases
              philmodjunk

              I have no idea what you mean by "I am using a calender."

               

              Is this yet another table? Is it portal of date records, a list view? a table?

               

              The possible interpretations here seem endless.

              • 4. Re: Creating records from multiple databases
                ekloeselvp88

                The Calender is a datbase that contains a table that is titled Events. Events has the fields: id, date start and date end and then a summary.

                 

                Then Events is linked to a Month View Table by date start which tells it where to display the information on the calender. 

                 

                Then I have Venue events (another table) which each Venue has the fields: id, date start, date end, and summary.  Venue is in a relationship with events through id.

                 

                Then I have Interview Events (another table) which each Interview has the fields: id, date start, date end, and summary.  Interview would also be in a relationship with events through id. 

                 

                That way when I add an event through either Venue or Interview it would show up on the calender.

                 

                My problem is this.  I can get one or the other to work.  But not both.  I know why I just don't know how to fix it.  What I am doing now to get one of them to work is going through and doing a set field calculation of the id portion of the Event and then doing an auto look up for the dates and sumary.  This works great if I only wanted to add information from one table.

                 

                Problem is that I can't auto look up information for a field from two different tables ( I am assuming it is getting confused.) 

                 

                Hopefully I was a little more clear this time.

                • 5. Re: Creating records from multiple databases
                  philmodjunk

                  That's because your relationship can only point to one table and you have two.

                   

                  I'd put all your events and interviews records in a single table and add one more field to label each record as either an "interview" or an "event" record.

                  • 6. Re: Creating records from multiple databases
                    ekloeselvp88

                    The only problem I have with that Phil is that I do not want to have to enter the information twice....and if I put them all in an already created database then I will be trying to import too many different fields.

                     

                    This is the picture of one of my databases you can see there is a ton of information then there is another database here that also has a ton of information on it

                     

                    If I could find a way to send only selected fields from each of these databases to an event list that list without having to have two different date fields and two different summary fields (one for each database) that would be ideal.  I mean I guess I could look at doing some sort of import but I think that would not be effecient.

                     

                    Here are the two pictures of the databases Picture 2Picture 1

                    • 7. Re: Creating records from multiple databases
                      philmodjunk

                      I based my advice on the information you posted:

                       

                      Then I have Venue events (another table) which each Venue has the fields: id, date start, date end, and summary.   

                      Then I have Interview Events (another table) which each Interview has the fields: id, date start, date end, and summary. 

                       

                      You described the exact same fields for both tables and that's why I suggested using just one table.

                       

                      That approach may still work.

                       

                      You can define or (with fmp advanced) copy and paste the field definitions from one table to another until you have all the fields in one table. Then you'd import all the data from the other table (you'd only do this once.) You can use separate layouts for data entry but still be entering the data into the same table. Under no circumstances would you be entering the data twice.

                       

                      An altenative is to set up a small table that serves as a join between the two tables and your calendar. It sould store teh dates and have two link fields, one to venue shows and one to events to connect you to the details of each. Your summary fields would then summarize fields in this new table.

                      • 8. Re: Creating records from multiple databases
                        ekloeselvp88

                        Ok Phil,

                         

                        The problem I have with one table with all the information is that it doesn't make sense with the amount of information I have in each of the other tables it would be too much to consolidate them.  Not too much work but too much information combing together and to much going on between these tables already as well as other tables that I have not mentioned here.  I need to keep my interviews and venue dates in seperate databases.  If you look at the pictures above you can see part of the information that is in one of the datbases.

                         

                        So I guess my question would be if I was to use a joining table.  And I only wanted to have the table id, date start, date end, and summary field in this joining table.

                         

                        I don't understand how I can actively import from two different tables .

                         

                        One being the venue events and one being the interview events; both of them have id fields and  both of them have the same other fields that need to be imported i.e. date start, date end, summary...plus these tables have about 500-700 other fields that I won't need imported in each of them as well.

                         

                        What I need to happen is each time an event is added into either the venue event databse or the interview event databse it automatically adds those events and the date start, date end, summary fields into the joining table that corrosponds with the id's.

                         

                        Is this possible?

                         

                        Hopefully this clarified things a bit.

                         

                        Thanks

                         

                         

                        • 10. Re: Creating records from multiple databases
                          philmodjunk

                          Sorry, but my time to spend on the forum has been limited and is likely to remain so til the end of the week.

                           

                          "The problem I have with one table with all the information is that it doesn't make sense"

                          You have the full picture and what you want to do. I only have glimpses. From those glimpses---including your posted screen shots--I think it's still an option, but hey it's your database and merging the two tables isn't the only option here.

                           

                          "I don't understand how I can actively import from two different tables"

                          Huh? I don't follow that question--It's not something I recommended either.

                           

                          "500-700 other fields"

                          That is truly a lot of fields to have in one table. I could be wrong here, but generally, when you get that many fields in one table, it's time to take a look at your system and see if there isn't a way to restructure and simplify.

                           

                          "What I need to happen is each time an event is added into either the venue event databse or the interview event databse it automatically adds those events and the date start, date end, summary fields into the joining table that corrosponds with the id's."

                          You can use a script trigger to perform a script that updates a related record in this table when the record is committed or when a required field is exited.

                           

                          Your script might look like this:

                           

                          Set Variable [$DateStart ; Value: YourTable:: DateStart]

                          Set Variable [$DateEnd; Value: YourTable:: DateEnd]

                          Set Variable [$EventID; Value: YourTable:: EventID]

                          #continue for each field you want to do this with

                           

                          Go To Layout [//specify a layout for the other table]

                          New Record/Request

                          Set Field [JoinTable::EventID ; $EventID ]

                          Set Field [JoinTable:: DateStart ; $DateStart]

                           

                          and so forth

                           

                          Last line of script can be: Go to Layout [Original Layout]