the Otter

  • Content count

  • Joined

  • Last visited

  • Days Won


the Otter last won the day on October 15 2014

the Otter had the most liked content!

Community Reputation

2 Neutral

About the Otter

  • Rank
    second-generation FM developer
  • Birthday 04/10/1975

Profile Information

  • Gender
  • Location
    Cincinnati, OH, USA

Contact Methods

  • Website URL

FIleMaker Profile

  • Profile Updated
  • FM Application
    15 Advanced
  • Platform
    Mac OS X El Capitan
  • Skill Level
  • Certification
  • Title
    Senior Application Developer
  • Industry
    Brand Building

Recent Profile Visitors

1,302 profile views
  1. Yes, thank you. That’s what I get for typing hurriedly. I have fixed my previous comment.
  2. Thanks, jbante. Actually, the custom function only ignores error #0 if any error (including 0) was previously recorded. However, you are correct that I have apparently misunderstood the issue: I tried a simpler custom function that sets a variable and it was able to pass that variable to the script. Despite my misdiagnosis of the problem, there is still a change in behavior: the first version of the script still crashes FileMaker 14, but not 15; and the second version still fails to set $Error to 0 in FileMaker 15, but not 14. I will update the original post once I have more accurately diagnosed the scenario in which this occurs.
  3. As most people here probably know, the Let ( ) function can be used to define a Local variable. As such, it is possible to build a custom function that defines such a variable, and it is further possible to set said variable to a value including itself. An example would be the following custom function, ErrorList, consisting of the following calculation: Let ( $ErrorList = List ( $ErrorList ; Get ( LastError ) ) ; "" ) If a Set Variable script step sets the same variable as a custom function like the one above, e.g. Set Variable [ $ErrorList ; Value: ErrorList ] …the script step will run appropriately, so long as the contradictory variable—in this case, $ErrorList—is not yet defined. However, once this variable has been defined, executing the preceding script step will cause FileMaker 14 (and perhaps other versions) to suffer an Error #1213 and crash the application. The workaround for this behavior is to have the Set Variable script step set a dummy variable, e.g. Set Variable [ $x ; Value: ErrorList ] Even if $x is not referenced anywhere, having a script call the ErrorList function passes the variable $ErrorList to the script’s own context, thus allowing its value to be accessed by later steps in the same script (including subsequent calls to the ErrorList function itself). In FileMaker 15, this behavior has been changed: local variables defined within a custom function are now valid only within the scope of the function itself, including any recursions. While this alleviates the problem of application crashes, it also results in unexpected behavior when scripts written in earlier versions of FileMaker rely on custom functions to set local variables. When migrating to FileMaker 15, each affected script step must be updated to set the target variable explicitly instead of relying on the custom function to do the work. In other words, the code: Set Variable [ $ErrorList ; Value: ErrorList ] …which proved fatal in FileMaker 14, is now required grammar for FileMaker 15: FileMaker 15 believes that what happens in the function stays in the function, instead returning the result of the calculation to the variable defined in the Set Variable script step. The FileMaker 14 grammar, Set Variable [ $x ; Value: ErrorList ] …thus sets $x to the intended value of $ErrorList while leaving the value of $ErrorList as null. Unfortunately, this cannot work effectively in a mixed-installation environment: the FileMaker 14 grammar leaves FileMaker 15 clients with unintended null values; the FileMaker 15 grammar causes FileMaker 14 to crash. When upgrading all users to FileMaker 15 is not feasible, the best workaround is to use the FileMaker 14 grammar, then once all relevant script steps are complete, check the value of the intended variable (e.g. $ErrorList) and, if empty, set it to the value of the dummy variable (e.g. $x). ErrorTest.fmp12
  4. Sorry to resurrect an old thread, but I’ve just discovered that FileMaker 13 does allow you to ORDER BY the same field as GROUP BY. So, this should work: SELECT MAX(citystatezip_id),zip FROM citystatezip WHERE city='Wilmington' GROUP BY zip ORDER BY zip Just FYI….
  5. Aussie John, Ignoring the data cleanup for a moment, how about: If ( PatternCount ( ¶ & List ( "tutor" ; "class" ; "lecture" ) & ¶ ; ¶ & roomname & ¶ ) //end PatternCount ; Roomarea ) //end If Obviously this could be simplified with a custom function, but that’s basically what you’re looking for, right?
  6. If you’re going to be doing this kind of thing often (and if you’re using FileMaker Pro Advanced), it would make sense to use a custom function. However, if you’re just looking for a one-off solution to this specific scenario, we can use the following: Let ( [ startText = "¶Ads¶Company Info" ; sStart = Position ( fieldName ; startText ; 1 ; 1 ) ; sLength = Length ( startText ) ; sEnd = sStart + sLength ; endText = "Map" ; eStart = Position ( fieldName ; endText ; sEnd ; 1 ) ] ; //end define Let Middle ( fieldName ; sEnd ; eStart - sEnd ) ) //end Let Let’s analyze what we’re doing here: The Let function allows us to define variables in our calculation. By using brackets, we can define multiple variables in a single let function. The portions of the code preceded by // are comments and thus optional, but help us read what’s going on here. startText is the text you want to find first. (Note: the pilcrow character, ¶, represents a carriage return in FileMaker calculations.) sStart is the position where the first occurrence of startText appears in the field, starting from the first character in the field. sLength is how many characters are in startText. sEnd is the position of the first character in startText plus the number of characters in startText, so it gives us the position of the first character after startText. endText is the text you want to find second. eStart is the position where the first occurrence of endText appears in the field, starting from the first character after startText. To get the portion of the text you want, we use the Middle function, which takes three parameters: the text string (in this case, the field) you want to search; the number of the first character you want to start with, and how many characters you want to return. In this case, the first character we want to get is the one immediately following the startText, so sEnd (i.e. sStart + sLength); and the number of characters we want is however many characters are between the two strings, so eStart - sEnd. As an aside, you could also simplify this by using a Web Viewer and the GetLayoutObjectAttribute function to get the contents of the web page automatically, instead of copying and pasting. Would this help?
  7. Stephanie, Perhaps I’m missing something, but I’m also not getting how the question in this thread differs from the one in the other thread. As was said over there, the answer to question #1 is to create a Calculation field (let’s call it unit_area_pool) with the following calculation: Case ( unit_type = "pool" ; unit_area ) The answer to question #2 is to create a Summary field that displays the total of unit_area_pool. Please let me know if I’m not understanding this correctly.
  8. Perhaps I’m stating the obvious here, but your database windows are completely different sizes. Are each of them the result of the Resize to Fit command?
  9. Don’t you just love it when you figure it out on your own?
  10. If this is a Mac-only implementation, you could also use Perform AppleScript: I do this often, and it works beautifully!
  11. That’s totally odd, dragonfly. Have you tried recovering the file? How about deleting and reinstalling FileMaker on that computer? I know it‘s displaying everything else fine, but corrupt files (or installations) can do some really weird stuff.
  12. Okay… here’s a toughy. I’ve got a corrupt file, so we need to get all the data into a clone of a known-good backup. Obviously I can import it directly, but to be absolutely certain we’re getting rid of all corruption, my supervisor has requested that I export the data to merge files and then re-import it into the known-good clone, which sounds great to me. Unfortunately, there’s a snag: a lot of the data includes formatted text, which is very important to maintain. So, here’s the solution I came up with, repeated for each table in the solution: 1) Export all modification information (mod account, mod timestamp, etc.) to a merge file. 2) Loop through each field of each record of the current table and check for formatted text, using the following calculation: GetAsCSS ( GetField ( $FieldName ) ) ≠ GetAsCSS ( TextFormatRemove ( GetField ( $FieldName ) ) ) Any data that returns True is replaced with its CSS equivalent. 3) Export the data to a second merge file. 4) Import the data from the second merge file into the backup. 5) Perform step 2 in reverse, using a slightly modified version of Christopher Gauntt’s CSStoFMText custom function. 6) Import the modification info from the first merge file into the backup. I’ve gotten the script to the point where it’s working perfectly, but it’s taking forever: it’s been running step 2 on a particular table (62 fields, 2.3 million records) for over 12 hours and is still only about 60% complete. Once it’s done, it still has to run the remaining four steps on that table (I expect step 5 to take as long), then process the remaining 33 tables (most of which are smaller, but still). Any thoughts on what I can do to optimize this further? Perhaps some method of finding formatted text? Thanks!
  13. I’m working in a database that needs to be moved to the top of the display when a certain layout is entered. Obviously I’m using a OnLayoutEnter trigger to call a script including the step Move/Resize Window [Current Window; Top: 0]. Unfortunately, this doesn’t work reliably because almost every user has a multi-display setup and places the database on the larger (not necessarily primary) display. So, when it sets the window top to 0, it uses 0 from the primary display, which is only very rarely identical to 0 on the display where the window actually resides. Anyone have any bright ideas for moving the window to 0 on the relevant display? (Note: cross-platform, FM13 solution.)
  14. Thanks for the confirmation.
  15. Nice idea! Thanks for sharing!