13 Replies Latest reply on Apr 29, 2011 8:45 AM by philmodjunk

    How do I filter out text in a calculation

    lbellino

      Title

      How do I filter out text in a calculation

      Post

      I have a field named "Department", which in turn are broken out into categories, which is another field named, "category". Within a specific category, which is named "Shell", I need to remove a department name, which is labeled "Floor Openings", which is part of that Shell category. What's the best way to do this? Below is what I currently have for the field name and calculation. Thanks.

       

      Field Name: total_gross_shell_noflooropenings   Calculation: Sum ( sto_STOSPC__shell::Area_Calculated )

      FMP_Example.PNG

        • 1. Re: How do I filter out text in a calculation
          philmodjunk

          It would help to know the tables you have here and how they are related. Then I could describe the following in more detail:

          Option 1: Change your relationship so that Records with "floor openings" are no longer related records.

          Option 2: Define a calculation field in STOPSPC (I assume that's the actual table name here), that excludes values when the department name is "floor openings" and returns Area_Calculated when it is not. Then Sum this calculation field instead of Area_Calculated.

          Option 3: If you have FileMaker 11, you may be able to define a summary field in STOPSPC and display it on your layout in a one row portal with a filter expression to filter out the "Floor Opening" department.

          I'd use Option 2 only if I could not find a way to get Options 1 or 3 to work for me as it is very inflexible and often requires creating numerous calculation fields if you have a lot of such "selective sums" to calculate.

          • 2. Re: How do I filter out text in a calculation
            lbellino

            Option 1 is a no go. Can't unrelate the floor openings from the Shell category.

            Option 3, even though I have FMP11, summary won't work, since I'm working with 1100 records where 80% have floor openings and I need to know what each record has without any floor openings, whether it has it or not.

            Option 2 is probably the way to go. What functions would use in the calculation field? I tried to save an image to this post, but I don't think you'll be able to view it very well. If there is a better way to send a print screen of what my tables look like, please let me know. Thanks.

            • 3. Re: How do I filter out text in a calculation
              philmodjunk

              Don't rule out options 1 and 2 so fast. I don't see anything that makes them a no go yet, but I am still gathering info here.

              From a layout based on what table occurrence (box in the above graph) do you need to calculate this sum?

              I would guess STO_store, but need to be sure.

              This part of your original description isn't clear to me:

               "I need to remove a department name, which is labeled "Floor Openings", which is part of that Shell category."

              Does that mean you have one field named "Department Name" and you need to exclude records from the "Floor Openings" department? (Don't see what that has to do with the category field here.)

              If I am right, Option 1 and work just fine and Option 3 isn't impossible, but depends on whether you need to use this sum in calculations or just display it as it is a "display only" technique.

              • 4. Re: How do I filter out text in a calculation
                rjlevesque

                http://www.dropbox.com is free and excellent for sharing files...

                • 5. Re: How do I filter out text in a calculation
                  lbellino

                  To clarify, each record that I have has a Gross Ground, Gross Mezzanine and possibly a Gross Basement and Floor Openings, depending on the layout of a store that I'm working with (each store is a record in my database). These areas are classified as departments listed under a category named, "Shell". Currently, I am able to sum the total of all these items listed in the Shell category. But what I'm looking to do is to sum up everything, minus, the floor openings for now. Hopefully, this explains a little better what I'm trying to do.

                  I have to get persmission first before I can download the software for the dropbox website, but that's good to know that it's available. 

                  • 6. Re: How do I filter out text in a calculation
                    philmodjunk

                    Sorry, but that doesn't help.

                    These areas are classified as departments listed under a category named, "Shell"

                    Is "Shell" the field name and "Floor Openings" text that might be entered into that field?

                    • 7. Re: How do I filter out text in a calculation
                      lbellino

                      One Field Name is "DepartmentName"

                      One Field Name is "Category"

                      • 8. Re: How do I filter out text in a calculation
                        philmodjunk

                        And which one stores "Floor Openings"? DepartmentName?

                        If so, you can create a relationship that excludes Records with "floor openings" in the DepartmentName field.

                        • 9. Re: How do I filter out text in a calculation
                          lbellino

                          Yes, floor openings would be under the "departmentname" field. But I don't want to exclude records that have floor openings. I want to list all records (stores), but somehow remove the floor openings info(number) from the calculation that I already have.

                          • 10. Re: How do I filter out text in a calculation
                            philmodjunk

                            As I read your Sum function, you need to exclude records where DepartmentName = "Floor Openings" from the records used in that calculation, which requires this relationship:

                            STO_store::StoreNumber = sto_STOPSPC_ShellNoFLoorOp::StoreNumber AND
                            STO_store::_ka_Constant_Shell_ct = sto_STOPSPC_ShellNoFLoorOp::Category AND
                            STO_store::_ka_Constant_FloorOp ≠ sto_STOPSPC_ShellNoFLoorOp::DepartmentName

                            Define _ka_Constant_FoorOP as a calculation field with "Floor Openings" as its value.

                            Then Sum ( sto_STOPSPC_ShellNoFLoorOp::Area_Calculated )

                            If defined in STO_store, should calculate the total you want here.

                            I've used a new table occurence and relationship here just incase the current sto_STOSPC__shell table occurrence is needed as currently defined for other uses.

                            • 11. Re: How do I filter out text in a calculation
                              rjlevesque

                              quick input - don't need software for dropbox, just use their web interface.

                              • 12. Re: How do I filter out text in a calculation
                                lbellino

                                Wanted to say that the last answer, which I noted as the "Best" answer, worked like a charm! Glad that you were able to somewhat read the screen shot that I uploaded. Thanks for the help!

                                • 13. Re: How do I filter out text in a calculation
                                  philmodjunk

                                  Didn't have any trouble reading the screen shot. I've got some "spider webs" in some legacy systems that are far more complicated than that! And your consistent naming conventions helped a lot.

                                  If you are interested in a method of bringing more order to the potential chaos of the relationships map, you might read up on the Anchor Buoy method of keeping things neat and easier to work with: 

                                  http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/