Newest date from several places

[Bekah]

We need an unstored calc called LastContact in my Contacts table that always displays which date is the newest (closest to today) between:

Call Logs table - CallDate (date field)

Contacts table - LastReach (date field)

Invoices table - InvoiceDate (date field)

Ive trying multiple case statement but it seems very bulky and unnecessary. I'm already tired. It looks like this roughly:

Case(Last(Call Logs::CallDate) > Last(Contacts::LastReach) and Last(Call Logs::CallDate) > Last(Invoices::InvoiceDate); Last(Call Logs::CallDate ...

well, you get the picture. That's only one of, ummm, how many possibilities?

OH sorry. They are related like this:

Contacts ContactID related to Invoices ContactID

Contacts Phone related to Call Logs::Number and both these keys use Filter()

Not sure if that made a difference. All the keys are text. There must be a simpler way to write this formula but I can't get it. Thank you for any help for me.

[MarkWilson]

Max( CallLogs::CallDate ; Contacts::LastReach ; Invoices::InvoiceDate )

[Bekah]

Holy smokes!!! I expected shorter but not a miracle! Thank you so much Mark.

Uh, then I thought it was broke but I had something wrong. It's perfect although as I scroll through the records, occasionally it'll make this groaning noise and go a bit blank. I know the feeling. It also seems to dance around a bit. Maybe because it has to think and calculate so much?

[Vaughan]

Aggregate functions like Max and Min can take a while to process if there are a lot of records to go through. It's possible that users will need to wait a couple of seconds (or longer) as the record count gets high.

Any dynamic display of data such as has a performance cost.

[Fenton]

Would using the Last() function, as Bekah originally was, be faster? Psuedo code:

Max( Last(CallLogs::CallDate) ; Last(Contacts::LastReach) ; Last(Invoices::InvoiceDate) )

FileMaker won't accept the above. But it should accept:

Let ( [

LastCall = Last(CallLogs::CallDate);

LastContact = Last(Contacts::LastReach);

LastInv = Last(Invoices::InvoiceDate) ] ;

Max ( LastCall; LastContact; LastInv )

)

I don't really know if it would be faster. Last is faster than Max, but it does use 2 functions instead of 1. Also Last would be unnecessary if the relationship were sorted descending by date; maybe that would be faster?

[Bekah]

Hi there Fenton:)

I will try this. invoices is 230,000, contacts is 60,000 and calls are 855,000. Sort - last reach should be easty to sort by. call logs might problem - it's huge and newest dates at bottom. it might be more obvious than this hesitation I get now. Let? ok. I don't know that one yet but I will try this. Thank you.

[-Queue-]

In this case you are only comparing the value of three fields (the first related ones according to the relationships' sort orders). The fields don't appear to be calculations either. So Max shouldn't take much time at all. It isn't really an aggregate function in this scenario.

[Bekah]

Hi Queue, thank you for helping.

"(the first related ones according to the relationships' sort orders). The fields don't appear to be calculations either. "

that's where I messed up above - I had originally pointed to Invoices::InvoiceDate but it wasn't including the invoices in the LastContact display. Then I remembered that relationship is based upon our old ID still and not ContactID - not sure if that matters. And I remembered I already had a calc in contacts unstored called zcLastInvoice = Last(Invoices::InvoiceDate) which I need for other things so I switched to that instead and it then included the invoice date in the LastContact! Is that my problem?

None of these relationships are sorted either. Should they be? Is that why Max is taking longer or is it because I used a calc in it? I'd be concerned changing the sort order. Does relationship sorting override any portal sorts I have?

Invoices and call logs are in date order with newest at bottom. I could sort the call log relationship no problem. Are you saying I should do that to put the latest contact call at the top? Same with invoices and use Invoicess::InvoiceDate instead?

[Fenton]

Queue's right. It is only comparing 3 fields. But unless the relationships are sorted descending, then it's only the first records, which is not what you want.

So, either use Last() as I did, or sort the relationships and use Mark's calculation. They are 2 slightly different ways to do more or less the same thing.

I believe someone once tested and found sorted relationships are slightly faster than Last(relationship::field). I don't know.

Portals can be sorted independent of the relationship in 7. But I would think you'd want them sorted descending anyway, if you have a lot. Yes, you'd need a mechanism to enter new rows. "Allow entry of related records" for portals is just too weak for this quantity of data, IMHO.

No matter what you do, with 855,000 records a calculation is not going to be real fast using related fields. If it is just too slow you should consider creating a controlled interface, so that a script sets this value, for each of the tables, into fields in Contacts. Then it would just be:

Max (field1, field2, field3), all stored dates, all in the same table; very fast.

But that would require quite a bit of work. A simple uncontrolled interface would not work for this.

[-Queue-]

Let me clarify. I was referring to Vaughan's comment regarding Max's speed. I should have said last related and not first related, though. I would use Fenton's calculation. If your relationships aren't sorted, Last may not return the correct value though. It will return the most recently created record's value, which may not be the one with the largest date. If you are confident that Invoices and Call Logs creation order is sequential, then you should be okay.

And yes, relationship sort order, not portal sort order, will determine the first and last related records. If it didn't, much chaos could result.