10 Replies Latest reply on Nov 27, 2012 8:53 AM by Annette

    Portals and Related Value Lists



      Portals and Related Value Lists


            Hi All,

           Please help.  I have the following tables/fields

            - Clients Table: Client ID, Name, Address, etc

            - Diagnosis Table:  Diagnosis ID, Diagnosis 1, Diagnosis 2, Diagnosis 3, Diagnosis 4  (There are duplicates because I wanted it to be like cascading (correct term?) choices.  e.g. :

           Diagnosis 1                     Diagnosis 2                      Diagnosis 3                  Diagnosis 4

           Fracture                            Lower Limb                       Foot

           Fracture                            Upper Limb                      Arm

           Sprain                               Lower Limb                      Ankle

           (Those probably arent best examples but basically in Diagnosis 1 if i make a drop down i want fracture and Sprain to be choices, then depending on which is selected drop down in Diagnosis 2 to give choices of Upper limb or Lower limb, etc)

            - Client_Diagnosis Table:  ID, Diagnosis ID, ClientID, Diagnosis 1, Diagnosis 2, Diagnosis 3, Diagnosis 4 ( I created this thinking I needed a join table between the client and the diagnosis)

           I was told that I had to create additional tables in order to get that related affect to the drop down lists, say Diag1, diag2 tables, etc.  and relate them to fields in the client table.  Then create value lists for each, say value list looking up diag1 field in diag1 table, a list looking up diag 2 field in diag 2 table, etc. 

           Now if I was just having one of each of these four diagnosis fields per client it works fine.  But I wanted to create a portal since they can have several.  So I need each portal row to have Diagnosis 1-4 . 


           Basically have I done this all backwards? Am I on the right track?  no matter what way I try to reference to portal and fields in it I am not able to select anything from the lists even through allow creation is checked in the relationships and browse is ticked in the inspector. 



        • 1. Re: Portals and Related Value Lists

               Sounds like you are on track if your value lists are working for you. The same method works in your portal as for a single set of fields--but make sure your relationships and value list settings refer to the portal's table not the table of the layout on which you have placed the portal.

          • 2. Re: Portals and Related Value Lists

                 Nope, still not working.  First question....do I need all those separate tables?  I know I need the diagnosis table with the ID field and diagnosis 1-4, but am I correct in creating a join table (client_diagnosis) AND a table for each diagnosis?

                  If I am correct in creating all those tables then I clearly have screwed up the relationships between the different tables.  The original diagnosis table is not related to anything.  The client_diagnosis table is related to clients by the client ID.  Each individual diagnosis table is the same fields as those in diagnosis table just not sure what to relate them to.  Do I create diagnosis 1-4 fields and relate diagnosis 2 to diagnosis 1 in clients, then diagnosis 3 to diagnosis 2 in clients, diagnosis 4 to 3 in clients?  If so does diagnosis 1 relate to anything?  

                 My portal is on a layout which is related to clients table but the portal is referencing client_diagnosis.  

                 The value list for diagnosis 1 from field diagnosis1 on diagnosis 1 table.  Value list for diagnosis 2 from diagnosis 2 field on diagnosis 2 table and related values only is checked but left it saying unknown etc for value lists for diagnosis 3 and 4.

                 so not sure whether the fields in the portal should be from corresponding diagnosis table or join table. 

                 SO many places to mess up and I'm not sure where to start! 

            • 3. Re: Portals and Related Value Lists

                   It depends on the purpose for each table.

                   How do you use your diagnosis table? What does one record in that table represent. If one record in that table represents one standard diagnosis that can be linked to many different patients, then  you need the join table as a patient can have more than one diagnosis and a diagnosis can be selected for more than one patient.

                   If, however, each record in diagnosis represents one diagnosis for one specific patient, then you do not need the join table.

                   From your first post, I must conclude that you do need Client_Diagnosis to serve as a join table linking a client to a diagnosis.

                   It is my understanding that when you had just one set of these fields, your conditional value lists worked correctly. Is that the case?

                   If so, then you need to start with the portal you are setting up. On what layout did you place it? Is it a portal to Client_Diagnosis or a Protal to Diagnosis? It should be a portal to Client_Diagnosis placed on your Clients layout.

              • 4. Re: Portals and Related Value Lists

                      Ok, so I was correct in creating the join table then because yes, clients can have nore than one diagnosis and a diagnosis can be for many different clients. 

                     Now, in my efforts to get this work I've changed things around and now I can't get the conditional value lists to work even outside of a portal. 

                     The portal would be on a layout that shows records from Client Info.  The portal is showing records from Client_Diagnosis.  So do I have the fields associated to Client_Diagnosis table or Diag1 to Diag1 table, Diag2 to Diag2 table, etc.?

                • 5. Re: Portals and Related Value Lists

                       Let's recap what you have to start:


                       Clients::ClientID = Client_Diagnosis::ClientID
                       Diagnosis::DiagnosisID = Client_Diagnosis::DiagnosisID

                       You then need to relate additional table occurrences to Clients to get a set of what I call "hierarchical conditional value lists". These are a "chain" of value lists where each value selected in the previous field's value list determines what set of values appear in the current field's list of values.

                       You do not need any additional tables for this, but you will need additional table occurrences. A table occurrence is what we call one of the "boxes" found in Manage | database | Relationships that we then use to define relationships. What you may or may not know is that more than one such "box" (table occurrence) can refer to the same table and thus we can have more than one relationship between the same two tables.

                       Let's create 4 value lists named Diagnosis 1, Diagnosis 2, Diagnosis 3, Diagnosie 4. The last 3 will be hierachical conditional value lists.

                       Set up Diagnosis 1 to display all values from Diagnosis::Diagnosis 1.

                       In Manage | Database | relationships, make a new table occurrence of Diagnosis by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be DiagnosisD1.

                       We have not duplicated the table. Instead, this is a new reference to the same Diagnosis table.

                       Add it to your relationships like this:

                       Client_Diagnosis::Diagnosis 1 = DiagnosisD1::Diagnosis 1

                       Go to Manage | Value Lists, create a new value list, Diagnosis 2 and set it to display values from DiagnosisD1::Diagnosis 2. Select Include Only Related values and select Client_Diagnosis as the starting from table (occurrence).

                       Now we repeat this process, but matching by different fields to produce:

                       Client_Diagnosis::Diagnosis 2 = DiagnosisD2::Diagnosis 2
                       Client_Diagnosis::Diagnosis 3 = DiagnosisD3::Diagnosis 3

                       Set up value list Diagnosis 3 to list values from DiagnosisD2::Diagnosis 3 and Diagnosis 4 will list values from DiagnosisD3::Diagnosis 4. In both cases, Client_Diagnosis will be your "starting from" table occurrence.

                  • 6. Re: Portals and Related Value Lists

                         Ok, following my post yesterday I hit the internet looking for some more help.  I found an article that was pretty helpful but is slightly different from what you said above.  I've attached a screenshot of the relationships that I've set up according to the directions on that article as I already had this done prior to recieving your response. 

                         It seems to be working in that I can select all the four fields and they are relating to each other in the lists.  The only thing I'm not sure of as it never explained this part in the article, is it made me choose a value for z, and I had to put something in the data section under options for Z, I used 1 as did the example given.  so now for every diagnosis i enter it lists them on the client_diagnosis portal table with 1 in the Z field.  What does this mean?  Why do I have the feeling that I'm missing a serial field or relationship to creat an occurance on a table somewhere there I can track it back properly in reports, etc. 


                    • 7. Re: Portals and Related Value Lists

                           I'd need to know how your layouts use these table occurrences. That "z" field does not make since to me for a portal as it does not appear to be specific to a given client. I'd test what you have with two or more client records and make sure that this solution keeps diagnosis data for each client separate from the other.

                      • 8. Re: Portals and Related Value Lists

                             Yeah so I tried adding another client and a diagnosis and it didnt work, the portal is showing all the ones entered between the two clients.  I would have throught since I have the Client_Diagnosis table related by ClientID this would have worked properly. 

                             I'm not entirely sure what you mean when you say you need ot know how the layouts use the table occurrances.  If it helps I'll tell you what the data source for the above tables are

                             Client_Diagnosis Portal - Source= Client_Diagnosis Table

                             Diagnosis_Diagnosis 2 - Source = Diagnosis

                             Diagnosis_Diagnosis 3 - Source = Diagnosis

                             Diagnosis_Diagnosis 4 - Source = Diagnosis

                             Client_Diagnosis_Diagnosis 2 - Source =Diagnosis

                             Client_Diagnosis_Diagnosis 3 - Source =Diagnosis

                             Client_Diagnosis_Diagnosis 4 - Source =Diagnosis

                             Client_Diagnosis is related to the Client by Client ID and I have checked allow creation of records in Client_Diagnosis side

                        • 9. Re: Portals and Related Value Lists

                               I suggest that you go back to my previous posts and do what I suggested for the relationships, value lists, etc instead of this method.

                               Table occurrences are the "boxes" shown in your last screen shot. Every layout refers to one of these table occurrences in Layout Setup | Show Records From and every portal refers to one of these in Portal Setup | Show Related Records From. The relationship between the two table occurrences then controls what data appears in the portal--possibly filtered further with a portal filter expression. Posting a screen shot of your table occurrences and relationships, while very helpful, often needs additional info before someone else looking at it can tell which layouts refer to which table occurrences--a key detail in understanding the structure and function of your database.

                          • 10. Re: Portals and Related Value Lists

                                 Thank you very much for taking the time to explain this all to me.  I went back and altered what I had to match your directions and it's working great perfect now.  It was just referencing the the value lists slightly different, although it worked clearly i didn't need the additinal tables and portal table. 


                                 Thanks again!