3 Replies Latest reply on Sep 7, 2013 11:03 AM by philmodjunk

    Snag when trying to create self-join relationship to link to "prior record" after a sort of records



      Snag when trying to create self-join relationship to link to "prior record" after a sort of records


           Hello, I am an intermediate user of FileMaker Pro 12 Advanced, accustomed to some scripting and to forming relationships among tables, but I have hit a snag.  I need to script a solution in which I annotate a grouped report with some special required character strings.  Basically, I am sorting 200 records on two fields, and I need to put some special character strings at each level of the report.  The characters in the strings provide information on the structure of the grouped report.  (Background:  These are "hierarchical levels" related to preparing an 837 professional claim for medical services [http://www.cms.gov/Medicare/Billing/ElectronicBillingEDITrans/downloads/5010A1837BCG.pdf]).  A simple analogy would be a table of home addresses, sorted by state and city.

           The strings are a bit tricky--one part of the string is a numeral that increments whenever the report breaks into a lower part, or reverts to a higher part.  Another part of the string is a numeral that refers to the parent entry in the grouped report.  And a third part of the string indicates whether the line in the report has a child.

           Sample grouped report of address records, sorted alphabetically by state and city (with lines numbered for ease of referring to them in this posting):

           1) Alabama

           2)   Birmingham

           3)         123 Main St

           4)         456 Green St

           5)   Montgomery

           6)         789 First Ave  

           7) Texas

           8)     Dallas

           9)          546 Eastwood

           10)       3438 MLK Highway


           So I need to insert a character string at the beginning of each line in this report, and the string needs to include i) the line number in the report (i.e., numerals 1 to 10 in the above list); and the string also needs to include ii) a reference to the parent (i.e., the line 10 string needs to include the numeral 8, because the "Dallas" header is the direct parent of line 10); and the string needs to include iii)  a 0 or a 1 to indicate whether the line has a child (i.e., the "Dallas" line would have a 1, since it has a child; the "546 Eastwood" line would have a 0, since it has no child).


           The addresses are in a table called "Addresses".  My strategy so far depends on creating a self-join where I duplicate "Addresses" and call it "Addresses 2" and I use this relationship to link the current record in the sorted report to the previous record.  This allows me to detect whether certain fields are changing, which queues me to update one of the above-described numerals.  So I first perform the sort by state and city; then I loop through the records assigning my number field "currentrecordnumber" with the value get(recordnumber) and assigning my number field "priorrecordnumber" with the value get(recordnumber)-1.  Those are the fields which I then use to match in order to do the self-join:  I do a self-join such that the "Addresses" table's "priorrecordnumber" matches "Addresses 2" table's "currentrecordnumber".  I thought this would work perfectly!


           But it doesn't work.  After I run the script, when I am in an "Addresses" record and I query the linked record in "Addresses 2", it is not the prior record in the sort; it's a completely different record from a completely different line in the sorted report.  I can't even begin to figure out "what is this computer thinking to link to that particular record?"


           My hunch is that somehow "Addresses 2" is not being sorted when "Addresses" is sorted, so the records in "Addresses 2" remain semi-randomly ordered and I thereby do not achieve my desired linkage.  But why would that happen if I have sorted the records in "Addresses"?  Shouldn't the records in "Addresses 2" automatically be sorted exactly the same way?

           Thanks for any advice.

        • 1. Re: Snag when trying to create self-join relationship to link to "prior record" after a sort of records

               When you have multiple related records (such as 5 address records with the same City), a direct reference to a field from the related table occurrence refers to the "first" related record. That will be the first related record to be created unless you double click the relationship line and specify a sort order.

               But you haven't described HOW you match fields in your relatinship, so this may not be the key issue here.

               PS. Are you using a summary report with lines 1, 2, 5, 7 and 8 coming from sub summary layout parts?

          • 2. Re: Snag when trying to create self-join relationship to link to "prior record" after a sort of records

                 Thanks for your reply. 

                 Well, I am not matching on the sort fields themselves. 

                 First, I create the self-join and specify match fields ("currentrecordnumber" and "priorrecordnumber") in the Relationships graph, BEFORE I have actually populated the match fields.  Then, I do a "perform sort" of the records using the two sort fields, state and city.  I then populate the match fields (currentrecordnumber and priorrecordnumber) with a looping "set field" script that auto-enters get(recordnumber) in the currentrecordnumber field and get(recordnumber)-1 in the priorrecordnumber of each record in the sorted set.  Each record should then have a unique value in these fields, and the self-join should link the current record in "Addresses" to the prior record (per my sort order) in "Addresses 2".  The relationship should link to only one record; so I have not chosen to sort in the self-join setup screen.  (Actually, I have tried it both ways:  sorting both "Addresses" and "Addresses 2", sorting neither, and sorting only one.)

            • 3. Re: Snag when trying to create self-join relationship to link to "prior record" after a sort of records

                   If your records are successfully matching to one and only one related record, the sort order makes no difference. Could it be that other records in your table are also matching because they previously got the same record number but aren't part of the current found set of records?