13 Replies Latest reply on Jul 15, 2009 2:51 PM by mrvodka

    Assigning formula for calculating a field

    dss

      Title

      Assigning formula for calculating a field

      Post

      Hi everybody,

       

      i have a problem specifying the formula for calculating a field.

       

      The field can take either value 1 or 2.

       

      there is main file and current file,

      all the fields are same in both the files. I have made relation between these fields (a corresponding field in current and main) in such a way that when the field (which is a drop down list) in current file is changed, it automatically changes the field in main file.

      Everything above said works perfectly well.

      The problem is that

      There is a small field beside the main field in main file which should take only numerics 1 or 2 only.

       

      research: Yes : 1

      Counseling: No : 2

      this is the way they look like.

      But i am unable to specify a formula for the numeric field. I tried to specify using "IF" but the values does not keep changing when i change the yes to no or viceversa

       

       

        • 1. Re: Assigning formula for calculating a field
          philmodjunk
            

          Are you using the auto-enter tab to specify a calculation or defining a field of type calculation and entering it there?

           

          if the field containing "yes" or "no" is in the same table this will work if you also clear the "do not replace existing value" option.

           

          If the fields are not all in the same table, and I think that's what you are describing, change your field to a calculation field and enter your if function there.

          • 2. Re: Assigning formula for calculating a field
            dss
              

            Thanks PhilModJunk 

             

            The fields are seperate boxes here.

             

            I used auto-enter tab and specified a calculated value with NO tick in "Do not replace existing value of field" 

             

            The formula used for one of the fields is

             

            If ( AdmisAsst = "Yes" ; 1 ; If ( AdmisAsst = "No" ; 2 ; 2 ) )

             

             

            • 3. Re: Assigning formula for calculating a field
              philmodjunk
                

              Try defining it as a calculation field and see if that works.

               

              Copy your expression.

              Create a new field and select calculation as its type.

              Paste the expression into it.

               

              Put this field along side your version that doesn't update and see if it works. If it does, either redefine your original field or replace it with the new copy you've just created.

              • 4. Re: Assigning formula for calculating a field
                dss
                   Ok thank you, Let me try that.
                • 5. Re: Assigning formula for calculating a field
                  dss
                     Thanks and I really appreciate your help.
                  • 6. Re: Assigning formula for calculating a field
                    dss
                      

                    Hi Phil,

                     

                    I am just wondering, whether i can have different calculation formulas for same field.

                     

                    I have Program status(alum and current) field and I need to have different values for fields like Research(R), Internship(I) etc...

                     

                     So what i need is have separate values for R,I .. for alum program and current program status.

                     

                    All these fields are in the same file.

                     

                    IS THIS POSSIBLE BY ANY MEANS USING FM8.0 

                    • 7. Re: Assigning formula for calculating a field
                      philmodjunk
                         If I understand your question correctly, you should be able to do what you want using either an If, Case or Choose function. Look these three terms up in the on-line help and see if they might work for you.
                      • 8. Re: Assigning formula for calculating a field
                        dss
                          

                        To be clear, I need a formula for

                         

                         

                        Test1 has results Current(result1) and  Prior(result2)

                         

                        perform test1

                         

                        if it is result1 then perform test2, if it is YES;1 and if it is NO;2

                         

                         

                        if it is result2 then the result should be a default no. value (9). 

                         

                        I hope this is clear, please let me know if you need any additional information. 

                         

                         

                        they are in same file but different layouts. 

                        • 9. Re: Assigning formula for calculating a field
                          TSGal

                          dss:

                           

                          Thank you for your posts.

                           

                          If (Test1 = result1 ; 1 ; 2 )

                           

                          This should give you a good start.

                           

                          TSGal

                          FileMaker, Inc. 

                          • 10. Re: Assigning formula for calculating a field
                            dss
                              

                            My requirement is 

                             

                            If (Test1 = result1 ; test2 ;1 or 2 based on result )

                            If (Test1=result2; test3;3)

                             

                            • 11. Re: Assigning formula for calculating a field
                              mrvodka
                                

                              I have had a hard time following exactly what you are trying to do.

                               

                              Case ( Test1 = result1 ; If ( test2 = "true";1; 2 );

                                        Test2 = result2; If ( test3 = "true"; 3 )

                                      )

                               

                               

                               

                              or since there are no other option in your sample for test 2 combined with test3.

                               

                               

                              Case ( Test1 = result1 ; If ( test2 = "true";1; 2 );

                                        Test2 = result2 AND test3 = "true"; 3

                                      )

                               

                               

                              • 12. Re: Assigning formula for calculating a field
                                dss
                                  

                                I am sorry if you cant understand me.

                                 

                                Let me explain you this way.

                                 

                                formulate this:

                                 

                                I have Main student file(MSF) and Current student file(CSF)

                                there is a relation between MSF and CSF by SSN field.

                                 

                                So, basically all the information needed for CSF are pulled from MSF with simply entering SSN and <tab> in CSF

                                 

                                The MSF contains all files of students, like Current students(these students are the ones in CSF), prior students and Alumnae students..

                                 

                                there are 10 fields like Research, Internship, Teaching Exp, etc... in both CSF and  MSF,  and beside all the fields in MSF they have 2 fields, one is drop down list with "yes" or "no" or "prior" and the second one is number field, 1 for yes and 2 for no and 9 for prior ; But for CSF there is only one field beside all the 10 fields, Yes or No drop-down list

                                 

                                Now I am trying to establish following relation

                                There is a layout called Annual report in MSF which has these 10 fields

                                 

                                All the 10 fields should show up as value "9" for all prior students in MSF

                                and for current students it should show: what ever is says in CSF, if in CSF i select Yes for research, then the research field in MSF Annual report should say " YES" and Show "1" in the fields there. 

                                 

                                All these should not effect the prior student records which should have "9" in all the 10 fields 

                                • 13. Re: Assigning formula for calculating a field
                                  mrvodka
                                     Why bother having two files? Your structure is very much flawed. You are basically maintaining 2 copies of the same file and want the current file to update all the main file's records. I suggest you keep it in one file with different filters ( or found sets ) to display the current students.