Using max function to return related text

[SGT]

Hi everyone,

I have a database that keeps track of imported documents, their title, and the date they were created. I'm creating a report and want to include a field for the title of the most recent document created. However, I'm not sure how to do this. I can use the max function to return the date of the most recent document, but I want to return the *title* of the document associated with it. Does anyone know how I can write a calculation to return this text? Thanks!

[Raybaudi]

Hi SGT

and welcome to FMForums.

If you sort the relationship by date ( descending ) the first related record will show the title of the most recent document.

So you do not need something like the Max ( ) function, but only to put the related field "title" in the report layout.

[Fitch]

If your report is sorted so that the most recent record is the first one, then you can simply put the title field in the header, no additional field/calc needed.

Edited December 3, 2008 by Guest
A minute too late! :)

[bruceR]

Yet another alternative is to create a calc that concatenates the date and title, get max of this, then strip off the date.

Something like

Year( theDate) &

right("00" & month(theDate); 2) &

right( "00" & day( theDate); 2) & " " &

Title

[comment]

I don't think that will work well, since

Max ( "20080101 First" ; "20081231 Last" )

will return "20081231" only.

[bruceR]

Oops, you're right. Odd that it should do an unrequested number conversion.

[comment]

Not that odd, considering:

http://fmforums.com/forum/showpost.php?post/282943/

[SGT]

Hi everyone,

Thanks for the suggestions! How do I sort the title field by date created, though? If I go into new report layout, choose title, and click descending, it always posts the *first* document I imported. What am I missing here? Thanks!

[comment]

How does your report need to be sorted at the end?

[SGT]

I just have a columnar report with fields like subject, document title, date. Each subject has a set of documents associated with it, and I want to show the most recent document title in the report, as well as the date it was created. I know how to sort the date field, but I do not know how to sort the document titles so only the most recent one shows up.

[comment]

So for your report you would sort by the subject first, then by date? I am asking how your report needs to be sorted at the end - this is not related to the question of how to get the title of the most recent document.

Each subject has a set of documents associated with it, and I want to show the most recent document title in the report, as well as the date it was created.

Do you mean you want to show the most recent document title IN EACH SUBJECT, or the most recent document title IN THE ENTIRE REPORT?

[SGT]

Oh yes I'm sorry. I would like to sort by subject first, and then date. I want to show the most recent document title in *each subject*. So if I have five subjects, I first want to sort them alphabetically, and then *for each subject* I want to show the most recent document.

[comment]

I would like to sort by subject first, and then date.

I'm afraid that's still not clear. By date ascending, or descending? Should your report look like this:

Subject: Algebra

Last Document Title: Exponents

• Jan 1 2008: Equations

• Feb 8 2008: Variables

• Apr 4 2008: Exponents

Subject: Biology

Last Document Title: Mammals

• Mar 1 2008: Genes

• Mar 9 2008: Mutations

• May 2 2008: Mammals

or something else?

[SGT]

That's pretty close to what I want. Something like this:

Subject: Genetic Engineering

Most Recent Document Title: Telomerase

Most Recent Document Date: 12/5/2008

Subject: Mammals

Most Recent Document Title: Omnivores

Most Recent Document Date: 12/5/2008

So say there are three documents under genetic engineering. Telomerase (12/5/08), alleles (12/4/08), and chromosomes (12/3/08). In my report, I want to sort the documents *in each subject* by creation date but *only show* the name of the one created most recently *for each subject*, in this case telomerase and omnivores for Mammals.

Does that help clarify things? I'm sorry if I'm being confusing, I'm relatively new to FileMaker.

Edited December 5, 2008 by Guest

[comment]

OK, now we're getting somewhere. You need to add a sub-summary (when sorted by Subject) part to your report. You can make it either leading or trailing, but the required sort order will be different in each case, so let's make it trailing. Put the Subject, Title and Date field in this part, and delete the Body part from the layout.

Now when you sort the records by Subject and Date (ascending), you will get your report.

[SGT]

I see where you're going with this, but I can't get it to work. Here's what I did: I went to insert, part. Sub-summary when sorted by: subject. I made it trailing. I then moved the subject, document title, and document date fields into the sub-summary part and deleted the body.

However, now when I go to print preview, my report is completely blank. Am I doing something wrong?

After I get it sorted by subject, how then do I sort for date (ascending)?

[comment]

Make sure your fields are entirely within the sub-summary part. Sub-summary parts show only in Preview mode (and when printed), and only when records are sorted by the defined breakfield.

To sort by more than one field, just move it into the sort order area.

[SGT]

I see how that should work, but if I add a sub-summary field sorted by subject, and then move the fields into the sub-summary area, they no longer appear in preview mode. It's just a blank layout. I've made sure that the fields are completely within the sub-summary area, too.

Edited December 5, 2008 by Guest

[comment]

It's hard for me to tell from here what's wrong over there. See if the attached helps.

DocReport.fp7.zip

[SGT]

Hi everyone,

I'm using core 2 crm to build a database of correspondence with companies. I'm using the "documents" tab to import all of the documents we send and receive to each company and need to create a report field that displays the title of the most recent document imported.

I'm trying to create a columnar report that lists the date the most recent document for each company was imported, and the *title* of this document. So a company might have 10 documents (e-mails, faxes, whatever), but in the report I want to list the title of the *most recent one.*

Does anyone have suggestions on how to do this? I can't use the max function because that returns a number. Other people have suggested using sub-summary to sort document titles by date, but I want a strictly columnar report. Any help would be *greatly* appreciated!!

The weird thing is that in the documents tab, documents are already sorted by date, but once I put the document title field in my report, it only lists the first doc imported and never changes after that.

[comment]

Isn't this the same question as the one you asked here?

http://fmforums.com/forum/showtopic.php?tid/199708/

[SGT]

Yes, but I realized I wasn't being specific enough in that I'm using Core 2 Crm. Thought people might have other ideas. I appreciate your help on the other thread, but couldn't get it to work.

[Lee Smith]

I have merged your two topic.

Please do not Double Post your questions.

Lee