find first with a value

[grumbachr]

please help me make this a little easier.

I'm trying to find the first record with values for each person. its a One to Many relationship but not every first related record has a value. The value might not show up until 3 or 4 records of 10 or 15.

If I use the GTRR record trick that I've recently started using I get a report with a lot of "first" records being empty.

Any suggestions are greatly appreciated.

[comment]

What exactly do you mean by find? You could GO TO the first related record with a value by GTRR (show related only) then loop through the records until you have a value. Or you could constrain the found set after GTRR, to get a found set of all related records with a value (this might mess up your sort order, so you might need to sort again).

[grumbachr]

So I'm doing an actual find, where I need to find all Contacts (contacts_table) that have a "Risser_Score" (medical_records table)and it can be any value.

The final report just needs to be Contact_ID, Risser_Score and the Age from when the score was recorded.

But many time the first related wont have a RisserScore.

I'm not explaining this well. ARGH...

[comment]

No, I'm afraid you're not. If the report is done from the medical_records table, why do we care about the related contacts? Why not find records that have anything in the Risser_Score field, sort by ContactID, and print?

[grumbachr]

that would be good but each contact can have 10 medical records and I only want their first with a risser score. they can have more than one.

but their first with could be their 3 or 5 or 7 record.

Edited January 24, 2008 by Guest

[Fenton]

You could use the Last() function. It has a capability which the regular relationship (which gets the 1st, whether it's empty or not) does not, in that it returns the value from the last record (via the relationship) who has a value in the specified field. The Last() function is in the Repeating functions, because it was originally designed (in FileMaker 2) for repeating fields.

FileMaker Help mentions this, but then has a subtle glitch in its example. They first say:

Returns the last valid, non-blank value in field. If field specifies a repeating field then it returns the last non-blank repetition. If field specifies a related field, then it returns the last non-blank value in the related set.

Note The last related value will depend on the way related records are sorted. If the related records are not sorted, then the Last function returns a value based on the creation order of the records.

But in their example below, they say:

Last(Payments::PaymentDate) returns the payment date in the last matching record in the Payments table.

It is not really the "last matching record" of the relationship, it is the last matching record that has data in that field. Perhaps they construe the word "matching" to mean that, but it seems odd to me.

Since you really want the first matching record that has data in the field, you would need to create a new relationship for this which was sorted descending.

[grumbachr]

Thanks Fenton I'll give that a try.

But after almost giving up the method I've found that works for me is to

Find all Med Records with the values that I'm looking for, Sort by Age, Export those to a new file.

GtRR to get the list of contacts, Export those to a new file.

Open one of those Import the Table and fields, then import the records

Set the relationship to sort by the age. So this assures me the first related records will be the one that I want.

Once I've done all that I can generate my list. Yeah it might seem like a long process but it save me hours of time going through thousand of records in excel and removing all the first.

[comment]

It's not that it's a long process - it's a long and unnecessary process. In addition to the method suggested by Fenton, you could also find records with Risser_Score values, sort by ContactID and Age (ascending), and print only a leading sub-summary by ContactID.

I haven't tested this, but I believe this should also work if you're exporting your records instead of printing.