1 2 3 Previous Next 33 Replies Latest reply on Apr 11, 2014 2:13 PM by philmodjunk

    Conditional Value Lists

    rgennaro

      Title

      Conditional Value Lists

      Post

           My Objective:

           In the Find and Browse Mode, I want the user to be able to complete any of the fields and the values in the other fields dynamically change. 

           Problem:

           I receive an error message of "<no values defined>".

            

           Steps Taken:

           1. Read material on creating Conditional Value Lists;

           2. Created 6 "Self" Relating Tables;

           3. Indexed the Fields being used;

           4. Created Value lists from the Indexed Values; and

           5. Formated the Fields on the "Single Unit Overview" Layout Form to show each Field's respective Value Lists.

           Notes:

           a. I'm using a MacBook Pro OS Ver. 10.9.2.

           b. I can deliver Screen shots of each Step.

            

           Thank you in advance for your help. Rock

            

            

            

            

      Single_Unit_Overview.jpg

        • 1. Re: Conditional Value Lists
          philmodjunk

               1. Read material on creating Conditional Value Lists;

               Where those from links that I provided? If so, are you using the method described as "Option 2" in the forum tutorial and also as the only method in the KnowledgeBase article?

               Do the value lists work if you test this layout when in Browse mode?

               Screen shots of the relationships or detailed descriptions would be helpful. And it will help to identify the table occurrence selected in Layout Setup | Show Records From for the layout in your screen shot.

               A screen shot of the manage | Value list dialog where you selected the "include only related values..." option for at least one value list would also be very helpful.

          • 2. Re: Conditional Value Lists
            rgennaro

                 Please forward to PhilModJunk. 

                  

                 Thank you for your help; I apologize for my slow response time.   

                 Below are responses to your questions:

                 1. I was attempting to use the Method described in the KnowledgeBase for creating Conditional Value Lists.

                 2. I found and read the "Option 2" forum tutorial that you described. 

                 3. No, the Value Lists do not work in the Browse mode either.

                 I will post the 3 Screen shots that you requested.

            • 3. Re: Conditional Value Lists
              rgennaro
              /files/469ca720fe/Value_List_-_Street.jpg 1162x978
              • 4. Re: Conditional Value Lists
                rgennaro
                /files/7d99c488b8/Value_Lists_Definitions.jpg 2314x722
                • 5. Re: Conditional Value Lists
                  rgennaro

                       Thank you again for your help. Rock

                  • 6. Re: Conditional Value Lists
                    rgennaro

                         PhilModJunk also asked for the below screenshot.   Thank you for your help learning and implementing Conditional Value Lists.  Rock

                    • 7. Re: Conditional Value Lists
                      philmodjunk

                           But You left out one detail. For the layout shown, which of the table occurrences shown in your screen shot of your relationships is listed in Layout Setup | Show Records from?

                           It looks like you have only one table in your database, with 6 different Tutorial: What are Table Occurrences? of this one table. Is that the case?

                           That seems a very unusual design for your database.

                           A conditional value list uses the relationship between the table occurrence named for Field 1 in the value list (TABC Mixed Beverage Report) and the table occurrence specified as the "starting from" table (TABC Mixed Beverage Report 2). The pair of occurrences you have selected here would typically be selected for a Layout that specifies TABC Mixed Beverage Report 2 in Layout Setup | Show Records from. On such a layout, the value list would list data from Location for all records with the same value in the TABC Permit Number field. But the selection of Location from TABC Mixed Beverage Report 2 as the second field does not make sense to me. You are listing the same data in both fields from two occurrences of the same table and that seems very strange here.

                           If your layout is based on TABC Mixed Beverage Report 2, try, as a temporary test, clearing the "also display values from..." check box and see what your value list does.

                      • 8. Re: Conditional Value Lists
                        rgennaro

                             Please forward to PhilModJunk.

                             PhilModJunk,  there is only one Table.  Originally, there were 6 Table Occurrences.  The field entitled, "Location Address" in the First Layout, the screenshot that I included in my original question,  was being being pulled from the "TABC Mixed Beverage Report 2".

                             Originally, I believed that I needed six Table Occurrences in order for all of the fields in the First Layout, to dynamically change as the User completed any one of them.  After reading your response and the material you wrote about Table Occurrences, I deleted those Table Occurrences.

                             There must be a simple way to accomplish my objective. I thought the solution was Conditional Value Lists but it maybe a custom script upon existing any of the fields in the First Layout. Therefore, I'm open to any ideas.

                             I have check marked "Auto-complete" in the Inspector so, as I type into any of the Fields, the list of values shorten for that field only.  I also want the other Fields, in the "First Layout" to shorten.  For example, when the User types, "Bob's Steak & Chop House", I want both the Location City Field and the Location Address Field to shorten to only include Bob's Steak & Chop House locations.  Currently, each field lists all of the "values" in that Field in the database.  

                             What am I doing wrong? What am I missing? 

                             Thanks again for your help,

                             Frustrated 

                        • 9. Re: Conditional Value Lists
                          philmodjunk

                               There is no need to "forward anything" wink

                               When you post here, I'll see the thread pop back up into recent items where I 'll open and respond. If you don't get a message after a few week days have gone by, feel free to click the "Caulkins Consulting" icon and send me a private message with a link to your thread. (I comment on 1,000s of threads here, so help me to help you by including that link so that I can easily re-open your thread.)

                               I'm afraid that I'm not fully able to picture what you want to do here. Let's break this down into small steps so that I get a clearer picture...

                               You now have simplified this down to a single table occurrence and appear to have just one table in your solution.

                               Do you start by creating a new record?

                               And then you enter data into which field, the Location address field?

                               And if that Location Address field exactly matches the location address field of an existing record in your table, you want other fields (city, state and zipcode?) to auto-fill with data from that previous record with the same address?

                               If that's correct (I'll wait and see if it is), there are several options that can be made to work, but I suggest that you take a careful look at setting up at least two actual tables where each specific address appears only once that is then linked to your permits table where you may have multiple permits all linked to the same record in the table of addresses. Should you ever need to update an address--such as to correct a data entry error, you can make a single correction to a single record in the table of addresses and all the permit records will automatically update to show the corrected address.

                          • 10. Re: Conditional Value Lists
                            rgennaro

                                 This Table is for Review Only by most Users.  I update it monthly with approx. 10k records. 

                                 You stated: "And if that Location Address field exactly matches the location address field of an existing record in your table, you want other fields (city, state and zipcode?) to auto-fill with data from that previous record with the same address?"

                            The above statement is very close. Most Users will not know the exact address or trade name but as they type, their choices narrow automatically.  So by using the drop down arrows in related fields they will be able to "select" an exact Trade Name or Address and City and thereby accurately select the Location for which they are looking.  The database also contains chain restaurants that have the same Trade Name but are in different locations and have different Permit numbers for each location.

                            I agree, this database should have three distinct tables. There is a lot of unnecessary duplicate information.  Originally, I formatted the database, this way, to make updating, the database, an easy one step process.  The original information is made available sequentially; each month the results for each Permit is published.  The actual database is not accessible. 

                            If you know of an easy way to subdivide each monthly CSV report and post the updates to their respective tables, I'm all in!   First, the License Holders and their information; second, the Permits and respective locations; thirdly, the Reporting Dates for each Permit and corresponding information.  Is there a simple way to copy and then remove the unnecessary fields from each of the copies?

                                 First help me auto-complete the related fields in the First Layout. Unless you see an easier way for me to tackle this.

                                 Appreciatively, Rock

                            • 11. Re: Conditional Value Lists
                              philmodjunk

                                   Sorry, but I still do not have a clear understanding of what you are trying to do with this database.

                                   I cannot tell if you want to facilitate the creation of a record for a brand new location (but which might be in the same city or have the same zipcode as another location or if you are trying to enter data for a new record that has exactly the same address as another record previously created in the database.

                                   

                                        You stated: "And if that Location Address field exactly matches the location address field of an existing record in your table, you want other fields (city, state and zipcode?) to auto-fill with data from that previous record with the same address?"

                                   

                              The above statement is very close. Most Users will not know the exact address or trade name but as they type, their choices narrow automatically.

                                   I will now point out that as the auto-complete feature narrows the list of options down to a single value, they will have selected a value that exactly matches an existing value in the table, thus, the entering matching data that you describe is what I describe, but what happens once you have entered/selected that matching value is what is not clear.

                                   Note: If you just want to speed up data entry with auto-complete, you don't need to use any value list at all, you can select the "auto-complete using existing values" check box for the field and still leave the field formatted as an Edit Box.

                              • 12. Re: Conditional Value Lists
                                rgennaro

                                     Phil, I'm not comfortable discussing this online anymore.  

                                     I have no one else to talk to about this project.  Can I contact you privately?   This database and application are unique.  It has not been copyrighted, has commercial value and is obviously a work in progress.  I'm willing to explain the purpose of the database with you, privately.  Rock

                                • 13. Re: Conditional Value Lists
                                  RickWhitelaw

                                       Rock, from reading this thread it doesn't seem to me that you're anywhere close to divulging any confidential information. As well, your solution will have no commercial value if it doesn't work as you want. One of the advantages to posting questions on this or any forum is that many people can offer potential solutions to whatever problems you may encounter in development. I have no idea if Phil responded to your request to communicate privately by the way.  I doubt, at this point, that I would have.

                                  • 14. Re: Conditional Value Lists
                                    rgennaro

                                         Rick, thank you for your observations. I agree that what makes a forum a useful tool is the open dialog between users.  

                                         PhiModJunk, I have appreciated your willingness to help and I apologize if I made you uncomfortable.  

                                         I noticed that this thread was viewed 60 times but the only responses were from you.  I also noticed that this thread was moving from generic FileMaker Pro advice to database specific advise.  So, I wanted to limit my exposure to the only person who was willing or able to help.  

                                         I sincerely apologize to the group if my request for privacy offended you; no malice was intended.  

                                         Respectfully, Rock

                                    1 2 3 Previous Next