5 Replies Latest reply on Jul 16, 2012 9:09 AM by philmodjunk

    Show one related record out of many

    mikedorn

      Title

      Show one related record out of many

      Post

      My employees have many jobs which have many reviews (usually one per year) but could be more. How do I get a certain related review to show from the many that are in the review table. 

      Relationships now are:

      Employee ID===>Jobs ID

      Employee ID===>Review ID

       

      Or should the relationships be:

      Employee ID===>Jobs ID===>Review ID

      I'm using a Goto Related Record script now, that show every Review for that Employee. I'm after the single related record by the review date.

       

      Please advise,

        • 1. Re: Show one related record out of many
          philmodjunk

          I'd change the name of some of those fields. You should have something like this:

          Employees::EmployeeID = Jobs::EmployeeID

          Employees::EmployeeID = Reviews::EmployeeID

          And this assumes only one employee is assigned to any given job and only one employee is linked to any given review. If this is not the case, the relationships would need to be changed.

          Sticking just with the Employees to Reviews relationship, if you have a portal to Reviews on your Employees layout, put a button inside the portal row and use Go To Related Records to pull up related records on a Reviews layout. The current record should be the same record as the one you clicked in the portal. If if is not, there is some other issue with your script that is interfering with this result.

          • 2. Re: Show one related record out of many
            mikedorn

            This is how I have my relationships formed.

            Employee::__kp_Emp_ID = Jobs::__kf_Emp_ID

            Employee::__kp_Emp_ID = Review::__kf_Emp_ID

             

            I'm using a Goto Related Record script button now, it shows every related Review for one individual employee. I'm after the single related record by the review date of the line where the button resides.

             

            • 3. Re: Show one related record out of many
              philmodjunk

              Yes, it shows every related review, but the one clicked is the Current Record. If you use a form view instead of list or table view, you'll only see the record whose row was clicked in the portal.

              You can also add steps to isolate that record in a single record found set if you wish.

              Set Variable [$ReviewID ; value: Review::__kp_Review_ID]
              Enter Find Mode [] // clear the pause check box
              Set field [Review::__kp_Review_ID ; $ReviewID]
              Perform Find []

              • 4. Re: Show one related record out of many
                mikedorn

                Is it possible for someone to look at my solution to check for proper relationships and structure? This is my first attempt at a complex solution.

                • 5. Re: Show one related record out of many
                  philmodjunk

                  You can upload a screen shot of your relationships graph found in Manage | Database | Relationships.

                  You can post your script that you are using.

                  If you upload a screen shot of your relationships, please identify which table occurrence (box) represents your layout and which your portal on that layout. Then indicate which one represents the layout specified in your go to related records step.

                  (Every layout is based on a specific table occurrence. You can see which one by opening layout setup... and checking the "Show Records From" drop down. Every portal is based on a specific table occurrence. You can see which one in layout mode by checking text in bottom left corner of portal or by opening portal setup and checking the "show related records from" drop down.)

                  To post a script to the forum:

                  1. You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                  2. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
                  3. If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                  4. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)