Join or not to Join

[Mike J]

Hey,

I have a question that I can't seem to address.

When if comes to genetics, one can imagine calling a group of genes a particular name that I will refer to as a Strain. The group of genes are broken down into Alleles, that have a name and are unique.

A new user comes to a system and enters a new strain and needs to build this strain by selecting the alleles that correspond to that strain. Geneticist like to order these alleles by importance.

Because we generate new alleles everyday, one could imagine having a new allele entered into the system that is more important than the previous.

Two tables:

• 
  Strains: One to many Alleles
  Alleles: (Can be in many different strains)
  

Right now, a user is setting the foreign key in the strains table by a return delimited allele ID.

Strain A

-kf_Allele ID =

• 
  Allele [color:blue]4
  Allele [color:green]1
  Allele [color:red]5
  

[color:red]

The user selected the alleles in terms of importance

[color:blue]Most Important

[color:green]Important

[color:red]Not Important

[color:orange]Is this the correct way to build or relate these two or should I have a join table?

Second:

I'm want the system to help the user define the Strain name by pulling in the Allele names by the List Function and replacing the returns with a semicolon and space.

Right now I have a calc Field for a suggested name that is sorted by when the record was created instead of by importance.

//

Let ( [

Namex = List (Alleles::dName);

Name = Substitute (Namex;""¶";"; ")];

Name)

//

[color:red]Result = [color:green]Allele 1; [color:red]Allele 5; [color:blue]Allele 4

What I want is:

[color:red]Result = [color:blue]Allele 4; [color:green]Allele 1; [color:red]Allele 5

How do I begin to think about sorting the list instead of someone going back to the Alleles table to create them in order of importance or reorder them to another parameter?

I thought about creating a new field that was used for importance. I would have a prefix or levels some how and have the table sorted based upon that. Is the list function based upon the table sort?

Thanks for your advice and help.

Mike

[comment]

1. You should have a join table between Strains and Alleles. This table should have a field for ranking the importance - preferably as a number, so it can be sorted correctly.

2. The List() function follows the sort order defined for the relationship. You will need to add an unstored calculation field to the join table = Alleles::Name. Then define the relationship between Strains and the join table to sort the join records by importance, and use the List() function to concatenate the related values of the calculation field in the join table.