1 2 Previous Next 16 Replies Latest reply on Jan 4, 2016 2:45 AM by 5104619256

    Calculate related values from an array

    5104619256

      Im stucked

      I want to make some kind of recursive function for summing related records accordning to the values in an array.

       

      My array looks like this:

      1

      5

      7

      9

       

      Now i want FileMaker to sum all records in a related table where the record looks like this

      5

      5

      9

      5

       

      I want o end up with something like this

      5 3

      9 1

       

      But how?

      Note there can be thousands of different values.

      I can do this with script and a loop but i would like a custom function or somthing similar.

        • 1. Re: Calculate related values from an array
          okramis

          I want to make some kind of recursive function for summing related records accordning to the values in an array.

           

          My array looks like this:

          1

          5

          7

          9

           

          Now i want FileMaker to sum all records in a related table where the record looks like this

          5

          5

          9

          5

           

          I want o end up with something like this

          5 3

          9 1

          You could use ExecuteSQL:

           

          Let ( [

          _array = Substitute ( YourArray ; "¶" ; "," ) //if the array values are numbers, else it's needed to put them in single quotes

          ] ;

          ExecuteSQL ( "

          SELECT YourValue, COUNT(*)

          FROM YourRelatedTable

          WHERE YourValue IN (" & _array & ")

          GROUP BY YourValue

          " ; Char(9) ; "" )

          )

           

          Otmar

          • 2. Re: Calculate related values from an array
            5104619256

            Thanks for your answer Otmar.

            I've had som thoughts about using sql but what i didn't wrote in my question was that i ended up with the array because of a filter function with six fields, like startdate, stopdate, < and >. The thing is that if i want to use sql i need to have all these terms as a part of my sql quote and that became a ridiculously complex question.

            If there isn't any recursive custom function to use for this i would have to create it with sql but i'm a little concerned about the complexity of the quote.

            If i remember correctly you can not use a sql quote through a relation, you then need to have all relational connections in the WHERE part of the quote and you ask the question directly to the source table not the related table? Or am i wrong?

            • 3. Re: Calculate related values from an array
              coherentkris

              You are correct that ExecuteSQL does not respect relationships on the graph. Where you need to retrieve columns from one table and records from another table that are related by a common attribute you need to use a JOIN clause or WHERE IN ()

              • 4. Re: Calculate related values from an array
                okramis

                as coherentkris stated, ExecuteSQL is not found set aware. If you don't like to rebuild your relationship in the where clause, you could do a combination. Get the found set IDs through your FM relation with List ( YourRelatedTableTO::ID ) and use it as IN-statement in the sql query:

                 

                .....WHERE \"ID\" IN(" & Substitute ( List ( YourRelatedTableTO::ID ) ; "¶" ; "," ) & ")"

                • 5. Re: Calculate related values from an array
                  5104619256

                  Otmar!

                  I really don't get it, and this is the most frustrating thing with FileMaker Sql, Sql is difficult enough with out all FIleMakers "" and \.

                  My table is called "OCCA"

                  The primary key is "k_id_OCCA"

                  Im in the context "FILE" where the path to OCCA is named "File_OCCA__Global"

                   

                  In your sql query there is a value called "YourValue" i can't see where it will get its value from? Do you mean that i need to set that value for each itteration in som kind of script? In case you mean that this will not be recursive, it will only calculate a value that have been set by somthing, or am i getting it all wrong? It has happend before

                   

                   

                  Let

                  (

                  [_array = Substitute ( FILE::Array ; "¶" ; "," ) ] ;

                  ExecuteSQL ( "

                  SELECT YourValue, COUNT(*)

                  FROM OCCA

                  WHERE \"k_id_OCCA\" IN(" & Substitute ( List ( OCCA::k_id_OCCA ) ; "¶" ; "," ) & ")

                  GROUP BY YourValue

                  " ; Char(9) ; "" )

                  )

                  • 6. Re: Calculate related values from an array
                    okramis

                    I really don't get it, and this is the most frustrating thing with FileMaker Sql, Sql is difficult enough with out all FIleMakers "" and \.

                    My table is called "OCCA"

                    The primary key is "k_id_OCCA"

                    Im in the context "FILE" where the path to OCCA is named "File_OCCA__Global"

                     

                    In your sql query there is a value called "YourValue" i can't see where it will get its value from? Do you mean that i need to set that value for each itteration in som kind of script? In case you mean that this will not be recursive, it will only calculate a value that have been set by somthing, or am i getting it all wrong? It has happend before

                    "YourValue" is placeholder for the field in OCCA that holds the value you want to be counted, means matches one value of your array, so you have to replace "YourValue" with this field name.

                    Where does your array data come from, is it a manually done selection, or is it the list of values through your FM relation?

                     

                    Let

                    (

                    [_array = Substitute ( FILE::Array ; "¶" ; "," ) ] ;

                    ExecuteSQL ( "

                    SELECT YourValue, COUNT(*)

                    FROM OCCA

                    //this should be

                    WHERE \"k_id_OCCA\" IN(" & Substitute ( List ( File_OCCA__Global::k_id_OCCA ) ; "¶" ; "," ) & ")

                    //assuming File_OCCA__Global is your constrained relation by "startdate, stopdate, < and >...."

                    //if the array is still from relevance you need to add

                    AND YourValue IN (" & _array & ")

                    GROUP BY YourValue

                    " ; Char(9) ; "" )

                    )

                    • 7. Re: Calculate related values from an array
                      taylorsharpe

                      SQL is the best way to go, but I sometimes use this quick and easy way from before SQL days to add up an array.  If the array is:

                       

                      1

                      3

                      5

                      9

                       

                      Then I do a Substitute ( $Array ; ¶ ; " + " ).  This gives me "1 + 3 + 5 + 9".  Then I do an Evaluate of the Array and it adds them all up. 

                       

                      So the formula would be:

                       

                           Evaluate ( Substitute ( $Array ; ¶ ; " + " ) )

                       

                      Works great as long as you don't have blank records or non-numbers. 

                      • 8. Re: Calculate related values from an array
                        5104619256

                        Thanks for your input guys!

                        taylorsharpe

                        Your function replaces carriage return with a plus and adds them but that not what i wanted.

                         

                        okramis

                        I want a recursive function and as i can see this code will onlye get med the value that is in YourValue, that menas that i need to set that value for each itteration and that not what i want to do. That can be done with a script and a temporary relation as well, or am i missing something.

                         

                        Im very thankful for your input but i think that i will have continue with a script and a loop to calculate, in case no one comes up with a recursive function.

                        • 9. Re: Calculate related values from an array
                          okramis

                          Isn't it, that you want to count how many times a certain value is in your OCCA table, matching a value from your array, that's what COUNT(*) and GROUP BY does. If not, you need to describe clearer what you're up to - upload a sample file...

                          • 10. Re: Calculate related values from an array
                            5104619256

                            okramis

                            Yes you'r right thats exactly what i want to do.

                            But the thing is that i don't get it and thats not your fault that mine, ill create a simple template file and upload that is properly easier.

                            A simple file can be downloaded here

                            Note!

                            In my system the field "array" is a complex calculation but the result in it is the same i end up with a bunch of unique values.

                            In my system the relation from "file" to "glob" is based on a couple of fields as well (filtering)

                            • 11. Re: Calculate related values from an array
                              Extensitech

                              arraySummary ( targetValues ; actualValues ) =

                               

                              Let ( [

                              x=1

                              /*

                              //testing

                              ; targetValues = "1¶5¶7¶9"

                              ; actualValues = "5¶5¶9¶5"

                              */

                               

                              ; str = GetValue ( targetValues ; 1 )

                              ; rem = RightValues ( targetValues ; ValueCount ( targetValues ) - 1 )

                              ; cnt = ValueCount ( FilterValues ( actualValues ; str ) )

                               

                              ] ;

                               

                              List (

                                Case (

                                cnt > 0

                                ; str & " " & cnt

                                )

                                ; Case (

                                not IsEmpty ( Substitute ( rem ; ¶ ; "" ) )

                                ; arraySummary ( rem ; actualValues )

                                )

                              )

                               

                              )

                               

                               

                              Chris Cain

                              Extensitech

                              • 12. Re: Calculate related values from an array
                                okramis

                                Here's your file back with a calculation field "SQLResult".

                                I renamed the field "Vakue" to "Value", but as "Value" is a reserved word in sql it has to be quoted (\"Value\").

                                Added a TO "OCCA" as it was missing and I don't like to use TOs in ExecuteSQL witch represent a FM-relation. When you work with ExecuteSQL, always look to have a TO in the graph with the base table name, no connections needed.

                                After those adjustments, my initial query works, just needed to substitute "YourArray" with "FILE::Array", "YourValue" with quoted "Value" and "YourRelatedTable" with "OCCA".

                                 

                                regards

                                Otmar

                                • 13. Re: Calculate related values from an array
                                  5104619256

                                  okramis

                                  Thanks a lot for your answer, iworks like a charm.

                                  I need to add all my terms from my original relations in order to fetch only the filtered amount of records, but that should just be a part of the Where statement.

                                   

                                  Thanks a lot

                                  • 14. Re: Calculate related values from an array
                                    5104619256

                                    Extensitech

                                    Thanks a lot for your answer.

                                    I think that this custom function i very interesting for a lot of Filemaker user, unfortunately i doesn't work for me, but maybe you could take a look at my file and see whats wrong it only returns one value.

                                    1 2 Previous Next