3 Replies Latest reply on Feb 18, 2010 12:21 PM by philmodjunk

    Creating records via relationship for a touring band



      Creating records via relationship for a touring band


      I am a fairly new user to FMP and am using FMP 10 advanced.  I m currently building a solution to help me.  I am a tour manager for a band and am building a solution to keep track of information and to help speed me up.  I have several tables, I have a table named "Show Advance", one named "Media", "Interviews", "Venue Info"


      I am having a couple of major problems so far.


      I do all of my work in my "Show Advance"  I create daily updates in this table.  This table is linked to the various others a nd some of them linked to others.  Each of the fields have key fields with auto created serial numbers.  On the "Show advance" file there are two sections titled "Media1" and Media2" within each of these sections are fields titled "media1 address, media1 city, media1 state, etc" and "media2 address, media2 city, etc".  Basically what these two sections do is tell me who to call for each show we are playing.  I only have one table that stores all of the different media information, "media".  I want to be able to select from a key field and it auto look up each media outlet and place all of the information in its respected field.  There will be two different media outlets i.e. "media1 and media2"


      The problem is that when you select a particular media it puts the same address in both media1 and media2 because currently I am using the auto enter look up feature.  Is there another way to do this?  I thought about creating a two different tables "media1 and media2" and then using the look up feature.  but the problem I have then is when I enter create a record into Media1 I want it to auto create a new record in media2 and auto inter all of the information that I key into the fields in media1 into media 2 information so that I wouldn't have to key the same information in twice?


      The second problem I am having is related to the previous.


      I also have merchandise table as well...what I want to happen is everytime I create a new date in the "Show Advance" table I want it to create a new date in the merchandise table.  I currently have been importing new data with a script everytime it opens via key fields with serials and it is supposed to only import new records but i find it double importing dates sometimes so I was wondering if there was a way around this?


      Hopefully this is not too confusing and thanks for the help in advance.

        • 1. Re: Creating records via relationship for a touring band

          In filemaker, there are tables and table occurrences. Filemaker doesn't make consistent use of the two terms within the application or its help files and this can be confusing.


          Take a look at your relationship graph in Manage | Database | Relationship


          Each "box" on that graph represents a table occurrence--not a table. It can be confusing because filemaker automatically creates a table occurrence for each table you define in Manage | Database | Tables and gives it exactly the same name.


          What you need are two table occurrences for media that each link to different fields in your Show Advance table. Click on your Media table occurrence to select it, then click the button with two green plus signs to make a duplicate of this table occurrence. This new table occurrence refers to the same data source table: Media, but now you can have two different relationships linking this data to Show Advance.


          Set up your two relationships like this:

          Show Advance::Media1ID = Media::MediaID

          Show Advance::Media2ID = Media 2::MediaID


          Other changes you can make. You don't actually have to use looked up values to copy the information from the media table unless you really want a physical copy of this information that won't automatically change when you edit the data in the Media table. Instead, you can simply use the Field tool to add fields from Media and Media 2 to your layout to show the related information for that record when you select its ID number in Media1ID and Media2ID.


          There's also another approach that could work for you. You could display your media records in a portal. It takes a more sophisticated approach using either a Join table or a multi-value key to select the media records you want to see. The advantage here is that you have one defined relationship, but can display any number of linked media records in your portal.

          • 2. Re: Creating records via relationship for a touring band
               Thank you so much Phil, I can not tell you how much I appreciate the help here.  Do you mind if I ask you another related question?
            • 3. Re: Creating records via relationship for a touring band

              By all means ask away. :smileywink:


              If it's a new topic, you can start a new thread--sometimes that get's more responses from other folks.--otherwise, just add it on to this thread.