2 Replies Latest reply on Oct 12, 2010 4:17 AM by DannyFoster

    Missing a huge piece of the puzzle!

    DannyFoster

      Title

      Missing a huge piece of the puzzle!

      Post

      Hi Everyone,

      After hours of searching I'm begging for some help here. Many years ago I built databases using MySQL and recently I tried to get back into it but I was lost. I decided to try out Filemaker and building the tables and forms has been pretty straight forward. However, I'm not getting the relationships at all!

      I have a table called 'people' and a table called 'roles'. I've also got a table called 'assignments' where I would like to give various roles to people and vice versa. I can't figure out how to do it! It seems that this should be very simple.

      In my searching I found this link to a document entitled White Paper for FMP Novices. In it is a section called "Record Editing in Portals". This looks exactly like what I want to accomplish. Here is an excerpt:

      "How then are you supposed to create portal records? Simple. Create an additional relationship to the table in question. Make it a one-to-one relationship using a global field as the primary key and the existing ID of the related re- cord as the foreign key. Put fields from that relationship on a platform on the layout, along with a New Record button. Attach this button to a script that creates a new record in the related table, grabs the ID number of the new record and inserts it into the new global primary key field. Also put a button over the portal row that sets the global primary key field to that record’s ID number, so that existing portal records may be edited in the fields on the platform. You’ll have to make certain your scripts empty this global field when you change records in the primary table. This is only a few minutes extra work and makes for a far more professional looking interface."

      Simple? Yeah right! Is there anyone out there who can point me to a tutorial on how to do this? Your help would be greatly appreciated.

        • 1. Re: Missing a huge piece of the puzzle!
          FentonJones

          I believe that paragraph describes an intermediate technique to create portal (related) records. It is not necessary, and, in my opinion, does not really belong in a paper for "novices". There are easier ways.

          If the portal is not sorted, and the relationship is simple, just turn on [x] Allow creation of related records in the relationship.

          If the portal is sorted, use something like his technique. Capture the ID of the parent record (say in a script Variable), go to any layout of the portal's table, create a record, set its parent ID to your variable, return to original layout, go to field of portal. The newly created record would be the 1st, as whatever field you're sorting by would either be the latest (if sorted descending by timestamp) or blank (which sorts to the top).

          People & Roles are "parent" tables; Assignments is a "join" table. This little example file is a generic join example, without a sort, just simple [x] Allow creation in the relationship.

          Join.zip

          • 2. Re: Missing a huge piece of the puzzle!
            DannyFoster

            Thanks Fenton. Your sample was very helpful. I've got it working now. I guess it's just a matter of getting my head around doing all this 'programming' through a GUI. I was working from the concept of INSERT INTO.... WHERE... but that doesn't seem to be how this is structured.

            I'm still only seeing numbers (primary keys) in my portal though. I'm using value lists based on other tables and have indicated that it should only show the second value but this seems only to work when accessing the DB through a browser on the intranet... not when using it directly in FMP.