Relationship Troubles

[Thumper87]

hey guys!

Im having some real trouble working with a relationship and portal...

i have a table that keeps records of my reptiles:

Record_ID - auto serial

Record_Date

Reptile_IDCode

Reptile_Nickname

Then I have a table that keeps track of different types of Morphs (color and pattern variations):

Morph_ID - auto serial

Morph_Name

Morph_genetics

so one reptile can have one or more different morphs related to it, on the form showing reptile records i would like to show a portal listing the morph(s) relating to that specific reptile.

is there a way to have this listed on the main reptile form? will i need to create another instance of the morphs table? any help is much appreciated!

[LaRetta]

If I understand correctly, your Morph table would be the 'many' side to each 'one' reptile. Therefore, you add the Record_ID from Reptiles into your Morph table and then join as:

Reptiles::Record_ID = Morphs::Record_ID

BTW, I do not understand why you have a Reptile_IDCode and a Record_ID. When placing keys into other tables (where they become the foreign key), it is best to have it be an ID clearly representing the table it comes from ... in this case, it should be ReptileID and then ReptileID in Morphs.

Once they are related, place a portal of Morphs on your Reptile layout. :^)

[Thumper87]

Thanks LaRetta,

The Reptile_IDCode field is just a field similar to Reptile_Name, each reptile is named like: M.PJ.3.11

Which means: Male - Pastel Jungle - #3 - DOB 2011

I followed your instructions above but had no luck unfortunately, looking back on things i found a much preferred way to have this all setup...

Table:Main (reptile records)

record_ID - Auto-Serial

all other fields

Table:Morphs

record_ID

Morph_Name

Morph_Short_Name

Morph_Genetics_Type

Morph_Phenotype

Morph_alleles (i may need to make another table for Morph_Alleles because one more can have several Alleles, such as:

morph name:BumbleBee

Alleles1:spider

Alleles2:pastel

*spider and pastel are jut other morph names, meaning if a spider morph is bred to a pastel morph then there will be a bumblebee morph.

With these tables i would like to display on the reptile layout the morph name, and below that the morph_alleles (the morph_alleles would need to be in a portal because there could be any number of alleles in a morph record.

I really hope i made some sense here. i really appreciate the help, i remember you helping me a lot a couple years ago when i was building this same database, but last year i lost all my data and im trying to start all over now.

[LaRetta]

Welcome back to FMForums!

Tables represent things (entities) and Main is not a thing. Reptiles are things. Why are you not calling the table Reptiles? You know you want to because even in describing the table, you say …Table:Main (reptile records)

The primary key in a table (its auto-enter serial) should clearly identify the table it comes from. Some put pk_ReptileID, some put ReptileID (I prefer the latter simply because if a ReptileID is in a Reptiles table then it clearly is the primary key) … but I side-track, now to your specific need (and yes I apologize because in the sample file, I named it Reptiles because that gives me a vision of the relationships between the 'things', LOL.

The demo ...Clicking View sets gSelectedMonth global with the MorphID. You can add new Morph records from here if you wish because I have Allow Creation on signified by _A in the graph. You can click into Alleles and add an Allele onto a selected morph because there is direct relationship between Reptiles and Alleles based upon Reptiles:: gSelectedMorph = Alleles:: MorphID. There is trigger (Layouts > Layout Setup) which fires OnRecordLoad. It sets the global with the current MorphID in the first row (which then also highlights) and displays that Morph's Alleles information as you scroll through records.

Just speak up if I missed the mark and we'll rework it.

I just replaced the file with a vs 2 - the first one had a global variable filter on a portal from a prior file, LOL.

Reptiles2.zip

Edited March 2, 2012 by LaRetta

[Thumper87]

I understand the the table rename to Reptiles...Makes sense, just never really thought of it that way.

Im just having trouble understanding the following:

"Clicking View sets gSelectedMonth global with the MorphID. You can add new Morph records from here if you wish because I have Allow Creation on signified by _A in the graph"

Whats is gSelectedmonth?

[LaRetta]

gSelectedMonth is a global field in Reptiles. It is set with the MorphsID when 1) you change records, it sets it to the first MorphID in the relationship and 2) when you click on View. This then relates to Alleles_A and is used to display that portal and allow adding records into Alleles.

[Thumper87]

Its hard to see from the example file how gSelectedMorph is working, from what i could see i set up all the tables same as you, also did the same relationships, but dont know how to script the view alleles button, and when i try to add alleles to a certain morph i get the following error msg:

"This field cannot be modified until "gSelectedMorph" is given a valid value"

im not sure if theres something im missing from the sample you sent, im unable to see any conditional formatting or scripts because its not modifiable.

[LaRetta]

I have no restrictions on that file. If you go to File > Manage > Database and Fields to Reptiles and the field called gSelectedMorph. It will show it is a global number. To make yours global, select the field and click Options and go to Storage tab. From there, check the box that says, "use global storage".

Also, you must create a Morph first so it highlights in the portal and then click into Alleles row

[Thumper87]

I have it set to global and many morphs are created, so my 3 reptile records each display a different morph, so im just trying to add alleles to those 3 record reptile morphs, but like i said that msg keeps popping up, i double checked that i set it up the same as you, but the file is deffinetly restricted in some way, i cant select any object in layout mode, i can see the manage database window but every click brings up a msg box saying "this action cannot be performed because this file is not modifiable"

[LaRetta]

Maybe it is a platform issue - I don't see yours listed? Did it unzip properly? But still, if you can get into field definitions, it doesn't make sense.

I just downloaded it, unzipped it again and it is fine. And you got Reptiles2? Regardless, Reptiles only had a filter on the Allels. Did you check the button attached to the View button in the Morphs row? Maybe post the file back ... I am at a loss ...

By the way, is there a reason that you only created one Morph for each Reptile? I thought you said that would be a one-to-many setup. Was I incorrect?

[Thumper87]

Oh Jeez! when you asked if it unzipped properly i realized i opened the file right from the zip folder without extraction, i never knew that affected the file, ill explore it again now that its unlocked lol, and yes it is reptiles 2.

[LaRetta]

I've never done that.

Yes I have!

[Thumper87]

Ok i really like the two portals you have all the morph records to show in the morphs portal at once, instead of instances for each reptile record, and that way when i click the morph name then the alleles would populate in the alleles portal, i would put this in a layout called "morph_Management"

that way i can open the layout and add several morph records and then also add a "select this morph" button that would run a script to take selected portal record morph and fill the field Reptiles::reptile_Morph and then pull the alleles into another portal on the reptiles table under the reptile_morph field.

sorry, im no good with relationships, they just seem to confuse me so much

[LaRetta]

And none of that will be necessary if a Reptile can only ever have one Morph. Can you clarify?

Can a Morph ever have more than one Reptile? :drool:

[Thumper87]

its hard to explain lol, well a reptile can only have 1 morph name, whether its a Pinstripe ball python (pinstripe being the morph name) or a bumblebee ball python (spider and pastel being the morphs in the one reptile, but can be simply called a bumblebee which is the name of the combo morph)...

I figure if i have a layout form where i can just enter all the morphs i can think of, and the alleles that make up those morphs, then i can ad a button on the reptile layout that would open in a pop up window the morph management layout and i can select from the portal the morph that the reptile is and then the morph name gets set in the reptile morph field on reptile layout and the alleles get sent to the portal thats also on the reptiles layout...

hope that helps.

[LaRetta]

its hard to explain lol, well a reptile can only have 1 morph name, whether its a Pinstripe ball python (pinstripe being the morph name) or a bumblebee ball python (spider and pastel being the morphs in the one reptile, but can be simply called a bumblebee which is the name of the combo morph)...

This isn't helping me, I'm afraid. Before we can determine the best configuration for your interface, we must determine the best structure. I thought I understood your need and I guess not. Each 'grouping' should have a table.

Do you have many Reptiles? Reptiles Table

Do you have many Morphs? Morphs Table

Do you have many Alleles? Alleles Table

... but ... if one Reptile can only have one Morph (or one Morph can only have one Reptile) then they should be in the same table. All of the Morphs can be listed in a Value List based upon the Reptiles table. Or do you have a table (or list) of Morphs ... independent of Reptiles? Can a Bumble Bee Morph be assigned to different Reptiles?

Hang in there - I just need to be clear on the relationships between the entities and then I'll modify the file for you. :^) BTW, approx. how many Morphs are you dealing with? So Alleles relates to Morphs only, correct?

[Thumper87]

hmm OK ill try and explain this better lol believe me i know how confusing it is, when i first got into breeding the genetics had me so overwhelmed at first.

First of all i need to clarify a change of the word Alleles to use the word genetic instead, i realized i had that set up wrong,

so i have a table called "Reptiles" to manage hundreds of ball pythons (or other reptiles)

there are thousands of different morphs specifically for ball pythons, see http://www.worldofballpythons.com/morphs/

however there are different morph names for Boa Constrictors and different morph names for leopard geckos and so on...

morphs are made up of Genetics...example: Bumblebee is made from breeding a Pastel x Spider.

http://www.worldofballpythons.com/morphs/bumble-bee/

and

Pastel is just a base form on its own so in the genetics field would just say Pastel

http://www.worldofballpythons.com/morphs/pastel/

Super Pastel is when Pastel X Pastel Breed...so the morph super pastel would be made up of the genetic Pastel as well.

Basic morphs:

Pastel - Co-Dominant Gene

Spider - Dominant Gene

Pinstripe - Dominant Gene

Mojave - Co-Dominant Gene

Cinnamon - Co-Dominant Gene

Piebald - Recessive Gene

Albino - Recessive Gene

Clown - Recessive Gene

Designer morphs: (designer morph Genetics are a combination of basic morphs

Bumblebee

Black Pewter

So in theory, Genetics (once called Alleles) are basically just items from the morph list, excluding Designer Morph names,

maybe we could add a field when entering a new morph, such as "Mutation_Type" Results being either "Basic Morph" or "Designer Morph"

then when selection all the Genetics that made up that morph, i could select them from a list of all the morphs, excluding the morphs that have the "Mutation_Type" field set as "Designer Morph".

I made a example draw up but i cant see how to attach a file on here?

[LaRetta]

To attach a file, click 'more reply options'. And when you add the file (at the bottom), be sure to then click 'attach file'. Also, it must be zipped.

[Thumper87]

got it! thanks

reptile_morphs.zip

[LaRetta]

I'm afraid this file isn't helping. I had asked for the relationship between Reptiles and Morphs and if there can be many Morphs assigned to one

Reptile.

"there are thousands of different morphs specifically for ball pythons,"

So the answer is yes, you can have many Morphs associated with a single Reptile record. Let me ask it this way:

You have a record in your Reptiles table. It belongs to Sammy the Snake. Sammy is a ball python and he is also a Spider and a Clown. Is this possible? Or would Sammy only be assigned ONE Morph description? Would you ever want more than ONE Morph assigned to ONE reptile?

[Thumper87]

Ok if sammy the snake is Clown Spider then the morph that is related to sammy is "Clown Spider" and the genetics to that morph are Clown and Spider

so yes each reptile record can only have ONE morph value, but that morph value is broken down into another field or portal to show "Spider" and "Clown"

http://www.worldofballpythons.com/morphs/clown-spider/

[Thumper87]

I would set up a relationship but that's where im mostly lost unfortunately

[Thumper87]

each reptile can only have one morph assigned to it....each morph could have several related reptiles, MEANING i could and do have 3 Pastel ball pythons and 6 Spider Ball Pythons

So the answer is yes, you can have many Morphs associated with a single Reptile record.

thats not right actually, its the other way around, you can have many reptiles associated with a single morph, however you can have many Genetics (Basic Morphs) associated with a single Morph .

[LaRetta]

Your original request was, “i would like to show a portal listing the morph(s) relating to that specific reptile. is there a way to have this listed on the main reptile form?”

We now understand that you want to select ONE Morph name and, if it is a Designer Morph, display the associated Basic morphs as its genetics. One Reptile can have only one Morph name assigned to it. But a Designer Morph must have one or more Basic Morphs and one Basic Morph can be on several Designer morphs. So you need a join table (I called it Designer_Basics) because it holds one record for every designer/basic combination. Since the Morphs exist in one table (with a Mutation field indicating whether Designer or Basic), I used the same table (Morphs) for both sides (designer and basics). All of the blue table occurrences are actually only one table – Morphs.

You do not need script for this but you might need a better selection tool, such as a portal instead of drop-down or pop-up. There are 1200 Morphs and even just basic morphs, there appears to be more than a hundred. I used drop-down so you can quickly type a letter and jump. That may work for your case. Pop-ups currently won’t jump for us on Windows and you don’t list your OS in your profile so I was unsure.

So since a Reptile can only belong to one Morph, it can be handed via calculation. I see no need to write Reptile Genetics to its own table. If a Reptile is born which has an additional genetic (Morph basic), then I assume you add that Reptile’s new Morph name to the Morph list. This is based on my understanding of how the Morph list is determined.

I created a table of the Morphs and created Designer_Basics records for all Designer morphs that I could – using looping script to attempt to determine which ‘basic’ morphs were included in the ‘designer’ name, but you will have to check them. There are some Designer morphs without a single basic morph or with only some of its genetics identified but not all. Some genetics could be determined if you could use the AKA. But in some cases, I was unsure if that logic would hold so I did not use the AKA in determining a Designer's Basic morph components. And I could find no on-line list of each Designer morph's genetic makeup but I have enough to understand the theory of how they relate and that was most important. I DID find a Genes section and I was able to specify the Genes for you while I was importing the other data.

As an aside, I am not much of a snake fan but I confess that some of those pythons are incredibly beautiful!!

If I have still misunderstood your need, let me know. :^)

Reptiles3.zip

[Thumper87]

Absolutely perfect!, every works so great, Thank you so much for all this! i know it wasn't easy lol