2 Replies Latest reply on May 21, 2012 1:53 PM by CodyRomphf

    How to associate a custom amount of records with an individual record of another field?

    CodyRomphf

      Title

      How to associate a custom amount of records with an individual record of another field?

      Post

      Hi,

      I am new to FileMaker pro and have a problem that may be already answered on this forum but I was unable to find anything that made sense to me. So I apologize in advance if this problem has been solved in the past.

      I am trying to create two seperate layouts, one that is a series of items, and the other that allows me to copy and paste information and have it associate with the specific record on the first layout. So say you had a series of essay titles as records all displayed on the first layout, I want to be able to press a button that takes you to the second layout where you could copy and past information. The trick I am running into is that if I want to copy and paste several entries they will not associate with the specific essay title.

      So far I have set up that you can press a button that takes you to the second layout and allows you to copy and paste said information into the database. But when I attempt to create a new record (2 items connected to one essay title) it does not associate with my original item. If it makes more sense what I want is an essay title as record 1. Then seperate pieces connected as 1a, 1b, 1c...etc. Not 1, 2, 3, 4.

      If anyone has any suggestions at all it would be greatly appreciated. Or if a more clear description is required/more information please let me know and I will provide what is required.

       

      Thanks in advance.

      Cody

        • 1. Re: How to associate a custom amount of records with an individual record of another field?
          GuyStevens

          This is pretty straightforward. And it requires a simple relationship between two tables.

          Let's say your first table is your essay table.This would require the following fields:

          Essay
          Id  -  A number field set to auto enter a serial number.
          A Title, name or description
          ...

          Your second table. Let's call it Documents would look something like:

          Documents
          Id  -  A number field set to auto enter a serial number.
          EssayIdFk  -  This is what's called the Foreign key. This is where you store the ID number of the essay you want it to be connected to.
          DocumentTitle
          ...

           

          So the trick is that you create a bunch of essays in your essays table. And that you enter the essay number in the EssayIdFk field of the Documents table.

          So a button on the Essay table layout would look like this:
          "Create new document linked to this essay"

          - Set Variable [$EssayId; Essay::Id]  - This means that the name of the variable is "$Essay" and the value is the ID number of the essay record you are currently on.
          - Go To Layout  -  The layout based on the Documents table
          - Set Field  -  Now you set the Variable you just created in the EssayIdFk field.  [Documents::EssayIdFk;$EssayId]

          The only trick is that when you are in a documents record and you want to create another document related to the same Essay you need to create anuther button "Add new document based on the same essay as this document".

          And that script would look like:

          Set Variable: [$EssayId ; Documents::EssayIdFk]
          New Record / Request
          Set Field: [Documents::EssayIdFk ; $EssayId]

          An alternative is that you create a portal on your Essay layout and enter documents in that portal, that way you don't need to set the EssayIdFk number, it will be set automatically.

          The only thing you do need to do there is to make sure that your in your relationship settings you "Allow the creation of records via this relationship" on the side of the Documents table.

          I hope this makes sense.

          Another option is that, on the documents layout you set the EssayIdFk field, and you make a dropdown list out of it so you can choose which essay this document belongs to.

          For an example of that, check out this little example file:

          http://dl.dropbox.com/u/18099008/Demo_Files/TwoTablesLinked.fp7

          • 2. Re: How to associate a custom amount of records with an individual record of another field?
            CodyRomphf

            Thank you very much for the detailed explanation and the step by step instructions. I really appreciate the help, it will allow me to make the database much more friendly. So thank you again for the post.

             

            Cody