6 Replies Latest reply on Jul 24, 2015 2:26 PM by mrosenhek

    Gathering Values of Related Records

    mrosenhek

      Not even sure how to ask this ...

      I need to create a field that will display all values of another field, based on a grouping of records that have one common field value.

       

      Details

      Existing field is called AFE

      Existing field is called STATUS

      I might have numerous records that have a different status, and it is these values that need to be  displayed in the FIRST occurrence of the record.

      The kicker is that this needs to be accomplished through a self join relationship (I think).

       

      ie

      AFE is the field in common

      Record1 AFE =AAA, STATUS = white

      Record2 AFE =AAA, STATUS = blue

      Record3 AFE =AAA, STATUS = blue

      The solution would display "white blue". In other words, all of AFE AAA's STATUS values. Preferably in each AAA record. Failing that, in the FIRST record of the 3 in question.

       

      Then it would need to have a similar  result in each other grouping of records.

      ie

      Record4 AFE =XXX, STATUS = horse

      Record5 AFE =XXX, STATUS = cat

      Record5 AFE =XXX, STATUS = dog

      The solution would display "horse cat dog".

       

       

      Ultimately this new field will be used as a display in a summary report. The client does not want to see a breakdown, but rather, just the values at the beginning of the report. Any takers on this? I hopes it's easier to create the solution that ask the question ... LOL. If you understand me, you deserve a medal.

      Thanks.

       

      -Moe

        • 1. Re: Gathering Values of Related Records
          Markus Schneider

          You did not specify the version of FileMaker You are running - but with FM12 or newer, I suggest using executeSQL

           

          executeSQL("SELECT DISTINCT STATUS FROM YourTO WHERE AFE=?";"";"";"AAA")

           

          'distinct' gets only one occurense ('blue' oly one time..). The '?' after the condition lets You use parameters, the condition can be entered instead of the '?' as well - but in a script, one can use the same sql with AAA/XXX in a $Variable..

          • 2. Re: Gathering Values of Related Records
            erolst

            Since you only want distinct values …

             

            • create a self-join relationship Table::AFE = Table_selfjoin::AFE

            • create a conditional value list “from field” Table_selfjoin::status, “Include only related values starting from” Table

            • use the calculation

            Substitute (

              ValueListItems ( "" ; "yourValueListName" ) ;

              ¶ ;

              " "

            )

             

            Or

             

            ExecuteSQL ( "

              SELECT DISTINCT Status

              FROM Table

              WHERE AFE = ?

              " ; "" ; " " ; Table::AFE // the respective record

            )

            • 3. Re: Gathering Values of Related Records
              mrosenhek

              That totally works and thank you very much!

              There is no SQL table involved so I used the substitute. I had the Cond VL set up, just needed the right function in the calc.

              -Moe

              • 4. Re: Gathering Values of Related Records
                mrosenhek

                One more question.

                This is what I get (the calc is the yellow field).

                 

                I tried to get commas to separate the values but what it did was.

                 

                Here is the calc I used. I noticed there is a carriage return in your calc but all the values were strung on one line (which is great actually, if I can get commas in there. Any suggestion.

                 

                Substitute (

                 

                  ValueListItems ( "" ; "AFE Status RPT" ) ;

                 

                  ¶  & ", " ;

                 

                  " "

                 

                )

                Thanks.

                -M

                • 5. Re: Gathering Values of Related Records
                  erolst

                  It would help if you knew how that function works, and how the data you're working with is structured.

                  (That goes for every function/script and any code you'll ever write, in any programming language.)

                   

                  The values in a value list are delimited by carriage returns (¶), and that is the character that you want to substitute by a new delimiter of your choice; in your case

                   

                  "white¶

                  blue¶

                  yellow"

                   

                  shall become "white, blue, yellow"

                   

                  Your version says “substitute a carriage return followed by a comma and a blank" with "a blank":

                   

                  Substitute  (

                    ValueListItems ( "" ; "AFE Status RPT" ) ;

                    ¶  & ", " ;

                    " "

                  )

                  … which does not work (and isn't what you meant, anyway) because the string "a carriage return followed by a comma and a blank" does not appear in your value list.

                   

                  What you really want is to substitute “a carriage return” with “a comma and a blank”, so use:

                   

                  Substitute  (

                    ValueListItems ( "" ; "AFE Status RPT" ) ;

                    ¶ ;

                    ", "

                  )

                  • 6. Re: Gathering Values of Related Records
                    mrosenhek

                    Of course. Thanks once again.

                    Have a great weekend.

                     

                    -M