7 Replies Latest reply on Aug 20, 2015 1:56 PM by SydneyBlock

    Using Reports to Display all Portal Records

    SydneyBlock

      Title

      Using Reports to Display all Portal Records

      Post

      Good morning!

      I have a Staff Personnel database, which has a portal to display an employee's salary/bonus history. I am trying to create a report that will display each salary year for an individual employee. I am not sure if the problem lies with how the tables are related, or how the report is structured, but after playing around with the report a little, what I have gotten to is that it will display a record from the portal, but doesn't associate it with an employee. Here is how my table relationships work;

      Personnel Records   Yearly Compensation

          xP_EmpID         -->      xD_EmpID     (Serial number to number field)

         p_ComSum        -->    EmpComSum  (Serial number to number field)

       

      As far as my report goes, the layout itself is displaying records from Yearly Compensation. It has a subsummary when sorted by Personnel Records::FirstLast (Leading) and a subsummary when sorted by Yearly Compensation::Year (Trailing). Any tips on what I'm doing wrong?

       

        • 1. Re: Using Reports to Display all Portal Records
          philmodjunk

          Don't see any need for two pairs of match fields. They appear to do exactly the same thing.

          If your layout is based on Yearly Compensation, The relationship shown--even if modified to use only the first pair of match fields, will have no effect on what Yearly Compensation records are currently visible on your layout. The relationship, instead, will control what data from Personnel can be shown on your layout.

          If your layout was based on Personnel Records, a portal to Yearly Compensation would list all Yearly Compensation records associated with the current employee. But this is not ideal for printing.

          A list view layout based on Yearly Compensation makes sense for printing, but you have to take steps to pull up a found set of the specific records that you want to print. You can use a script to perform a find or use Go to Related Records from the Personnel Layout to pull up specific Yearly Compensation records for your report.

          • 2. Re: Using Reports to Display all Portal Records
            SydneyBlock

            I'm definitely struggling with controlling what data from Personnel can be shown. Here is what it looks like, and the grey bar is where it should be sorting by employee;

             

            • 3. Re: Using Reports to Display all Portal Records
              MorkAfur

              Can you get the right results using a SQL statement?

              I always try to make sure whatever I'm asking FM to do is "Query-able" first.

              - m

              P.S. I like Phil's suggestion of trying GTRR - that command (script step, or whatever) alone makes FM easy to navigate.

              • 4. Re: Using Reports to Display all Portal Records
                philmodjunk

                I can't tell anything from your screen shot of a layout taken while in Browse Mode. Nor do I understand what you are trying to tell me about the "grey bar".

                I suggest that you show a screen shot of your relationships and then explain what parts of the above screen shot are fields from the Personnel table and whether you are or are not using a portal. From what I see, a portal to Personnel would not make any sense on a layout based on Yearly Compensation.

                • 5. Re: Using Reports to Display all Portal Records
                  SydneyBlock

                  My apologizes. I'm not actually trying to create a portal in any sense, but display the records entered into the yearly compensation portal on another layout. The only field that is from the Personnel is FirstLast, which is in the "grey bar", or subsummary when sorted by Personnel Records::FirstLast. 

                  EDIT: please ignore the g in fields marked as g_ComSum and so forth. It does NOT mean they are global fields.

                  • 6. Re: Using Reports to Display all Portal Records
                    philmodjunk

                    If the field does not show any data, then you have a record in Yearly Compensation that either does not match to any records in your Personnel Records or you have a record in Personnel where this field is empty.

                    Better check the data in your fields.

                    And I will repeat that you do not need two pairs of match fields in this relationship. It's possible that the extra pair of fields is why you are having this issue as it may keep records from matching that should match.

                    • 7. Re: Using Reports to Display all Portal Records
                      SydneyBlock

                      I got it working correctly, thank you for your help! What I did was get rid of the second match field, and instead of use Personnel Records:: FirstLast, I separated the two and used a merge field. I then changed the report parts to subsummary when sorted by Last Name(Leading) and subsummary when sorted by Year(Trailing). I then sorted by Last Name and then year in an ascending order. This returned all portal records per employee, sorted them alphabetically, and put the compensation info in chronological order.