j.hall wrote, in part:
IndvTypeTracking::Type = "Foster Parent" or IndvTypeTracking::Type = "Caregiver" and IndvTypeTracking::TypeStatus <> "Inactive" and Year(HomeVisit::HomeVisitDate)=TrainingTracking::CurrentTrainingYear
I think that you need to incorporate parenthesis, depending on the actual logic you intend to impose on this condition. For example:
( IndvTypeTracking::Type = "Foster Parent" or IndvTypeTracking::Type = "Caregiver" ) and
(IndvTypeTracking::TypeStatus ≠ "Inactive" ) and
Parenthesis tell the calc engine which parts of the condition to evaluate separately and which to use together. As originally written, it was unclear whether you wanted the first condition to be evaluated alone or with some of the later conditions. As rewritten you now have a string of three AND conditions which must all be met, including either of the two options in the OR condition at the start. (This is why I say your intent will decide where you need to place the parenthesis.)
I did that and now no records are being displayed...
But if I take out and (Year(HomeVisit::HomeVisitDate)=TrainingTracking::CurrentTrainingYear) it displays the first record in TrainingTracking table it finds.
Is that what you want it to do? If not, please explain the logic of what you want the calc to do in English so we can take another stab at where the parenths, ANDs, and ORs should be arranged.
Ok, sorry I might have not made that clear.
The individual in the portal should be an Active Person Assigned to the Home and the record from the Training Tracking Table should be the one in which the Training Year matches the Year of the Home Visit. This is to insure that if they need to be reprinted they have the correct information for they year on them.
Another thought, be sure the calc field result type of TrainingTracking::CurrentTrainingYear is set to number, not text, so it can match the Year() result. Text and Numbers won't equal properly.
TrainingTracking::CurrentTrainingYear is already set to a number as the data type.
Using a value from the TrainingTracking table is a problem if the portal isn't showing records from that table. You may need to do a calc test for a PatternCount on a LIST of record field values if the portal is relying on another table than the portal table.
Yes, the intent is to get the portal to show records from the TrainingTracking table. But I need to get it to show 2013 instead of 2014 for example.
You could do that with a global field for the user to pick which year to use and rewrite part of the the filtering calc to be based on the global field selection. This would avoid having to reference a separate value in another related table.
I have a picker and that works on another portal. But the same setup isn't working here...
OK, I think I figured it out... well, no, that's not true. I've figured out what it's doing, but I'm not sure how to fix it.
So If I go to the indivdual face sheet and manually set their training year, to 2014 and then go to a home visit form for 2014, it shows the correct record.
if I change it on the Individual Facesheet to 2013... They drop off the filter for 2014...
… and Year(HomeVisit::HomeVisitDate)=TrainingTracking::CurrentTrainingYear
HomeVisit -> HomeInfo -> HomeIdvAssign -> Individual -> TrainingTracking.
Try creating a calc field in HomedvAssign that reads in currentTrainingYear via the relationship, then use that to compare to year of homeVisitdate. I think you cannot use data that is located “past” the target (portal) TO in the relationship chain to compare, but must an actual attribute (field) of the target TO's table. No, that IS possible, must be something else
If that (or all else) fails, why not post a file with some (non-prívacy violating) sample data?
I'm beginning to think that the only way I'm going to get this to work is create dates on everything and setup new TOs so that you can view things "By Year".
I don't have a file I can post unfortunately.
My best guess right as to what it's doing is that it finds the first record, it doesn't match and it doesn't search for one that does.
I'm sorry, I'm kinda knew with all this. Very frustrated...
I guess I'm not understanding how portal filtering works yet.
If an individual has 2 records in a table, is there a way to automatically select one record over another in a portal? Sort of like a find a record?
if I tell the portal to show Records from TrainingTracking it shows ALL the records, doesn't filter them and it doesn't pull the right names for the records.
Why does it doe that? If the record is related to an individual? Why is the portal pulling the incorrect Name for that record?