It would help if you put a copy of your case statement on the forum.
You need to change this calculation to a non-stored calculation.
Sorry -- I thought there might be a fundamental reason why I was getting the unexpected behaviour, not that it might depend on my particular instance. This is what's giving the trouble:Case (memberships::membership_category = "Life" ormemberships::membership_category = "Honorary" ormemberships::membership_category = "Student Group A" ormemberships::membership_category = "Student Group B" ormemberships::membership_category = "Student Group C" ; "0" ;Max ( payments::renewalyear ) > Year ( Get ( CurrentDate ) ) andMin ( payments::status_code ) = "3" ; "1" ;Max ( payments::renewalyear ) = Year ( Get ( CurrentDate ) ) andMax ( payments::type_code ) = "1" ; "2" ;Max ( payments::renewalyear ) = Year ( Get ( CurrentDate ) ) andMin ( payments::status_code ) = "3" andMax ( payments::type_code ) = "0" ; "3" ;Min ( payments::status_code ) = "2" ; "4" ;Min ( payments::status_code ) = "1" ; "5" ;Max ( payments::renewalyear ) < Year ( Get ( CurrentDate ) ) ; "6" ;"7" )None of the fields referenced are empty.
Is this in a calculation field or in a field with an auto-entered calculation?
What is the context for this calculation? Memberships, Payments or some other table occurrence? (there's a drop down at the top of the specify calculation where you select this.)
What is the relationship between Memberships and Payments? Have you checked to see if the relationship actually matches records in the cases where you are seeing an empty result returned?
It's a calculation field, as I said in my first post. The field is in a local table related to the ESS database. Memberships -> Payments = one -> many. There's another table People whose relation to Memberships is usually one -> one, sometimes many -> one (there are Joint memberships and Group memberships). The relation of the local table to People is one -> one. I'm looking at a layout based on People, with various related fields from Memberships and the local table, and with Payments in a portal.
All the fields are displaying appropriate values in this layout EXCEPT for the calculation field in question, and only in a small number of records. The records in question are for individual members, so they correspond to one membership but more than one payment; as it happens, for all of them the most recent payment was for next year and their payment has been completed (status 3), so they ought to be returning 1. Plenty of such records are doing as they ought to.
NB: Hundreds of records in the database are returning correct values and none are returning incorrect values -- the problem is the small number that are returning NO value. If the relationship isn't actually matching records, wouldn't that show up in the other fields in the layout?
It's a calculation field, as I said in my first post
Just making sure, may people confuse the two.
The field is in a local table related to the ESS database.
And which table is that? Membership, Payment or some third table? From the sentence, I'd guess that it's Membership. And do you mean that this table is the "shadow table" for the ODBC data?
And what exactly is specified in the context drop down for this calculation? Membership or some other table occurrence?
And what are the relationship details for Membership to Payments? Membership::MembershipID = Payments::MembershipID? Is it a sorted relationship?
There's another table People...
Since this is not a table shown to be part of the Payment to Membership relationship nor is it referenced in your calculation, I don't see that this information has any bearing on the results that you are seeing for this calculation.
I am assuming for now that the context specified is Membership and that you are looking at a layout based on membership when you check the results returned. If that is correct, if you place an unsorted, unfiltered portal to payments on this layout, what do you see in the portal for the records where you are getting a null (empty) result?
Edit note: If you are reading my response in your email application, please open the forum thread in your web browser as I have extensively edited the last paragraph of my preceding response.
Sorry if I'm not being as clear as I'd like to be. The three tables People, Memberships, and Payments are all shadow tables; the Local table is a fourth table. The calculation field that is giving the trouble is in Local; the layout IS based on People, not Memberships, and I don't know whether that has any bearing on the difficulty I'm experiencing. The fields memberships::membership_category, payments::status_code, and payments::type_code are all displayed in non-repeating fields in the layout (therefore displaying the values of the most recent related record, if I'm not mistaken, but the calculation operates on all related records). The other Payments fields are displayed in a portal, not filtered but sorted in decreasing order by payments::renewalyear.
In the portal, the records that are showing a null result for the calculation do show a series of related records, all with values in the fields referenced, and all the other fields referenced in the calculation also display values; none are empty. Incidentally, I have had this as a non-stored calculation from the start -- I neglected to confirm that in my response to the first reply.
Is there any other information I can supply that would help? I do appreciate the effort you're making!
The calculation field that is giving the trouble is in Local; the layout IS based on People, not Memberships, and I don't know whether that has any bearing on the difficulty I'm experiencing.
And what is the relationship between that Local table and the Memberships and Payments? Those relationships and the fact that you have placed this field from "Local" on a layout based on People could easily play a factor here. The display of data from other tables, when that field is place on a layout, will be controlled by the relationship between the layout's table occurrence and that of the table from which the field is referenced. And this relationship will then match to data in that table as controlled by the values in the relevant match fields of the "current" record in that layout's found set.
However, if your portal to the same exact table occurrence shows the correct data, then this should not be the issue.
Is there any other information I can supply that would help?
I haven't seen any confirmation that the table occurrence context--as specified in a drop down in Specify Calculation matches the table occurrence selected for this portal that we just added. There's a very good chance that it is one and the same, but I can't tell that this is the case from here....
For the records that you are getting a null result, is there any pattern that you can see to what value is in Membership::membership_Category in those records?
but the calculation operates on all related records
You may have missed a post and the edits that I made to the post that preceded it. You are correct that the min and max functions refer to all the related records in payments, though the references to Membership::membership_Category is referring to the first related record.
The Local table is one-to-one with People; People is potentially many-to-one with Memberships, but in these (and most) cases in fact one-to-one; Memberships is one-to-many with Payments. So in these (and indeed in most) cases there is one record each in Local, People, and Memberships, corresponding to several Payments records. Other cases, in which there are two records in Local and People for one Memberships record, are behaving as expected, so I'm pretty sure I've got the relations set up properly. If it helps, Local and People are linked by a unique ID field; People, Memberships, and Payments by a different ID field, which is unique in Memberships but may be multiplied in the others.
The membership::membership_category value is Ordinary in the affected records, but this is also true of many unaffected cases. For clarity's sake, I should point out that the relations I've set up mean that Memberships is the table with the minimum number of records, so there will only ever be one related value in any Memberships field for records in related tables.
The affected records did all have distinguishing values in two other fields that are not referenced (later updated to values shared with other records), but at least one other record that also had those values before updating is unaffected.
I'm not exactly sure about your point:
I haven't seen any confirmation that the table occurrence context--as specified in a drop down in Specify Calculation matches the table occurrence selected for this portal that we just added. There's a very good chance that it is one and the same, but I can't tell that this is the case from here...
In the Specify Calculation dialog there's a drop-down at the top labeled "Evaluate this calculation from the context of:", and that is giving me only the option of the Local table. The other drop-down on the left side allows me to select tables, and I'm selecting the related tables from this. The relationship graph has only single occurrences of any tables, and all relations are ones of equality -- I'm keeping things simple.
I'm not exactly sure about your point:
The context table occurrence for a given calculation field controls what data is accessed by any references to related data in that calculation. By confirming that you have one and only one table occurrence for this data source table, we rule out some possible issues. I expected this to be the case but had to be sure.
So far, I am not seeing any reason for this calculation field to return a null result as we have ruled out the most obvious causes of a problem here.
It's a bit of a long shot, but you might try recovering the file just to be on the safe side. If you do, test the recovered copy even if no issues are reported by the recover process. Recover also rebuilds all your field indexes without trying to identify problems in the indexes themselves and this can affect how relationships and calculations work.
You might also try running a script with this step: Refresh WIndow [Flush Cached external data] to see if it makes any difference.
Well, I've tried recovering the file. No problems reported (and none from Consistency Check either), but the issue remains. I also added the Refresh Window [Flush Cached external data] step into one of my scripts, but that isn't changing anything either.
I guess I need to resign myself to bafflement. Thanks for your help!
Now to seek a workaround . . .