Kevin Frank

Members
  • Content count

    75
  • Joined

  • Last visited

  • Days Won

    4

Kevin Frank last won the day on July 20 2016

Kevin Frank had the most liked content!

Community Reputation

13 Good

1 Follower

About Kevin Frank

  • Rank
    grizzled veteran
  • Birthday

Profile Information

  • Gender
    Male
  • Location
    Humboldt County, California

Contact Methods

  • Website URL
    http://kevinfrank.com

FIleMaker Profile

  • FM Application
    15 Advanced
  • Platform
    Cross Platform
  • Skill Level
    Expert
  • Certification
    7
    8
    9
    10
    11
    12
    13
    14
    15
  • Membership
    FileMaker Business Alliance
  1. FM/SQL Portal Filter + Dynamic Sort

    Editor’s note: Today I’m pleased to present a guest article and accompanying demo file written by Joel Englander on using ExecuteSQL + a global multi-line key as an alternative to FileMaker’s built-in portal filtering mechanism, with dynamic portal sorting included as an added bonus. Demo file: fm-sqlportal-filter-and-dynsort.zip (75 Mb compressed, 230 Mb uncompressed) Portal Filtering […] View the full article
  2. Exploring Problem Spaces

    Given its primary role as a database product, it’s easy sometimes to forget what a great teaching tool FileMaker Pro can be. One of my favorite educational uses for it is building models to explore problem spaces, for example… viewing all possible outcomes for a problem with boundary conditions conducting trials to confirm the likelihood […] View the full article
  3. Modal Popovers + Magic Date Value Lists

    Today we’re going to explore a couple ideas that were glossed over in last month’s article on the CustomList custom function… implementing a “modal” popover using a “magic” value list to display a properly sorted list of dates …and you can download a copy of today’s demo file, Date Filtration, if you are so inclined. […] View the full article
  4. CustomList

    Earlier this year, in Virtual List Reporting, I used a custom function written by Agnes Barouh called CustomList to generate a couple basic lists, and then remarked… Well today I’m going to attempt to make good on that assertion. In case you aren’t already familiar with CustomList, it allows you to iteratively generate and/or parse […] View the full article
  5. Transfer a Found Set to a UI File

    Introduction If you work with the separation model, or any other multi-file configuration where a layout in file A is based on a table in file B, you may have encountered the following situation: while FileMaker is perfectly happy to allow you to transfer a found set from file A to file B… …there is […] View the full article
  6. Obvious now that you mention it. Thanks.
  7. Instead of Rental in the statement, try either... 'Rental' or ? If the latter, then you'll need to change this... ; "" ; "" ; invoiceID to ; "" ; "" ; invoiceID ; "Rental" Also bear in mind case-sensitivity... for SQL, Rental is not the same as rental A couple other thoughts: 1. in general, I avoid using SQL in calculated fields, for performance reasons 2. I have no idea what problem you're trying to solve with... decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ...but I'm guessing there's a less convoluted way to go about it.
  8. I don't find the parentheses to be necessary, but if you're going to use them, don't forget to remove the "AND" from the first "AND FieldName NOT LIKE ?" (I know you know this, but for the benefit of any newbies who copy your code and wonder why it's not working.)
  9. Substitute doesn't allow wild cards, so I don't see how that would work.
  10. Hi Ron, Off the top of my head, I'm guessing you'll need to hard code the "AND" portion 26 times, along the lines of... ExecuteSQL ( " SELECT FieldName FROM FileMaker_Fields WHERE TableName = ? AND FieldName NOT LIKE ? AND FieldName NOT LIKE ? " ; "" ; ""; "Notes" ; "zA%" ; "zB%" ) <=== keep going with "z%C", etc. Perhaps someone else can suggest something more efficient? Kevin
  11. Life After Anchor/Buoy

    INTRODUCTION Earlier this month I was contacted by a DevCon presenter asking permission to include some slides from my eleven-year old anchor/buoy materials as part of his presentation. I said I’d be honored, but then added, “You might mention that I haven’t used A/B on a new project in about eight years.” He asked what […] View the full article
  12. I think so. It would never have occurred to me to approach it the way you did. It makes more sense to me to do all the heavy lifting in the WHERE clause. Also, for the sake of argument, if voteYes contained 0 and 1 rather than nothing or 1, my second version would return the correct answer but the first would not (since the zeros would be counted also).
  13. You're welcome. Hope it helps.
  14. On reflection, having re-read your message, I would be inclined to construct the query like this: ExecuteSQL ( "SELECT Count ( * ) FROM MotionVote WHERE motionYear = ? AND voteYes = ?" ; "" ; "" ; 2016 ; 1 ) It makes more sense to me to constrain the results (via the WHERE clause), and then simply count them... but really that's just my preference.
  15. Answer #1: you don't need to wrap the year in single quotes. Answer #2: you don't need to escape the names with double quotes (since they aren't reserved words, don't contain spaces or apostrophes, or begin with an underscore character), and you can use a dynamic parameter, which frees you up from having to worry about whether to use single quotes or not, e.g., ExecuteSQL ( "SELECT Count ( voteYes ) FROM MotionVote WHERE motionYear = ?" ; "" ; "" ; 2016 ) Answer #3: for more info, check out this blog posting -- https://filemakerhacks.com/2012/05/02/fm-12-executesql-dynamic-parameters/