6 Replies Latest reply on Jul 25, 2011 12:00 PM by RyanFishy

    Help with Portal Concepts

    RyanFishy

      Title

      Help with Portal Concepts

      Post

      FM Pro 11, Windows XP

      I know what I want from a portal, but can't figure out how to get there.  I'm designing an addition to an existing system, so I have some flexibility in how these tables are structured but can't change everything.

      Relevant tables:

      Individual Metadata - Table of metadata on an individual (fish) (sex, weight, etc) - fixed

      Accessions (genetic samples) - groups of *collected* fish (collection location, project, species, etc) -fixed

      Metadata SETS - table to organize groups of individual data into experiments or similar - flexible

      SETS_Individuals join - table to relate individual metatdata to sets of that data. - flexible

      ___________

      Individuals can only be related to one accession, but could be related to mulitple Metadata SETS

      These are (currently) related as outlined in the picture;  SETS <-> SETS_Ind <-> Ind Metadata <-> Accessions. 

      Currently, I have a layout based on SETS that tries to show and summarize what is contained in the current SET.  On this layout I have a portal to 'Accessions' to show the different accessions that are related to the current SET.  On this portal showing accessions, I want a count of related metadata entries, so I can see how many from each accession are included in the current SET

      I've figured out how to get a total count of metadata for the SET (not broken down by accession) or I can get a total count for that accession accross all SETs, but neither of these quite fits the bill.

      I've tried a bunch of different self joins and count fields from different perspectives targeting different things but haven't figured it out.  Let me know if any more info is needed.

      Thanks

      -Ryan

      DB-snapshot.JPG

        • 1. Re: Help with Portal Concepts
          philmodjunk

          I don't see a table occurrence named "Accessions". Does each record in "genetic samples" represent one "Accession"?

          • 2. Re: Help with Portal Concepts
            RyanFishy

            Yes, we are in the proccess of renaming a few things 'genetic samples' is the table of accessions.  Sorry for the confusion.

            -Ryan

            • 3. Re: Help with Portal Concepts
              philmodjunk

              So your layout is based on Individual Metadata Sets and the portal refers to Genetic Samples?

              Any time you get more than one or two table occurrences bewteen portal and layout TO's you need to be very very careful as the data in the intermediate tables can be a major issue in what you actually see in your portal.

              Do you see each genetic sample listed only once? (I think you'll see an accession group listed once for each individual fish from that group here...)

              Assuming that you see each accessession (Genetic Sample) listed once in this portal, create a calculation field in Genetic Samples:

              Count ( Individual metadata join table::metadata record match )

              and will give you your count broken down by accession group.

              • 4. Re: Help with Portal Concepts
                RyanFishy

                Its get a bit complicated here. Sorry

                I had the portal filtered so that it would only show once for each 'Accession' (I was misunderstanding what I was doing, but it was "working",  Sadly 'Accessions" [genetic samples] is very old table and is not structured well at all, each Accession Number [sample number in picture] can occur more than once, so its not the key.  I was "solving" this by filtering the portal to show only "Primary" records from Accessions [genetic samples]. )  When I remove this filter, I get one record per fish.

                I had tried your calculation field solution previously, it gives the result of counting how many TOTAL entries in the Join table share the Accession number, not just the ones related to SET being viewed on the current layout.

                My goals for this system are as follows, I'm not really tied to this structure or relationships.

                Store metadata on individual fish. 

                Fish are grouped into Accessions (think populations)

                Organize SETS of fish that can span one or more accessions. 

                Fish data can be in more than one SET.

                Let these SETS be related to Accessions only by the fish in the SETS.  

                Do you think this is a reasonable set of goals?  So far I've got it working, just haven't been able to count as desired.  I could probablyt live without the counts, but does this suggest there might be other problems?

                • 5. Re: Help with Portal Concepts
                  philmodjunk

                  Good point, your calculation needs to combine the current SetID with the Current Sample number.

                  Frankly, if the Current Sample Number does not store a unique value in the Genetic Data table, you should add one that is. This can be easily done as you can define a new field with an auto-entered serial number then use Replace field contents with the serial number option to assign unique serial numbers to all your existing records. Then you'll need to update the Individual meta data table with this new value.

                  Let's set it up this way. Define a global field in Individual metadata as gSetID.

                  Select the Individual metadata table's occurrence in Manage | Database | Relationships and use the button with two plus signs to make a copy of it, named IndMetadata. Use the same technique to get a copy, named MetadataJoin, of your existing join table.

                  Link them to Genetic Samples like this: (and use a new serial number field if sample number is not unique.


                  Genetic Samples::Sample Number = IndMetadata

                  IndMetadata::gSetID = MetadataJoin::Individual Metadata Set Match AND
                  IndMetadata::Acession Number = metadata Record Match

                  now Sum IndMetadata ( Metadata record match )

                  should give you an accurate count if you first update the global field: gSetId with the current value of Unique ID. This can be done with an OnRecordLoad script trigger so that each time you navigate to a different record on your Set layout, the global field is automatically updated.

                  • 6. Re: Help with Portal Concepts
                    RyanFishy

                    Thanks Phil,

                    This approach of a global field worked, it won't let folks view counts within of two different SETS in a list view, but I can deal with that.  Just to clarify I did:

                    Genetic Samples::Sample Number = IndMetadata::Accession Number

                    IndMetadata::gSetID = MetadataJoin::Individual Metadata Set Match AND
                    IndMetadata::*Unique ID* = metadata Record Match

                    now in genetic samples:    *Count* (MetaJoin::Metadata Record Match )

                    I do have a unique serial number for records in 'genetic samples'.

                    Thanks so much for the help,

                    Ryan