I think you'll need to describe that all in more detail. "mask" means you do not want to show the contents of these fields at all? If so, don't put them on the layout in the first place. You want "GREAT!" for each item no matter what the value of the field!. If so, this could simply by layout text that reads "GREAT!".
But that all seems much too simple and an odd design, so I suspect that there are some key details missing here.
I agree with Phil. Remember that we only know what you mean by the words you put in your post. Your post made no sense to me and I stared at it for 5 minutes trying to figure it out. :^)
thx for your reply. details:
i have 8 fields, containing numbers 0 - 4.
i could create 8 correspoding calc fields such that IF field = 4 ; GREAT! ; IF field = 3 ; OK! ; if field = 2 ; UHOH! ; if field = 1 ; SUX!
and just place that in stead of the original number field.
i dont want to do that.
i want to place the original number field on the layout, just "masked" with the calculation.
Why would you want to place the field on the layout at all if you are going to mask it?
You can add layout text to your layout (One text object for each value) and use conditional formatting to control which text is visible on the layout depending on the value of the field.
Thus, you can add "GREAT!" to your layout and use: YourTable::Field ≠ 4 as the conditional format expression, then specify a font size of 500.
With those settings, "GREAT!" will only be visible if the value of the field is 4.
You'd then layer the other three text objects on top of each other with similar conditional format settings.
YOu need to change your design so those 8 fields are RECORDS in a related table. Then the calculation for the SINGLE number field would be (result is text):
Choose ( numberField - 1 ; "GREAT!" ; "OK!" ; "UHOH!" ; "SUX" )
UPDATE EDIT ... reverse that list to:
Choose ( numberField - 1 ; "SUX" ; "UHOH!" ; "OK!" ; "GREAT!" )
i might be incorrect, but i dont think these save me any time.
the table structure is such that students have a whole lot of scores, which are then converted to grade point values, 0 - 4. (A - F, in report card speak)
all those values are naturally compiled to give a GPA, or an average, for the set of scores.
i really just want a way to display an A instead of 4, a B instead of a 3, for each of these scores, without having to create a calc field for each test score.
i dont think i was able to ask the right question in my original post.
"i want to place the original number field on the layout, just "masked" with the calculation."
This makes no sense either. Why not replace the number field with the calculation? Just double-click the field and re-point to the calc or do as Phil suggests, just stack transparent text objects and attach the 'true' condition to each one. For instance, for text box GREAT! you would attach formula = numberField <> 4. On OK! text, formula = <> 3 etc. Then select them all and Arrange > Align > Center and then Middle. Group them so they are easier to work with.
Perhaps you want the user to select a value and then the field should disappear, replaced by the text?
That is nothing like your original question.
"i have 10 or so fields on a layout that are possible value 0 - 4."
If you have 10 fields which all contain numbers 0 - 4 then you have an incorrect structure. Do you? If you do then, if you do not change it, you will continue to have issues even worse than this one.
Your scores should be records and you are hitting the first real reason why. By using multiple fields instead of records, you will have to create 10 calculations every time you want a calculation; you will have to create 10 relationships every time you wish to filter your relationships and on and on it will go.
im pretty sure my question is the problem. i appreciate your help so far. let me try this:
1. a table contains a record for one student, with 50 fields containing 50 scores on 50 different assessments, all taken by that same student.
2. a calc field weighs each of these assessment scores, and rescores them with a value of 0 - 4, meant to give perspective on what would otherwise be a worthless value.
3. a report is generated, such that each score on each assessment is listed, followed by the 0 - 4 value from the calc field.
how can i represent those 50 0 - 4 fields as A - D values without creating 50 new calc fields.
hopefully im communicating now!
We are communicating ... you just aren't getting my point yet:
"a table contains a record for one student, with 50 fields containing 50 scores on 50 different assessments, all taken by that same student."
If you wish ONE calculation which will change ALL 50 SCORES from number to text, it cannot be done. If you wish to physically CHANGE the data itself from number to text then you can do that. If you change your structure so that the Assessments table contains fields as:
AssessmentID (unique auto-enter FM-generated serial number)
StudentID ( unique identifier for students)
Rating (number field holding the 1-4)
... then you just add a calculation as I described. ONE calc. Otherwise you must address each of those 50 fields individually with either 50 calcs (50?) or 50 text objects with calcs.
In otherwords, Your 50 fields will become 50 records in a related table which makes for greater flexibility and simpler implementation of detaisl such as this calculation here.
Let me give you an example. What if you want the average score from those 50 fields? You will need to do this:
Field1 + Field2 + Field3 + Field4 ... through 50 fields or use something like Sum ( Field1 ; Field2 ; Field3 ... through 50 fields) ... and that only provides the total for THAT Score record and you have multiple Score records. So now you need to add those all together. Remember to consider how many fields you are using (50) in your average calculations. And if you end up adding another score (Field51) then you will have to change every calculation, script and relationship which is attempting to transform a 'flat file' design into a properly related one.
If you use one field to hold the value, you can aggregate easily using summary field. :^)
BTW, now that we know the true data for the calc (and it's purpose), calc would be:
Choose ( Rating ; "F" ; "D" ; "C" ; "B" ; "A" )
thank you both for your replies on this. it sounds like my real problem is a database fundamentals/design issue.
i'm begun reworking the database so that i have a one (central student database) to many (multiple records per student on the different tests) relationship, rather than the one to one i was using before.
this has made your previous answers easy to implement, so thanks for that.
i have a question that has arisen from the change.
i need to be able to control when and where values in the student test scores table display. when i had one record per student, with a field per test score, this was obviously easy. now that i am using a one to many relationship, the layouts just display the first score for each student.
in the above instance, each of the ORAL COUNTING scores would be in a single record. now that each score is in its own record, how can i make them display properly?
thanks so much!