1 2 Previous Next 20 Replies Latest reply on Oct 10, 2011 1:05 PM by arthursc

    Conditional Drop Down List??

    arthursc

      Title

      Conditional Drop Down List??

      Post

      The heading maybe incorrect but let me explain;

      I have 3 Tables;

      Estate

      Primary Location

      Secondary Location.

      Each ESTATE can have multiple PRIMARY LOCATIONS.
      Each PRIMARY LOCATION can have multiple SECONDARY LOCATIONS.

      I have an issue with SECONDARY LOCATIONS layout. There are 3 fields to this layout;

      ESTATE Name (Dropdown list from Estate Table)

      PRIMARY Location Name (Dropdown list from the Primary Locations Table)

      Secondary Locations Name.

      The Issue.

      When I select Estate Name it appears and populates the field as you'd expect.
      When I select Primary Location Name it displays ALL locations for ALL records in the Primary Locations Table and not those that releate to the Estate Name I have selected in the first field.

      How can I resolve this so that which ever ESTATE is selected from the first dropdown list, only the Location values relating to the selected Estate are displayed in the PRIMARY Location Name dropdown List

      Your help in this would be appreciated.

      Regards

      Colin

      Screen_Shot_2011-09-21_at_17.52.11.png

        • 1. Re: Conditional Drop Down List??
          philmodjunk

          Colin, I'm looking again at the relationship graph that you emailed me.

          On what occurrence of that graph (what box) is your layout based? SWDM_Outings?

          In what field are your selecting the Estate Name?

          And is that field set up to enter the name or a corresponding ID number such as __pk_Estate_ID?

          What I don't see is any relatinship that matches estates to locations here...

          • 2. Re: Conditional Drop Down List??
            arthursc

            HI Phil,

            Sorry the version you have is alittle out of date, so I m emailing you the updated version and an explaination of what fields and table to use.

            I will then update the post here so others can see.

            Regards

            Colin.

            • 3. Re: Conditional Drop Down List??
              arthursc

              Hi Phil,

              I have updated the relationship screen capture to show the relationships I have for this app. I have also removed the relationship with the Outings table for the time being.

              I have the Estate side of thing working on the secondary locations but I cannot get it to display the values in the primary location for those based on what estate I select in the 1st field.

              Hope this helps some more.

              Regards

              Colin.

              • 4. Re: Conditional Drop Down List??
                philmodjunk

                Estate name and Estate ID should not be the same value. One should be a name and the other should be an ID number. This isn't part of the issue for getting your value lists to work, but is an important detail in good database design. Consider what happens if someone buys an Estate and changes its name to something else or you mis-enter an Estate's name and later discover the mistake and have to correct it. Either way, changing the value in the name field will break the link to other related records--an issue avoided when you use an ID number.

                I cannot get it to display the values in the primary location for those based on what estate I select in the 1st field.

                We've been having issues where new image uploads do not appear when other users access the thread. I don't know if I am looking at the latest screen shot or not. (To make sure that a new image appears, edit the post to delete the first image. Save the changes, re-open the post for editing and then upload the new image...)

                Using what I see in the screen shot, if you select a value in TO_SWDM_Estate::Est_Name, fields added to your layout from SWDM_Locations should automatically display data from the matching record in Locations. If you have fields in SWDM_Estate that look up data from SWDM_Locations, they should copy data from their corresponding fields in locations.

                This assumes that SWDM_Locations::Estate_ID stores an Estate Name so that you get matching records and this is something that I recommend you change to set it up by matching ID numbers instead.

                • 5. Re: Conditional Drop Down List??
                  arthursc

                  Hi Phil,

                  I have deleted the image upload and then uploaded an upto date one.

                  Just to calrify; Looking at the imahe above I need a relationship as the following.

                  From TO_SWDM_ESTATE::__PK_ESTATE_ID (One to Many) to SWDM_LOCATIONS::__FK_Estate_ID

                  This will allow a user to select from drop down list in SWDM_LOCATIONS_LAYOUT the Estate Name.

                  They will then add the PRIMARY_LOCATION(S) for the estate they have just selected in the SWDM_LOCATIONS_LAYOUT.

                   

                  The Confusion from my point is what is the relationship I need to add for SWDM_SECONDARY_LOCATIONS

                  In this layout they need to select The Estate Name. This then displays the "available Primary Locations" for the estate they have selected.

                   The od thing is that I have done this on an Filemaker Example KB and it works, so this is almost certainly a relationship issue and my brain numbness!!

                  This is a sort of brick wall for me as what ever I do with the relationships and value list I cannot get it to work. I really hitting the preverbal brick wall on this one.

                  Regards

                  Colin

                  • 6. Re: Conditional Drop Down List??
                    philmodjunk

                    I need a relationship as the following.

                    From TO_SWDM_ESTATE::__PK_ESTATE_ID (One to Many) to SWDM_LOCATIONS::__FK_Estate_ID

                    This will allow a user to select from drop down list in SWDM_LOCATIONS_LAYOUT the Estate Name.

                    Actually, for your first, non-conditional value list, you do not need any relationship. You may need this relationship for other uses, but not to get your initial value list.

                    They will then add the PRIMARY_LOCATION(S) for the estate they have just selected in the SWDM_LOCATIONS_LAYOUT.

                    And where do they add these "primary locations" ?

                    Am I correct that for one Estate, you have several "Primary locations" and then each of these primary locations can have one or more secondary locations? This, on much smaller scale would be the same as having several countries, each of which are divided into provinces, and each province is then divided into counties... That analogy make sence here?

                    If so, you have one field where you select the Estate, another where you select the Primary location and a third where you select the secondary location. No relationship is needed for the list of estates if it is to list all estates, but then you need one relationship for the Primary locations and a different one for the secondary locations.

                    I think that you've got the first conditional value list, primary locations working. If not, set it up to list values from SWDM_Locations::__PK_LocationID, Show only related values starting from TO_SWDM_ESTATE.

                    Your second conditional value list, secondary locations, needs a different relationship than what you have here. Assuming that your locations value list is setup to enter a location ID into TO_SWDM_ESTATE::_FK_LocationID, you should add this relationship to what you already have:

                    TO_SWDM_ESTATE::_FK_LocationID = SWDM_Secondary_LocsByLocation::_Fk_LocationID

                    Then your second conditional value list can list values from SWDM_Secondary_LocsByLocation, Include only related values starting from TO_SWDM_ESTATE.

                    • 7. Re: Conditional Drop Down List??
                      arthursc

                      Hi Phil,

                      Thanks for the prompt reply.

                      Your absolutly correct in your statement;

                      "Am I correct that for one Estate, you have several "Primary locations" and then each of these primary locations can have one or more secondary locations? This, on much smaller scale would be the same as having several countries, each of which are divided into provinces, and each province is then divided into counties... That analogy make sence here?"

                      I have followed your instructions and I still cannot get this to work. I have emailed you the most recent copy and would appreciate it if you could do as you have described and highlight the TO in a different colour and create new value list with PC as a prefix so I can see where I am going wrong as I sure I am.

                      Kind regards

                      Colin.

                      • 8. Re: Conditional Drop Down List??
                        philmodjunk

                        When I open the file, I don't see the value lists that I have recommended here.

                        On which layout do you need these value lists?

                        • 9. Re: Conditional Drop Down List??
                          arthursc

                          Hi Phil,

                          Thanks for looking at this.

                          The files being used for this are;

                          SC_Interface_V3.fp7 - This file lists the Layouts, relationships, scripts and Value Lists

                          SC_Data_V3.fp7  - This holds the data.

                          SC_Resource_V3.fp7 - This holds the resource data (Deer Species, Diseases etc) Users will not be allowed to edit this DB.

                          The Layouts I refer to in the issue I am having are;

                          Layout Estates - This is where users enter the estate details.

                          Layout Locations - This is where the users selects the ESTATE from the dropdown list and enters PRIMARY LOCATIONS for that selected estate.

                          Layout Secondary Locations - This is where the user selects the ESTATE form the dropdown list and the "conditional value list" displays ONLY the PRIMARY LOCATIONS for that selected estate. it is here that the user then enters the SECONDARY LOCATIONS based on the selections I have just mentioned.

                          When I split (data separation) databases, I assume that relationships, value lists only work correctly or are selectable from the SC_INterface_V3.fp7 db, is this correct?

                          I am also unsure if the data is being sorted correctly for the "Estates".

                          Hope this helps.

                          Regards

                          Colin

                          • 10. Re: Conditional Drop Down List??
                            philmodjunk

                            Item 1

                            On layout locations, you have the wrong field on the layout. It should be SWDM_Locations::_FK_EState_ID and formatted with a drop down list of ID's in field1 and estate names in field 2 taken from your Estates table. You can then add the TO_SWDM_ESTATE::Est_name field to this layout to show the estate name instead of just the uninformative ID number here.

                            Item 2

                            Here again, you need to chang the field for specifying the Estate. Make it SWDM_SECONDARY_LOCS::_fk_Estate_ID.

                            Now the conditional value list:

                            It should list values from SWDM_Locations in both field 1 and field 2.

                            Field 1 would list __pk_LocationID and Field 2 would list: location_name.

                            Then specify Include only related values starting from SWDM_SECONDARY_LOCS.

                            One mental discipline that can help you set up conditional value lists is to ask yourself: "what do I need in order to set up a portal that would list the same items as what I want in my conditional value list? If you can figure that out, the portal's table occurrence becomes the source of values for your field 1 and field 2 settings. Your layout's occurrence (most of the time) becomes your "starting from" occurrence in the Include only related values option.

                            • 11. Re: Conditional Drop Down List??
                              arthursc

                              HI Phil,

                              Thanks for the update. I am alittle confused about the 1st paragraph.

                              On layout locations, you have the wrong field on the layout. It should be SWDM_Locations::_FK_EState_ID and formatted with a drop down list of ID's in field1 and estate names in field 2 taken from your Estates table. You can then add the TO_SWDM_ESTATE::Est_name field to this layout to show the estate name instead of just the uninformative ID number here.

                              I have changed the Estate field to Display Data From SWDM_Locations::_FK_EState_ID. 

                              But I am not sure which table you refer to in the next bit

                              and formatted with a drop down list of ID's in field1 and estate names in field 2 taken from your Estates table

                              Which table are are you refering to for "id's of field1" Teh Estate table? If do I use values from first field SWDM_ESTATE __PK_ESTATE_ID and also display value from second field SWDM_ESTATE EST_NAME and "show only values from second field"? 

                              The other bit also throws me.

                              You can then add the TO_SWDM_ESTATE::Est_name field to this layout to show the estate name instead of just the uninformative ID number here.

                              I see what your asking but not sure what you mean. add another field from TP_SWDM_ESTATE? if so that means I would have 2 estate name fields.

                              Regards

                              Colin

                              • 12. Re: Conditional Drop Down List??
                                philmodjunk

                                But I am not sure which table you refer to in the next bit

                                Since there is no relationship filtering this list of values, any table occurrence of Estates can be used for this list of values. They all refer to the same data source table and thus all produce the same list of values and names.

                                __PK_Estate_ID would indeed be field 1. Est_Name would be field 2. Choose any occurrence of Estates you want for these two and you'll get the same value list. Just make it the same Table occurrence for both fields.

                                if so that means I would have 2 estate name fields.

                                On the Layout Locations layout, you have one field for estate name in the copy that you sent to me. It is formatted as a value list. When you change this to be SWDM_Locations::_FK_EState_ID, you no longer have and estate name field on this layout. You have just a number field. Thus, you should add the field from TO_SWDM_ESTATE to this layout to display the name of the estate to which you have linked this location record.

                                You can resize and group these two fields, te EstateID and Est_name fields together so that they look and function like one field. The user need not actually see the estate number once they exit the drop down.

                                • 13. Re: Conditional Drop Down List??
                                  arthursc

                                  HI,

                                  Is it possible you could show me this on the copy I have sent you and send it to me?

                                  Regards

                                  Colin.

                                  • 14. Re: Conditional Drop Down List??
                                    arthursc

                                    Hi Phil,

                                    Many thanks for your example now that I have had a look and seen how your doing.

                                    I do have a question on how your example is done as appossed to the one in Answer ID 5833.

                                    http://help.filemaker.com/app/answers/detail/a_id/5833/~/creating-conditional-value-lists

                                    I used this example to test what I wanted to do and it worked all ok. The difference being from the fields used in the example to mine are that ID fields were used in mine and Text fields in the example.

                                    Is this the only difference and if the example were to use id fields in the method I use them would we still have to "mask" that field with TO field (ESTANE NAME) that you have done?

                                    Regards

                                    Colin.

                                    1 2 Previous Next