4 Replies Latest reply on Apr 29, 2013 7:04 AM by philmodjunk

    Trouble understanding date's

    NathanVeitch

      Title

      Trouble understanding date's

      Post

           Hi There,

           I need to ask a question regarding date functions. Why is it that if I have a calculation 

           Month(Staff Register::SalaryStartDate) + 1 that gives me a value of 5 yet 

           MonthName(Month(Staff Register::SalaryStartDate) + 1) gives mt the value January. 

            

           I am trying to find the best way to get the next following month from a date field. I am trying to work out a payslip formula for my client. They clock all the employees in and out every day and then need to work out their pay for the end of the month. I have gotten so far as to generate all the pay info per day and then that will be printed to a payslip so that the employee can see what they got paid per week of that month. Now i am trying to make a routine where by the admin can select a month from a drop down list and then veiw the payslip for that employee for that month. I have stored a variable that saves when the first day of the pay cycle would be, for example, the 26th of the month. Now I need to figure out how to get the next months value from that start date field, so that when the search is done, it will find the right months values. 

            

           Any help is greatly appreciated. 

        • 1. Re: Trouble understanding date's
          NathanVeitch

          Ok, I got it to work in a very round about way, but now I feel that my database is going to have a lot of extra data that might not be needed. What I have done is create a calculated field that saves the months name using this calculation, MonthName(Date(Month(SalaryStartDate)+1;Day(SalaryStartDate);Year(SalaryStartDate) )).

          Now my worry is that I am going to have a lot of fields with the same data sitting in my database, and all it will be used for is to do a search to get the payslip lines for that month. I have an idea that I can build this into my search script when the admin searches for payslips by month, but I am unsure of the way to do the script. 

          If I use the calculated field, would it store the actual value there, or does it jsut recalculate it when it is needed. Cause I am just worried that I fill my database with repeating data. 

          • 2. Re: Trouble understanding date's
            hbrendel

                 MonthName takes a date as a parameter, not a number. When you feed it with a number this will evaluate to a date. The date 5 will be 05-01-0001 (january).

                  

            • 3. Re: Trouble understanding date's
              NathanVeitch

                   Cool, thanx for this. 

                    

                   I was able to get it working when I added in the Date(month;day;year) function. Now I need to find out, would it be better to use a calculated field to store the Month name, or should I script it in to insert the month name into the field? What I am trying to get is a field that I can use to search against should the client need to search for a prevous months payslip. 

                    

                   I have the thought of writing a search script with a whole bunch of if statements that would use the SalaryStartDate field as the match field, for example, If GlobalSearchField = "May", then search for SalaryStartDate = 26/04/2013 ( or where the month = "4". 

              • 4. Re: Trouble understanding date's
                philmodjunk

                     In most cases, I would not insert the month name into a field. Any time you need to display the name of the month, you can use data formatting in the inspector's data tab to format the month field to include the name of the month.