12 Replies Latest reply on Mar 2, 2014 8:54 AM by wimdecorte

    Looping through $ or $$ variables  -- Super variable?

    slayden@msn.com

      Thanks in advance for any help.

       

      Is there a construct in FmPro that allows looping through variables, making a "super variable" or group of super variables?

      Then possibly query against the "super variable(s)?"

      One use in particular would be to create and hold several loan amortization loops in memory, then have the ability to pull down, say, the results or row 60, 120, 180, or any other row and do sumarization calcs and compare the costs of different loan options at different loop iterations.

       

      I realize I can clunkily name every variable by concatenating row info, then stripping it back out if I pull it down and use the vairalbe data, but it seems like there must be a way to just name the rows and not have to mess with adding some ID to the row's data to pick a row's data out.

        • 1. Re: Looping through $ or $$ variables  -- Super variable?
          rgordon

          $$ Variables can have repeat values so each repeat could hold a row.  The repeat value could correspond to the row. 

          • 2. Re: Looping through $ or $$ variables  -- Super variable?
            Mike_Mitchell

            If you're asking what I think you're asking (and I'm not sure I do), it sounds like you want an array. Your need can probably be met most closely in FileMaker through the use of a repeating global field or set of fields. That allows you to call a specific repetition (which variables don't) and to leave particular positions blank (which variables don't either).

             

            Another option would be to use a delimited variable, in a sort of matrix arrangement. Might look something like this:

             

            ID-01|$1.00|2014-Feb

            ID-02|$1.01|2014-Mar

             

            where each row represents a "record", and each "field" is separated by a specified delimiter (in this case, a pipe character). I do this sometimes. You can grab a particular row using GetValue. I use a custom function to pull out a particular column. Here it is:

             

            ---------------------------------------------------------------------------------------------------------------

            /*

            GetColumn function

             

            Author

            *COMMENT Visual Realisation

             

            Format

            cfGetColumn ( text ; columnNumber ; columnDelimiter ; result )

             

             

            Parameters

            text - any text expression or text field

            columnNumber - any numeric expression or field containing a number

            columnDelimiter - any text expression or text field

            result - initially empty; used for tail recursion

             

            Data type returned

            text

             

            Description

            Returns a carriage return-delimited list of values from the requested column in a tabular text array.

             

            July 7, 2008

             

            Modified February, 2014 to use tail recursion

            Mike Mitchell, Net Caster Solutions

            www.netcastersolutions.com

             

            */

             

             

            Let ( [

            row  = GetValue ( text ; 1 ) ;

            cell = MiddleValues ( Substitute ( row ; columnDelimiter ; ¶ ) ; columnNumber ; 1 ) ;

            cell = Substitute ( cell ; ¶ ; "" ) ;

            countRows = ValueCount ( text )

            ] ;

             

            Case (

            countRows > 0 ;

            cfGetColumn ( RightValues ( text ; countRows - 1 ) ; columnNumber ; columnDelimiter ; List ( result ; cell )) ;

             

            result

             

            )

            )

             

            ---------------------------------------------------------------------------------------------------------------

             

            HTH

             

            Mike

            • 3. Re: Looping through $ or $$ variables  -- Super variable?
              erolst

              rgordon wrote:

               

              $$ Variables can have repeat values so each repeat could hold a row.  The repeat value could correspond to the row. 

               

              Actually, they don't:

               

              http://fmforums.com/forum/topic/89942-list-variable/?hl=variable+repetition

               

              … but you can build a list variable with … well, List(), and reference your values by using GetValue(); see the same thread.

               

              EDIT: Or, of course, use something more sophisticated, like the solution Mike suggests …

              • 4. Re: Looping through $ or $$ variables  -- Super variable?
                beverly

                Variables that otherwise don't contain returns can be a LIST of values. And that leads to using the value functions on the list. Getting is much easier than setting a particular value in a list, but it can be done.

                 

                -- sent from my iPhone4 --

                Beverly Voth

                --

                • 5. Re: Looping through $ or $$ variables  -- Super variable?
                  rgordon

                  Really?

                   

                  So if I have 5 records that contain the following data in the serial field: 1,2,3,4,5

                  and I run the following Script

                   

                  Go to Record/Request/Page [First]

                  Loop

                     Set Variable [$count; Value:$count + 1]

                     Set Variable [$$Repeat Variable[$count]; Value:serial]

                     Go to Record/Request/Page [Next; Exit after last]

                  End Loop

                   

                  and the Data Viewer shows

                   

                  $$Repeat Variable [5]             5

                  $$Repeat Variable [4]             4

                  $$Repeat Variable [3]             3

                  $$Repeat Variable [2]             2

                  $$Repeat Variable                  1

                   

                  What is the $$Repeat Variable?  You might call it something different like 5 different variables but to me it looks like one variable with 5 repetitions  so I'll agree with Jbante and call it a repeating variable. I believe if you have a large list and say you want to retrieve row 160, $$Repeat Variable[160] is going to perform more efficiently than GetValue($$list;160)

                  • 6. Re: Looping through $ or $$ variables  -- Super variable?
                    wimdecorte

                    slayden@msn.com wrote:

                     

                    seems like there must be a way to just name the rows and not have to mess with adding some ID to the row's data to pick a row's data out.

                     

                    That's the concept of "named buckets".

                     

                    Say that you have a found set of record and you need to store some info about them.  You can use the ID (primary key) of the record as the index of the repeating variable to store the data in:

                     

                    Say you  have 3 records with IDs

                    1000

                    1001

                    2000

                     

                    you can store what you need in

                    $record[ 1000 ]

                    $record[ 1001 ]

                    and

                    $record[ 2000 ]

                     

                    These do not need to be sequential.

                     

                    Taking this a step further.  If you want to do this by something's name and not say the unique record ID you can turn a string into a number to use for the variable's index by using the Code() function

                     

                    $record[ Code( "Wim" ) ]

                    $record[ Code( "Bob" ) ]

                    $record[ Code( "Bev" ) ]

                     

                    This way you can always retrieve the values by addressing the "named bucket"

                    • 7. Re: Looping through $ or $$ variables  -- Super variable?
                      rgordon

                      Wim, thanks for sharing the Code tip.  I try to use variables whenever possible, especially when developing for FMGO.  This tip will definitely be handy to have.

                      • 8. Re: Looping through $ or $$ variables  -- Super variable?
                        Tom_Droz

                        I have a solution where I create  amortization schedules of a loan.  I create a set of records for each payment (Interest, principal, payment amount, payment number, balance due, date due etc.)

                         

                        When I need a new loan, I delete all the old records.  Perhaps you could use this process to do comparisons but modify the table or add tables for multiple loans?

                         

                        Tom

                        • 9. Re: Looping through $ or $$ variables  -- Super variable?
                          slayden@msn.com

                          So I'm clear in my mind,,,For multiple values in each record:

                           

                          Go to Record/Request/Page [First]

                          Loop

                          Set CounterVariable [$count; Value:$count + 1]

                          Set Variable1 [$$Variable1[$counterVariable]; Value:serial]

                          Set Variable2 [$$Variable2[$counterVariable]; Value:loanbalance]

                          etc. for all needed variables

                          do logic

                          End Loop

                           

                          Then, I would simply call by naming the $$VariableX[CounterVariable] as I needed it?

                           

                          Thanks in advance!

                          • 10. Re: Looping through $ or $$ variables  -- Super variable?
                            slayden@msn.com

                            So, how would one create and and set a list of values that needed to

                            1. hold multiple fields
                            2. have logic dynamically performed on each iteration of the loop (lot's of if/thens), then
                            3. "saved" in memory each row to be called for as long as the session lasts or until overwritten?

                             

                            How would one call, say, a named variable within a list in a row with multiple variables (principle, interest, equity, etc.)?

                             

                            Thanks!

                            • 11. Re: Looping through $ or $$ variables  -- Super variable?
                              slayden@msn.com

                              So, would this allow an array within each bucket that I could call a particular row variable within that bucket?  That sounds closest to what I need....and call on the variables within a bucket.

                               

                              In this case, I need to compare total costs at various points in time for various mortgage loan options (higher rate/lower upfront costs versus lower rate/higher upfront costs and at different amortizations ie 15, 20, 25, 30 years....)

                              In a nutshell....grabbing data from various arrays and comparing them to each other for the lowest cost solution.

                               

                              So, each option would be its own bucket?

                              The option record would have its own ID (optionID) and contain the variables the amortization loop would need to run accurately.

                              The array would be created in memory and be held in its optionID bucket space for the length of the session or until overwritten (this would require $$ variables, correct?)

                               

                              I come from a Coldfusion background, where we would use dot notation to get at the variable in a particular bucket (in that language, called a struct)

                              OptionID.BucketID.ArrayName.ArrayRow[X].VariableName

                               

                              How would I create, populate and call data from these in Filemaker?

                               

                              Specifically :  $$OptionRecord[ID] - how to fill it up with an amortization array (row number, each variable of each row)?

                               

                              Thanks in advance!

                              • 12. Re: Looping through $ or $$ variables  -- Super variable?
                                wimdecorte

                                Not quite sure what you are aiming at.  Is your question: can a variable repetition hold another variable with repetitions?  Then no.  Each rep of a variable can hold a return delimited list of info and using custom delimiters they can hold a "grid" of columns and rows as long as each row is return delimited.