4 Replies Latest reply on Jan 25, 2012 11:30 AM by philmodjunk

    Report based on a single result coming from multiple fields

    NanetteHartley

      Title

      Report based on a single result coming from multiple fields

      Post

      I have a database set up for tracking analytical work in our laboratory.  We keep records based on a "request' for testing, which may have many samples in it.  Within each sample, there may be many types of testing requested.  I have set this up in a portal as shown here.  Each test can have a different analyst assigned to it, and each test can have it's own due date.  Each test can also be listed as "pending" or "completed."

       

      I need to be able to generate a report of all work that is still active in the lab, and that will need to be pulled from the Status of Analysis 1-6.  I know I can limit the samples that show based on the analysis status, but don't know how to not show any Analysis that has already been completed.  I would prefer to have the report show only those items that are still active, and not tests that are already done (even if they are for a request that is not complete).  

       

      Similarly, we need to have a report of all work that is assigned to an analyst.  That data will be pulled from the Analyst 1-6.  I know how to limit the records to those that are assigned to a particular analyst, and then report on those.  The problem I have is that I also want to limit the tests that are shown on the report to those assigned to that analyst (for instance, if Analysis 1 and 2 are assigned to RM, then Analysis 4-6 should not show on this report).   Since any analyst can have work in any Analysis slot, I am not sure how to do this.

       

      I am open to better suggestions for how to set this up, if that is the best way to do it.  (we just started using this, and I can still move things fairly easily) Otherwise, I look forward to suggestions for how to modify the reports I have.  

      portal.JPG

        • 1. Re: Report based on a single result coming from multiple fields
          NanetteHartley

          Oh, I forgot; 

          Similarly, we need to have a report of all work that is assigned to an analyst.  That data will be pulled from the Analyst 1-6.  I know how to limit the records to those that are assigned to a particular analyst, and then report on those.  The problem I have is that I also want to limit the tests that are shown on the report to those assigned to that analyst (for instance, if Analysis 1 and 2 are assigned to RM, then Analysis 4-6 should not show on this report).   Since any analyst can have work in any Analysis slot, I am not sure how to do this.


          In this report, I would also like to sort (2nd sort key) by analysis due date.  Since the analysis due date can come from Analaysis Due Date 1-6, how can that be done?

           

          Thanks again!

          • 2. Re: Report based on a single result coming from multiple fields
            philmodjunk

            Do you have any other tables here than Requests and Samples?

            Requests::RequestID = Samples::RequestID

            It looks like you really need this structure:

            Requests----<Samples----<Analysis>-----Analysts

            Requests::RequestID = Samples::RequestID
            Samples::SampleID = Analysis::SampleID
            Analysts::AnalystID = Analysis::AnalystID

            Such a structure makes it possible to set up summary reports on Analysis that can be filtered down do just the records you've specified.

            To see the same data in a report with one line for each sample and data from the Analysis table arranged in columns, you can set up a list view layout based on Samples and use filtered portals to Anlysis to display that data in your columns.

            • 3. Re: Report based on a single result coming from multiple fields
              NanetteHartley

              The structure I have is shown on the relationship graph attached.  I have set it up this way so that the samples can be set up as "line items" from request records through a portal.  Given the way I have this set now, would I be able to do this through a single portal?  I didn't think so.  I would really prefer not to have to do multiple steps to do a request entry. 

              • 4. Re: Report based on a single result coming from multiple fields
                philmodjunk

                Even when I open the attached image and zoom it, the names of the table occurrences are too small to read.

                You can't do a portal inside a portal, no, but a list view layout based on Samples with fields added from Requests could be used so that you get a one line to a sample layout with a series of one line filtered portals used to set up the details for each analysis.

                This would be a more complex layout design, but one that supports a simpler relationship graph and opens the door for much more flexible reporting of your data.