9 Replies Latest reply on Feb 28, 2012 10:22 PM by philmodjunk

    use of portal with a conditional value list

    RachelG

      Title

      use of portal with a conditional value list

      Post

      Hello,

      Attached is a screen shot of the relevant tables to hopefully make my question easier to understand.

      For data entry purposes I want to do a portal for the table Ttn. However, the Orgnztn_Srvc_ID_fk is not a field that will make sense to users when attempting to enter information. Instead I would like to have the column Srvc_ID appear (to be used with a value list) that restricts the list to only the applicable services offered by this organization (previously entered in a separate tab). Users could then select one of the services from the popup and enter tuition information. There will be instances of multiple tuition entries per service.

      Do I need to add the Srvc_ID_fk to my Ttn table to do this? And how would I create my conditional drop down list of services based on responses from data entry?

      Thanks so much for any time spent on this.

      RachelG.png

        • 1. Re: use of portal with a conditional value list
          philmodjunk

          Orgnztn_Srvc_ID_fk May not make sense to users but it can still be used in a value list. You can set up a value list where the _fk field is listed as "field 1" and a name field users can make sense of as "field 2". Then they can select an organization by name from the value list, but it enters an ID number.

          • 2. Re: use of portal with a conditional value list
            RachelG

            Given the hours I have spent on this I clearly don't understand what you are describing. 

            I have searched the forum and tried to follow other instructions you've given on creating conditional value lists, specifically http://forums.filemaker.com/posts/65e97cc22c.  I have not been successful.

            I have done the following:

            File|Manage Value|Lists|New|Name_Value_List, Select use values from field|Use Values from first field, Select table Orgnztn_Srvc_Inf,  Select Orgnztn_Srvc_ID_pk as first field.

            I check also display values from second field table Orgnztn_Srvc_Inf field Srvc_ID_fk.

            I select option to include only related values starting from: I can't select the current table (Orgnztn_Srvc_Inf) which is where the values are stored that users enter so I select the table Ttn which is where I want to restrict the selections users can make based on the data entered in Orgnztn_Srvc_inf 

            I keep sort values by first field just to see if this will work.

            I created a portal to enter data into table Ttn. I assign a pop up control to the field Orgnztn_Srvc_ID_fk to display values from my value list. I select to display data from table Orgnztn_Srvc_inf field Orgnztn_Srvc_ID_pk.

            The only thing that will appear in the pop up is the first Orgnztn_Srvc_ID_fk for an org when there are at least two to 5 options that should appear dependng on previous data entry.

            If anyone can shed some light on this that would be great. 

            Thanks

            • 3. Re: use of portal with a conditional value list
              philmodjunk

              What I described in my last post would not be used in a conditonal value list. It would be simply a value list for selecting an organization. I was responding to this statement at a point in time where I had very little time to post anything in response to your question: "However, the Orgnztn_Srvc_ID_fk is not a field that will make sense to users when attempting to enter information." My point being that this is true of any ID number and the simplest solution is to pair it with a name field to produce a value list based on two fields.

              As I understand it, YOu want to first select an Organization and then select from a list of services appropriate for the selected organization. Is this correct?

              If so, then step one is to select organization from a value list that enters the ID number into the Orgnztn_Srvc_ID_pk field, then you'd select a service offered by that organization in your conditional value list. If I have that right let me know and I'll post some details.

              If, on the other hand, you want to select a service and from there choose from a list of organizations that offer that service, that can also be set up.

              In both cases, I'm assuming that each service offered by an organization is recorded as a record in the Organization_Srvc_Inf table. If an organization offers 5 different services, this table would have 5 records--each with a different service ID, but all with the same organization ID.

              • 4. Re: use of portal with a conditional value list
                RachelG

                Hi Phil,

                Please see my answers in blue:

                As I understand it, YOu want to first select an Organization and then select from a list of services appropriate for the selected organization. Is this correct?

                Correct. I have already done this. The list of services that is offered by each organization is recorded in a table Orgnztn_Srvc_inf.  This table has a pk of Orgnztn_Srvc_ID.

                If an organization offers 5 different services, this table would have 5 records--each with a different service ID, but all with the same organization ID.

                Correct, 5 rows entered into the table each with the same org ID but different service IDs.  They would also all have unique Orgnztn_Srvc_IDs which is an auto-generated number pk I created to track this info instead of looking at a combination of the org_ID and service_ID fields.  

                What I want to do now is provide a means for users to select from this list of services they entered to add tuition information.  E.g. Org A offers service 1, service 3, service 5. To enter tuition information, I want a user to be able to select only service 1,3 or 5 from a pop up menu to eliminate the possiblity that tuition info could accidentally be entered for a service not offered at this organization.  

                Thanks


                • 5. Re: use of portal with a conditional value list
                  philmodjunk

                  I'm assuming that this data entry is taking place on a layout based on TTN.

                  The first requirement is a relationship like this:

                  Ttn::Orgnztn_ID_fk = Organization_Srvc_Inf::Orgnztn_ID_fk

                  Since is is a new relationship, you may need to add a new occurrence of Organization_Srvc_Inf for use with this value list to keep your current relationship intact.

                  Format Ttn::Orgnztn_ID_fk with a value list that draws values from your Orgnztn table. Make field 1 Orgnztn::Orgnztn_ID_pk Make field 2 Orgnztn::Orgnztn_nm (assuming here that this is a name field). This is the field that set's up the "filter" needed for your relationship to return only related records from Organization_Srvc_Inf. At this point, if you placed a portal to this table occurrence on your layout, you'd see just the records for the selected organization. This is a good test to do to see if your relationship is set up correctly, but you do not need the portal to make this work.

                  Now open up define value lists and specify that your values be taken from Organization_Srvc_Inf with Organization_Srvc_ID_pk as field 1 and whatever field in Organization_Srvc_Inf that can serve as the name field to name the service for that record. Final step is to specify "include only related values starting with Ttn".

                  Keep in mind that the names I am using for Organization_Srvc_Inf  must be changed if you have to keep the current relationship in place for other uses in your database. If you create a new occurrence of Organization_Srvc_Inf, you'll need to refer to it by name in the value list set up.

                  • 6. Re: use of portal with a conditional value list
                    RachelG

                    I'm assuming that this data entry is taking place on a layout based on TTN.

                    Correct

                    Since is is a new relationship, you may need to add a new occurrence of Organization_Srvc_Inf for use with this value list to keep your current relationship intact.

                    Yes I set this up.

                    Format Ttn::Orgnztn_ID_fk with a value list that draws values from your Orgnztn table. Make field 1 Orgnztn::Orgnztn_ID_pk Make field 2 Orgnztn::Orgnztn_nm (assuming here that this is a name field). This is the field that set's up the "filter" needed for your relationship to return only related records from Organization_Srvc_Inf. 

                    I did this.  I already hd a value list like this created and simply reused it for Ttn::Orgnztn_ID_fk 

                    At this point, if you placed a portal to this table occurrence on your layout, you'd see just the records for the selected organization. This is a good test to do to see if your relationship is set up correctly, but you do not need the portal to make this work.

                    Yes I tested this and it worled as you described though I was only able to select the name of the organization from a pop up list.  The information for the services field remained blank regardless of what was selected in the org id field.  This may be my 1st problem.

                    Now open up define value lists and specify that your values be taken from Organization_Srvc_Inf with Organization_Srvc_ID_pk as field 1 and whatever field in Organization_Srvc_Inf that can serve as the name field to name the service for that record. 

                    Problem number 2- I have no field in the Orgnztn_Srvc_inf table that can serve as the name field to name the service.  I only have a service ID field which in other data entry layouts is assigned a value list of services (from a services table) so that the names appear.  

                    Keep in mind that the names I am using for Organization_Srvc_Inf  must be changed if you have to keep the current relationship in place for other uses in your database. If you create a new occurrence of Organization_Srvc_Inf, you'll need to refer to it by name in the value list set up.

                    Problem number 3- I don't think I understand what you mean here.  I had to create a new occurrence of Orgnztn_Srvc_info so I need to use that new occurrence for the value list?

                    • 7. Re: use of portal with a conditional value list
                      philmodjunk

                      this may be my first problem.

                      I see no problem here from what you describe. Just make sure your portal is to the new occurrence of the join table, not the previous one.

                      Problem 2.

                      This is a problem. A conditional value list of services requires that you have a table with two indexed fields. One you have: Organization_Srvc_ID_pk and the other is not present in your table. If the table where you find service names is a related table, you can refer to the field from the related table for your service name. (Since this is obviously a join table, I should have spotted that issue in my last post.)

                      Problem 3.

                      Yes, you need to refer to the new table occurrence in the value list setup. If you refer to the original table occurrence, you are specifying a different relationship than the one needed for your conditional value list. In addition, to get that service name, you'll need to add and link in an occurrence of that table as well.

                      You'll end up with these relationships. I'll supply some table occurrence names for the example but you can use your own names as long as you also use them in the value list setup:

                      Ttn----<Organization_Srvc_Inf 2>-----ServicesTable 2

                      In define value lists, field 1 would specify: Organization_Srvc_Inf 2::Organization_Srvc_ID_pk. In Field 2, specify your name field from Services Table 2 and you would still specify Ttn as the "starting from" field.

                      • 8. Re: use of portal with a conditional value list
                        RachelG

                        I am sorry to report that I simply can not get this to work. I have tried everything and I have been over all of our posts on this with a fine tooth comb. There is something that isn't right and at this point I am not even sure how to troubleshoot this. Since I have no choice but to figure this out I am trying to start fresh by reiterating exactly where I'm at right now.

                        1) Orgnztn_Srvc_Inf 2::Orgnztn_ID_fk = Ttn::Orgnztn_ID_fk
                        Orgnztn_Srvc_Inf 2::Srvc_ID_fk = Srvc_Lst 2::Srvc_ID_pk

                        Orgnztn_Srvc_Inf::Orgnztn_Srvc_ID_pk = Ttn::Orgnztn_Srvc_ID_fk

                        2) I have a conditional value list set up with a second occurrence of
                        Use values from first field Orgnztn_Srvc_Inf 2:: Orgnztn_Srvc_ID_pk

                        Also display values from second field (second occurrence of)
                        Srvc_Lst 2::Srvc_Nm (my name field for the service)

                        I have Include only related values starting from: Ttn (This is the table where I would like to use my conditional value list to enter info)

                        Show values only from second field

                        3) I want to use the conditional value list with a popup to enter info into the Ttn table.

                        I apply my conditional value list to the pop up select tool and choose to display data from Orgnztn_Srvc_Inf 2::Srvc_ID_fk

                        When I do this the field is blank though there is data for 2 services that should appear in the popup.

                        4) I change my choose display data from to be Orgnztn_Srvc_inf::Srvc_ID_fk.
                        I get one service ID that appears in the field (no name) and when I try to select the pop up I get no values defined.

                        Any help on what to do to fix this would be appreciated.

                        • 9. Re: use of portal with a conditional value list
                          philmodjunk

                          "...apply my conditional value list to the pop up select tool and choose to display data from Orgnztn_Srvc_Inf 2::Srvc_ID_fk"

                          This field should display data from a field in Ttn, not the field serving as a source of values. You are editing a record in Ttn, not Orgnztn_Srvc_Inf. From your screen shot, that would be: Ttn::Orgnztn_Srvc_ID_fk.

                          "When I do this the field is blank though there is data for 2 services that should appear in the popup."

                          You must first select an organization in the Ttn::orgnztn_ID_fk field before a value list will appear in the pop up.