8 Replies Latest reply on Mar 29, 2015 8:10 AM by philmodjunk

    Calculations in Checkbox fields



      Calculations in Checkbox fields


      I have a 'checkbox' Region field where values are 'global' (meaning the whole world), continents or countries. Depending on what Region(s) is chosen, a condition list pulls up 'Companies' whose products occur in those Regions. The hierarchical list works fine but has a problem. If I was interested in 'Germany' and checked the box, I would want not only the companies who only operate in Germany, but also those who operate in all of Europe and Globally because those companies also operate in Germany.

      I've tried using an Auto-enter calc but am failing to think up the syntax needed to 'check' a Continent or Global Regions if a smaller (country) Region is chosen.

        • 1. Re: Calculations in Checkbox fields

          Might be quicker for you to post the calc you have now and someone can help with the modifications.

          • 2. Re: Calculations in Checkbox fields

            Case(Region = "Europe" or Region = "Asia" ;  "Global")

            It definitely doesn't work. There needs to be a syntax that allows addition of 'upstream/parent' regions without losing any of the values already checked.


            • 3. Re: Calculations in Checkbox fields

              Are there no 'takers' for this query?

              • 4. Re: Calculations in Checkbox fields

                I see two complications here:

                1) there could be more than one check box value selected since these are check box fields after all. You might have asia and Europe selected for a given company. And is it possible that for one company, both Asia and Global might be selected? Or have you set up safeguards to prevent that? (Seems like if "global" is selected, no other values should be selected for that company.) This would thus require some fairly complex handling to parse out just the specific value that indicates that a given company is a member of the selected group.

                2) Different selected values can select a record for inclusion in the list (Global, Europe and Germany, when Germany is selected)

                Since your values in your check box can be grouped into three "levels": Global, continent, Country, I'd set up your category field (the one controlling what values appear in the conditional value list, to automatically update with additional values using a value list that omits "global" as an option. If you select "Germany", a script or calculation also selects "Global" and "Europe", for example. It may be easier to run such a script from a Portal based set of "check box" buttons, one value per portal row, so that you can process each such value selection and value deselection individually. Once you use such a method to build a return separated list of values such as:


                the relationship will match to all records with "Global", "Europe" OR "Germany" specified.

                • 5. Re: Calculations in Checkbox fields

                  I'm still processing your 2nd point, but re. the hierachical lists, in total there is: Region < Company < Product. Region has companies listed (and ultimately products) by Global, continent or country. Ultimately a single produce will have will have a single company and single Region listed. ie. product_X could be CompanyA and Global, product_Y, again companyA but only Germany.

                  It's not a perfect system because of the haphazard Company/Product distribution.

                  Does the above make a difference to your 1st point?

                  • 6. Re: Calculations in Checkbox fields

                    It does not, this was my understanding from the beginning. The problem here is that you want a selection of "Germany" to match to records with more than one value: Global, Europe OR Germany. So you need a way to take the selection of "Germany" and add in the other match values. By using a return separated list of these three values, you can match by Global, Europe OR Germany in the region field.

                    "Global" is easy, as you can set up a calculation that adds that Value automatically to any selection made by the user. It's the continent value that will require some thought and effort. For best results, you are probably looking at a table where each record has a field for continent and a field for country, one record for each country with which you do business. You can use this table to look up the continent for each specified country.

                    List ( "Global" ; Countries::Continent ; UserSelectedValueHere )

                    comes to mind as a match field that produces that result. Note that if you select a continent, Countries::continent returns null as no records would match and you get a list of just "Global" and the selected continent.

                    • 7. Re: Calculations in Checkbox fields

                      I think I will keep the above in reserve, because I can foresee situations of overlap that confuse the user. I will initially try simplifying to Global and continents, for which an Auto-enter seems to work: List("Global"; Self)

                      One of practical problems with hierarchies like this is that a user may know the name of Product (grandchild) but not know the Region±Company. It would be good to have a 'Product lookup' so that a) the user could at least see to what Region and Company the product belongs to 'navigate' to it b) but it would be even better for the user, having found the product, to be able to directly add the Product to the list.

                      • 8. Re: Calculations in Checkbox fields

                        I don't quite see where there would be any user confusion with this. The user would select "Germany" and all records with Global, Europe or Germany in their Region field become a match to their selection. I don't quite see the confusion there.

                        But your concerns about selecting values and finding records do bring to mind some of my examples found in the "Adventures in FileMaking" series--both Hierarchical Conditional Value lists that do not require selecting each subsequent "category" in order as well as auto-filtering search portals that can be placed inside a popover (FileMaker 13) or a small modal window (FileMaker 12) that can be opened to find and select a value for your current layout.

                        If I recognize this project, I've recommended these before in another thread, but for those that might be "reading along at home":

                        You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

                        Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
                        Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

                        Caulkins Consulting, Home of Adventures In FileMaking