Find the latest date

[Skooney]

I have records with 6 possible "run dates" and I need to come up with a calculation that finds the latest date of the 6. It is possible and likely that most of the fields will be empty.

12/12/19

1/1/20

2/15/20

The calculation should look at the 6 fields and determine which has the latest date.

Thanks for any help you may offer.

[Fitch]

What you're looking for is the Max function.

https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Fmax.html%23

Welcome to the forums.

[Skooney]

Wow - presto it worked!

Thanks so much, you saved me a bunch of time!

[Skooney]

Since that worked so well, let me ask a follow up question.

Of those 6 possible date fields how do I find which date is closest to a Today (date field).

Of the 3 dates in the original post. The result would be 12/12/19, today is 12/9/19.

Any ideas or do I need to clarify?

[comment]

First, I would suggest you use related records instead of fields.  Everything will become easier once you do that. For example, you could define a calculation field in the related table that returns the difference between the date and today, then sort the records by this field and show only the first one. 

To calculate the date closest to today from a list of dates would require a rather complicated custom function - perhaps something similar to https://www.briandunning.com/cf/1266. Might be easier now that SortValues() and While() functions are available - but still not trivial.

 

[Fitch]

As @comment says, this one's more complex, and it's not clear to me whether those dates are fields in a single record, or are a single field in multiple records.

Essentially you need to do understand a couple of things:

- you can subtract one date from another to get a number of days, e.g. Get(CurrentDate) - RunDate

- you can then work with those numbers in various ways, e.g. the Min function

- if you use a calculated field to derive the date difference from the current date, you'll generally want it to be unstored so that it updates when used. Be aware that unstored fields can be slow if whatever you're doing involves large numbers of records, or e.g., you're using calculated fields for those Max/Min results rather than storing the results.

[Skooney]

Thanks to Comment and Fitch! I'm glad to hear you both calling this complicated, I don't feel so dumb now😄.

The good news is, from the information you have shared, I think I can come up with a solution that will work.

Ralph

[Johstrom]

On 12/9/2019 at 9:25 PM, Fitch said:

What you're looking for is the Max function.

https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Fmax.html%23

Hi,

I have a similar problem. I have, in a related database, 1 field with due date and 1 field with status. I would like to find the latest due date among the records with a certain status. If I use the Max function, I get the last due date for all posts, of course. I have tried to set it in a If function, like "If ( status = "status1" ; Max ( duedate ) )", but it still show the last due date of all records. Any idea?

With kind regards, Johan.

[comment]

2 hours ago, Johstrom said:

I have a similar problem.

Actually, it is not similar at all - and you would have done better to post this as a new question.

Filemaker has no conditional aggregate functions similar to Excel's SUMIF() and COUNTIF(). There are several alternatives:

1. Use a summary field defined in the child table. Place this field in a one-row portal, filtered to show only records that meet the condition. This is suitable when the number of child records is not very high and the result is for display only.

2. Define a second relationship that matches only child records that meet the condition. Then use a calculation field in the parent table (or a summary field in the child table) to summarize these records.

3. Define a calculation field in the child table along the lines of:

If ( Status = "status1" ; DueDate )

Then summarize this field using one of the options mentioned in #2.

4. Use either a recursive custom function or the While() function to loop over the related child records. This has the advantage of not adding anything to the file's schema beyond the one calculation field in the parent table.

Which of these options is best for you depends on your particular situation of which we know practically nothing.

--
P.S. Please update your profile to reflect your version and OS so we know what you can use.