Calculation based upon latest record in a related table

[SlimTech]

Hi all,

 

I'd greatly appreciate any help with this, please. I've looked through previous posts but couldn't find a solution.

 

I've got a Customers table related to a Customer_status table by Customer_nr. Any single customer can have from zero to ten or so status records. These records are displayed in a portal on the Customers layout. The records in the portal are sorted descending by Date_of_contact so the latest record appears at the top of the portal. The portal then, shows a history of previous contacts made and what was achieved.

 

What I'd like to be able to do if there's at least one Customer_status record is enter a value in a field on the Customers layout, Days_before_next_contact and add this value to the latest date found from all related Customer_status records. This would then result in a date for the next contact to be made with the customer relative to when contact was last made.

 

Ideally and to make things even easier Oh Really! , if the value entered in Days_before_next_contact resulted in a Saturday or Sunday, its value would be adjusted automatically to give the first weekday.

 

Thanks for any help on this,

 

Mike

[Norma_Snockurs]

A calc field in your Customers table defined:

If(
  IsValid ( Customer_status::Date_of_contact ) ; 
  Let (
  cDate = GetAsDate ( Max ( Customer_status::Date_of_contact ) + Days_before_next_contact ) ;
  cDate + Choose ( DayOfWeek ( cDate ) ; 0 ; 1 ; 0 ; 0 ; 0 ; 0 ; 0 ; 2 )
  )
)[size=2]
[/size]

will give you your nearest weekday future contact date.

 

You could always sort the relationship between Customers and Customer_status instead of sorting the portal. You wouldn't need to test for the Max ( Date_of_contact ) in the calc then as the latest date value in the relationship (sorted descending ) would be returned by just using Date_of_contact on it's own.

It may not be desirable to sort the relationship in your context however or you could always use another T/O instead.

 

However all this is taking a simplistic approach to a problem that may be far more complex in reality. What do you do to allow for your Holiday days/weeks/seasons?

[SlimTech]

Thanks very much, Norma. I just copied and pasted your solution into an "Auto enter" calculation for my field, "Date for next contact". At first I got a few "Invalid function" errors but realised it was due to hidden characters (gremlins?) copied from the web and pasted. I got rid of them by putting your solution on one line and then it worked fine. I tested that it gives only weekdays!

 

For now, I won't bother taking account of holidays or seasons. I might at some distant time in the future if ever I have enough free (holiday?) time! smiley-smile

 

Thanks again,

 

Michael

[Norma_Snockurs]

Thanks very much, Norma. I just copied and pasted your solution into an "Auto enter" calculation for my field

Happy to help. One caveat though, an unstored calculation field returning a date result will be necessary here, not an Auto-Enter calc performed on a regular date field. There are relational/aggregate parameters involved so you can't rely on a re-calc being triggered by a fresh value for Date_of_contact.

[SlimTech]

I changed my date field from an "Auto enter" calculation to an unstored calculation field (as you'd suggested in your first reply!) and now "Date for next contact" magically becomes updated for any change (extra, later value) in Date_of_contact.

 

Thanks again,

 

Mike