3 Replies Latest reply on Jan 11, 2012 9:37 AM by holbrowa

    Max (date) from multiple tables

    holbrowa

      Hi,

       

      I'm trying to create a calculation that will set a field with the maximum date from 2 other related tables. I've tried this:

       

      Case (SL_PD = "Yes" or MAPC_Transfer = "Yes"; Max (MA_Charting_2011::SV_Last_Done; MA_Charting_2012::SV_Last_Done); Max (${SL_Charting_2011}::SV_Last_Done; SL_Charting_2012::SV_Last_Done))

       

      but the results are the max date from MA_Charting_2012 or the first date from MA_Charting 2011. Can anyone help me figure out why the calculation is not working and/or point me in a better direction?

       

      Thanks,

      Alice

        • 1. Re: Max (date) from multiple tables
          psijmons

          Aline, the brackets  and/or dollar sign are wrong in your statement.

          Either choose a local variable (remove brackets and calculate the Max in the variable) or a field (correct brackets and remove $)

           

          Max ({SL_Charting_2011::SV_Last_Done)

          • 2. Re: Max (date) from multiple tables
            comment

            Try it this way:

             

            Case ( 
            SL_PD = "Yes" or MAPC_Transfer = "Yes" ; 
            
            Let ( [
            max11 = Max ( MA_Charting_2011::SV_Last_Done ) ; 
            max12 = Max ( MA_Charting_2012::SV_Last_Done )
            ]; 
            Max ( max11 ; max12 )
            ) ; 
            
            Let ( [
            max11 = Max ( ${SL_Charting_2011}::SV_Last_Done ) ;
            max12 = Max ( SL_Charting_2012::SV_Last_Done )
            ]; 
            Max ( max11 ; max12 )
            ) 
            
            )
            

             

            Notes:

            1. I am not sure why your TO "SL_Charting_2011" is marked as an invalid name, but it's something  you should look into.

             

            2. You should probably look into a solution that would eliminate the hard-coding of data (2011, 2012, etc) into your structure.

            • 3. Re: Max (date) from multiple tables
              holbrowa

              Thank you both very much for your replies.  Comment, your solution worked like a charm. 

               

              Also, thanks for the suggestion about hard-coding the data - I'm trying to upgrade some very old databases, and there is so much wrong, I hardly know where to start!  Also, being new to database design, I'm not always sure of the best practice, so I appreciate your tip.

               

              Thanks,

              Alice