Tom R.

  • Content count

  • Joined

  • Last visited

  • Days Won


Tom R. last won the day on November 19 2012

Tom R. had the most liked content!

Community Reputation

5 Neutral

About Tom R.

  • Rank
  • Birthday September 30

Profile Information

  • Gender
  • Location
    Denver, CO

FIleMaker Profile

  • FM Application
    14 Advanced
  • Platform
    Mac OS X Yosemite
  • Skill Level
  • Membership
  • Title
    Attorney/database user
  • Industry
    Legal/Natural Resources

Recent Profile Visitors

2,507 profile views
  1. Wim, Thank you for the reply. My ID fields are all numeric, and I'm currently the only user so I don't think it would have to do with that. A guy on facebook, Richard Christian, pointed out a possible bug in FMS 15v3 that I think may be the culprit. Thanks, Tom
  2. Fitch, thanks for the reply. I did a manual search is the Grantees table, using the _id_assignment (foreign key) value, which returned six join records, including the one for the original Mollie Sternberg record; but the ExecuteSQL query omitted the Mollie Sternberg record, even though it returned the other 5 records, which had the identical foreign key as the Mollie Sternberg record. So unless I'm missing something, the data was correct and the ExecuteSQL querry should have returned all six records.
  3. I am dealing with a very weird condition in my solution where an ExecuteSQL statement was failing to return a matching record. My solution tracks documents such as deeds and leases, where a document can have N grantors and N grantees. So I have a Parties table, a Documents table, and identical join tables, Grantors and Grantees. I noticed the problem when printing out a report, and a particular document didn't have any grantees. The report uses the executeSQL query in question. So I looked for that document in the database, and the particular grantee appeared as expected in the portal. I looked in the Grantee join table, and there was a matching record, complete with the ID of the Party and the ID of the document. I then checked, and every record where that particular party appeared, in the report that party was omitted for every record where it should have appeared. In one record, that party was 1 of 6 grantees. The ExecuteSQL statement returned the other 5 parties, but not that particular one. In the course of troubleshooting, I created a new record for that party, nameFirst = Mollie, nameLast = Sternberg. Then I added the new Mollie Sternberg record to those documents. The original Mollie Sternberg record, I set the formerly empty field nameMaiden to "Bugged". Then I re-ran the report. The new Mollie Sternberg record appeared as expected, but so did the original record, Mollie Sternberg Bugged. So I don't know why changing a field in the party table for that record would cause the ExecuteSQL statement to return it, when it hadn't been returning before. I'm freaked out, because I identified a second party that was having the same issue. Changing that party's original record to nameMaiden = Bugged also caused it start showing up, too. But I don't know how many other Party records may be having the same problem, and I have no way to identify a characteristic of the Party record that may have caused the probelm, so that I can identify potential problem Party records. Is this a bug in FileMaker? Or an issue with my SQL query? Any thoughts or suggestions most appreciated. The query is below. Thank you, Tom // Find Document Grantors // Built by SQLExplorer. Compliments of SeedCode… Cheers! Let ( [ // Define Carriage Return Substitution Character ReturnSub = "\n" ; // Enable the second line here if you want the header in your results header = ""; //header = ""; // Define Table variables a@GRANTORS = Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id ) ; "::" ; ¶ ) ; 1 ) ) & " a" ; b@PARTIES = Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::_id ) ; "::" ; ¶ ) ; 1 ) ) & " b" ; // Define Field Variables acapacity = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::capacity ) ; "::" ; ¶ ) ; 2 ) ) ; a_id_parent = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id_parent ) ; "::" ; ¶ ) ; 2 ) ) ; a_id_party = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::_id_party ) ; "::" ; ¶ ) ; 2 ) ) ; aakaFull__c = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::akaFull__c ) ; "::" ; ¶ ) ; 2 ) ) ; ainterestConveyed = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @Grantors::interestConveyed ) ; "::" ; ¶ ) ; 2 ) ) ; b_id = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::_id ) ; "::" ; ¶ ) ; 2 ) ) ; bnameDisplay = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( @Parties::nameDisplay ) ; "::" ; ¶ ) ; 2 ) ) ; // Build SQL Query q = "SELECT " & bnameDisplay & " , " & aakaFull__c & " , " & acapacity & " , " & ainterestConveyed & " FROM " & a@GRANTORS & " INNER JOIN " & b@PARTIES & " ON " & a_id_party & " = " & b_id & " WHERE " & a_id_parent & " = ? " ; // Run SQL Query result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $_id_assignment ) ] ; // Clean up carriage returns List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) ) )
  4. Here are two examples of using the FMStandards scheme. One is a script that runs on the script trigger OnRecordLoad. It runs if TriggersAreActive, but exits if not TriggersAreActive. The other is part of a script that creates a new window, but I do not wish the OnRecordLoad script to run; so I disable the triggers before opening the new window, and then re-enable the triggers after going to my target layout.
  5. I use those functions in my solutions. I usually just use a script vairable, $triggerStatus, and use a set variable step to call the custom functions. Set Variable $triggerStatus = TriggersDisable, and $triggerStatus = TriggersEnable. Just remember to use the functions before any halt script steps or where necessary in any if statements.
  6. Sorry, I guess that was more of an aside than a because. A join table between two occurrences of the Parties table? Or between the Parties table and...? I will play around with it and see what I can figure out. Thanks!
  7. Hi, For my solution I have set up a party model, with separate tables for People, Organizations, and Parties. Since a Party can be either a person or an organization, the user creates a new party record, selecting whether it's a person or org; then a related child record is created in the appropriate table. My question is, I would like to come up with some sort of compound party model, but am not sure how to structure it. In my solution, Parties can own interests in leases, and they get credited with line items in a line items table. So for example I might have Bill Smith and Jean Smith with separate records in the Party table (and associated records in the People table). I want to be able to create a compound party out of them, based on their relation as husband and wife; so the party would be "Bill Smith and Jean Smith, husband and wife". I would like the new party record to be based on the existing records in the Parties and People tables; not a completely new record based on an Organization. Similar examples might be "Jennifer Jones, Mike Carlson and Jeff Black, co-trustees of the Jones Family Revocable Trust", where each of the individual people/organizations, are existing records already. I'm not sure whether I might be able to do something with repeating fields, or if I need an additional join table, or what. Any advice appreciated. Thanks, Tom
  8. Hi, This is an interesting problem, and one that I've given some thought to in the past in the context of my work as a title attorney creating chains of title to parcels of land. I'm curious what the nature of your research is? Also, when you say you'd like to "show" the various incarnations, what exactly to you have in mind? Something like a map, or more of a narrative/textual history or report? Also, are you dealing with metes and bounds described tracts or PLS sections, quarter-sections, etc (assuming the lands are even in the U.S.)? At any rate, this is a classic many-to-many relationship, where a given tract of land could potentially have multiple child tracts and/or multiple parent tracts. This is usually dealt with using a join table. Taking this approach, you would need a table for Tracts (or parcels, or your preferred naming convention), that would include an ID field, a legal description field, and whatever other data you might want to track, maybe acreage, etc. You would need a second table to use as join table; I can't think of a meaningful name for it, so you could call it something like TractJoin. This table would have at a minimum a field for a parent tract foreign ID and a child tract foreign ID. You would then handle creating new records by script - for a tract that is subdivided into smaller tracts, save the ID of the parent tract in a variable, then in a loop create a new Tract record, save its ID in a variable, then create a new record in the Join Table, and set the parent and Child ID fields with the appropriate variable. For combined tracts, it would be the opposite - save each of the parent tract IDs in a variable, create a new tract record and get its ID, then loop through and create records in the Join table. I hope this helps!
  9. Hi, I have a solution set up that is hosted on a dedicated Mac Mini running FM Server 14; the container fields are set up to "Store container data externally" with the "secure storage" option checked. The field contents are all PDFs. I would like to write a script to open the field contents directly in Preview (or the user's default PDF viewer), but am not sure how to get the path where the file is stored, or otherwise how to proceed. Any advice appreciated! Tom
  10. Comment - perfect! Thank you so much. That did the trick.
  11. Hi Lee, Thanks for your reply. In my solution, I'm calling the CF and passing a number, and the result is written to a text field in a virtual list. The attached file is representative. My test data is "1/6000" for the expression (evaluated using the Evaluate ( ) function); if precision is set as 8 you get "0.00016667", which is correct. If you set it to ten, you get "0.+101666667", which I assume is the correct number is scientific format, but the result I want is "0.0001666667". Thanks, Tom CF_test.fmp12
  12. Hi, I've been using Geoffrey Gerhard's FormatNumberAsText custom function ( for quite a while and have found it very handy. It accepts a number as input and outputs a text string formatted to a specified number of decimals (among other useful things). In my solution, I've always specified 8 digits for the number of decimals and had no problem, but a recent project I've needed to specify 10 digits. With that precision, the function sometimes returns a number in scientific notation rather than decimal - for example 0..0e+107440 when I was hoping for 0.0000107440. I've looked at the function to see if there's anything I could change to make it always return a decimal rather than a scientific number, but frankly the logic is over my head. I was wondering if anyone here might have a suggestion. Thanks, Tom Here's the function: /* Function: FormatNumberAsText ( number ; currency ; separator ; decimal ; precision ) Parameters: number = the number you want to format currency = the string that identifies the currency or is a prefix - optional separator = the punctuation that breaks up big numbers - optional decimal = the punctuation that separates integer digits from fraction digits It defaults based on FMP's localization unless rounding is <= 0, in which case it's omitted precision = number of places for rounding - defaults to 2 if parameter is not a digit or is unspecified Purpose: This function formats numbers for use in text strings or calculations with a text result. It's especially useful when there are multiple numbers in a text string and at least one must be displayed in a different format than the others. Notes: Could expand to include colorization parameter (for negative numbers) Could expand to include choice of negative symbology for numbers < 0 Could expand to include trailing/leading parameter for currency notation */ Let ( [ t = Int ( GetAsNumber ( precision ) ) ; prec = Case ( Length ( t ) ; t ; 2 ) ; dec = Case ( Length ( decimal ) ; decimal ; Filter ( 1/2 ; ".," ) ) ; n = Round ( number + 0 ; prec ) ; sgn = Case ( n < 0 ; "-" ) ; n = Abs ( n ); x = NumToJText ( Int ( n ) ; 1 ; 0 ) ; y = NumToJText ( n * 10^prec ; 0 ; 0 ) ] ; sgn & currency & Substitute ( x ; "," ; separator ) & Case ( prec > 0 ; dec & Right ( 10^prec & y ; prec ) ) )
  13. I have the Apple Server software installed on the same machine as FM Server, but have not upgraded from 10.10 to 10.11. If I upgrade, would I just need to turn off all services in the Apple Server or would I need to completely remove the software? I'm currently running FMServer 13 and would like to upgrade to 14. tThanks
  14. Thank you for the reply. The Open Record/Request script step does exactly what I need.
  15. I am trying to come up with a way to keep multiple users from working with the same record. I have a main table of leases, and for each lease a user needs to create (or link an existing) record in an orders table. All of the data entry on the lease layout is done using global fields - user enters the order data in the globals, then a script trigger fires a script that either finds a matching order or creates a new one, then creates a link record in a join table. After a lease is processed, a flag is set in the lease record as "completed". Then a script takes the user to the next record that is not flagged as "completed". I'm aware of checking for error 301, to see if a record is locked. But if the user is just viewing the record (not editing a field's contents), I don't think that error will occur - and I'm guessing that if the user is in a global field, the record will not be locked? At any rate, I would just like to make sure that multiple users aren't taken to the same non-"completed" record at the same time. Any advice appreciated. Thanks!