1 2 Previous Next 19 Replies Latest reply on Jul 8, 2012 10:37 AM by GuyStevens

    Calculate filtered results



      Calculate filtered results


      Hello people!

      so... this time i am having problems with a calculation of filtered results:


      i have 20 Workers, and 50 different products with different costs.

      I filter per Date, Worker and Status how much they sold. until there all fine.

      Now i jus need to have the Sum of each status, the field "Provi". So that "Boni" Should be 275 and so on...

      i really feel like a moron with FM, cuz i work days and days trying to figure out something that in the end looks pretty obvious :(

      Tx in advance!



        • 1. Re: Calculate filtered results

          That's actually really simple and you'll be kicking yourself that you didn't find this yourself :)

          What you seem to have set up is a subsummary part when sorted by "Worker"

          The Provi field is probably a total. What you need (and might already have) is a Summary field that calculates the total of Provi. (Sum_Provi)

          Then you either:

          - Add that summary field (Sum_Provi) to the subsummary part (the grey area)

          - Add a trailing sub summary field when sorted by "Worker" and add your Summary field (Sum_Provi) to that part.

          This will show the total per worker when sorted by "Worker"


          This file also uses this technique, you can look at it if you can't figure it out:


          • 2. Re: Calculate filtered results

            Hi again, DaSaint :)

            No way... It wont work...

            I make all exactly as you say, but it just wont work...  Grrrrrrrr!


            and now for last it decided not to show the Provision Field anymore... huhu! yes, i have a real talent...

            Could you pleease take a short look at my mess again? Thaaank You 10000 times!



            • 3. Re: Calculate filtered results

              Well, the Provision field has become completely empty.

              Did you remove the values?

              Or was there a calculation in that field?

              • 4. Re: Calculate filtered results

                What you have works perfectly. You only seem to have removed the data from your Provision field. The Provision field is empty in every record.

                Did you make any changes to the field setting? Like set it to calculation and then back again to number or something?

                • 5. Re: Calculate filtered results

                  Ok, I found the provision field in the Zeitschrift table.

                  So I changed the Provision field in the Bestellung table to be a calculation.

                  And now everything seems to be working.

                  The problem was that your Provision field came from another table.

                  Just be warned that when you change the Provision value in the Zeitschrift table it will also change in the related records in the Bestellung table.

                  If you don't want that, this provision needs to become a lookup in stead of a calculation, but then the data will only be entered when you create a new records so you would need to enter the data in all the older records with a loop script.


                  If you need any more help, just let me know.

                  • 6. Re: Calculate filtered results

                    There was no calculation, there was just the field Provision as it appears on Bestellung.

                    and the calculation was supposed to appear above it in the grey area.

                    I also fight here try to find out what happened :(

                    • 7. Re: Calculate filtered results

                      There was no calculation, there was just the field Provision as it appears on Bestellung.

                      Yeah, but the field Provision exists in two tables.

                      It's in the "Bestellung table" but it also exists in the "Zeitschrift" table.

                      I'm assuming the Provisions need to come from the Zeitschrift table.

                      So you need to get them into the Bestellung table. You can do this different ways:

                      - If you want the Provision in Bestellung to change when Provision is changed in the Zeitschrift table you use a calculation.

                      - If you don't want the Provision in Bestellung to change after you have created the Bestellung you need to make that field a lookup tat looks up the Provision from the Zeitgeist table when a new records is cretead in Bestellung.

                      This situation is one you see on invoices.
                      You add a product to an invoice when you sell it. At that moment you do a lookup of the price because when the price changes later you don't want it to change on your old invoices that have already been sold a long time ago.

                      • 8. Re: Calculate filtered results

                        omg, i am so glad you exist.. i worked days on this calculation, unsuccessfully!

                        Its perrrrrfect! how do you do such things!!

                        thank you soooo much, you are the bestest!!


                        • 9. Re: Calculate filtered results

                          I've been in these kinds of situations myself before, that's how you learn. Laughing

                          Next time you have such a problem, don't waste days on it, just let us know  Cool

                          • 10. Re: Calculate filtered results

                            okkk! but i also wanna learn, and when i make millions of mistakes, in the end i ended up learning some other stuff that some day i could maybe need!

                            as i said before i know FM since 14 days, i am still a baby here...


                            So... i keep working here on sunday :(  have a good day!

                            • 11. Re: Calculate filtered results

                              Soooo... now i need again some light :)
                              In the blank space under the head part i would like to have following fields:
                              Gutschrift   460,00€
                              Boni           115,00€  
                              Sprung        45,00€
                              Widerruf     295,00€
                              It also must make a calculation like:  Gutschrift-Boni-Sprung-Widerruf= 5,00€
                              I dont know how to create a field in my table that represents the Summe of each Status (gut, boni, wid) so that i could use those fields to make a formula...

                              do you have any idea?

                              Thank you very much!

                              • 12. Re: Calculate filtered results

                                Sounds to me like you need to make 4 table occurrences of the table this layout is based on. 

                                Those need to be related to 4 global fields, each containing the exact value of the status "Boni" "Gutschrift" "Sprung" and "Widerruf"

                                That way from the context of this table in each table occurrence you can only see all the records of that particular status.

                                Then you put the summary field of each table occurrence on this layout, in the header part if you want.

                                Then, in the table this layout is based on you cerate a calculation field that subtracts the sums of all four table occurrences.

                                And also put that on your header.

                                Should work nicely.

                                So in short: In the table you are showing in your screenshot, create 4 text fields and make sure they are set to global storage. Name them:


                                Make sure they contain one status each. You only have to enter that in one record, because they are set to global storage they will contain one and the same value on each record.

                                If there is ever a chance you will delete all records from this table you need to set these fields to auto enter the status as data.

                                Then create 4 new table occurrences in your relationship dialogue (use the two green pluss signs on the bottom.

                                Also give them logical names. Add the Stetus to the end of the name. Like: if your original table is named "Bestellung"

                                Call the TO's:  Bestellung_Boni, ...

                                Then create relationships between the g_Boni field from the original TO to the Bestellung_Boni::Status field.
                                From the g_Gutschrift field to the Bestellung_Gutschrift::Status field, ...

                                And so for all 4 TO's.

                                This way from the Bestellung table you can only see records with the Status "Boni" in the Boni table, so when you put the summary from the Bestellung_Boi table on the Bestellung layout, it will show the total of all Bestellung with the status "Boni.

                                Clean and simple.


                                • 13. Re: Calculate filtered results

                                  wow "clean and simple" hehehh


                                  ok lemmetry!



                                  • 14. Re: Calculate filtered results

                                    One thing to note though is that that will always show the totals for ALL fields of that status.

                                    If you want there is another way.

                                    You can create 4 calculated fields.

                                    For instance. You create a c_Value_Boni field with the following calculation.

                                    If (Status = "Boni" ; Zeitschrift::Provision ; "" )

                                    That way in this field you only get the Provision if the status is "Boni.

                                    Then you make three more fields for every Status type.

                                    And then you make four summary fields to make the summary of each calculated field.

                                    s_Summary_Boni, ...

                                    Then you can make a calculation field that calculates s_Summary_Boni - s_Summary_Gutschrif - ...


                                    It's different, and the difference is that this does only show the totals within the found set.

                                    1 2 Previous Next