14 Replies Latest reply on Oct 22, 2012 5:01 PM by margotjacqz

    filered value list/ filtered table occurance

    margotjacqz

      Title

      filered value list/ filtered table occurance

      Post

           I am trying to limit a drop down list in the following set up.

           TO=Action related to a join table of IDs which link to several other TOs of Jobs, Names, Companies.

           New Action Layout has portals to the others. I know how to set up the lists, and show the resutling choices.

           What I want to do is limit the list, for example to Jobs that are open or companies in the us. I found the concept of creating a calculation field such as [if job status= open or job status=hold;1;0]. I am failing at the next step which is how to create the value list limited to x=1.

           Apologies if this has been covered many times, i found a couple of possible answers but lost track of the concept in the particular details and couldn't replicate. thanks all as always.

        • 1. Re: filered value list/ filtered table occurance
          philmodjunk

               To use a calclation field--not always the best option, but easy to setup, it should be written as:

               If ( JobStatus = "open" or JobStatus = "Hold" ; __pkPrimaryKeyField )

               And then you can set up your value list to draw values from this calculation field instead of __pkPrimaryKeyField.

               For some links and a demo file on conditional value lists:

               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: filered value list/ filtered table occurance
            margotjacqz

                 yep that's what i read.

                 trying 1. setting up with the new calc field is not working .. perhaps it's when does this value get calculated, and what happens when the status changes. some might go from open to closed to open again ...

                 otherwise, I was trying to follow your KB article, option 2. but am totally flummoxed about theTOs - create a duplicate of JobAction? and what that relationship is and which to use when setting up the list.

                 Partly it's that it's not conditional on anything already on the layout. It's a subset based on it's own properties. 

                  

                  

            • 3. Re: filered value list/ filtered table occurance
              philmodjunk
                   

                        yep that's what i read.

                   But it's not what you posted here as your sample calculation in your original post. Returning a value of 1 or 0 isn't going to do it. The calculation has to be a stored, indexed calculation field that returns an actual value you need your value list to be entered.

                   Why don't you describe what you want to do in more detail--including descriptions of the fields, values and tables involved?

                   Then I can use your actual tables fields and relationships to set up an example.

              • 4. Re: filered value list/ filtered table occurance
                margotjacqz

                     I have corrected the calculation so field <is_open> is calculated:  If ( JobStatus = "open" or JobStatus = "Hold" ; pk_JobID ) Using <is open> instead of <JobID> in the value list did not drop down a list of only open/hold records. Curious if that might be a poor choice in indexing/ storage. And question : what happens in that field when status chages back and forth. And what is it's value if status= closed, for example.

                     Second attempt: I reread the articles you linked to, (I had read them previously) and still can't get it sorted.  So ..

                     Relevant Tables: Action, ActionJoin, JobSheet. ActionJoin holds keys for Action and three other tables (for multi-multi relationships). In this instance  Action <Act ID> Action Join <JobID> JobSheet.

                     Of interst here is the item on the Action layout linking to JobSheets using a portal: ActionJoin and a drop down Value List based on <JobID> / <JobRef> for a field showing <JobRef>.

                     * What I want to do is change that Value List so the choices are limited by activity - in this instance to show only Jobs with Status =Open or Hold.

                      

                • 5. Re: filered value list/ filtered table occurance
                  philmodjunk

                       Make sure that your field is of type calculation or an auto-entered calculation with the "do not replace existing value" check box cleared.

                       

                            what is it's value if status= closed

                       If the value is neither "open nor "hold", the field is empty--this is what keeps the values from records of the other status values from supplying ID numbers to the value list.

                       If you just put this field on a layout do you see the expected value in each individual field?

                  • 6. Re: filered value list/ filtered table occurance
                    margotjacqz

                         yes its working as expected as a plain field  <is_open> on a layout. field value is empty or =JobID as appropriate.

                         However, moving along, On the layout described above, Drop-Down Value List  Jobs [<JobID/JobRef>]  works as before and Value List JobsOpen [<is_open> /< JobRef>] shows all records as well, not the limited list expected. Tested on layout without portal, same thing: JobOpen list shows all items, not limited. 

                         (side bar: as a calculation, I dont see a way to check "do not replace.. " And besides, the status is changing on/off posisbly on again. )

                          

                          

                          

                    • 7. Re: filered value list/ filtered table occurance
                      philmodjunk
                           

                                side bar: as a calculation, I dont see a way to check "do not replace.. " And besides, the status is changing on/off posisbly on again.

                           I'm trying to determine what field type you used. There are two possible methods that can be used--a field of type calcuation or a field of type number or text with an auto-entered calcualtion. If you set up the latter, an auto-entered calculation, there's an option titled "do not replace existing values, if any" that must be cleared. If you use a field of type calculation, this is not an option and there are no possible issues with changing the status failing to update the values in the value list.

                           Time to check the rest of your setup. I need to see two things:

                           The dialog box where you set up the value list options and the relationship graph in Manage | Database | relationships. You can capture both as screen shots and use the controls below "Post a Answer" to upload jpeg, gif or png image files (NOT PDFs). After seeing those two images, I'll either be able to tell you where you've gone wrong or be able to ask some very specific questions to further narrow the  possible issues down.

                           Value List JobsOpen [<is_open> /< JobRef>] shows all records as well

                           Are you hiding or showing the is_open column of values in your value list? If you are hiding it, change this setting and tell me what you see in the value list.

                      • 8. Re: filered value list/ filtered table occurance
                        philmodjunk

                             I was just helping someone else with the same problem and discovered the solution to an issue my own incorrect assumptions created in your database file. blush

                             If you are going to hide the first field or sort on the second field, use this calculation field for the secondary field in the value list:

                             If ( JobStatus = "open" or JobStatus = "Hold" ;NameFieldGoesHere )

                             You can use the normal primary key field, instead of the calculation field for the primary field.

                        • 9. Re: filered value list/ filtered table occurance
                          margotjacqz

                               1. used a calculation

                               2. JobList Open Value List with both columns shows the open jobs with IDs and the not open ones without.

                          • 10. Re: filered value list/ filtered table occurance
                            margotjacqz

                                 relationship graph requested .

                            • 11. Re: filered value list/ filtered table occurance
                              philmodjunk

                                   Please note that my response has appeared before yours even though I posted it after you did.

                                   Time to send ModMan another Bug Report...

                              • 12. Re: filered value list/ filtered table occurance
                                margotjacqz

                                     not at all I think you responded to a post I replaced because the image was the wrong one.

                                     meanwhile, back at the value list, [ref_open the new field for jobname] <if_open / ref_open>... it's showing only one item. the first by id number.

                                • 13. Re: filered value list/ filtered table occurance
                                  philmodjunk

                                       That was it.

                                       Check the return type for your if calculation. I would guess that it is set to return "number" when it should return "text". This is controlled by a drop down in the lower left corner of the Specify Calculation Dialog.