Propa-Gator: Modular Record/Request Creation

by DarrenBurgess

propa-gator-wide

Features Overview

Propa-Gator allows for creation of new records and requests.  The goal was to create a module that accomplished the following:

  • Create a record or request on a target layout and set any number of fields in that record to any desired value (calc, literal, field)
  • Allows data values to have paragraph returns
  • Immune to field renaming
  • No required dependencies
  • Traps and returns various errors
  • Returns the ID of the created record
  • Allows for specification of refresh type using a refresh subscript (NEW with version 1.0.2)

Applications

  • Related record creation
  • Log record creation
  • Flat record normalization
  • Record creation from global UI inputs
  • Scripted find request creation

License

Anyone may do anything with this software. There is no warranty.

Support

Feedback and questions are welcome via darren_burgess@mightydata.com or twitter: @darrenburgess.

There is a blog post documenting and describing the script parameter technique for this module at www.MightyData.com.

Download

05-07-2013: version 1.0.1 – added ability to include paragraph returns in data values.  See internal documentation for syntax.

07-19-2013: version 1.0.2 – added Refresh subscript.  Allows refresh of window as required – none, cache, external, both, cartesian.  See internal documentation.

12-11-2013: version 1.0.3 – bug fix. Insert of data would fail if the data contained “::” or “$”

Download Propa-Gator V 1.0.1.fmp12

Download Propa-Gator V 1.0.2.fmp12

Download Propa-Gator V 1.0.3.fmp12

33 responses to “Propa-Gator: Modular Record/Request Creation”

  1. Nicholas Orr says:

    Darren,

    Does this method not handle field data with new lines in it?

    Cheers,
    Nick

  2. Hey Nick,

    Good point. Field data with carriage returns causes the data to be inserted in the wrong field. I will modify and post a new version. Thanks for the heads up.

  3. Updated to Version 1.0.1. Now allows paragraph returns in data values. See internal documentation for syntax.

  4. Paul Jansen says:

    Very interesting. I deleted the parent-child relationship to test for working with unrelated tables – of course it works which is excellent.

    Thanks for this one.

  5. Thanks for stopping by, Paul. Yes, parent-child is just an application of the module – it is not dependent on such. Do you have any thoughts on the error trapping? I threw that in, however my original script had no trapping, as I was using only for myself and would only use the script with correct implementation of the parameters.

    • Paul Jansen says:

      I agree that generally the parameters would always be correct especially as field names can be selected from a list. The Layout name is the one vulnerability – and one that would catch me out as I am a rubbish typer!

      I think it would be good to trap for the layoutTable matching the first field just in case I pick a layout based on a different TO to the fields I specified in the script parameter.

      Other than that I think it is robust enough. I try not to make things any more complex than necessary.

      This is a nice module that I will certainly adopt.

      My only concern (not just limited to this module) is that I am not wholly convince by the let format for declaring variables. Not sure why, it just seems to produce parameters that are rather more complex than I would like. Personally I use a different format which is less syntax dependent and a single script step referencing a custom function to create all the local variables from the parameter.

      I guess I also don’t like to have the same code in multiple locations as it is a pain to modify compared to a custom function…

      • Regarding your suggestion for an additional error trap, I believe that situation (wrong TO) would be caught when the script hits set field by name. If you have any more suggestions for improvement, do let me know.

        • Paul Jansen says:

          You are of course correct. I missed that. I was focused on the Field name when I looked at that error trap 🙁 Oops

  6. I wrote a more extended MightyData blog post describing this module: http://wp.me/p2RjLR-Tx

  7. […] I posted my first contribution to Todd Geist’s new collaborative effort to create a library of shared FileMaker modules. This script was originally inspired by the need to create reusable code for my customer projects […]

  8. Dale Long says:

    This script can produce problems if a field happens to be empty. For example, I am working on a different method to import data from a MySQL table using this script, as I really like the concept of using this script to normalize flat data. In this import, on any given record some fields may or may not contain any data, and some data may or may not contain carriage returns. Since this script was designed with a markup for fields with carriage returns, I anticipated this problem and first made a custom function to use for each item in the $CR_Data list that substitutes out any carriage returns in a field with the ~CR~ markup. However, when a field contained no data I discovered that this also screwed up the $CR_Data script parameter. So I tried updating that custom function, and using a – character if there was no data in the field…unfortunately there’s still something in some records that disagree with this script that I can’t find yet, and data ends up in the wrong fields on some records during the import. I’ll continue trying to refine my custom function, but I just wanted to put this out there so that the author and others adopting this script are aware of a potential pitfall with fields that don’t always have data in them.

    • Thanks for the heads up regarding this. I will look at this and post and update that handles this use case.

    • Dale, I was thinking on this some more. It seems the best way around this is to wrap the CreateRecord step in some conditional logic, and so only run the create if the source field [not isempty()]. This is how I constructed my scripts to normalize flat data using the CreateRecord script.

      Will that work in your case?

      • Dale Long says:

        Hi Darren, Thanks for the followup. I don’t think this would work in my case, since the record still needs to be created whether field x from the flat source contains data or not ( fields y and z still have data that needs to be imported).

        What I did to address my problem was customize the CreateRecord script to work with other conventions in our database. Long ago I adopted a method from the guys at sixfriedrice for passing multiple script parameters as name-value pairs ( http://sixfriedrice.com/wp/passing-multiple-parameters-to-scripts-advanced/ ), which handles multiple parameters by using a series of dictionary custom functions. I use these methods of passing parameters into the slightly rewritten CreateRecord script instead; the dictionary functions used in this method are not thrown off by a potentially empty field, so this was able to resolve the particular problem I was having. This might not be easily adopted into your module, since it relies on a combination of custom functions and scripting; however, the base structure of your script did prove very helpful to get my brain thinking in the right way for methods of normalizing our data!

        • Good, glad the module was good starting point for you. The convention at Modular FileMaker is to avoid custom functions were possible, but I will look into possible updating the parsing script step to account for null values.

  9. Paul Jansen says:

    I am just implementing this module to create records in a temporary table for export and have come up with a couple of issues. I want to be able to optionally prevent the Refresh[Flush cached join results] as it is not relevant in the situation and also not return the ID as this too is irrelevant in this situation.

    I am trying two additional optional script parameters $CR_NoID and $CR_NORefresh. If these are True then the relevant script step is skipped. If they are not provided all proceeds as normal.
    If[ $CR_NoRefresh ≠ True ]
    Refresh Window[etc]
    EndIf[]

    Open to improvements or better ways to handle this, but I do think it should be included for this to be a useful generic record creation function.

    • Paul,

      Thanks, you just uncovered a limitation that warrants adding to the script parameter. I would take your idea a step further. Let me know what you think of this:
      1. add a parameter $CR_RefreshType ( 0 for no refresh; 1 for Refresh Window [Flush Cache]; ; 3 = Refresh [Flush Cache, Flush External] ; 4 = Cartesian flush )
      2. add a parameter $CR_RefreshField

      In the case of type = 4, the script would Set Field By Name a global field across a cartesian relationship, allowing the script to refresh just that relationship. Additional Schema required for this. Refresh Window (flush Cache) is overkill in many instances and can cause performance issues over LAN or WAN as per: http://www.teamdf.com/weetbicks/ditch-those-flush-caches-use-cartesian-join-instead/42/

      What do you think?

      • Paul Jansen says:

        Sounds like a good enhancement to cover all the options. Using numbers does seem a bit obscure – I know I would have to keep checking which number represented which option. Don’t have any brilliant suggestions for solving this although one possibility does occur.

        $CR_RefreshType = choose( 0 ; “none” ; “cache” ; “external” ; “both” ; “cartesian” ) would be somewhat self explanatory.

        Regarding the ID issue, could a check be added to see if the ID field exists before attempting to return it as the script result?

        Possibly the refresh options could be a mini module or a ‘shared’ subscript

        • Yes, you are right that the Types should be plain english. Regarding the ID field, why is it important to you to have the option to not return such? I have not encountered a use case where passing such when it is not needed created a problem. Have you?

          • Paul Jansen says:

            I guess it would be simpler to add an ID field to my temporary table then at least everything would be consistent. That’s probably the simple solution. I think I’ll do that rather than complicate the module. 🙂

            What do you think about the Refresh being a subscript for more general use?

    • Paul,

      Yes, refresh should be a subscript. I was considering creating a ‘grab bag’ module with a library of various small scripts that don’t warrant their own module

  10. Hi!

    Just a thought – If you open a new window before you go to the $CR_LayoutName and then close this window once commit of the new record is done the user will be returned to the original layout in the same state as when he/she triggered the script. Same field will be active, same tabs will be active and the scrollbar will not have changed…

    This is as far as I know not the case when switching layouts and using goto original layout is used. New window is our friend!

    • Kalie,
      Thanks for stopping by and contributing. My original script, now several versions back, did just that. I moved to the freeze window technique as it works better for Windows users that maintain their window as maximized. I would prefer to user your technique. The CreateRecord script should be run as a sub-script if you want to restore tabs and what not. Or run a tab memory script on layout load? Or you can modify CreateRecord to suit your needs.

      Darren

  11. Tom R. says:

    Hi,

    I noticed in your blog post at mightydata.com that, when creating records based on global fields, the module is supposed to nullify the contents of the global fields after the record is created. This doesn’t seem to be happening in the example file I downloaded. Is there something in particular I need to do to enable or disable that behavior?

    Thanks!

    Tom

    • Tom,

      That was extremely weird. What you uncovered was that the FieldType() function fails when the file name has period in it as in “Test.v.1.fmp12”. If I removed all of the dots from the filename, the globals would be nulled as expected. What was perplexing about this was that the script works perfectly in customer production databases. Of course, those production databases do not have dots in the filename. The gotcha here was that this module did not originally have dots in the filename, so it worked fine initially, and I probably have not tested that functionality since.

      Good catch! thank you. What I have done is change the field type function as follows:

      Case (
      PatternCount ( FieldType ( Get ( FileName ) & “.fmp12” ; $value ) ; “global” ) ;
      1;
      “”
      )

      Which will return ‘1’ when the field type is global. Adding the & “.fmp12” fixes the issue. Of course this will BREAK when/if FileMaker updates their file extension again. Hopefully that is a long way off. 😉

      I have updated the version 1.0.2 download link above.

  12. Bob Ellis says:

    Hello Darren – thank you for this great submission, I have been using it extensively. I recently created a logging database where users can log changes made to databases. The note field is used for whatever notes the developer would like to leave in regards to the changes made to a particular system. The issue arose when entering a variable name or field name into the notes along with some notes as to the usage of the variable, field name etc. I did some digging and found that there is a check for “::” or “$”. If either of these are found the data is evaluated. In this instance, it was returning a ? because either of those things with a string of text encompassing it will not evaluate properly. To alleviate this issue I changed the calculation to the following:

    Let ( [

    data = $value ;

    IsFieldName = PatternCount ( data ; "::" ) ;

    IsVariableName = PatternCount ( data ; "$" )

    ] ;

    Case (

    ( isFieldName = 1 or isVariableName > 0) and IsValidExpression ( data ) ;
    Evaluate ( data ) ;
    data

    ) // end Case

    //data
    //IsFieldName
    //IsVariableName

    ) // end Let

    I have not extensively tested this, but for what has been thrown at it, it seems to be working as intended.

    I have also modified the script to handle updating records. Basically I am passing another parameter that tells it to find and update a record instead of creating a new one. If the intended record is missing it will then create a new record instead of updating and return the id as usual. If you would be interested in looking these over and possibly adding them to your module, let me know.

    Again, thank you for the contribution!
    Bob

    • Thanks Bob! Great catch and fix regarding the presence of $ and :: in the data. I am also considering your idea to include and record find and update function. Just have to make sure that if a developer updates the script with a new version that the new script does not break calls to the script previously installed in a system.

      I am uploading v1.0.3 now

  13. Bob Ellis says:

    Hey Darren, apologies for the delayed response.

    This is the change I made to the script:

    If [$UpdateRecord and not $NewRecord]
    Enter Find Mode []
    Set Field By Name [Get ( LayoutTableName ) & "::id"; $FindRecord]
    Perform Find []
    If [Get ( LastError ) = 401]
    Perform Script ["CreateRecord 2"; Parameter: Get ( ScriptParameter ) & ";¶" & "$NewRecord = " & Quote("1")]
    Set Variable [$!; Value:#Assign ( Get ( ScriptResult ))]
    Go to Layout [original layout]
    Commit Records/Requests []
    Exit Script [# ( "id" ; $id )]
    End If
    Else
    # Create the record/request
    New Record/Request
    Set Variable [$error; Value:Get ( LastError )]
    If [$error ≠ 0]
    Go to Layout [original layout]
    Exit Script [# ( "error" ; $error )]
    End If
    End If

    And the upper part of the calling script looks like this:

    "$CR_LayoutName = " & Quote("TableName: Blank") & ";¶" &

    "$CR_RefreshType = " & Quote("none") & ";¶" &

    "$UpdateRecord = " & Quote("1") & ";¶" &

    "$FindRecord = " & Quote( TableName::idField ) & ";¶" &

  14. […] posted my first contribution to Todd Geist’s new collaborative effort to create a library of shared FileMaker modules. This script was originally inspired by the need to create reusable code for my customer projects […]

  15. […] Propa-gator is a simple module for record creation.  It allows you create a record in any context and set any number of fields in that new records.  I wrote about this module in the MightyData blog as well. […]

  16. Robert Naud says:

    I’m coming late to the party (Thanks Darren for this great contribution!), but when using the CreateRecord script to create find request, I feel like adding an optional parameter that would allow for the find request to be omitting records rather than finding them. I’m having a hard time to think of a scenario where someone would want to omit the created record while in browse mode, so maybe the CreateRecord script could test to see we are in find mode before it runs the “Omit Record” script step.

    • Thanks Robert. Sorry for the late response. I don’t think I got an email notification regarding your suggestion.

      It is a good one. I will add it the road map for this project.

Leave a Reply

Your email address will not be published. Required fields are marked *