8 Replies Latest reply on Oct 18, 2014 8:12 PM by philmodjunk

    Using Portal to select related records

    PittAdminAsst

      Title

      Using Portal to select related records

      Post

      Hello,

      I'm sure that this is not a complicated problem, but have searched for days without finding a solution.

      I am using filemaker to organize many different types of contacts.  One type is students, which eventually become alumni.  In our center we routinely send out surveys to get updates on alumni activity.  The problem we ran into was that if someone responded to the survey twice their old info would be lost when importing the new info. 

      My solution was to make the alumni info a separate table so that you could have multiple "alumni surveys" associated with one contact via contact id relationship. (one to many relationship so it doesn't need a join table right?)

      So on the layout that shows student info there is a tab for their alumni info in a portal.  Here I would like to have a dropdown menu showing the dates that someone has filled out the survey so that the user can select them to show the different responses. 

      I have gotten it to the point where I have the dropdown menu in a portal that shows the proper dates. My problem is that when I select the *other* date instead of bringing up the other survey it modifies the data to show they both have the same date.  I know this is the expected behavior but how can I use this to trigger the other record.

      I have thought about this a number of different ways and haven't been able to figure it out.  I'm pretty sure a global field is out of the question because I am not filtering based on global values, these are specific dates associated with specific records.  I guess what I need is dynamic portal filtering based on a specific field.  Instead of telling the portal ahead of time how to filter (static) I want it to look at the related records and display the available options in a dropdown.

      Any help is greatly appreciated!

        • 1. Re: Using Portal to select related records
          AppGuy

          Could you please show your relationship graph how you have the students connected to alumni and also the alumni surveys table??  Or do you only have 1 table so far with just students and your adding a new table called alumni surveys..  Either way post your graph...

          • 2. Re: Using Portal to select related records
            philmodjunk

            I don't think that you need the portal. The key detail is to select the correct field to format with your value list of survey dates.

            Either that or you use the portal to list the survey records and use a button with Go to related records to bring up the particular related record.

            • 3. Re: Using Portal to select related records
              PittAdminAsst

              Hey guys thanks for getting back so quickly.  I'm not sure the correct way to respond to your posts so I'm sorry if this is wrong way.  Also couldn't figure out how to upload more than one image so I combined them into one.

              JP- Here is a screenshot of the tables.  It starts with general contacts<-->Student Info<-->Alumni Info.  I know I could have gotten away with lumping all the student info fields into the contacts table but I didn't like the idea of that data being in the same place. For both relationships Contact ID is used which is auto entered and then passed down the line to students and alumni.

              PhilModJunk- I am not sure exactly what you mean with your first recommendation.  The second sounds like the approach I have taken. Here is a picture of my layout.  The layout is based on the student info table.  The dropdown at the top is a portal to related alumni info records (I believe the form below needs to be in a portal too).  I have tried setting it up two different ways.  The first was the normal style with multiple rows.  I could get this to open the related record in a new window (but always in table view for some reason), but I didn't like the way it looked.  Ideally clicking the survey date will bring up the info in the form below.  The second approach is formatting the field as a dropdown as shown.  I don't care which style the button ends up being I just want to make it so it shows the available dates and when selected shows the appropriate survey info below.

               

              • 4. Re: Using Portal to select related records
                philmodjunk

                The dropdown at the top is a portal to related alumni info records

                There is no layout shown in your post. How can a drop down also be a portal? Are you describing a FileMaker 13 Popover?

                My problem is that when I select the *other* date instead of bringing up the other survey it modifies the data to show they both have the same date. 

                So if you set up a different field with this value list, selecting a date in it will not modify data that should not be modified. That field might serve as a match field in combination with contact ID to match to just one record in Alumni Info. It could also be a field with global storage specified that can be used to perform a find for the correct Alumni Info record.

                Or you can set up a portal to Alumni Info that lists all the survey dates for the current StudentProfile (or Contact) record and then a button in the portal row could be set up with Go to related records to take the user to just that specific Alumni Info record on another layout and/or in a new window.

                • 5. Re: Using Portal to select related records
                  PittAdminAsst

                  There is no layout in your post. How can a dropdown also be a portal?

                  Yikes, I attached a photo with a portion of the layout and the relationships.  Is that not visible?  The field in the portal is displayed as a dropdown list instead of an edit box.

                  Or you can set up a portal to Alumni Info that lists all the survey dates for the current StudentProfile (or Contact) record and then a button in the portal row could be set up with Go to related records to take the user to just that specific Alumni Info record on another layout and/or in a new window.

                  This is pretty much the approach I took.  I was just hoping to make it so it all happened in one layout/window for a cleaner experience.  Although, I guess I could use clicking the tabs as a way to trigger layout changes, hadn't thought of that.

                   

                  • 6. Re: Using Portal to select related records
                    philmodjunk

                    The upper part of your post was not visible when I last posted. This forum sometimes get's a bit funky with uploaded graphics.

                    What you have is not what I was suggesting. What you show does not look like you need the portal. By my suggestions, you would either use the value list to select a survey record or a portal with a button to select the survey record. You would not have a value list formatted field inside of a portal to the survey records--which does not seem to serve any useful purpose here.

                    • 7. Re: Using Portal to select related records
                      PittAdminAsst

                      Thanks Phil. Sorry I didn't clarify.  The dropdown list was a bad idea, I shouldn't have tried that approach or mentioned it here.  In the recent one there is a portal showing available survey dates for an individual and below that is the form.  Ideally the portal with the dates would dictate which survey is shown below so you could easily switch between them without opening multiple windows. Not sure if this is do-able. 

                      My most recent attempt was setting a local variable based on the active portal row number (if none default to 1) while also setting up the surveys to number themselves for each individual. Each person has a survey1, survey2, etc.  Then put the form in a portal with filters to only display a record when the survey #=portal row number (since they are sorted 1st portal row=first survey).  That may have been ill advised/a little out of my league, I was just experimenting to find a solution.

                      Sorry I haven't communicated my issues/goals well.  Let me know if this sounds unreasonable or more trouble than it is worth.

                       

                       

                      • 8. Re: Using Portal to select related records
                        philmodjunk

                        By adding another occurrence of the survey records, you can have these relationships:

                        SelectedSurvey-----Alumni-----<Surveys

                        Alumni::__pkAlumniID = Surveys::_fkAlumniID
                        Alumni::_fkSurveyID = SelectedSurvey::__pkSurveyID

                        For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                        You set up a button in the portal row to do this script:

                        Set Field [Alumni::_fkSurveyID ; Surveys::__pkSurveyID ]
                        Commit Records

                        and place on your layout from SelectedSurvey to show the data from the survey selected by clicking a button in your portal.

                        Caulkins Consulting, Home of Adventures In FileMaking