Tom R.

Hi, I'm trying to implement NightWing's Duplicate Hierarchy v2 technique in my solution. The file (my solution ) has been in use for years, but does not appear to be corrupt. But the technique relies on the RelationInfo function, and it does not appear to work at all in my file, although it does in other files. Any clue as to what could cause this? I've taken screen shots of the Monitor window showing that it is passing correct parameters, and part of the graph showing the table & its relations. Thanks for any help!

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 ] ; [ "|*|" ; ¶ ] ) ) )

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.

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

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!

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

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.

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 ] ; [ "|*|" ; ¶ ] ) ) )

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.

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.

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!

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

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!

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

Comment - perfect! Thank you so much. That did the trick.

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

Hi, I've been using Geoffrey Gerhard's FormatNumberAsText custom function (http://www.briandunning.com/cf/871) 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 ) ) )

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

Thank you for the reply. The Open Record/Request script step does exactly what I need.

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!

What sort of system are you trying to model, and what are some of the tables you plan to use? Depending on what sort of graph model you adopt, it may make a difference. I think the standard for Anchor-Buoy is to use 3 or 4 letter acronyms for your table, and then name the TO based on it's position in the TO group. For example, a table occurrence group consisting of People, Addressses and Phone Numbers, where the People TO is the Anchor, I would name People "PPL" (or similar), Addresses "ppl_ADDR"; and Phone Numbers "ppl_PHON". The point being to put the name of the actual table in all-caps, and to prepend the names of the anchor table and any preceding buoy tables in lower case. For join tables, I sometimes just use the two names of the tables to be joined as the name of the join table; for example, a join table between people and events I might name "PersonEvents"; but depending on your model, the join table might store additional info and have its own purpose, so you could call it "Registrations" instead of PersonEvents. There's website called Filemaker Standards (http://filemakerstandards.org/display/cs/Overview) that has lots of info on coding standards, naming standards, and so forth; but their standards are a little arcane, and from my perspective as an intermediate level developer, somewhat hard to grasp in some respects. If I were in your situation, I would be inclined to learn Anchor-Buoy first and get used to that. Then after your comfortable, you could move on to Selector Connector, which actually can be used in conjunction with anchor-buoy, rather than a complete replacement of it. Hope this helps. Tom

I second Mark's recommendation of Todd Geist's video on Selector Connector model. There's a video on youtube that I found helpful in addition to Todd's video. Matt Petrowski does a good job explaining things for people - such as myself - who are not on quite such an advanced level. https://www.youtube.com/watch?v=ml-WF7qfMB0.

I'm glad to find this discussion. I have a solution that catalogs different types of documents filed in county courthouses, such as deeds, leases, mortgages and so on. Each type of document has different data to include, so it doesn't make sense to have a document table that includes fields for all of the separate document types (for the reasons that've been discussed above). Right now, I'm using an entity-attribute-value approach with a table for documents and a connected table of attributes. I'm running into difficulty using the attributes in calculations, so was thinking of changing to a model with a documents table, and then separate tables for each of the document types, such as lease, deed, mortgage, etc. I'm unsure of how to set up the relationship. From the perspective of validating uniqueness, it seems like it would make sense to put an id_any field in my documents table, that would hold the primary key of the related lease/deed/mortgage record. That way, I only need to validate in the documents table and the related record's table. However, since record creation will be done on a layout based on the documents table, it seems like it would be easier to put an id_document field in the lease/deed/mortgage tables. But then, for instance, for a lease document I would validate uniqueness in the document & lease tables, but would there be a need to also check the deed & mortgage tables? Thanks, Tom

Has anyone been using FMP Server 14 in beta and have an opinion on whether it's ready for use in a production deployment? Or should I wait until a rev or 2 has be released and then upgrade from Server 13? Oh, and also the same question about FileMaker Pro 14. Thanks! Tom

Hi James, I adjusted my settings to disable screen saver and prevent screen from turning off, and left my database open all night last night. This morning, everything was fine, and I had not been disconnected. So yes, I think it must be the same issue you have run into. Maybe this has been fixed in FMPro 14? (It's within the realm of possibility, but I wouldn't lay odds.) Thanks, Tom