9 Replies Latest reply on Dec 11, 2014 8:22 AM by andyk3005

    Creating a global with full record details including field names

    andyk3005

      Hi,

      I am new to FileMaker and I am considering using it to develop and replace legacy 4GL systems built for a number of clients over the last twenty years. The product seems ideal in many respects for fast prototyping and development, however I am having some trouble with the fact that scripts and functions appear to have different techniques for addressing programmatic issues.

       

      For example;

      I want a global variable to represent the content of a specific table record and I would wish it to be general to all tables. It would be preferable to set it up as a function.

       

      Table with Field1 and Field2

       

      Required output

      $$Table =

      “NameField1; Content1

      NameField2; Content2”

       

      Procedurally I would expect to

       

      In a script:

      Address Table

      Find required record

      Address first field

      Set variable to run function

       

      In function:

      Loop

      Write to variable:

      Get(ActiveFieldName) & “;” & Get(ActiveFieldContents) & <CR>

      Goto NextField at end of file exit.

      Goto LOOP

       

      Any tips to address this or similar issues would be appreciated.

       

      Thanks

      Andy

        • 1. Re: Creating a global with full record details including field names
          DavidJondreau

          What is the purpose of this? What end-user experience are you trying to achieve?

           

           

          Also, do you want all the fields or just the fields on the layout (for example primary keys are rarely accessible to the user on the layout, but every table has them).

           

          If it's all the fields, then ExecuteSQL() is the way to go. If it's the second then a Looping script that goes to each field will probably work fine.

          1 of 1 people found this helpful
          • 2. Re: Creating a global with full record details including field names
            l_allen_poole

            Hi Andy -

             

            Looping within custom functions, which you can author if you have FileMaker Advanced, is possible. Since function execution can't be monitored by the script debugger, though, I recommend you start with a scripted loop.

             

            The List function is handy for constructing arrays:

            Set Variable $$Table = List ( $$Table ; Get(ActiveFieldName) & “;” & Get(ActiveFieldContents) ) will append the new information to the end of your growing array of name/value pairs.

             

            You can discern the names of fields on a layout using the function and script step you describe, and you can also work with fields _not_ on a layout if you first determine their names. ExecuteSQL can return a list of all fields names in a table, and I use this when I do not want to be limited by any specific layout. (Note also that a layout can display field data from other tables, and the GoTo Next Field script step doesn't guarantee you land only on fields from a particular table. Note also: GoTo Next Field doesn't have any inherent exit condition and will loop all layout fields indefinitely unless you tell it when to stop.

             

            If you want your script to be context-agnostic, I suggest you write it to depend less on layout objects. You'll still need 1 table occurrence in your relationship graph per table you want ot work with, but Execute SQL can free you from FileMaker's traditional dependence on both layouts and relationships.

            1 of 1 people found this helpful
            • 3. Re: Creating a global with full record details including field names
              Mike_Mitchell

              Andy -

               

              Following on Allen's suggestion to use ExecuteSQL (the function, not the script step), this post from DataBuzz will probably be of some use:

               

              http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/

               

              Mike

              • 4. Re: Creating a global with full record details including field names
                andyk3005

                Hi Guys,

                Thank you for your prompt and helpful replies.

                • At present I am not looking at an end user just trying to get my head around why scripting and custom functions/calculations don’t use the same syntax/processes.
                • Having to create a process differently in a script to a custom function and not being able to debug through the custom function has left me slightly confused.    From what I can gather in a function/calculation to LOOP one has to use something like Case(Counter > 0; calculation;exit).   Is this right
                • I am used to having complete freedom to address any file, write and call any procedure and not be limited to opening a layout or having a defined relationship.     The purpose of the exercise was an attempt to better understand the relationship between scripts and custom functions.
                • I will look into the SQL call although the attempt was to capture the field names  and the content of a single record.   Not just the field names of the file.

                 

                Regards

                Andy

                • 5. Re: Creating a global with full record details including field names
                  coherentkris

                  Andy,

                  You said "From what I can gather in a function/calculation to LOOP one has to use something like Case(Counter > 0; calculation;exit)"

                   

                  To "loop" within a function or calculation you would use recursion. http://help.filemaker.com/app/answers/list/search/1/kw/recursive%20custom%20functions/suggested/1

                  • 6. Re: Creating a global with full record details including field names
                    Mike_Mitchell

                    Andy -

                     

                    "Scripting" and "functions" in FileMaker are significantly different animals, perhaps a departure from what you might be used to in other environments.In many other environments, you write procedures and functions essentially using the same coding language; the only difference would be how the code is applied or how parameters might be passed.

                     

                    In FileMaker, scripts and functions occupy completely different areas of the object model. You can write a script that corresponds (loosely) to a procedure, and you can write a script that corresponds (again, loosely) to a function in some other environment by configuring the script to accept parameters and use strictly memory variables. This might correspond more closely to a paradigm with which you might be familiar.

                     

                    Functions, on the other hand, operate strictly within the confines of the calculation dialog. FileMaker's calculation engine is used in several places (field definitions, several functions - built-in and custom - script steps, and others). It represents a completely different area, however, from scripting or coding, and operates along completely different rules. This is why the term "function" means something different in FileMaker than it does in many other programming environments.

                     

                    HTH

                     

                    Mike

                    • 7. Re: Creating a global with full record details including field names
                      wimdecorte

                      andyk3005 wrote:

                       

                      • I am used to having complete freedom to address any file, write and call any procedure and not be limited to opening a layout or having a defined relationship.     The purpose of the exercise was an attempt to better understand the relationship between scripts and custom functions.

                       

                      Everything in FM is based on context.  And context is based on a layout, a layout is based on a table occurance (TO) which is based on a base table.  What you can do and what data you can touch is based on the TO of the layout you are on: the relationships that branch out from that TO to other TOs on the graph.

                       

                       

                      When I said everything I meant everything except ExecuteSQL() calls.  But those can only do SELECT, not INSERT or UPDATE.

                      • 8. Re: Creating a global with full record details including field names
                        l_allen_poole

                        Hi Andy -

                        andyk3005 wrote:

                        • At present I am not looking at an end user just trying to get my head around why scripting and custom functions/calculations don’t use the same syntax/processes.

                        FileMaker's been around for decades, and while it's evolved considerably since inception the scripting/calculation divide is a legacy decision that remains in effect. To elaborate on what others have said, it's not a complete divide: you can use calculations (and functions) in many script steps. You just can't embed a script (or even a script step) in a calculation or function.

                        • Having to create a process differently in a script to a custom function and not being able to debug through the custom function has left me slightly confused.    From what I can gather in a function/calculation to LOOP one has to use something like Case(Counter > 0; calculation;exit).   Is this right

                        Yes, exit conditions need to be created/stated explicitly in both scripted loops and in recursion. Some like to locate their recursion in a "wrapper" or "handler" custom function that calls one or more non-recursive CF to reiterate tasks—Will Baker at Beezwax introduced me to this trick—while others like to embede the recursion inside the CF that does all the work. Different memory limits and performance expectations apply to tail recursion, so that's worth taking a separate look at as well.

                        I am used to having complete freedom to address any file, write and call any procedure and not be limited to opening a layout or having a defined relationship.     The purpose of the exercise was an attempt to better understand the relationship between scripts and custom functions.

                        Here's a gotcha you might not anticipate: global variables are limited in scope to the context of the _file_ they were defined in. If a script or function (the Let function can instantiate variables) creates a global variable, that varaible is accessible to all scripts & calculations & layouts within that same file. This one of the many considerations that go into choosing a singe file vs. multi-file application architecture.

                        • I will look into the SQL call although the attempt was to capture the field names  and the content of a single record.   Not just the field names of the file.

                        It's a multi-step process: use the ExecuteSQL function to gather an array of field names, and then loop through this array to get field contents for each.

                         

                        Cheers,
                        Allen

                        • 9. Re: Creating a global with full record details including field names
                          andyk3005

                          Hi Guys,

                           

                          Even if the filemaker coding areas are unweildy on first aquaintence the community help is certainly first class.   I will now try a few of your suggestions.

                           

                          Thanks again

                          Andy