1 2 Previous Next 27 Replies Latest reply on May 16, 2011 3:33 PM by simx48

    Help!  Suggestions welcome

    simx48

      Title

      Help!  Suggestions welcome

      Post

      I have a databse that I'm working on that will be storing data for different strains of algae.  I'm having a problem because some of the strains are the same but were grown with different methods.  Right now I have a record for each individual strain (example: Strain 1A, Strain 1B, Strain 1C, Strain 2, Strain 3, Strain 4A, Strain 4B).  Each strain has a "homepage" type layout that I have made radio buttons on to link to the more specific data on that strain.  The strains with only one type of growth method are pretty straight forward, I jsut made a button to "go to related record" but I'm having a problem figuring out how I can do this for the strains with multiple growth methods.  Any suggestions on how to do this?

        • 1. Re: Help!  Suggestions welcome
          LaRetta_1

          "Right now I have a record for each individual strain (example: Strain 1A, Strain 1B, Strain 1C, Strain 2, Strain 3, Strain 4A, Strain 4B).  "

          You need two tables:  Strains and Methods

          Strains would have a field called StrainID (which would be unique, auto-enter, FM-generated serial number) and it would hold strain1, strain2 etc.  Methods would have a MethodID (unique, auto-enter, FM-generated serial number) and this table would hold details about the growing method.  Methods table would also have a number field called StrainID.

          You then relate the tables in your graph as:

          Strains::StrainID = Methods::StrainID

          While there at the bottom, you will see a checkbox 'allow creation of related records' and you might want to check that on the Methods side.  Thenon your Strains layout, add a portal based upon your Methods table.  Now all the growing methods for the strain are displayed in the portal as multiple entries and, if you click into the last row of the portal, it will add a new Method record if you wish.

          • 2. Re: Help!  Suggestions welcome
            bbry

            Hi this is simx48, I couldn't seem to log in with my other account so I just made a new one.  One other question about the portal.  Will that just display the 'growth methods' field for the related records, or the entire layout?  I would like to find a way to display the entire layout because the strains with different growth methods also have different data related to that (all of the data along with the growth method is on a separate layout).  I would like to make it so I can link the homepage layout for the strain to the other layouts I have for all the data on the strains (including the growth method).

            • 3. Re: Help!  Suggestions welcome
              LaRetta_1

              I'm not sure I understand your question.  I do not know what you mean by 'the entire layout' since records are based upon table (and specifically an occurrence of the table).  You would be on a layout for the parent (Strains) and view the growth data in a portal (they would be related on StrainID).  Neither do I understand what you mean by 'homepage layout' unless you mean a Main Menu?

              If you wish to show other tables relating to Strain then you can use a tab control panel and place portals to each of the other related tables there.  Or if you wish to see other strains on a strain layout then you can create a relationship from this Strain table to another copy of itself (known as a self-join).

              But I need to translate what you mean by layouts and homepages and what other data is related and how it is related.  Can you insert a screenshot of your relational graph here?  If not then please describe in different words what exactly you want to display (what table) and where you want it displayed (the table which a layout is based upon).  We'll work through this ... :^)

              • 4. Re: Help!  Suggestions welcome
                philmodjunk

                By any chance do you have the same "method" which applies to multiple strains of algae?

                If so, you have a many to many relationship which in turn requires a join table to implement the link between an "Algae Strain" record and a "method" record.

                • 5. Re: Help!  Suggestions welcome
                  bbry

                  Okay so basically I have a table for general data on the strain (origin, species, genus, etc) which is acting as the "homepage" layout for each strain.  From this layout I want to be able to link it to another layout based upon another table which contains component data.  It's in this table that contains the component data that I have the different growth methods (ex. I have four different growth methods for Strain 3).  Would it be possible to do what you said earlier - using the portal to show the different growth methods on the general "homepage" and have a user be able to click on a specific growth method to go to the other table? 

                  • 6. Re: Help!  Suggestions welcome
                    simx48

                    I'm also having another problem with relating the StrainID from the strain table to the method table.  The strain table only has one StrainID per strain but the methods table has multiple StrainID's for the certain strains with multiple growth methods.

                    • 7. Re: Help!  Suggestions welcome
                      philmodjunk

                        The strain table only has one StrainID per strain but the methods table has multiple StrainID's for the certain strains with multiple growth methods

                      What "method" information do you need to record here? Is this a single value that you can select from a drop down or does selecting a given method need to bring up a larger set of information specific to that method?

                      And this question is key: Can one strain be linked to more than one method?

                      If so, this is a many to many relationship. If not, it's a one to many relationship.

                      If it's one to many (one method, many strains), then you can link a strain to a Method by entering a MethodID into a methodID field defined in the record for the strain. This can be a simple as setting up the field with a drop down list or pop up menu that lists the methods.

                      If it's many to many, you need a join table. If that's the case, let me know and I'll expand on the concept if a follow up post.

                       

                      • 8. Re: Help!  Suggestions welcome
                        simx48

                        Well certain stains only have one method, and I have about 10 strains that have multiple growth methods.  If I follow what you're saying then I need a join table?  I'm having trouble understanding how I can make my strains layout the same for all of the strains I have but still link the ones with multiple growth methods to the appropriate data (since the layout will be the same).  

                        Also to answer your other question "does selecting a given method need to bring up a larger set of information specific to that method?" Yes.  

                        • 9. Re: Help!  Suggestions welcome
                          LaRetta_1

                          "And this question is key: Can one strain be linked to more than one method?
                          If so, this is a many to many relationship. If not, it's a one to many relationship."

                          This is why the Methods table was created ... there was one Strain and many Methods and it is a 1:n (one-to-many).

                          If re-reading the original request, it was clear that one strain can have multiple methods (signified by the A, B, C etc), as indicated by:

                          "The strains with only one type of growth method are pretty straight forward, I jsut made a button to "go to related record" but I'm having a problem figuring out how I can do this for the strains with multiple growth methods."

                          ... and that is why we split Methods into its own table.  So Methods would hold the StrainID because it is the many side.  I think, Phil, that you just got the portion in blue backwards.  And of course if one Method have apply to many strains, it MIGHT indicate a many-to-many but not necessarily as a multi-line value list could handle it.  This would depend upon reporting needs.

                          • 10. Re: Help!  Suggestions welcome
                            simx48

                            I'm still not quite clear on what you mean by making a methods table.... I should create a separate table with jsut the methods on it?  Then that table will be able to link to the other table with all of the specific data related to that method???

                            • 11. Re: Help!  Suggestions welcome
                              philmodjunk

                              Ok, then all your answers point to the need for a Join table to support the "many to many" relationship you need between "strains" and "methods"

                              Set up this relationship for this part of your system:

                              Strains----<Strain_Method>----Methods

                              Strains::StrainID = Strain_Method::StrainID

                              Methods::MethodID = Strain_Method

                              You can place a portal to Strain_Method on your Strains layout and use it to list methods for that Strain. You can also use this portal to link a strain to as many Method records as you need by placing the Strain_Method::MethodID field in this portal and formatting it with a drop down list or pop up menu that lists the MethodID's in column 1 and a Method name/description in column 2. Selecting a Method from this list will enter the Method's ID and thus link your Strain to this method. You can also place fields from Methods inside this portal's row to supply additional data about that method.

                              Also, a Portal to Strain_Method placed on the Method's layout could list all strains that specify this Method.

                              Here's a demo file that matches "contracts" to "companies"  in such a many to many relationship. If you renamed the two tables "Methods" and "Strains" you'd have a possible example of this approach.

                              http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                              • 12. Re: Help!  Suggestions welcome
                                simx48

                                I'm having trouble setting up the relationship that you mentioned.  When I do it, it just creates another methods table occurance and not a strain_method one.

                                • 13. Re: Help!  Suggestions welcome
                                  simx48

                                  Do I need to manualy make a separate strain_method table and insert the fields myself, and then link them that way?

                                  • 14. Re: Help!  Suggestions welcome
                                    philmodjunk

                                    Strain_Method should be created and defined in Tables and fields should be defined in it in Fields, before you click the Relationships tab to link it up in a relationship to the other two tables.

                                    1 2 Previous Next