Jump to content
Sign in to follow this  
Bekah

Newest date from several places

Recommended Posts

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? shocked.gif

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. smile.gif

Share this post


Link to post
Share on other sites

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

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? smile.gif

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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. grin.gif

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Hi Queue, thank you for helping. wink.gif

"(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? confused.gif

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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. wink.gif

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    AlesD 
×

Important Information

By using this site, you agree to our Terms of Use.