6 Replies Latest reply on Dec 30, 2008 10:42 AM by raybaudi

    Beginning Filmmaker Pro user requesting help building a database with a portal

    amanda

      Title

      Beginning Filmmaker Pro user requesting help building a database with a portal

      Post

      Dear FileMaker Pro Community,

      I am new to FileMaker Pro and I am writing because I need a little bit of help creating a database I have envisioned. The database I am trying to create is for my own personal use, an extremely simple, stripped down contact manager. I do not want or need any complicated advanced features- what I am trying to build is something simple, elegant, pure and easy to use. I know that compared to what the FileMaker Pro Community is busy developing, the database I'm making is laughably simple. However, for me, it is still quite a challenge to build.

      I will now describe in greater detail what I'm building. I have of course already created the CONTACTS table and its fields, but I don't know how to relate the CONTACTS table to the CORRESPONDENCE table, and I cannot figure out how to create a portal in the CONTACTS table in order to view and edit related records from the CORRESPONDENCE table. But to start from the beginning- here are details on the two tables in my database.

      CONTACTS table
      This table contains 7 fields:
      1) First name
      2) Last name
      3) email address
      4) phone number
      5) Notes field A (in this field I would write, for instance, "I met Susan and John Bates and their children at the Smithsonian when I was visiting Washington D.C. in 2007." I will use this field for conducting keyword searches; for instance, if, in 2009, I wanted to reconnect with the Bates family, but I forgot their names, I could search the database for such keywords as "Washington," or "Smithsonian," and I would be able to bring up the record that way)
      6) Notes field B (in this field I would enter any additional information on this person, such as a postal mailing address, or a second email address, or a second phone number, or any additional notes)
      7) Date of creation.

      CORRESPONDENCE table
      This table would contain 2 fields:
      1) Correspondence (for pasting in a text email message, just text, no graphics).
      2) Date of creation.

      FUNCTIONALITY
      I would like this database to work in the following way. When I look at the database I see, on the top of the page, the 7 fields of the CONTACTS table. But in the middle and bottom of the page I see the related records in the related CORRESPONDENCE table. So, for instance, let's say I'm looking at the record for Susan Bates. I can see that on July 12th, I pasted in an email message which Susan sent me in which Susan tells me that she and her family are planning a trip to Massachusetts in November. I can also see that on July 15th, I pasted in an email message that I wrote back to Susan in which I told her the exact dates that my family would be heading to Massachusetts, and when we could meet. So basically, all the related records from the CORRESPONDENCE table appear in a window here in the CONTACTS table- I think this is called a portal- and from the CONTACTS table, I can view and edit the CORRESPONDENCE table's related records. By viewing, I mean that I would like to be able to scroll through each of the various related records, with the most recent record appearing first. By editing, I mean that I would like to be able to create, from where I am here in the CONTACTS table, a new CORRESPONDENCE table related record and type or paste text into it- i.e., a button that says "enter a new correspondence letter." I would also like to be able to delete an existing CORRESPONDENCE table related record if necessary. What is important tome is that I am able to do this viewing and editing of related records from within the CONTACTS table!

      Again, I know that for developers and technical people, my database is something simple that could be built in no time, but please remember that I am not a technical person and that I am doing my best to figure this out without a technical background. Your help would be greatly, greatly appreciated.

      Thank you very much,

      Amanda Spring
      amanda.spring2@gmail.com













        • 1. Re: Beginning Filmmaker Pro user requesting help building a database with a portal
          TSGal

          Amanda:

           

          Thank you for your post.

           

          Your database is very doable, so hopefully, my instructions will be clear enough.

           

          In order to match the correct Correspondence to the Contact, we need some sort of "key" field.  This field doesn't need to be visible, but it is essential.  Therefore, in both the CONTACTS and CORRESPONDENCE tables, create a new field "ID".

           

          While still in "Manage -> Database", click on the Relationships tab.  This now displays a graphical representation of your two tables.  Click on the ID field in either CONTACTS or CORRESPONDENCE, and drag it over to the ID field in the other table and let go.  A line now connects the two tables together, and ID appears at the top of both tables.

           

          In the middle of that line connecting the two tables is an icon.  Click on that, and an "Edit Relationship" dialog box appears.  At the bottom of that dialog box, check the option on both sides to "Allow creation of records in this table via this relationship".  This will allow you to add a Correspondence from your CONTACTS table (and vice versa, if needed).

           

          Since you already have existing CONTACTS, you will need to assign each contact a unique ID.

           

          Pull down the View menu and select "Layout Mode", and go to the layout that shows your CONTACTS information.  On the left side of the screen are several tools.  Below the oval tool is the portal tool.  This allows you to see records from another table.  In your instance, the CORRESPONDENCE table.  Draw a rectangle with this tool, starting in the top left corner and dragging to the bottom right corner and let go.  A "Portal setup" dialog box appears.  For "Show related records from" pop-up, select CORRESPONDENCE, and then click OK.  Another dialog box appears, and select only the fields you want to see.  That is, Date of creation and Correspondence.  There is no need to show the ID field.  Click OK.

           

          Now, return to Browse Mode (View menu), go to a particular Contact, and start entering information into the portal.  That record will then be added to the CORRESPONDENCE table, and the ID will automatically be added, too.  The next time you revisit that record, you will see the record you added.

           

          I know there is a lot of information here, and I don't expect you to grasp it immediately, so take it slowly.  If there is something I missed, or if you need clarification for anything I covered, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Beginning Filmmaker Pro user requesting help building a database with a portal
            amanda
              

            Dear TSGal,

             

            Thank you very much for your help.  I have now built a database with 1 file, two tables in that one file, a relation between the two tables, and a portal on the main layout, Contacts, showing the related records from the second table.  It's very simple, but it's been so exciting for me that I want to tell people, "if I can do it, you can do it too."  I am really enjoying this program.

             

            I have one more follow up question to ask.  I cannot for the life of me figure out how to create a button that creates a new related record in the portal.  I know that if I scroll down, past the existing related records in the portal, I can simply start typing in the empty related record field on the very bottom- typing into the box automatically creates a related record.  But what I really want is this: a single button- a "create a new related record" button- which, when I click it, does 2 things: (1) a new related record is created in the portal; and (2) this new, empty, related record, which is waiting for me to type into it, appears on the TOP of the portal, so I don't have to scroll all the way down past all the existing related records in order to enter data into the blank portal record on the very bottom.  I just want to click a "create a new related record" button so that the new related record appears on the TOP of the portal, empty and ready for me to start entering data into it.

            This seems simple and maybe it is, but for the life of me I have been unable to figure out how to do it.  I tried my best to figure it out BEFORE asking you again for your help- I really mean it!  If you could let me know how to proceed on the "create a new related record" button, I would genuinely appreciate it.  I know that this is a simple database but it's exactly what I want- something very simple that's easy to use.

            Thanking you,


            Amanda


            • 3. Re: Beginning Filmmaker Pro user requesting help building a database with a portal
              TSGal

              amanda:

               

              I'm glad my reply was helpful!

               

              It sounds like you have your relationship between the two tables set up properly, and we'll make a few more changes.

               

              One of the options in a portal is to sort the records.  Therefore, we want to add a record and have it appear at the top for easy entry.  Here is how it is done....

               

              Return to "Manage -> Database" and click on the Relationships tab.  Between the two linked tables, double-click on the icon, and the "Edit Relationship" dialog box appears.  At the bottom, where you have checked the option to allow creation of records, also check the option to "Sort records", and sort the records by a field (other than the ID key field) that will always have data.  That is, if a record is added, we want it to be listed first in the portal.  Once that is done, we are ready to create a script.

               

              Pull down the Scripts menu and select "ScriptMaker".  Click on the bottom left icon (New script), and a new dialog box appears.  On the left side are the commands that can be used, and we'll move some of those commands over to the right side.

               

              At the top of the dialog box, change the Script Name from "New Script" to "Add Portal Record" or some name that makes sense to you.

               

              Along the left side, find "Enter Browse Mode" and double-click it.  You will now see it appear on the right side of the dialog box.

               

              Next, find "Set Variable" and double-click it.  In the lower right portion, click the "Specify... " button.  A new dialog box appears, with a few options.  Fill in the following:

               

              Name:    $ID

              Value:    <Your key field>

                  (To the right, click on Specify, and then select the key ID field from the current table; not the related table)

               

              Click "OK".

               

              I won't go through every step, but your script should end up looking similar to the following:

               

              Enter Browse Mode []

              Set Variable [$ID; Value: <main table:: key ID field> ]

              Go to Layout [ <layout name where your related table fields are displayed> ]

              New Record/Request

              Set Field [ <related table:: key ID field> ; $ID ]

              Go to Layout [ <back to original layout where you want the record to appear first in the portal> ]

              Go to Field [ <related table:: first field displayed in portal ]

               

              ---------------

               

              In short, this grabs the key ID field and puts it into a variable $ID.  We then switch to the layout of your related table and add a new record.  We set the key ID field equal to the variable $ID, so we now have a blank record link between both tables.  Then, we return to the original layout, and put our cursor in the first field in the portal.  Since we are sorted, that will be the blank record we just added.

               

              Does that make sense?

               

              Try this out, and if you run into any difficulty, let me know.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Beginning Filmmaker Pro user requesting help building a database with a portal
                raybaudi
                  

                Hi TSGal

                 

                "... also check the option to "Sort records", and sort the records by a field (other than the ID key field) that will always have data.  That is, if a record is added, we want it to be listed first in the portal."

                 

                This point can't be clear to amanda ...

                 

                Sorting by an indefinited not empty field doesn't give the security that the new record will be the first of the portal !

                 

                Amanda has to create a new field in the related table that auto-enters the creation timestamp and sort ( descending ) by that field the records of the portal.

                • 5. Re: Beginning Filmmaker Pro user requesting help building a database with a portal
                  amanda
                     Dear Daniele and FileMaker Forum,

                  I have almost finished creating my very first database, but I am currently at a roadblock which I cannot get past.  I am having trouble passing data into a variable, and performing a search using that passed data.  I have tried my best- I just don't know what I am doing wrong.

                  My database has 2 tables: one table is called "Contacts," and the second table is called "Letters."  Each Contact record represents a person; each Contact can have multiple "Letters" associated with him or her.  A key field in the Contacts table, "kp_Contacts_ID", acts as the primary key.  In the related table, "Letters," there is a foreign key field called kf_Contact_ID."  This is how the two tables are related.

                  As I said, each "Contact" record can be related to many "Letters" records.  I have created a portal in the Contact table that shows me the related records from the Letters table.  I also created a script that allows me, from within the Contact table's portal, to create a related record in the Letters table.  For me, creating this functionality took a great deal of time- I am a beginner at FileMaker Pro.  Anyway, so far this works.

                  I am now however trying to add two new scripts.  It is these two new scripts that I am having trouble with.

                  In the Contacts table, there is a button that is marked "xxxxxxxxxxx."  Basically, what I want is that when I click the xxxxxxxxx button, that I go to a layout where only that particular letter record is displayed.  I already created the layout- it's called Letters.  It displays just one record.  What's important to me is that each record in the Contacts portal has its very own xxxxxxxxxx button, so that whichever particular record interests me, I can click that record's corresponding xxxxxxxxxx button and thereby go to the Letters layout and see that record alone, up close.

                  The script I created, which doesn't work, is called "this_ONE_letter_big." The "this_ONE_letter_big" script corresponds to the xxxxxxxxx button.  In the script I am trying to take the value of the letter's Letter_ID, then go to the destination layout, do a search, and pass that Letter_ID value for the purpose of the search.  Here is the "this_ONE_letter_big" script I wrote.

                  this_ONE_letter_big script

                  Enter Browse Mode
                  Set Variable [ $letterID; Value:Letters::Letter_ID ]
                  Go to Layout [ “Letters” (Letters) ]
                  Enter Find Mode [ ]
                  Set Field [ Letters::Letter_ID; $letterID ]
                  Enter Browse Mode
                  Go to Field [ Letters::Letter_text ]

                  Why won't it work?  What am I doing wrong?

                  The second script I am trying to create is one that would do the following.  I am in the Letters layout.  I am looking at one single Letter.  When I am done reading the letter, I would like to return to the Contacts layout- however, I want to return to the Contacts layout and arrive at the one related parent record.  I named this script "back_to_Contact," and it corresponds to a button labeled "yyyyyyyyyyyy" in the Letters layout.  In this "back_to_Contact" script I am trying to take the value of the Letter record's foreign key, kf_Contact_ID, then go to the destination layout (Contacts), and do a search for the Contact table parent record with that exact same value in its primary key (kp_Contacts_ID).

                  Here is the "back_to_Contact" script I wrote.

                  back_to_Contact script

                  Enter Browse Mode
                  Set Variable [ $kfcontactID; Value:Letters::kf_Contact_ID ]
                  Go to Layout [ “Contacts” (Contacts) ]
                  Enter Find Mode [ ]
                  Set Field [ Contacts::kp_Contact_ID; $kfcontactID ]
                  Enter Browse Mode
                  Go to Field [ Contacts::Notes_keyword ]

                  Why will this script not work?  What am I doing wrong?

                  Your help would be greatly appreciated.  I have tried my best to figure it out on my own but I'm not sure what's wrong.

                  Thank you- and I wish you very Happy Holidays.

                  Best regards,

                  Amanda
                  amanda.spring2@gmail.com

























                  • 6. Re: Beginning Filmmaker Pro user requesting help building a database with a portal
                    raybaudi
                      

                    Hi amanda


                    Problem 1

                    why a so big script when all you need is a simple script step:

                    Go to related record

                    to apply to a button placed in the portal row ?


                    Problem 2

                    the answer is the same: use that script step ( its acronym ( you'll need to know ) is: GTRR )