1 2 Previous Next 18 Replies Latest reply on Aug 30, 2011 4:34 PM by slaglemark

    help formatting data based on calculation

    slaglemark

      Title

      help formatting data based on calculation

      Post

      hi, thanks for your time.

      i would like to do the following:

      place a field on a layout that could mask whatever the source field is based on a calculation.

      so, i have 10 or so fields on a layout that are possible value 0 - 4.

      i want the field to mask all 4s as GREAT!, and just change the source data field.

      can this be done?

      id rather not create a corresponding field for each of those 10 fields to do the calculation.

      thanks in advance

      -m

        • 1. Re: help formatting data based on calculation
          philmodjunk

          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.

          • 2. Re: help formatting data based on calculation
            LaRetta_1

            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. :^)

            • 3. Re: help formatting data based on calculation
              slaglemark

              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.

              • 4. Re: help formatting data based on calculation
                philmodjunk

                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.

                • 5. Re: help formatting data based on calculation
                  LaRetta_1

                  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!" )

                  • 6. Re: help formatting data based on calculation
                    slaglemark

                    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.

                    -m

                    • 7. Re: help formatting data based on calculation
                      LaRetta_1

                      "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.

                      • 8. Re: help formatting data based on calculation
                        philmodjunk

                        Perhaps you want the user to select a value and then the field should disappear, replaced by the text?

                        • 9. Re: help formatting data based on calculation
                          LaRetta_1

                          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.

                          • 10. Re: help formatting data based on calculation
                            slaglemark

                            im pretty sure my question is the problem.  i appreciate your help so far.  let me try this:

                            scenario:

                            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!

                            • 11. Re: help formatting data based on calculation
                              LaRetta_1

                              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. 

                              • 12. Re: help formatting data based on calculation
                                philmodjunk

                                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.

                                • 13. Re: help formatting data based on calculation
                                  LaRetta_1

                                  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" )

                                  • 14. Re: help formatting data based on calculation
                                    slaglemark

                                    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.  

                                    URL: http://cl.ly/012u22030g1j0J1I0r46

                                    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!

                                    -m

                                    1 2 Previous Next