8 Replies Latest reply on Jun 8, 2015 2:09 PM by philmodjunk

    Calculated Field Not Working

    comfortfalcon

      Title

      Calculated Field Not Working

      Post

      In my database I have tables that contain test scoring information, and I have a table that needs that information to calculate whether or not students increased scores. I have calculation fields that get the difference and check to see if it increased, decreased or stayed the same. But now when I enter new values for students and their test scores, it doesn't calculate on my results table. 

       

      Capture.PNG

        • 1. Re: Calculated Field Not Working
          philmodjunk

          You have unstored calculation fields that simply copy the value of a record in a related table. Such a field is usually not needed in FileMaker except for certain very specialized purposes--such as pulling a value into a related table so that a relationship or portal can be sorted. that doesn't seem to be the case here. Instead, you can access the same value by referring to CYRM_PRE::Subscore: Individual in each place where you currently refer to PRE CRYM Individual.

          What value you see here will be determined by how your relationship is set up. What is the relationship involved here?

          My best guess is that the table, from which you captured this screen shot is linked to CYRM_PRE in a one to many relationship. In otherwords. One record in the first table links to multiple records in the CYRM_PRE table (or table occurrence). In such cases, the value returned will only be from the first related record. Adding more related records with different data won't change what you see as the data in those new records aren't being accessed at all in this expression.

          • 2. Re: Calculated Field Not Working
            comfortfalcon

            I think this is different then what you are thinking of? I am repurposing a database that was created for a nonprofit, and the guy who came before me set up all the relationships. I can't tell if he did it right or not. Everything is linked through the student id, just like this. 

            I was using lookup, but it wouldn't auto look up when values changed either? It did seem to be working at first.

            • 3. Re: Calculated Field Not Working
              philmodjunk

              In which table is the calculation defined?

              Students?

              Student Improvement?

              Given the "crows feet" that I see on the CYRM_PRE side of the relationships, it looks like it is exactly what I am describing: a one to many relationship where you are adding new records to CYRM_PRE and they have no effect the values returned in your calculations as the combination of expression and relationship is such that it does not refer at all to the data in those added records.

              • 4. Re: Calculated Field Not Working
                comfortfalcon

                It is defined in student improvement.

                Okay so if i want to have student improvement for each student correctly look up their scores, what is the easiest way? 

                I guess I am asking if I need to redo all the relationships in the database. 

                I do think it is set up that you can only create records in the student table, and not the other way around. like this?

                thank you so much for the help so far

                • 5. Re: Calculated Field Not Working
                  philmodjunk

                  Whether you can or cannot create related records via the relationship is not at issue.

                  You have these relationships:

                  CYRM_PRE>------Students-----<StudentImprovement

                  A single record in Students can link to multiple records in CYRM_PRE and to multiple records in StudentImprovement.

                  Your calculation is defined in StudentImprovement, but refers to a field from CYRM_PRE. To "get to" data in that table, it has to "tunnel" through the Students table. The current StudentImprovement record will match to one and only one student record. This record, in turn, matches to possibly multiple records in CYRM_PRE. But your calculation can only reference data from a single record so it will refer to the first (usually the oldest) related record in CYRM_PRE. What this means is that all records in StudentImprovement with the same student ID will refer to the same record in CYRM_PRE. If you add more CYRM_PRE records with the same Student ID, their data will not appear in these calculation fields.

                  • 6. Re: Calculated Field Not Working
                    comfortfalcon

                    I don't need to have multiple CYRM_PRE per student. Every single table only needs to have one record tied to it. Should I change all the relationships to single to single?

                    IF i only have one CYRM_PRE linked to the student ID then it should work correct?

                    Am i missing an obvious solution here? 

                    • 7. Re: Calculated Field Not Working
                      comfortfalcon

                      Well I'm not sure what's going on, but if i try to alter the fields, they fill out correctly, but only if i try to change them and press enter. I have no clue why. 

                      • 8. Re: Calculated Field Not Working
                        philmodjunk

                        If you only have one per student, it should work. And as I have previously stated, it does not look like you need these fields at all. You'll need to describe what you are trying to do in more detail.