10 Replies Latest reply on Nov 30, 2012 9:03 AM by philmodjunk

    Load a drop-down list with predicate clause

    AlexanderBurbello

      Title

      Load a drop-down list with predicate clause

      Post

           Hi all,

           I have a data modeling with tables Project -> Task -> Working Hours.

           I want to insert a record on the layout Working Hours, but once I choose the field Project in a Drop-down list, the next field is required to choose a Task Name that is related to that project.

           I wold like to have this field using a Drop-down list and load with only the tasks related to the project chosen before. Is it possible??

           I already tried using the Value Lists but I cant added some "where clause" on this.

            

           I dont see an easy solution, but any clue how I can implement this is very welcome?

           Thank you.

            

            

        • 1. Re: Load a drop-down list with predicate clause
          philmodjunk

               It sounds like you want a conditional value list. Have you checked out these links?

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Load a drop-down list with predicate clause
            AlexanderBurbello

                  

                 All your sources are great ... but I am still strugging myself to make it work.
                  
                 I look at the demo file, but it looks a bit confusing ... although there are lots of concept applications and examples.
                  
                 I will keep working on it.
                  
                 Thank you for all your information.
                  
                 Alex
            • 3. Re: Load a drop-down list with predicate clause
              philmodjunk

                   Feel free to post a follow up question here, describing the tables, values and relationships that you have.

              • 4. Re: Load a drop-down list with predicate clause
                AlexanderBurbello

                     Cool ...

                     So let explain my development ...

                     I got the example/template given from the Starter Solution provided by FMP12 and created the option Project . In this file, we have 2 tables: Project and Tasks.

                     What I am trying to do is create a third table where I will record my working hours related to each task that is linked to the project. In this table I would like to have the fields: project, task, date, begin time, end time and  total time worked.

                     The question is when the user chooses the project field, how to load the pop-up Task field with only the values (tasks) the project has.

                     I saw on the demo file you mentioned that I have to duplicate the Task table, but I am not sure which field I have to link. I tried several ways and nothing works.

                      

                     Once again, thank you for your prompt help.

                • 5. Re: Load a drop-down list with predicate clause
                  philmodjunk

                       Do you have these relationships?

                       Projects-----<Tasks-----<Hours

                       You might want to use a portal on a layout based on tasks for recording hours worked on each task. That would eliminate the need for conditional value lists.

                       Using what you have, you need new occurrences of the Tasks and Projects tables, but not actual duplicate tables.

                       In Manage | Database | relationships, make a new table occurrence of Tasks by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

                       We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                       Repeat these steps for Projects.

                       Set up these relationships:
                       Projects-----<Tasks-----<Hours>----SelectedProject-----<ProjectTasks

                       Hours::SelectedProject = SelectedProject::PROJECT ID MATCH FIELD
                       SelectedProject::PROJECT ID MATCH FIELD = ProjectTasks::PROJECT ID MATCH FIELD

                       Format HoursSelectedProject--a new field added to the table just for this purpose, with a value list of values from Projects::PROJECT ID MATCH FIELD for the first field and from Projects::Project for the second field.

                       Format Hours::TASK ID MATCH FIELD with a value list of values from ProjectTasks. Select "Include only related values, starting from Hours."

                  • 6. Re: Load a drop-down list with predicate clause
                    AlexanderBurbello

                         Simply worked perfect ... following exactly what you have described.

                         But with that came up the last question ... once the user choose Project, and the next field Taks ... if the user get back to Project and switch to any other but he doesnt fix the task ... it will be inconsistent.

                         Can I validade it somehow before the user leaves the record or layout??

                         But once again, thank you very much for your help.

                         Alex

                    • 7. Re: Load a drop-down list with predicate clause
                      philmodjunk

                           Hours doesn't link directly to Projects it links directly to tasks. To specify a different project for an Hours record, it has to be linked to a tasks record assigned to that project.

                           This is one of the reasons why I mentioned using a portal from Tasks to log the hours worked on that task. This avoids some of these complications.

                           A validation rule on the Task ID Match field can specify that it only be a member of the conditional value list.

                      • 8. Re: Load a drop-down list with predicate clause
                        AlexanderBurbello

                             That is a great idea ... I didn't realize that would be better that way.

                              

                             Anyways, with your help I learned how to create conditional value list.

                        But I will work on this and implement what you have suggested ... sounds much better.

                              

                        Once again, thank you so much for your help and patience. :D

                              

                        • 9. Re: Load a drop-down list with predicate clause
                          AlexanderBurbello

                               So sorry for opening up this thread, but one last question came up on this topic.

                                

                               As I have a  relationships: Projects-----<Tasks-----<Hours.

                          How can I create a calculated field (in some table, perhaps Taks) where I can add up all hours worked in a specific task and show this info with a field on the Task Details layout.

                                

                          I tried to create a field on Task table, and this field is Time type, with the option "Calculated value" and specified the formula Sum( Hours::TotalTime ). This field is displayed on the Task Detail layout, however its showing empty!!

                               What am I doing wrong?

                                

                               Thank you again and sorry for bothering with this topic.

                                

                               Alex

                          • 10. Re: Load a drop-down list with predicate clause
                            philmodjunk
                                 

                            I tried to create a field on Task table, and this field is Time type, with the option "Calculated value" and specified the formula Sum( Hours::TotalTime ). This field is displayed on the Task Detail layout, however its showing empty!!

                                 Assuming that TotalTIme is NOT a summary field, use this expression in a field of type calculation with time as the return type. You can also define a summary field in Hours that computes the total time and then Hours::sTotalTotalTime, when placed on your Tasks layout, will show the total time. I recommend using Sum() if this is a layout where you enter new hours data in a portal as it will update smoothly where the summary field from hours will not.