1 2 Previous Next 21 Replies Latest reply on Nov 23, 2016 8:22 AM by LoriE

    Relationships

    LoriE

      I have a projects.fmp file and a meeting.fmp file

       

       

      Files                                                      Tables                                                   Relationship

       

      1.Projects                                            Projects                                               Projects<---Meetings

                                                                                     

                                                                                     

                     

      2.Meetings                                         MeetingDetails                                 Minutes---->MeetingDetails<----Checlist             

                                                                      Minutes

                                                                      Checklist

       

      On the minutes layout I need to include the
      project number FK in order for the project number and name from the projects file to populate on my minutes layout in my meetings.fmp file.

       

      Why is this?

        • 1. Re: Relationships
          David Moyer

          Hi,

          what version of FM are you using?  Versions below 7, if I remember correctly, require separate files for separate tables.  I'm trying to get at why you have two separate files.  (This should help others help you.)

          • 2. Re: Relationships
            philmodjunk

            That field should not need to be on that layout unless you are using it on this layout to select the appropriate project for a given minutes record.

            • 3. Re: Relationships
              LoriE

              Filemaker pro 13. I was asked to develop a meetings file to go with the projects, contacts, communications, contract documents suite of files.

              • 4. Re: Relationships
                LoriE

                the minutes will be related to the meeting and the project. The files will remain separate and this is why I figured out a work around.  the field that I need have been put at the bottom of the layout in a section marked Do Not Use

                • 5. Re: Relationships
                  BruceRobertson

                  You are perhaps being a little loose with your terminology.

                  Do you actually mean a FILE? Or a table?

                  All the tables can be set up within a single file and  that is usually the best approach.

                  • 6. Re: Relationships
                    LoriE

                    I mean a file (or if you prefer we could call it a database)

                     

                    projects.fmp  (file, database)

                    meetings.fmp    

                    contacts.fmp

                    communication.fmp

                     

                    I did not develop the group of file but I am developing the meetings.fmp, database.

                    • 7. Re: Relationships
                      erolst

                      LoriE wrote:

                      I was asked to develop a meetings file to go with the projects, contacts, communications, contract documents suite of files.

                      I was asked to develop a NASA Mars Lander program. I said sure, why not, it can't be rocket science!

                       

                      btw, to indicate a one-to-many-relationship, try using One --< Many. Your arrows are somewhat confusing as they stand.

                       

                      And when you say "populate", do you mean "values appear in the related fields placed on the layout, since this child record now has a parent", or as in "a lookup is performed to copy data redundantly into local fields"?

                      • 8. Re: Relationships
                        LoriE

                        Ah, maybe I should use a lookup. I was placing a field from the projects file into the layout in the meetings file. when I say populate I mean that I want the information to show in that field. So on the minutes layout I have a field for project number. I have place the project number field on the minutes layout in meetings.fmp from the projects.fmp file

                         

                        Relationships:

                         

                        Minutes>----MeetingDetails----<Checlist 

                        • 9. Re: Relationships
                          philmodjunk

                          To repeat, it should not be necessary to do this just to make data from a related table appear on the layout. As long as the correct value has already been entered into the appropriate match field, simply placing the fields that you want for showing data from that table should be all that you need. You should not also need to add the match field itself--that would only be necessary if you are linking the current record to that related table on this layout.

                          • 10. Re: Relationships
                            erolst

                            LoriE wrote:

                             

                            Ah, maybe I should use a lookup. I was placing a field from the projects file into the layout in the meetings file. when I say populate I mean that I want the information to show in that field.

                            No, don't.

                             

                            In general, copying (and redundance*) is bad, referencing is good (unless it is bad; don't reference the global VAT field in your orders, or product prices in your line items, or you'll be in a world of trouble; copy them over to keep a snapshot in time).

                             

                            * At least in relational databases it is; AFAIK, it's different in, say, engineering.

                             

                            So it seems you're doing the right thing. If minutes is a child table to Projects, all you need is to enter the Project's primary key into the Meeting's foreign key for Project** - these two fields (and these only!) are your matchkeys for the relationship between the tables. If the relationship definition is correct, then a related field from Project should display the correct data.

                             

                            **There are far less cumbersome ways to create child records, e.g. portals.

                             

                            As for the terminology: File (.fmpxx) --< Tables --< Table Occurrences --< Layouts. You relate tables in theory, but in practice the relationships are defined between table occurrences; that allows you to have different relationships between the same tables (and do other interesting things).

                             

                            And a database is simply what you want it to be - one or more files with one or more tables that are used in conjunction.

                            • 11. Re: Relationships
                              LoriE

                              Thank you erolst,  I am still getting my mind wrapped around the whole relationship thing. What is AFAIK?

                               

                              The following is the relationship screen from my meetings file/database

                               

                               

                              Capture.JPG

                              • 12. Re: Relationships
                                erolst

                                LoriE wrote:

                                What is AFAIK?

                                As far as I know.

                                 

                                Well, the relationship looks OK, AFAICT. You've added Projects as an external data source and added a Table Occurrence. All very textbook.

                                 

                                If you place a Project field on a minutes layout, you should see the project name, being passed through the relationship with MeetingDetails. If that doesn't work, go bug-hunting.

                                 

                                First try to see if the Meeting-Project relationship works. Then do the same with Meeting-Minutes. If those work, then Minutes-Meetings-Projects should as well. If not, find the problem ... look at the field types, look at the data that's in those fields. Do you find what you expect?

                                 

                                I'd offer to take a look at the problem; can you condense the databases to a few sample records?

                                • 13. Re: Relationships
                                  LoriE

                                  I will give this a try. Will let this post know how it progresses.

                                   

                                  Thanks to all

                                  • 14. Re: Relationships
                                    LoriE

                                    Hi Erolst,

                                     

                                    Just to give you an overview of how a meeting comes about from the project file:

                                     

                                    From the project file there is a button “New Meeting” this
                                    button performs the script “New Meeting”:

                                    New Meeting
                                    Script(in projects file)

                                    Set Field [Project::CCAGlobal; Project::projected]

                                    If (IsEmpty (MeegingDetails::Project_IdFk)

                                         Perform Script
                                    (“New Record from file: “Meetings”

                                    Else

                                        OpenFile[“Meetings”]

                                        Go to related
                                    record[showonly rlated record; from table: “meetingdetails”;External; Using
                                    layout: “MeetingsLog” (meetingdetails)]

                                     

                                    So from the projects database:you are in for example project number
                                    1303 and you want to create a meeting and subsequently the minutes to that
                                    meeting. You would press the meetings button and it there are meetings already
                                    in the meeting database it will bring you to a log of all the meetings…..1303  mtg 1, 1303 mtg 2, etc

                                    If there is no meeting it will bring up a dialog box
                                    asking if you want to create a first meeting.

                                     

                                    New Record
                                    Script(in meetings file)

                                    Enter Browse Mode

                                    Go to layout [“MeetingDetails” (MeetingDetails)]

                                    Show custom Dialog “New file…There is no meetings file
                                    started for this project. Would you like to creat one?”)

                                    If {Get (lastmessageChoice) =1

                                       New
                                    record/request

                                       Set field
                                    (Meetingdetails::Project_IdFk; Porject ::CCA Global]

                                       Commit records/requests
                                    [skip data entry validation;no dialog]

                                       Select window[Name:”Meetings”]

                                    Else

                                       Exit script

                                    End If

                                     

                                    These are scripts from the CCA Suite of files (Projects,
                                    Contacts, Communications, CADocuments) that I have copied and modified in order
                                    to model after the existing databases structure.

                                     

                                    And remember, I am the receptionist here, not a
                                    developer. And I love doing this but at the same time it is getting stressful  because I want it to be set up with integrity.

                                    1 2 Previous Next