4 Replies Latest reply on Feb 23, 2010 7:56 AM by wadef

    Displaying Data from Related Field

    wadef

      Title

      Displaying Data from Related Field

      Post

      I'm new to databases in general and Filemaker in particular and am experimenting with the demo version of FilemakerPro 10 on my Mac.  I'm sure there's a simple answer to my problem - it sounds really really basic.  I just can't figure it out.

       

      I'm trying to set up a database to track my investment activities and portfolio.  I'm starting with 2 tables: one for Activities (buy, sell, dividends, etc) and one for individual Securities (stocks, bonds, etc).

       

      I'm having difficulty displaying a field from a related record.  In the Activities form, I have linked to the Security table via a pull-down list using the ::security_name field.  This works fine but when I select a security name, I would like to automatically display the stock/bond symbol for that security (from the Securities table) in the Activities form, but I can't figure how to access a second field from the same related record.  In the Field/Control Setup box I set a field to 'Edit Box' and selected 'Display data from:' and chose ::Symbol_or_CUSIP.  But it always comes up blank.

       

      As a debug attempt, I also tried to display the ::Security_ID field, but it comes up blank too.

       

      Can you help?

        • 1. Re: Displaying Data from Related Field
          LaRetta_1
            

          Hi dwf,

           

          You should have a Security_ID field in your Activities table.  Then go to your relationships tab (File > Database) and create the join as:

           

          Activities:: Security_ID = Securities:: Security_ID.  Then your value list should be based upon Securities and the SecurityID, all values.  Then display the second field Security_Name.  Use a pop-up field so that only valid securities can be selected.  You can specify 'show values only from second field' at the bottom of the value list.  By using a pop-up menu, your Security_ID field in Activities will display the Security Name but insert the SecurityID (which is what you want).

           

          UPDATE:  Then simply 'insert field' and select your Securities table and place your stock/bond symbol for that security directly onto your Activities table.  As soon as you select the proper SecurityID, your related Security symbol will display.

          • 2. Re: Displaying Data from Related Field
            wadef
              

            Thanks for the response LaRetta

             

            I had already created the join of Activities::Security_ID = Securities::Security_ID

             

            In the values list I had it set to use values from the first field as Securities::Security Name and values from the second field as Securities::Security_ID.  Include all values is set; Sort values using second field is set.  This let me insert the name from the pull-down list into the field, (but not the ID number for some reason).

             

            If I change the Use Values From first field to Securities::Security_ID, and the Second field to Security_Name, both the ID and the Name are displayed, but only the ID number is inserted into the field.

             

            But in either case, when I insert a field, specify the Securities list and select ::Symbol as the field I get a blank field displayed.  


            • 3. Re: Displaying Data from Related Field
              LaRetta_1
                

               


              dwf wrote:

              I had it set to use values from the first field as Securities::Security Name and values from the second field as Securities:: Security_ID.  Include all values is set; Sort values using second field is set.  This let me insert the name from the pull-down list into the field, (but not the ID number for some reason).

               

              You are close but reverse it.  On the value list, your Security_ID should be the first field; you want to insert it into your Activities:: Security_ID field and not the name.  Your relationship remember should be:

               

              Activities:: Security_ID = Security:: Security_ID  so that is why you must put in the Activities:: Security_ID field (and make sure they both are the same field type, usually auto-enter serial number).

               

              If I change the Use Values From first field to Securities:: Security_ID, and the Second field to Security_Name, both the ID and the Name are displayed, but only the ID number is inserted into the field.

               

              And this is correct.  But you can specify (see bottom of value list) to only use values from second field so you only see the names.   

               

              But in either case, when I insert a field, specify the Securities list and select ::Symbol as the field I get a blank field displayed.  

              This means that you don't yet have a proper relationship.  If you set up your structure so the Security_ID is inserted and if there is a matching Security_ID in the Securities table AND if there is a symbol in the Security:: Symbol field,  it should appear.



              From the Activities record, manually type a Security_ID into the field, choosing one which you know is in Security already and that has a Symbol.  The Symbol should display when you have made a match through the relationship. :smileyhappy:

               

              You are just about there.  Once you make your first relationship, you will be excited to see this:  You can now place any field from the Security table onto your Activity  table.  You don't need to re-create the data within Activity.  The Security_ID binds the relationship and makes the Security data available automatically.  Just place fields on your Activity layout.  This is the true power of relational and I still grin bigtime at how elegant the concept of cross-placing fields (once they are related). :smileyvery-happy:

               


              • 4. Re: Displaying Data from Related Field
                wadef
                  

                LaRetta you are a genius!  Thank you, thank you, thank you.  :smileyhappy:

                 

                I don't know why, but I couldn't make your suggestion work yesterday, but after sleeping on it overnight, I've got exactly what I want (and I haven't even had my coffee yet).  I can look up the security in a pull down list, display the security name in a second field, and even display the security symbol in a third field.  Wow!