1 2 Previous Next 15 Replies Latest reply on Apr 5, 2011 3:35 PM by philmodjunk

    Query about Valuelists

    deepakgupta_1

      Title

      Query about Valuelists

      Post

      Dear All,

      I am new to filemaker and would like to count the number of times a particular value of a valuelist is selected in a table.  This is to be part of a separate summary table.   Can you point me in the right direction with respect to what functions I need to do this?

      Thanks in advance.

        • 1. Re: Query about Valuelists
          philmodjunk

          You may not need a separate summary table. There's more than one approach that works for this.

          With a summary table:

          1. In your summary table, define a matching field where each record receives one value from the value list. This table can actually serve as the source of values for your value table if you define the value list to take its values from this same table.
          2. Define a relationship between your summary table and your main table similar to this:

            SummaryTable::valueField = MainTable::valueListField

          3. Define a calculation field in the Summary table with this expression to count the records:
          4. Count ( MainTable::valueListField )
          5. And it will count each instance of a particular value in the Main table.

           

          Without a summary table:

          1. Define a summary field as the "count of" your value list field.
          2. Create a new layout based on your main table and change the body layout part into a sub summary part "when sorted by" the value list field. Place the value list field and this summary field in the sub summary part.
          3. Enter browse mode and sort your records by the value list field, select the view as list option, and you'll see a list with one row for every value and the count that goes with it.

           

          The key difference between the two methods is that if a value list item is never selected for any record in the main table, the first method will list that value with a count of 0. In the second method, the value will not be listed at all.

          • 2. Re: Query about Valuelists
            deepakgupta_1

            Dear PhilModJunk,

            Many thanks,

            I got your first suggestion to work.  However, on the same record, it only counts one particular value.  If you want to count another value of the same valuelist, you have to create a second record on the summary table and type in another value of interest before you get the count output.  I was wanting all the values of the value list to be displayed on one 'page'...the same 'record' in this case.  How would I do that?

            have not had a chance to try your second suggestion yet.

            Regards,

            • 3. Re: Query about Valuelists
              philmodjunk

              Use a list or table view of your layout with one record for each value in your value list and you'll see all the counts at once.

              • 4. Re: Query about Valuelists
                deepakgupta_1

                Ok, I view it in list view!

                I will play around with your other suggestion later....

                Many thanks,

                • 5. Re: Query about Valuelists
                  deepakgupta_1

                  Hello again,

                  Further query..

                  If this was to be a permanent summary table, how would you prevent the users from altering the values of the valuelists in this table?  Would you auto-enter the data to being with for example?

                  Regards

                  • 6. Re: Query about Valuelists
                    philmodjunk

                    You can use the behavior section of the inspector's Data tab to prohibit access to the field. You can also use Manage | Security... so that the table is read only for all users except those that open the file with a [Full access] password, or possibly some kind of administrator account that gives the user edit privileges.

                    • 7. Re: Query about Valuelists
                      deepakgupta_1

                      Thank you.

                      As somewhat of a theoretical exercise, I tried to use your first suggestion and populate a valuelist in one field in one table from values in a field in another table through a relationship as you suggested.   I could not get the FM to display the data as a drop-down list/checkbox list; it would only display values in list/table view and then some values were missing.  What am I doing wrong?

                      Regards

                      • 8. Re: Query about Valuelists
                        philmodjunk

                        First, there is no relationship needed to use the values in a field of one table as a value list elsewhere unless you need a conditional value list that only lists certain values from the table based on a value in a field in the current layout. (Select fruit in field 1 and see 'apple', 'pear', 'kiwi' in field 2's drop down. Select vegetable in field 1 and see 'carrot', 'lettuce', 'beet' instead.)

                        Here's the basic steps to change a custom values value list (values are typed directly into the value list setup) into a value list based on all the records in a table:

                        1. Open manage Value lists and either open an existing value list or create a new value list.
                        2. Select the "use values from field" option
                        3. Select the table you want to use from the drop down.
                        4. Click the field you want to use as the source of values to hilight it.
                        5. Click OK until you've dismissed all the dialogs.
                        • 9. Re: Query about Valuelists
                          deepakgupta_1

                          Thanks, I am sure that was basic stuff for you but was really helpful.

                          Regards

                          • 10. Re: Query about Valuelists
                            deepakgupta_1

                            Hello again Phil,

                            I have to resurrect this thread with another related query.  I tried the solution with the summary table as you suggested to count the checked values in a value  list but did not manage create an elegant summary layout that I was after.  I came up with the following script (after working on a different issue)...to count the number of times values 'x' and 'y' are checked; the script is run on pressing a summary button on the main table and results sent to a summary layout...

                            Go to Layout [ “testingtesting” ]
                            Enter Find Mode [ ]
                            Set Field [ testingtesting::test; "x" ]
                            Perform Find [ ]
                            Set Variable [ $variable_x; Value:GetAsText ( testingtesting::totalrecords ) ]
                            Show All Records
                            Enter Find Mode [ ]
                            Set Field [ testingtesting::test; "y" ]
                            Perform Find [ ]
                            Set Variable [ $variable_y; Value:GetAsText ( testingtesting::totalrecords ) ]
                            Go to Layout [ “Summary” ]
                            Set Field [ testingtesting::Total x; $variable_x ]
                            Set Field [ testingtesting::total y; $variable_y ]
                            Exit Script [ ]

                            Works fine.
                            I then thought about the situation where there are no records with the value of interest checked, so tried to put an error capture into it.....without success.....I tried the following....

                            Go to Layout [ “testingtesting” ]
                            Enter Find Mode [ ]
                            Set Error Capture [ On ]
                            Set Field [ testingtesting::test; "x" ]
                            Perform Find [ ]
                            Set Variable [ $error; Value:Get ( LastError ) ]
                               If [ $error=0 ]
                               Set Variable [ $variable_x; Value:GetAsText ( testingtesting::totalrecords ) ]
                               Else
                               Set Variable [ $variable_x; Value:"Error" & "$error" ] 
                               End If
                            Set Error Capture [ Off ] 
                            Show All Records
                            Enter Find Mode [ ]
                            Set Error Capture [ On ]
                            Set Field [ testingtesting::test; "y" ]
                            Perform Find [ ]
                            Set Variable [ $error_y; Value:Get ( LastError ) ]
                              If [ $error_y=0 ]
                              Set Variable [ $variable_y; Value:GetAsText ( testingtesting::totalrecords ) ]
                              Else
                              Set Variable [ $variable_y; Value:"Error" & "$error_y" ]
                              End If
                              Set Error Capture [ Off ]
                            Go to Layout [ “Summary” ]
                            Set Field [ testingtesting::Total x; $variable_x ]
                            Set Field [ testingtesting::total y; $variable_y ]
                            Exit Script [ ]

                            Still new to scripting, so can you tell me what I am doing wrong?

                            Regards

                            • 11. Re: Query about Valuelists
                              philmodjunk

                              I don't see why you need a script for this...

                              How does the second script fail?

                              (You do not need exit script at the end of either script, by the way, it does no harm but is not needed.)

                              • 12. Re: Query about Valuelists
                                deepakgupta_1

                                I was working on something else actually and modified the script to do this...probably my inexperience with FM showing....

                                But as an exercise in scripting, perhaps you can tell me where I am going wrong......The second script works fine if the 'x' checkboxes are deselected and there are 'y' values selected; but if there are only 'x' and nil 'y', or nil 'x' or 'y' then both fields return blank....(i used some inverted commas in the set variable function (lines 10 and 23) that I have now deleted).  

                                Regards

                                • 13. Re: Query about Valuelists
                                  philmodjunk

                                  What kind of fields are "Total_x" and "Total_y". If these are not fields with global storage enabled, no values will be set if the second Find produces zero found records.

                                  • 14. Re: Query about Valuelists
                                    deepakgupta_1

                                    That did it, thank you.

                                    What, briefly, is the theory behind that?

                                    Regards

                                    1 2 Previous Next