6 Replies Latest reply on Jul 22, 2012 11:37 AM by philmodjunk

    Auto fill drop down value list

    sk

      Title

      Auto fill drop down value list

      Post

      Please see attached screen shot for a visual reference.

      I need help to auto populate a field with a drop down list if there is only one related value. I tried a script trigger and a ValueCount, but later found out that ValueCount only counts the number of items in a checkbox set and not a drop down value list.

      I just want to make it simpler for the users, so that they don't have to enter the Quote ATTN field to select the contact name if there is only one related contact. Of course they would have to if there is more than one related contact that has placed the ordered.

      Any help would be appreciated greatly!

      quoteATTN.jpg

        • 1. Re: Auto fill drop down value list
          philmodjunk

          but later found out that ValueCount only counts the number of items in a checkbox set and not a drop down value list.

          Value count counts the values in any list of values that you pass to it, this can indeed be all the values in a value list, if you use valuecount with the valuelistItems function--but you don't need that option here:

          Use this expression for the auto-enter calculation:

          If ( Count ( Contacts::ContactID ) = 1 ; ContactID )

          You can specify any field from the related contacts table as long as it is a field that is never empty.

          • 2. Re: Auto fill drop down value list
            sk

            OK I tried that but it is still not working.

            Please see attached screenshot of the calculation. Not sure what I'm missing...

            • 3. Re: Auto fill drop down value list
              JimMac

              Check this post for similar idea.  http://forums.filemaker.com/posts/611472e764 

              and...

               

              If you select Calculated value, you can edit the field value in Browse mode (ifProhibit modification of value isn't selected). The value is calculated when you create a record, or when one of the referenced fields changes and the destination field is empty. (However, if you select Do not evaluate if all referenced fields are empty in the Specify Calculation dialog box, the value isn't calculated when you create a record and all fields referenced by the calculation are empty. Instead, the value is calculated when one of the referenced fields contains a value.)

              Jim...

              • 4. Re: Auto fill drop down value list
                philmodjunk

                "didn't work" doesn't tell us HOW it failed--a key detail that can help us to help you.

                Also, adding or changing an auto-enter calculation doesn't affect the value in an existing record, only in a new record that you create, so make sure you test this by creating a new record.

                • 5. Re: Auto fill drop down value list
                  sk

                  Your right I should have explained better. I did find a way to do it: not sure if it is elegant, but it works.

                  As I couldn't use the auto enter calc, since the user could be changing or adding additional project contact types on the fly and not just when the record is first created, I had to find a way to bring the data over from the primary calculation field that was referencing the related table to the text field that had the drop down list, in case there were more than one project contact assigned the type of "ordered by".

                  What I did was apply a script trigger to the the project contact type field, so that when the user modified it a script ran to commit the record and then go to the calculation field. The calculation field also had a script trigger that fires when entered. This script checked first to see if the calculation field was not empty, and if it wasn't, it set a variable, and then set the text field with that name that was in it. If it was empty it would go to the text field and clear it. I did the last step to clear any name in the text field if the calculated field was empty, so if the user deleted the project contact it would clear this field to avoid any confusion.

                  So, now if there are more than one "ordered by" types the user will need to go to the Quote ATTN text field and select from the drop down value list which person they will send the quote to, and if there is only one "ordered by" type it auto fills with that contact's name.

                  I appreciated you help gurus!

                  • 6. Re: Auto fill drop down value list
                    philmodjunk

                    I couldn't use the auto enter calc, since the user could be changing or adding additional project contact types on the fly and not just when the record is first created,

                    I would think that this could also create issues for you when using your scripted method--which seems more complex than it needs to be here. An auto-enter calc should work to enter the value that is correct at the time the record is first linked to the related table and then a script trigger could update this at any point when data in the related table is modified--but if this is a concern then maybe should not be using any kind of "data copy" method here.