4 Replies Latest reply on Apr 27, 2017 12:14 PM by beverly

    join data field

    nonsequitur

      Hello,

       

      This is either not possible or so simple I am just missing the subtlety.  I have three tables.  The following data is an oversimplification, but works for discussion.

       

      Table 1: Part_Description

      Part_Number

      Part_Name

       

      Table 2: Part_Properties

      Property_Key

      Property_Description

       

      Table 3: Part_Desc_Prop

      Part_Number

      Property_Key

      Comment

       

      Relationships are established between Part_Description and Part_Desc_Prop on Part_Number and Part_Properties and Part_Desc_Prop on Property_Key.  Members of each table for discussion purposes would be

       

      Part_Description

      PID00001 Honey

      PID00002 Molassas

       

      Part_Properties

      1 Color

      2 Appearance

       

      Part_Desc_Prop

      PID00001  1  light brown

      PID00001  2  sticky

      PID00002  1  dark brown

      PID00002  2  sap like

      In my layout, for the Part Description (and it may be so simple as I am laying it out on the wrong view) I have the Part_Number, Part_Description but I would like to assign the various properties to specific fields i.e.

       

      Part Number: [PID00001]    Part Description: [Honey]

      Color: [light brown]               Appearance: [Sticky]

       

      This works fine when using a portal.  But when I try to access the data in discrete fields, using "Display Data From" in the Inspector, I can only access the first record in the record set.  I've even tried to assign calculation fields with no success.

       

      Part Number: [PID00001]    Part Description: [Honey]

      Color: [light brown]               Appearance: [light brown]

       

      I figure there has to be a way to show the data in fields and not just in the portal.  But because I need to be able to view and modify the data as appropriate a list won't work on the same layout a list won't work, at least as I understand it.  There is some subtlety I am missing about how to access subsequent records in the join table record set, am I trying to access the data from the wrong layout?  If I use a different layout I still have the issue of being able to only access the first returned record.

       

      Any answers anyone may have on this issue would be much appreciated.

        • 1. Re: join data field
          beverly

          If you just use the related fields, you will get the first match.

          You can use a portal, but specify the starting row and how many rows.

          This would allow you to see the second set of values as separated.

          or

          It may be a 'context', and perhaps what you want is a report from a layout not of the part, but of the part_properties (the join) where you can pull the fields from the two other related tables.

           

          beverly

          • 2. Re: join data field
            philmodjunk

            What you want to do makes no sense. To recap, you have these relationships:

             

            Part_Description---<Part_Desc_Prop>-----Part_Properties    (----< means "one to many" )

             

            But when I try to access the data in discrete fields, using "Display Data From" in the Inspector, I can only access the first record in the record set.

            you cannot access any data using "display data form". This only shows you the name of the table occurrence and the field from which the data will be accessed. I think that you are describing adding a field from one of the other two tables directly to your layout and not inside a portal row.

             

            If you are on the Part Description layout, you can only use a portal to access data in multiple records of Part_Desc_Prop or Part_Properties for the simple reason that there could be 1000's of records in those two tables linked to a given part description. You need a mechanism such as a portal for selecting which of those multiple records are accessible and editable on your Part Description layout. Only fields defined in Part Description would normally be placed outside of that portal given these relationships. Placing a field from a related table occurrence onto your layout, as you have discovered only accesses the "first" related record for data.

             

            On the other hand, you could set up a list view layout based on Part_Desc_Prop and include any fields that you need from both Part_Description and Part_Properties. that's because any given record in this table links to no more than one record in the other two tables.

            • 3. Re: join data field
              beverly

              Ah yes, I had the wrong table as the join: "Part_Desc_Prop" Thanks for the diagram, Phil!

              • 4. Re: join data field
                nonsequitur

                You know how sometimes you have to ask an extremely stupid question to figure what you want out.  With the responses I've received I came to the understanding that it's not the portal I'm objecting to.  What's bothering me is the way the portal is appearing on the layout - as multiple records.  Since there are some rows I would like to have different formats for the input (read radio buttons instead of text boxes) what I need to do is have portals that are filtering for a single record, set the format for that specific record key (from the requirement table) and then enter data.  In this manner I can specifically display the type of information I would like in response.

                 

                So yeah, it was a very simple subtlety that I was just missing.  Thanks for your responses.

                • 5. Re: join data field
                  beverly

                  "I love it when a plan comes together." - Colonel John “Hannibal” Smith, A-Team