Selfjoin won't sort certain records

[BuckyE]

Most records perform fine. But when I've changed the data in the selfjoin field, it breaks!

Fields:

GC USN = Number, indexed

EVIDENCE DATE SORTER = Calc, text, indexed (concatenates two other fields)

dat looks like: 1670-04-14-01, 1670-04-14-02, etc.

HEADER EXPORT = If(GC USN here::EVIDENCE DATE SORTER = EVIDENCE DATE SORTER, "first", "")

Relationship:

GC USN here = GC USN to itself

This is simple stuff, intended to create a header for the first record in each set of records that share the same GC USN. It seems to work, but it won't "recalculate." It assigns a Header to, I think, the first record created, then sticks. I've created other fields using similar formulae (If(GC USN here::test = test, "first", ""), but changing the data in EVIDENCE DATE or in test doesn't create a change in the header creators. I've tried turning Indexing on and off for all the fields involved, but to no avail. Can you help me make my Header field "live" and unstuck? Thanks a million.

[Edited to add: I see part of the problem. The records in question won't sort in order of the EVIDENCE DATE field. Other records will, but not these in question. I can't figure out why the wonky records won't sort, but that's the problem. Sound familiar to anyone? Thanks!]

Edited March 20, 2006 by Guest

[Ender]

Welcome Bucky!

A possible sort problem would be if you're trying to sort by that EVIDENCE DATE SORTER field, which seems to include a text version of a date. Remember, text fields sort as alphabetical (they would need to be yyyy-mm-dd to sort correctly.)

If this is for a printed report, you might consider simply using a columnar report with sub-summary parts instead. Using a Sub-Summary by GC USN, and sorting by GC USN would give you a part for each value of GC USN. Putting a summary Count field on that part would give the count for each value. With this, there's no need for a self-join or that wierd HEADER EXPORT calc.

If this is not for a printed report, then maybe you can explain the purpose of flagging the first record like that. Perhaps you can just normalize the data a bit so this wouldn't be necessary.

[BuckyE]

Thanks, Ender. I've been working with it some more, and can report that bad data entry was responsible for the seemingly improper sorting by EVIDENCE DATE SORTER. The data in EVIDENCE DATE SORTER is indeed in yyyy-mm-dd-xx format; I'm VERY familar with that technique, thanks.

The data in my file will be repurposed in several ways. It will never be used in FMP per se. One purpose can be seen at http://lovebunnies.luckypro.biz/genealogy/histories/hist_04-3_ghost_cav.php

The text on that web page was generated by my Exporting calcs, which add CSS style sheet tags to the data. Fun, huh?

If you would be so kind as to look at that web page, you will see that there are "facts" presented in large bold text followed by "evidence." Some facts have only one bit of evidence, some have many. Each bit of evidence has its own date. Evidence should be presented in date order. Indeed this IS exactly like a subsummary report, except that it's intended to be exported and used for print and web.

Evidence is being added as new sources are seached, so a Fact might get evidence added to it at any time, and the evidence won't be added in date order. But I want, obviously, the Fact "header" and a couple of other fields to print out as part of the first evidence record, no matter what record that ends up being.

This is all simple FMP usage, and the reason to use a database. (You should see what a mess the others working on this project before I came into it made trying to do this kind of thing in MSWord! Whoo boy!)

I set up EVIDENCE DATE SORTER because every now and then, the actual evidence date is inexact: 1642 might be as close as we can pin down the actual date. But we still want to present some pieces of evidence before others: even when we can't assign a date from the evidence, the order of events is still clear. I have a field "Arrange if on same date" into which I place, by hand, data of the form 01, 02, etc. My original "date" field is text: 1642, 2 dec 1642, feb 1642, etc. A full date is calced: 1642 = 1642-01-01; 2 dec 1642 = 1642-02-02; feb 1642 = 1642-02-01. The Arrange data is added, so that if two dates of 1642 ONLY occur, they end up as

1642-01-01-01 and 1642-01-01-02.

So, the evidence sorts correctly, every time (so long as I enter the data properly!) But there are, I think two, Facts whose evidence (although it sorts properly) refuse to calc properly. You can see this happening at "NH house robbed, burned" where the preceding two bits of evidence should be beneath the "NH house robbed, burned" heading, not above it. It is the third evidence record (in date order) getting the heading.

(Evidence dated: 27 Aug 1647: is the first in date order, Evidence dated: Nov 1647 (Approximate date from “nov 1647”) is the second, and Evidence dated: Dec 1647 (Approximate date from “dec 1647”): is the third. They're sorted correctly, but the self-join, which works for almost every other fact, doesn't work for these three records.)

I don't know if its significant that the 3rd evidence in sort order was the first entered. All I know is that nothing I can do makes the self join calc work for these 3 records, and one other set of records.

I have: re-entered their GC USNs; re-entered their dates; turned indexing on off and sideways; changed their Indexing language from English to ASCII and back; recovered the file. Nothing will convince FMP the "3rd" record of this set is not the "first." Please note this is not the first time I've seen this happen. It's just the first time it's been important enough to worry about.

Any thoughts? Thanks.