This calc is leaving me with a ? for an answer... Any
suggestions....????

lastdate
=max(if(fieldA = table2::fieldA; table2::date)

table relationship =
table1::lastdate = table2::date
table1::fieldA = table2::fieldA

I have a feeling I cannot restrict the max with an if inside it, but
without I'm getting the max of all records versus records that test
positive.

The above does work where the fieldA matches the fieldA and the max
date is the max date overall... Otherwise I'm getting the ?

Dazzed and Confused....

Lara

I have a feeling you have left something out in what you posted. Given
your posted calc:

max(if(fieldA = table2::fieldA; table2::date)

your If() statement is missing an argument:
if(fieldA = table2::fieldA; table2::date

Anyhow, the max() function works across all related records, while the
If() statement inside (if you CAN put an if() statement inside) would
work only against the first related record.

But assuming your calc WOULD work, what are you accomplishing? The
max() function works across related records -- those are valid, related
records -- which means the If() statement isn't doing much.

If you are simply trying to find the maximum date across the
relationship, then max(table2::date) should work. Any empty dates would
be at the minimum anyhow, so should not affect what is the max. But if
table1::lastdate is part of the relationship that you are testing
through, then you kind of have a circular relationship, because
table2::date has to update itself based on the max() through the
relationship, which could change the relationship, which would
re-evaluate the max, which would change the relationship, etc.

Please tell us more specifically what you are trying to accomplish so
that we might perhaps help you with the relationship(s) issue.



Carpeflora wrote:

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

On Feb 27, 1:51 pm, Howard Schlossberg
<how... (AT) antispahm (DOT) fmprosolutions.com> wrote:
Ok...
On the if statement...I think I could have used a case instead...
Regardless, you are correct in there being a potential (or real)
circular relationship. Taking out all ifs and just leaving max in as
noted, I'm getting what I originally had (which had me running
circles...litterally.... Here are more details...really hope you can
see what I'm missing...spent a day on this...way too much time!

table1
record1 - fieldA = ABC (stored value)
lastdate (calc, unstored) = MAX (table2:date) (in example should
pull in 2/1/06)
record2 - fieldA = DEF (stored value...I'm checking this)
lastdate (calc, unstored) = MAX (table2:date) (in example should
pull in 11/2/05

table2
record1 - fieldA = ABC; date = 1/2/05
record2 - fieldA = ABC; date = 2/1/06
record3 - fieldA - DEF; date = 8/1/04
record4 - fieldA - DEF; date = 11/2/05

I have a feeling there is something wrong with the relationship, but I
cannot see it.

When I remove the table1:fieldA=table2:fieldA from that relationship,
I end up with the max of all fields in table1:lastdate

Thank you for your advise!!

If the goal here is to get the max of related records in Table1 for the
current record in Table2, then I would do one of two things:

1) Relationship should be strictly from Table1::FieldA = Table2::FieldA.
Calc field in Table1 should be max(Table2:ate)

2) Relationship should be strictly from Table1::FieldA = Table2::FieldA,
sorted by Table2:ate in reverse order. Calc field in Table1 should be
Table2:ate (which takes from the first related record, which is the
latest date because the relationship is sorted in reverse order).

If either one of these options is not working for you, then there is
something about the relationship that you are overlooking.


Carpeflora wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

On Feb 27, 4:43 pm, Howard Schlossberg
<how... (AT) antispahm (DOT) fmprosolutions.com> wrote:
Thank you Howard! I had a criteria in the relationship between table1
and table2. Working perfectly! You're an angel.

Lara