2 Replies Latest reply on Apr 17, 2015 3:35 PM by lumberjacklane

    Custom Function working differently in FM Pro / FM Pro Advanced

    lumberjacklane

      Summary

      Custom Function working differently in FM Pro / FM Pro Advanced

      Product

      FileMaker Pro

      Version

      13.0v5 (advanced) 13.0v9 (pro)

      Operating system version

      Mac OS X 10.10.2

      Description of the issue

      I created a custom function to pull values from a "value list" table, set those values to a global variable, and use a virtual list technique to populate a value list for that field.  This technique has worked great for a while, and continues to work great on my machine running FM 13 Pro Advanced.  We now have customers who get a blank value list, and I have isolated the issue to the custom function; it appears to simply not work correctly on their end.  They are running FM Pro 13 0v5 or 0v9 (confirming now). 

      I have downloaded a trial of FM Pro 13 on my machine, and the value lists and custom function still work fine.  I just downloaded a trial of FM Pro 13 onto another clients computer, and everything works fine.

      So, it seems like this cannot be an issue of security or access (all security accounts work on my machine, none on my clients), or advanced vs. pro (works in both on my machine, works in Advanced with one client, does not work in Pro for others). 

      Is there anything in the custom function below that anyone can spot that may not work in FM Pro 0v5?  Anyone had any similar issues?

      Exact text of any error message(s) that appear

      /*

      This custom function was built to make it very simple to assign the virtual list parameter to field script triggers

      */

      Evaluate (

      Let ( [

      _field = Get (ActiveFieldName ) ;
      _table = FMbaseTable ( Get (ActiveFieldTableName ) ) ;
      _listID = ExecuteSQL("SELECT uidList FROM valueListAssignment WHERE fieldName = ? AND layoutID = ?" ; "" ; "" ; Get (ActiveFieldName) ; GetValue ( LayoutIDs ( Get (FileName ) ) ; Get ( LayoutNumber ) ) ) ;

      $$virtualListType = "" ;

      $$virtualList =

      Case (

      //  BROWSE MODE OR HELD OPTION DOWN

      ( Get (WindowMode) = 0 ) or ( Get (ActiveModifierKeys) = 8 ) ;

      ExecuteSQL ( "SELECT name FROM valueListItem WHERE uidList = ?" ; "" ; "" ; _listID ) ;


      //  FIND MODE


      (Get (WindowMode) ≠ 0 ) ;

      ExecuteSQL ( "SELECT DISTINCT ( " & Quote ( Get (ActiveFieldName) ) & ") FROM " & _table; "" ; "" )

      )
      ]
      ;
      ""
      )

      )

        • 1. Re: Custom Function working differently in FM Pro / FM Pro Advanced
          philmodjunk

          The code base for Advanced and PRo are nearly identical as I understand it. Pro is just Advanced with some features "turned off". So it seems very unlikely that pro vs. advanced is the source of the trouble. If it is, this is a bug to be reported in report and issue.

          But I do see a potential source of trouble in these function calls:

          Get (ActiveFieldName )
          Get (ActiveFieldTableName )

          Both assume that the user's cursor is located inside a field, making it the active field referenced by these functions. If the cursor is not always in the field at the time this function evaluates, these functions return null.

          • 2. Re: Custom Function working differently in FM Pro / FM Pro Advanced
            lumberjacklane

             

            Hey Phil, as always, thanks so much for your input.

            An update: one of my clients just updated to 0v9, and the values lists (virtual list) is back!  I have to assume that is the problem, and will have the other clients update to make sure.  

            In answer to your warning, this function is called as a script parameter, in an onObjectEnter trigger on the fields using the virtual list.  So, user clicks into the field, the first thing that happens is the parameter fires off, uses SQL to generate a global variable of related value list items, the virtual list table / fields update to take their values from that global variable, and the list populates.  I understand how it works on a pretty deep level, but it still seems a bit like magic.  But it works great, keeps my client from having two hundred value lists that don't get migrated during upgrades, and allows a slick user interface for editing these value list records.  

            Thanks again for your prompt reply!

            Kevin Lane