11 Replies Latest reply on Jan 27, 2011 5:38 PM by ryan9721

    Form: Selecting records based on 2 dropdown boxes

    ryan9721

      Title

      Form: Selecting records based on 2 dropdown boxes

      Post

      Hi all,

      I've been looking through the forums/Google and have  found people with similiar questions to mine, but so far I haven't come  across the right solution (or perhaps I just didn't understand). I've  been working on this for way to long, so any help would be appreciated!

      ----------------------------

      I have this table:
      Contacts (CustomerID, Company, ContactName, Address, Phone, Email, Time Zone)

      CustomerID  is the Primary Key. I have 1000+ records in this table. Multiple  records exist with the same Company value (multiple contacts within one  organization). I would like to create a form with the following functionality:

      - The user selects a Company from a dropdown. Based upon this selection, the form populates  the Contact dropdown with all contacts (ContactName) within that  organization.

      - The user selects a Contact from a dropdown.

      - Based  upon the Company and ContactName selected, the form displays all information  associated with that record (Address, Phone, etc.).  This information could be edited and the table updated from the form.

      ----------------------------

      This seems like it should be easy to do...thanks in advance for your help!

      -Ryan

        • 1. Re: Form: Selecting records based on 2 dropdown boxes
          Abhaya

          Hello Ryan,

           You hv  a Contact table with Layout.OK

          Make a new layout that coming from Contact.

          Make a  selfjoin for contact table by there company name.(Say Contact_self)

          Put there Company name  as dropdown, Value from contact table company name field.

          put there contact name field as dropdown  value from Contact _self  table Contact name field(In this pop_up box just check include only related values starting from and fill in the box  as contact (table name))

          This will show u

          for  1 Company name corresponding contact name  in dropdown list .

          then write a script that find all the related field from the contact table and put in the form layout.this script must be attached by script trigger(as on object save)

          i hope this will bring ur Ans.

          • 2. Re: Form: Selecting records based on 2 dropdown boxes
            ryan9721

            Thanks Abhaya, I'm working through the steps you outlined right now. Could anyone give me direction on the script?

            Thanks!

            -Ryan

            • 3. Re: Form: Selecting records based on 2 dropdown boxes
              philmodjunk

              I'm not sure from your post where the process breaks down for you. Your title mentions sorting the records, but your example describes finding the records.

              Can you get the value lists to work? If not see this thread:  Custom Value List?

              Is the problem in getting the script to find your records.

              Make the fields you've formatted with drop downs fields with global storage and use this script:

              Enter Find Mode []
              Set Field [Contacts::Company ; Contacts::gCompany] //gCompany is your global drop down list of companies
              Set Field [Contacts::ContactID ; Contacts::gContactID] //gContactID is your global drop down of contacts
              Set Error Capture [on]
              Perform Find[]
              Sort [no dialog ; restore] //optional

              • 4. Re: Form: Selecting records based on 2 dropdown boxes
                ryan9721

                Hi PhilModJunk,

                You are correct, I guess what I'm trying to do is find the correct record based on the two dropdown box values.

                I am getting value lists to work, so that is progress. I don't understand your instruction about global storage. The field I've formatted with dropdown fields are Company and ContactName - if I make them fields with global storage wouldn't that disable their functionality (storing various contact/Company names)?

                Thank you for your help!

                -Ryan

                • 5. Re: Form: Selecting records based on 2 dropdown boxes
                  philmodjunk

                  Yes it would and this would be a good thing. You should define new fields just for this purpose and add them to your layout. You wouldn't want to use the same fields for both purposes (Data entry and searches). If you use the same fields for both, its very easy to accidentally modify the current record when you thought you were actually performing a search.

                  • 6. Re: Form: Selecting records based on 2 dropdown boxes
                    ryan9721

                    I created global felds named gCompany and gContactName within the Contact table (all I did was create a new field, go to field options, and check use global storage...was there something else I was supposed to do?). There is no data in the column.

                    When I insert the global fields onto the layout and follow the steps that Abhaya outlined to get the value lists to work again, I run into problems. The gCompany dropdown - linked to values from the Contact table Company field - displays all companies correctly. However, the gContactName dropdown - linked to values from the Contact _self table ContactName field (including only related values starting from the Contact table) - does not display Contacts from the selected Organization in the dropdown. Instead, it displays the Contacts from the Company in the record currently being displayed on the layout. If I scroll through the records on the layout the contacts will change in the gContactName dropdown box, but selecting a different Company from the gCompany dropdown does not change the gContactName dropdown values.

                    I just tested using the ContactName (not global) field again on the layout and it is also now failing to display contacts based on the Company selected. Therefore I'm assuming I've done something wrong in setting up the gOrganizaton field on the layout (if I switch gCompany with just Company the value lists work correctly again). Sorry to go backwards, but any idea on what I did wrong?

                    Thanks!

                    -Ryan

                    • 7. Re: Form: Selecting records based on 2 dropdown boxes
                      philmodjunk

                      Update your relationship to match gCompany to Company instead of Company to Company. That way the company you select in the global field will control the values listed in the contacts drop down.

                      • 8. Re: Form: Selecting records based on 2 dropdown boxes
                        ryan9721
                        Oh of course...thank you sir! Okay, now I'll work on using the script you provided, thank you so much for the help. -Ryan
                        • 9. Re: Form: Selecting records based on 2 dropdown boxes
                          ryan9721

                          I feel like an idiot, but how did you get the semicolon in Set Field [Contacts::Company ; Contacts::gCompany]? I thought it was through a calculation, but I don't see it.

                          • 10. Re: Form: Selecting records based on 2 dropdown boxes
                            philmodjunk

                            You don't enter it at all.

                            You have two specify buttons that appear when you add Set Field to the script or when you click it in the script editor.

                            Click the upper button and select the field to the left of the semi colon.

                            Click the lower button and enter the expression to the right of the semi colon.

                            After you click OK, you'll see the step as I typed it into the eariler post. FileMaker will have supplied the semi colon for you.

                            • 11. Re: Form: Selecting records based on 2 dropdown boxes
                              ryan9721

                              Thank you Phil and Abhaya, I'm up and running with this part of the project! I have a question on my next step, but I'll open a new thread for the new topic.

                              Thank you again,

                              -Ryan