I have a database of advertisements. Each time i compile a report, I
need to know the most recent date -- before today's -- that the
advertisement was published. I tried setting a field mostRecentInsert
equal to the following calculation:

Max(Insertions::coverDate) < Get(CurrentDate)

(Where Insertions is a related table tied to the advertisement)

Unfortunately, for all advertisements (regardless of rather they have
previous insertions or not) the field returns "?"

Ideally, I'd like to have the field be a case statement that, in effect
says

if(mostRecentInserts is a valid date) //i can't find a funciton for
this either

{ then show mostRecentInsert
} else {
show "new"
}

Any suggestions? Thanks

Your calculation returns a ? because you are expressing a "logical" or
"boolean" concept (one that returns true or false) not a date. If the
maximum value in Insertions::coverDate is less than the current date, it
will return a "true" (1), otherwise it will return "false" (0).

The actual method you use to report this information will depend on how you
have set up your database.

The most likely way would be to have one table, "advertisements," which
contains one record per advertisement, related to another table,
"placements," which has one record per instance of publication. The tables
would already be connected via an advertisement ID, which is unique in the
advertisements table.

To obtain the most recent publication date, you would do the following:

1) create a calculation field, called Today, in the advertisements table.

= Get(CurrentDate)

with a date result. Storage option, Global.

2) Create a second instance of the insertions table in the relationships
graph. This second instance, placements2, would have *two* criteria for the
relationship:

a) advertisements::advertisement ID = placements::advertisement ID (just
like the original insertions table)
b) placements2::insertionDate < advertisements::today

This effectively tells FileMaker to only consider dates less than today
within this relationship.

3) With the new relationship set up, you may now create a calculation in the
advertisements table, mostRecentPlacement:

Max ( placements2::Publication Date )

with a Date result. Note that I am using placements2 here. This tells
FileMaker to report the maximum of the dates of records in Placements that
match the advertisement ID and are less than (but not equal to) today's
date.

Bill

"chimpgrrl" <schamberlin (AT) gmail (DOT) com> wrote

Correcting a small typo for clarity:

....

a) advertisements::advertisement ID = placements2::advertisement ID
b) advertisements::today > placements2::insertionDate

....

"Bill Marriott" <wjm (AT) wjm (DOT) org> wrote

Thanks Bill. The structure is already as you suggested; sorry if i
wasn't clear there. I will try the 'second relationship' idea... I'm an
SQL person so the idea of additional relationships is a little
difficult for me to wrap my head around, but I understand your
suggestion for the 2nd relationship. With my field definition, I was
effectively trying to say "select the most recent date before today"
(again, just due to the way I conceive of queries..) Thanks. //skc

chimpgrrl wrote:
Not a second relationship -- just a second condition on the same
relationship. That is something you do all the time in SQL but that we
in FileMaker have only been able to do since FM7.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

If she tries to add the second condition to the first relationship

(advertisements<-->placements)

she will break the usual link between the two tables.

She has to add a second instance of the insertions table and a second
relationship

(advertisements<-->placements2)

with the two criteria specified in order for it to work. So it is two lines,
two relationships

Bill

"Howard Schlossberg" <howard (AT) antispahm (DOT) fmprosolutions.com> wrote

Ok-- I admit I probably didn't read everything in its entirety, so I
must've missed this point. Before I saw your original post, Bill, I had
actually started my own reply that would have used GetNthRecord(). May
or may not be a possibility for this situation.

Bill Marriott wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

Howard,

Chimpgrrl's stuation is just a prime example of when to use another "table
occurrence" in the relationships graph -- which I think was an unusual
concept for all of us to grasp initially. The magic of these things is that
each "relationship" specifies a sort and filter between the two tables.

We could not rely on GetNthRecord() alone to return the correct result,
because there could be multiple insertions of an advertisement today in
multiple publications. We wouldn't know the record index to use in this case
without complicated logic.

The beauty of creating the second relationship is that FileMaker can use it
to automatically winnow down the universe of insertions to just those for
this advertisement, prior to today's date. I can still remember the hoary
days when we would have to script complicated Finds and Sorts in conjunction
with calculated fields and summary fields to accomplish this.

Bill


"Howard Schlossberg" <howard (AT) antispahm (DOT) fmprosolutions.com> wrote