1 2 Previous Next 19 Replies Latest reply on Feb 3, 2011 4:12 PM by PecCars

    Cascading Dropdown List Issues

    BradenDouglass

      Title

      Cascading Dropdown List Issues

      Post

      Ok all, I am thinking that this might be a little difficult to explain but I will give it a whirl:

      Need: A layout that allows me to choose a Company name (dropdown list) then it will populate another field named address with all of the addresses associated with the company.  When I select the address I would like it to fill in the city, state, zip, etc from the address table.  If it is a new company, I would like to be able to type in the name and have it create a new record in the company table, and the same with the address table.

      What I have is both tables created along with another table which is just someone's name.  The relationship is between the companyName (unique) and the ship address. 

      Problem: I can get the company name to show but once I click on it, it automatically autopopulates the shipping fields with the first shipping value.  If I change the address value it will overwrite it in the shipping table; however, it will not change the rest of fields.

      It is almost like the other fields need to be repopulated after the change?  I know this isn't a really great explanation but any help would be golden, thanks all.

      What I want (in a nutshell)

      Customer Selection (Pop Up from customer tab) (able to add record back to original table if new)

      -> shipping address (Related to the specific customer, drop down list) (able to add record back to original table if new)

      -> have all of the remaining fields fill in depending on the address chosen (city, state, zip)

        • 1. Re: Cascading Dropdown List Issues
          philmodjunk

          You have a number of issues that are overlapping and some details are either missing or not clear. Let's take a run at what you should have step by step and maybe you can then spot where you need to change your design to get things working:

          A layout that allows me to choose a Company name (dropdown list) then it will populate another field named address with all of the addresses associated with the company.

          Since you have more than one address for a company, a related addresses table would seem to be in order and this can then give you a list of addresses for a selected company from which you can make a selection.

          CompaniesTable::CompanyID = Addresses::CompanyID   //CompaniesTable::CompanyID should be an auto-entered serial number.

          With this relationship, you can set up a conditional value list of addresses or you can setup a portal of addresses where you click a portal row button to select the desired address. For an introduction to conditional value lists, see this knowledgebase article:  http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list/session/L3NpZC9jaFJTNThkaw%3D%3D

          When I select the address I would like it to fill in the city, state, zip, etc from the address table.

          I'm not sure what table you have specified for your layout. This layout presumably refers to a table separate from your companiestable and your addresses table. We'll just call it "Main" for this discussion.

          You should have two more relationships for this to work:

          Main::CompanyID = CompaniesTable::CompanyID
          Main::AddressID = SelectedAddress::AddressID    //Addresses::AddressID should also be an auto entered serial number

          SelectedAddresses is an additional table occurrence of Addresses and you create it by clicking Addresses then clicking the button with two green plus signs in the relationship graph.

          Main::CompanyID should be set up with a two column value list where column 1 lists the values from CompaniesTable::CompanyID and column 2 lists the values from CompaniesTable::CompanyName. Main::AddressID would be set up with your conditional value list specifying Addresses::AddressID in column 1 and the Addresses::address field in Column 2. You'd specify "Use Only related records starting from Main" for this value list.

          You now have two options here: You can define a set of address fields in Main and use the Looked up Values field option to copy address data from SelectedAddress or you can just place the actual fields from SelectedAddress on your Main layout. If you use the looked up values option, address changes made in the addresses table will not change the addresses shown in existing records in Main unless you specifically choose to relookup the data. The changes would only appear in any new records you create--this gives you a "snapshot" of what address info was current at the time the record in Main was created. If you use the actual fields from SelectedAddress, the changes will automatically appear in existing records.

           

          • 2. Re: Cascading Dropdown List Issues
            BradenDouglass

            Sweet, thank you phil mod.  The knowledge base article is a great leaping point on this.  Take the article and what I would like to do:

            1. say if I wanted to add another item and type to the order table.  I want these new values to be copied back to the original menu table instead of them being random instances in the order.  The way the item is set up in the knowledgebase, this isn't possible.

            2. If I add a third field to both the menu and the order tables called chef, I would like once I have filled in the item field (the second field) for filemaker to auto-populate the chef field for me since the chef field is unique to each item ordered.  

            Does this help?  Thanks for the knowledge base article though, it is definitely getting me closer.

            • 3. Re: Cascading Dropdown List Issues
              philmodjunk

              1) This is most definitely possible. But it may take a scripted approach. It might also be easier to add a "new address" or "new company" button to to perform a script that assists the user in entering data in the new record.

              2) I've described two different ways to do this with the addresses in my previous post.

              • 4. Re: Cascading Dropdown List Issues
                BradenDouglass

                Phil,

                I have been trying to put your recommendations into action over the past couple of hours and for some reason I am coming up without the answer.  I was wondering if you had a second to look over what I have and provide some feedback?  The link looks a little odd but I can assure you it is fine (getcloudapp.com \a great way to share different files if you own a mac.  Thank you for your help!!

                http://cl.ly/2yj6

                • 5. Re: Cascading Dropdown List Issues
                  philmodjunk

                  None of your "ShipTo" records had assigned CustomerID numbers, so there were no related records in Ship To that could be used to populate the conditional value list of addresses.
                  The second new value list needed this option selected: Show only related values starting from Orderer. (Starting from Customer also works.)
                  Some of your ShipTO records did not have unique shipping ID's

                  Once those where corrected, I could select a customer and then see their list of possible shipping addresses in the ShipID field's drop down.

                  This also puts things in place so that you can use looked up value settings on your data fields to look up values from SelectedAddress--not ShipTo as you've currently specified.

                  • 6. Re: Cascading Dropdown List Issues
                    BradenDouglass

                    Ok, I am currently fixing the very first issue with the CustomerID being placed in the records (since this seems to be the largest headache).  Although the table knows there is a relationship between the two tables at the customerID column it won't auto enter the customerID serial when I select the customerName (under the shipTo layout)?  Wow, FileMaker has never given me this kind of brow beating before.

                    It seems, that if there is a relationship between the customerName on both tables along with the customerID number that IF I fill in the name with the customerName, it should fill in the CustomerID number automatically?  No?

                    • 7. Re: Cascading Dropdown List Issues
                      philmodjunk

                      You should be selecting the Customer ID not the name as your relationship is based on ID rather than name. That's why I've described two column value lists for this with the customer ID in column 1 and the name in column 2.

                      You could also use a portal to shipto on your customer address and if you have "Allow creation of records via this relationship" enabled, you can enter the addresses and the CustomerID value will be entered for you each time you enter data into the bottom blank row of this portal.

                      • 8. Re: Cascading Dropdown List Issues
                        BradenDouglass

                        Phil, is it possible for you to share what you did to fix it? I for some odd reason, am coming up short and need a visual representation to pick through?  You can email me it at g3funk [at] gmail.com

                        • 9. Re: Cascading Dropdown List Issues
                          philmodjunk

                          I uploaded a copy to here:  http://www.4shared.com/file/wzBI7H_y/ContactV2.html

                          Click the blue download button. Wait for 20 sec countdown, then click download link that appears.

                          • 10. Re: Cascading Dropdown List Issues
                            BradenDouglass

                            Ok, good, now we can be on the same page.  This is very much what I made before the entire thread was started.  Notice if you have multiple addresses that when you select a customer it auto fills in all of the fields regardless of the shipID selection.  If you change the shipID/shipAddress, nothing else changes, they remain the same.  

                            I need them to change to the specific shipID/Address and have the fields change with it.  Thanks for all the help man, you have been amazing!

                            • 11. Re: Cascading Dropdown List Issues
                              philmodjunk

                              You'll recall that I mentioned two methods for this in my earlier post.

                              Try this version:  http://www.4shared.com/file/bXRx_385/ContactV2_2.html

                              What you wanted is on the right with the original fields left untouched for comparison. I just added the fields from SelectedAddress directly to this layout. In fact, if you edited one of the right hand fields, those changes will be made directly to the appropriate record in the Ship To table without ever having to switch to the ship To layout.

                              Note: many businesses prefer the looked up value option as they want a record of what contact info was used at the time the invoice or order record was created so that they can better resolve issues where an order may have been shipped to the wrong location due to the fact that the updated contact info wasn't yet entered into the system.

                              • 12. Re: Cascading Dropdown List Issues
                                PecCars

                                All,

                                I have a similar problem as described above by Braden. I am migrating an MS Access 2003 app to Filemaker Pro 11. This is about two tables: Inquiry and Property with Inquiry::PropertyID and Property::PropertyID with autonumber.

                                The idea is I have a layout based on Inquiry in order to enter details of an inquiry. I put a drop down list for the properties, these being picked from the Property table and I allow the user to pick from them. I have implemented this via a dynamic value list, that picks the Property::Name. However, when the user picks a property from the drop down list, FM updates Property::PropertyName instead of assigning the corresponding ID to the Inquiry::PropertyID.

                                I could of course use directly the Inquiry::PropertyID for the drop  down list, but that looks silly picking a number for a property. I would  rather have the user pick from a list of property names.

                                MS Access calls this option Enforce Referential Integrity in a one-to-many relationship and works seamlessly there. In fact the MS Access solution works based on a cascaded sql query. Meaning behind the drop down list an sql query is issued that takes care in the background of pulling the property names, displaying them on the drop down list and assigning ONLY the corresponding PropertyID to Inquiry::PropertyID.

                                Any ideas for implementing this in FileMaker without breaking my fingers with some complex scripts ?

                                • 13. Re: Cascading Dropdown List Issues
                                  BradenDouglass

                                  Hmm Pec, is your question to just change the file so they can select the PropertyID (numeric) and the description? Guessing that you might be using FileMaker 10+, you can go to value lists => modify the value list that is associated with PropertyID => and click the checkbox on the right that allows the list to show another value alongside the number.  You can then make a second field next to the PropertyID that relates to whatever you want your users to see. That way as you choose the PropertyID field, a descriptor will show up in the related field that you just created.  Does that make sense?  Here is a working version from what Phil explained to me if you want to piece through things.

                                  http://cl.ly/4MNl

                                  • 14. Re: Cascading Dropdown List Issues
                                    PecCars

                                    thanks for quick response Braden ! Just took a look at your example.

                                    Almost there but not quite !

                                    I took the approach you suggested, so that in the layout I have Inquiry::PropertyID tied to a value list that picks Property::PropertyName as the "another value" based on PropertyID. So far so good, now ... when I pick an item from the drop down list it shows (correctly) the assigned ID in Inquiry::PropertyID.

                                    Here comes the point: PropertyID is irrelevant for the end user. He should only see the chosen property name on the screen, after Inquiry::PropertyID was set correctly in the background.

                                    Guess I need to use a screen field ... if FM provides that.

                                    1 2 Previous Next