13 Replies Latest reply on Sep 4, 2012 9:29 AM by philmodjunk

    Domain aggregate functions

    juanvicanamas

      Title

      Domain aggregate functions

      Post

      Is there anybody in Filemaker users or in Filemaker's employees, able to say me if the  domain aggregate functions exist in Filemaker ? You can see examples of these functions in the documentation of Access in Microsoft. You can't imagine how usefull these functions can be. You can get : max, min, count, values of a field. You can add a condition to find the answer and you can make reference to a field present in the layout (called Form in Access) I can't find the solution in one layout of my database. Thanks a lot, best regards.

        • 1. Re: Domain aggregate functions
          Sorbsbuster

          Max, Min, Count, are all standard Filemaker functions, documented in Help.

          • 2. Re: Domain aggregate functions
            juanvicanamas
            Yes these functions exist in Filemaker, but not in the same way they can be used in Access of Microsoft. You cannot insert a condition. In Access  you can use the value of a field wich exists in the layout.
            • 3. Re: Domain aggregate functions
              Sorbsbuster

              Can you give an example of what you want to achieve?

              • 4. Re: Domain aggregate functions
                juanvicanamas
                In a layout I have the following fieles : BuyCode (Primary key) ProviderCode (Numeric) ProviderSellCode (Numeric) In a new record when I give a value to ProviderCode or when I change the ProviderCode in aun existing récord , I should automatically get a value for ProviderSellCode. For the first buy from the provider, ProviderSellCode gets 1. For the next buy, it gets 2. The field ProviderSellCode depends of the provider and the quantity of buys from him.
                • 5. Re: Domain aggregate functions
                  Sorbsbuster

                  If there is only one table, then create another Table Occurance of that table.  If your table is called Buys then call the new TO, say:

                  buys_SelfByProviderCode

                  Relate the original Table (Occurance) of Buys to this new Tabel Occurance by ProviderCode = ProviderCode

                  Set the field ProviderSellCode to be auto-enter by calculation:

                  Count ( buys_SelfByProviderCode ; BuyCode )

                  In this case, you could also set the calculation to be Max ( buys_SelfByProviderCode::ProviderSellCode ) + 1

                  • 6. Re: Domain aggregate functions
                    juanvicanamas

                    Hello,

                     

                    Could you give me please the composition of the new table ? I do not understand very well how to make it.

                     

                    Your answer is very interesting and I am impatient to try it but I need more information about the new table.

                     

                    Thanks, best regards.

                    • 7. Re: Domain aggregate functions
                      juanvicanamas

                      I tried as you said to me, but my field ProviderSellCode gets always the value 1, when it should get 1,2,3,... in the introduction of a new record for the same provider.

                      If I change the ProviderCode the field ProviderSellCode should change to a new value depending of the number of buys to this provider.

                       

                      Did you understand my problem ? I am Spanish and I tried to use my best English to be in the Englis forum.

                       

                      I used the spanish forum but nobody gave me a solution.

                       

                      Best regards.

                      • 8. Re: Domain aggregate functions
                        Sorbsbuster

                        I used a different way here:

                        http://www.yousendit.com/download/TEhVYlJSbEFYSHgzZU5Vag

                        Note that there is only one table, but a second Table Occurance of the same table - I just duplicated the Buys Table and then linked it to itself.

                        I set the ProviderCode field to have a script trigger attached to it.  When you enter the ProviderCode it commits the record (to update the number of records for that Provider) and then sets the count into the correct field.

                        • 9. Re: Domain aggregate functions
                          Sorbsbuster

                          If you try it the first way you'll see that it does work - after you commit the record.  Until then the new record doesn't 'exist'.  It is awkward to explain to the user that they have to 'click to commit' the record first, then modify the ProviderCode!  The script trigger does everything in the right sequence.

                          You may have to build in a 'guard' - if you modify the ProviderCode again in a previous record it will update to a new value.  You could change the script to check if a value has already been allocated, and if so do not change it.

                          Don't worry about the language - I understand you completely.

                          • 10. Re: Domain aggregate functions
                            Sorbsbuster

                            That is the first time I have used YouSendIt to link here - let me know if you get the file OK.

                            • 11. Re: Domain aggregate functions
                              juanvicanamas
                              Hello, I got your database, it's working. I tried to do the same in mine but I get always the same value for all the records. I get 0 all times for the field ProviderSellCode. I do not understand why. I am very sad to see that these functions that I use very often in Access do not exist in Filemaker. Now I have difficulties to use in a script ExecuteSQL, I do not finf any good examples to know how to write the sentences. Best regards.
                              • 12. Re: Domain aggregate functions
                                Sorbsbuster

                                did you:

                                - duplicate the table
                                - link the original table to the new Table Occurance by the ProviderCode
                                - create the script to set the field
                                - attach the script to the ProviderCode fiels, as a Script Trigger

                                You can see all of those things in the file I sent.

                                I use the Max, Min, Average, and Count, functions very often, and they all work, so it will just be something small that is not right.

                                Is there anything else I can do to help?

                                • 13. Re: Domain aggregate functions
                                  philmodjunk

                                  A step by step on duplicating an existing table occurrence:

                                  In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

                                  We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                                  Note that in addition to the aggregate functions already discussed here, FileMaker has a second method for computing aggregate values--summary fields. Which method is best for a given problem depends on a number of design details in how you have modeled your data and designed your layouts.