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):
3) 123 Main St
4) 456 Green St
6) 789 First Ave
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.