Calc based on date and specific text via portal

[AlanP]

We currently use a portal to show 'training history'. It has training date, training type, Training location and Training hours. We are required to retrain these people every two years based on their last training date. So I was using this calucation to display their 'training expiration date':

Training Date + 730

The problem is we are now adding other types of training to this table such as specific training and directed training which does not require training every two years.

SO...because the portal sorts showing the most current training date first, the calcluation uses that date instead of the correct training date (say one or two portal rows down).

Is there a way to modify that calculation so it will only look for the 'training type' text that we wish to retrain every two years on?

Thanks everyone...sorry this was a long post!

[Fenton]

You could do it a couple ways. You could create what I call an "If" calculation in the child table, result Date.

Case ( Training Type = "2 year", Training Date + 730 )

In other words, it only has the date when the type is what you specify. Then target that with the relationship.

Alternatively you could create an unstored calculation field in the parent table, with the fixed words of the type:

_c2YrTrainingTxt = "2 year"

Then add that to a compound relationship to the child table:

_c2YrTrainingTxt=::Training Type

Notice that both methods depend on the actual text value in the Training Type field. You'd want that to be consistent; if you change the value list at a later time earlier records will no longer match.

[AlanP]

Thanks Fenton!

I liked the solution:

Case ( Training Type = "2 year", Training Date + 730 )

probablly because I didn't fully understand the other one :

I was able to successfully use the calculation like that (I created it in the table where the training history is kept). However when placed on the main layout it wouldn't show the calc/date if a new record was entered into the training history table.

We like to have this calculation on all our other layouts as a 'quick glance' type feature to see when their training expires.

Am I doing something wrong or is your other solution a better choice?

[Fenton]

I'm not sure what you mean. The calculation is in the child table (as you said). You could put it on the parent layout. You'd probably want to sort that relationship descending, so that you only saw the latest (in the case where the person has multiple training records of that same type). Otherwise you'd be seeing the 1st. That's how unsorted relationships work.

You could do this instead of sorting the portal (if that's what you've done); it has the same effect on the portal. It also effects scripts, and Go To Related Record [] steps for that relationship; but that's probably fine (as long as you know).

If you make a change in the child table you may not immediately see the change in the parent, unless you do something to refresh the window. It's just a visual limitation of many related calculation fields. The data is already changed (as you'll see if you use it in a script), it just doesn't refresh immediately on the parent layout unless something's done, like change layouts. Refresh Window [ Flush cached join results ] is the ultimate refresh, which works on very complex calculations; but this one is pretty simple.

[AlanP]

Fenton,

I went and created a new DB showing these two tables to help explain what is going on (please see attached). I created two training records. One with '2 Year' for the type of training we would like to see the expiration date for and then another record that we don't (I made the date for 'today' to make it appear at the top).

I'm sure it's something really simple that I did wrong!!!

Thanks again for your help

Alan

training_example.zip