Jump to content

Tom R.

Members
  • Content count

    95
  • Joined

  • Last visited

  • Days Won

    2

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
    newbie
  • Birthday September 30

Profile Information

  • Title
    Attorney/database user
  • Industry
    Legal/Natural Resources
  • Gender
    Male
  • Location
    Denver, CO

Recent Profile Visitors

2,854 profile views
  1. Thanks, Fitch. That was a fragment of the abstracted calculation that SQLExplorer generated; I didn't think about it not making sense outside of the whole calculation. But here's the full calculation, using variables. Still curious why I was unable to use field names to supply the field values of the current record. // 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@LINEITEMS = Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id ) ; "::" ; ¶ ) ; 1 ) ) & " a" ; // Define Field Variables a_id = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id ) ; "::" ; ¶ ) ; 2 ) ) ; a_id_lease = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id_lease ) ; "::" ; ¶ ) ; 2 ) ) ; a_id_tract = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id_tract ) ; "::" ; ¶ ) ; 2 ) ) ; a_id_unit = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id_unit ) ; "::" ; ¶ ) ; 2 ) ) ; atype = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::type ) ; "::" ; ¶ ) ; 2 ) ) ; // Build SQL Query q = "SELECT " & a_id & " FROM " & a@LINEITEMS & " WHERE " & a_id_lease & " = ? AND " & a_id_tract & " = ? AND " & a_id_unit & " = ? AND " & atype & " = ? " ; // Run SQL Query result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $leaseID ; $tractID ; $unitID ; "WI" ) ] ; // Clean up carriage returns List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] ) ) )
  2. I never figured out why I couldn't use field names; but I did work around this by pushing the field contents into a local variable, and referencing the variables in the SQL query.
  3. Having trouble with an ExecuteSQL calculation. I used SeedCode's SQL Explorer to build the calculation. It works fine if I use literal values from a specific record; but if I instead change it to a field reference, the calculation fails. I get the message <unrelated table> in the Data Viewer. The code is below. Am I misusing the field names somehow? // Build SQL Query q = "SELECT SUM ( " & ainterestMineralDecimal & " ) FROM " & a@LINEITEMS & " WHERE " & a_id_lease & " = ? AND " & a_id_tract & " = ? AND " & a_id_unit & " = ? " ; // Run SQL Query result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; @LineItems::_id_lease ; @LineItems::_id_tract ; @LineItems::_id_unit ) ] ; Thanks, Tom RuBane
  4. Tom R.

    Genealogy: Relational database(s)

    There is an excellent series of articles, including a relationship graph, about a genealogy model that is more robust and flexible than the GEDCOM model used buy most commercial genealogy programs. You can find it here https://solumslekt.org/forays/tmg.php. The author is using sql but the concepts and relations can be implemented in FileMaker just as easily. If you need something with specific capabilities, or are working on this as an academic exercise, that makes sense. But there are many excellent genealogy programs available for Mac and Windows. I personally use Reunion for Mac. At any rate, good luck with the project!
  5. 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. https://community.filemaker.com/thread/168254. Thanks, Tom
  6. 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.
  7. 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 ] ; [ "|*|" ; ¶ ] ) ) )
  8. 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.
  9. 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.
  10. 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!
  11. 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
  12. Tom R.

    Trouble tracking land use over time

    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!
  13. 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
  14. Comment - perfect! Thank you so much. That did the trick.
  15. 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
×

Important Information

By using this site, you agree to our Terms of Use.