13 Replies Latest reply on Apr 13, 2010 12:37 PM by elizas

    merge field / portal display with conditional value lists

    general

      Title

      merge field / portal display with conditional value lists

      Post

      Hello,

       

      We use filemaker 10 to track our interactions with clients.  I am using a conditional value list to limit the number of facilities we visit to show only those related to the selected company.  So, when one of our employees creates a new visit record, (s)he selects a company (pop-up field that displays data from visit layout, company id, and values from companies value list that uses values from the companies table), then a facility (pop-up field that displays data from visit layout, facility id, and values from facilities value list that uses values from the facilities table).  Companies are related one-to-many to visits by company id, and one-to-many to facilities by company id.  On the visits layout, I have a merge field that displays the facility name from the facilities table (<<facilities::facility name>>).  On various other layouts, I have portal records that also display the facility name from facilities table.

       

      WHEN COMPANY ID one-to-many to VISIT::COMPANY ID as well as FACILITY::COMPANY ID

      -displays only the first record associated with selected company.

       

      WHEN COMPANY ID one-to-many to FACILITY::COMPANY ID which is related to VISIT::FACILITY ID

      -displays the selected facility. 

       

      I would like to create a conditional value list that limits the number of available facilities by the selected company (not only do we have some employees specifically assigned one company, it also reduces the amount of wrongly assigned facilities when we compile reports).

       

      I would also like to have the various merge fields and portal records to display the associated facility name, not facility id, as facility id is sequentially generated, and has no meaning.

       

      Thanks for your help!

       

      FMP10

      Mac OS X 

        • 1. Re: merge field / portal display with conditional value lists
          philmodjunk
            

          Here's a thread on conditional value lists. See if it helps you out:

           

          Custom Value List?

           

          I can't quite follow the rest of your post. Adding the Facility Name field from the related table should be as simple as adding that field to your layout--if you have the appropriate relationship set up and I can't quite follow your description of your table relationships.

          • 2. Re: merge field / portal display with conditional value lists
            general
              

            Thanks, but unfortunately, that didn't help my situation.  But it did help another situation I am having.

             

            I have several tables in this database, but the key ones are:

             

            companies

            facilities

            visits

            employees

             

            The "visits" table serves as kind of a hub, so that the companies table, the facilities table and the employees table are all related (one-to-many) to visits through their respective id numbers.

             

            So, an employee logs on, creates a new visit, selects the company and the facility.  What I want to do is limit the number of facilities shown by company.  So, I created a conditional value list, where if "company a" were chosen, "facilities 1...70" are available for selection.  When I did that, my merge fields and portal rows displayed incorrect data.

             

            The merge fields and portal rows are on the various other table layouts we use, so that employees can quickly review pertinent information related to their activity.  Without the conditional value list, the merge fields and portal rows display the correct related information on the layout.  So, if employee went to "x" location of "1" company, "Location X - Company 1" would display on the portal rows and merge fields of the related layouts.

             

            However, with conditional value listing, if employee selects "x" location of "1" company, "Location A - Company 1" displays on the portal rows and merge fields of the related layouts.  "Location A" is the first record when sorted by Company 1.

             

            Same thing happens when Company 2 is selected; the first record related to the company displays on the merge fields and portal rows. 

            • 3. Re: merge field / portal display with conditional value lists
              philmodjunk
                

              Your description suggests a "disconnect" between your conditional value list field and the relationship used to display your data. You'll need to carefully examine the field you've formatted as a conditional value list--especially if you use a 2 column format to your value list.

               

              The field formatted as your conditional value list must be part of the same set of match fields that define the relationship used by your portal and other fields. It must also be storing a value that is the specified data type and not the value of a secondary field used in the value list. (If you have two columns, column 1 must be the value that functions as your key in the match field.)

              • 4. Re: merge field / portal display with conditional value lists
                general
                  

                The conditional value list with the "disconnect" was called "facility" that was a two-column list that only displayed "facility name."

                 

                I took your advice and changed all the merge fields and portal fields to represent the selected value in the "visits" table, not the data in the related "facilities" table.  However, this displayed only the facility id numbers, which are meaningless to our employees.  So, as an experiment I changed the "facility" value list to be a one-column list displaying "facility name."  This kept the relationship, and displays meaningful information.

                 

                The only down side is that I have to go back through our data and re-assign a facility name to the facility id.

                 

                This current "fix" or "work-around" seems like I'm "back-dooring" a relationship, and I'm worried that it's really not strong, or the correct way to do this.  Should I keep the relationship based on the respective id numbers (a unique, serial, indexed value) or move it to the facility name?

                • 5. Re: merge field / portal display with conditional value lists
                  philmodjunk
                    

                  It sounds like your relationship is based on the facilty name instead of the ID number. That would fit the behavior you describe. I don't recommend using name fields as match fields in a relationship. Typos and name changes become a major challenge to manage without "losing" related records if you use a name field in this way.

                   

                  You might also want to confirm that both match fields are the same type. If one is a number field and the other is a text field storing a number, you might encounter some issues.

                  • 6. Re: merge field / portal display with conditional value lists
                    general
                      

                    Ok, so I tried using the conditional value list example from FMP knowledge base (answer id 5833) and I'm encountering the same problem.

                     

                    I added the following merge fields to "Order Layout": <<menu::type>>, <<menu::item>>, <<type>>, <<item>>.  When manipulating the record, <<menu::item>> always displays the first value of the selected type.  I also enable the merge field as a button to Go to Related Record using Menu layout, and it always points to the first record of the selected type.

                     

                    What is more important to me is to keep my current relationships (based on id numbers) so that all my portal rows and merge fields behave as desired.

                     

                    Is there another way to create a conditional value list through script?  Do value lists work with found sets?

                     

                    • 7. Re: merge field / portal display with conditional value lists
                      philmodjunk
                        

                      At this point, all I can say is that it should work using ID number based relationships. If it doesn't then something isn't set up right and I can't tell from here what that something is.

                      Identifying that "something" is the challenge.

                       

                      Let's check some basic settings and see if the problem becomes identifiable:

                       

                      You have the following basic relationships?

                      visits::facilityid = facilities::facilityid

                      visits::companyid = companies::Companyid

                      visits::facilityid = companies 2::facilityid (companies 2 is a 2nd table occurrence of companies)

                       

                      In layout setup, your layout refers to visits.

                       

                      In Manage | Value Lists..., you have two value lists: "Facilities" and "Companies".

                      Facilities simply lists all facilities listed in the facilities table using a two column format:

                      Column 1 = facilityid

                      Column 2 = facilityName

                       

                      Companies is the conditional value list.

                      Specify two column format (both columns drawn from companies 2):

                      Column 1 = companyid

                      Column 2 = company name

                       

                      Specify "conditional values" starting from visits.

                      Now assign your value lists to facilityid and companyid on your layout. To see the facility and company names, you can simply add the related fields from facilities and companies.

                      • 8. Re: merge field / portal display with conditional value lists
                        general
                          

                        GREAT!!!

                         

                        I actually wanted it the other way around (company limits facilities), but it worked!  The solution was to create the second occurrence of "Facilities" and then match merge and portal fields to the second occurance.

                         

                        Thank you so much! 

                        • 9. Re: merge field / portal display with conditional value lists
                          philmodjunk
                            

                          "I actually wanted it the other way around (company limits facilities)"

                          Apologies, that should have been obvious and somehow wasn't. Glad you got it to work anyway.

                          • 10. Re: merge field / portal display with conditional value lists
                            elizas
                            Suppose we have Sales table with duplicate ContactID and want to display records in a sales portal with unique ContactID, we only need to define a simple number auto enter calculation field with the option "Do not allow to replace existing contents" checked.

                            The auto enter calculation needs to be:

                            DuplicateContactID (Sales table) = Count ( SALES_SELF__ContactID::ContactID )

                            This is a self-relationship of Sales table based on ContactID (Desired duplicate field).
                             
                            Then you can use this "DuplicateContactID" field in your portal relationship (SALES_SELF__Constant_DuplicateContactID) as:

                            Constant (SOURCE Table) > DuplicateContactID (DESTINATION Table)
                             
                             
                            As per the above mentioned example both fields used in relationship are from Sales table.
                             
                            Here, the constant is calculation field in Source table having value 1. So, with the help of this relationship, you can get all the Sales records with their unique ContactID
                            Eliza
                            • 11. Re: merge field / portal display with conditional value lists
                              TSGal

                              elizas:

                               

                              You have been sent a private message (top of this page - right side - X Messages).

                               

                              TSGal

                              FileMaker, Inc. 

                              • 12. Re: merge field / portal display with conditional value lists
                                jmvatc

                                Any idea as to why I am getting the facility name listed twice in its home record?

                                 

                                Everything works perfectly with the second occurrence of the "facilities" table.  However, when accessed via IWP, the facilities::facility name field is modified to:

                                 

                                "original name"

                                "original name"

                                 

                                And everytime after that the record is selected in the conditional value list, the original field is duplicated again.  Sometimes to:

                                 

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                "original name"

                                 

                                etc.....

                                 

                                But only in IWP.  Any solution to that?

                                • 13. Re: merge field / portal display with conditional value lists
                                  elizas

                                   

                                   

                                  In Filemaker we don't have option for display unique records in portal. But we can do this by using a simple auto enter calculation field.
                                   
                                  Suppose we have Sales table with duplicate ContactID and want to display records in a sales portal with unique ContactID, we only need to define a simple number auto enter calculation field with the option "Do not allow to replace existing contents" checked.

                                  Hope this would add value.Any suggestions are welcomed.