13 Replies Latest reply on Feb 27, 2012 7:39 AM by MattLeach

    Calculated Value List?

    MattLeach

      Not sure if this is possible but wanted to run it by the group.

       

      I have a value list labeled Companies which is set to "Use values from field: Companies::CompanyName".

       

      There are multiple fields on a layout where the user would like the drop down to be a list of Companies based on their type, for example:

       

      FieldA - drop down of only companies where the company type = typea

      FieldB - drop down of only companies where the company type = typeb

       

      Is this possible?

        • 1. Re: Calculated Value List?
          PalmDBS

          Create constants in the Company table for each type you want to filter by, then, for each filtered drop-down create a self relationship to the Company table that includes the desired filter (Companies::CompanyID=CompaniesTypeA::CompanyID and Companies::CompanyType = CompaniesTypeA::TypeAConstant), then create your value lists based on the filtered table occurence (CompaniesTypeA::CompanyName)..

          • 2. Re: Calculated Value List?
            MattLeach

            I hope im not sounding too much like an amateur but what do you mean by constants?

            • 3. Re: Calculated Value List?
              PalmDBS

              No worries - FileMaker doesn't really deal in constants - I'm used to other programming languages.  My bad for using that terminology.  A constant is simply a field with a constant, unchanging value.  Let's say one of your types is "Vendor" - set up a new field called Companies:TypeVendor with an auto-enter value of "Vendor".  The auto-enter value will be automatically assigned on new record creation.  You will have to perform a Replace Field Contents to populate the field for all the records that existed prior to the field being created.  Do this for each type you have, then create the relationship as described in the prior post.

               

              Let me know if you have any further questions.

              • 4. Re: Calculated Value List?
                MattLeach

                I think i kind of get it, would you happen to have a sample showing how this works? Sorry, i'm more of a visual hands on person. Thanks

                • 5. Re: Calculated Value List?
                  PalmDBS

                  Working up an example now... quick question.  What is the base table for the layout that you wish to place the filtered company dropdowns?  Is it the company table, or is at a different table? 

                  • 6. Re: Calculated Value List?
                    MattLeach

                    It is a different table called Transactions

                    • 7. Re: Calculated Value List?
                      Malcolm

                      On 26/02/2012, at 1:58 PM, MattLeach:

                      I have a value list labeled Companies which is set to "Use values from field: Companies::CompanyName".

                       

                       

                      There are multiple fields on a layout where the user would like the drop down to be a list of Companies based on their type, for example:

                       

                       

                           FieldA - drop down of only companies where the company type = typea

                       

                           FieldB - drop down of only companies where the company type = typeb

                       

                       

                      Is this possible?

                       

                      Yes, it's possible. But the existing value list will not be useful. You will need to add the option to use related values only and have a relationship between the table that you are on and companies. That is two relationships, for type A and B. Create two value lists for typeA and another for typeB. In each value list you'll select the appropriate relationship for the filter.

                       

                      Malcolm

                      1 of 1 people found this helpful
                      • 8. Re: Calculated Value List?
                        PalmDBS

                        Here is a sample based on a real estate agent being related to a brokerage, preferred title company, preferred escrow company, and preferred home inspector.  Only one companies table exists, and the value list for the filtered company drop-downs are based on relationships from a "constant" value in the Agents table to the type field in the companies table.  Take a look and let me know if you have any other questions.

                         

                        Mike

                        • 9. Re: Calculated Value List?
                          MattLeach

                          I took a look at your example file and if i'm looking at it correctly, it looks like the constant you are referring to is basically a field where the user must select the company type in order for the list to populate based on the type?

                           

                          Using your example, if i were looking for a title company i would need to set the constant to Title and then the drop down would list the companies?

                          • 10. Re: Calculated Value List?
                            PalmDBS

                            No, in the example, I set the constant field when I created them.  I just

                            left them on the layout so you could see (and so I could do a replace field

                            contents if needed).  Those fields can be removed from the layout.

                             

                            The user, at this point, doesn't need to do anything for the brokerage and

                            preferred vendor drop downs to populate with the corresponding type of

                            company.

                             

                            Alternatively, you could have a setup like you just mentioned, where the

                            user has a dropdown of a related company type, then another dropdown

                            populates with companies of this type.  This possess some conceptual

                            problems and I would change the underlying framework.

                             

                            More information about your solution would be needed to determine the best

                            route to take.

                             

                            Do you need to provide many relationships per company type (ie multiple

                            inspection companies)?

                             

                            Mike

                             

                            Mike

                            • 11. Re: Calculated Value List?
                              MattLeach

                              I think i'm understanding it now and really hope i'm on the right track here, sorry for dragging this out if i'm off base:

                               

                              A constant is setup for each company type, then a relationship is setup for each different company type based on it's relative constant?

                              • 12. Re: Calculated Value List?
                                PalmDBS

                                Yep, exactly! :-)

                                • 13. Re: Calculated Value List?
                                  MattLeach

                                  Awesome!, thanks for your help!