Announcement

Collapse
No announcement yet.

clone or copy records from previous day to today? a hint please?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • clone or copy records from previous day to today? a hint please?

    hi guys, i have scenario that i have 10-20 records each day, with one of the fields is a date. next day, i need to "clone" those records again and have this date field show todays date instead of yesterdays date...

    what is the quickest way that can be done? even i could clone the records without changing the date field automatically to todays date that is fine.. i just want to help end user not to type all those records again every day, basically, end user will click to open a form in multiple records view or editiable grid for example with yesterday's data (as a copy)...he/she will edit necessary information then save it to the active table where there is a grid to show that active table to viewers group that want to see these active records of today

    any idea or hint is appreciated.


    Mike

  • #2
    Run button.

    Code:
    OnRecord:
    [selected][] = {primary_key_field};
    
    OnFinish
    if(count([selected]) > 0
    {
       $to_copy = "(".implode(',',[selected]).")";
       sc_exec_sql("INSERT INTO your_table SELECT Null, field2, field3, CURDATE(), field5 ... FROM your_table WHERE primary_key_field IN ".$to_copy);
    }
    sc_redir(where_ever_you_have_to_go);
    You have to list all the fields in the SELECT statement. The Null value is for the primary key (auto increment). CURDATE() is to update your date field (which would be field3).

    jsb
    Last edited by jsbinca; 08-08-2014, 05:35 PM.

    Comment


    • #3
      Although, I know you are genius my friend, no, i am sure of it, and I'm really thankful that you still around and have some time for poor us

      I really tried all what I could last 2 days without having the required stuff.

      First, created Run button in the grid of "yesterday's records" (after having difficult time, though this run button is in the form app looool)

      Next: inserted the code you provided then changed the fields accordingly.. i Have around 9 fields in the table, one of them is the date which will be changed before the save... does the fields order matter? because i didn't add them in the exact order, thought to keep field3 for {reviewdate} so it will match with your tutorial, but i'm sure that i added all the fields in the code..

      first i got the variable prompt for [selected] when tried to run the application - so I put the variable as out in variables section under Application... then I got the error says:

      Parse error: syntax error, unexpected '$this' (T_VARIABLE) in D:\sc8\wwwroot\scriptcase\app\ME\students_add_batc h\students_add_batch_apl.php on line 2222

      where the "students_add_batch" is the app name that i am using in multiple records form for adding new student batch... i put it in the redir line you provided


      what i understood from your solution, that a grid that displays yesterday's records will directly copy those records and change the date field into today's date, right?


      maybe my scenario was not clear, please bear will me, will try to explain it

      - each day, there is a bunch of records (students) with each record there is more than one date, and one image as well.
      - one of the dates is indicating today's date, field name: {reviewdate} (so i can view separately in a grid as the schedule of today)
      - next day, mostly same students will have almost same schedule, so i was thinking to retrieve the data of yesterday in a multiple record form (especially that maximum records will be 15-20) then change the field {reviewdate} either manually or automatically + update the fields if required, then save the new values as new records to the database.......
      - after all, if the new 15-20 records added to the database in the new {reviewdate}, then i can view them as todays schedule again using the grid that shows today's schedule because put in SQL where reviewdate = CURDATE() so i can always see today's students schedule by opening that grid



      hope it is more clear now, i think you might have better idea to do the needful as well based on your experience



      moreover, i listed all the fields as per your instructions, but the "play" button to view the application gives error unexpected "{" --- even on Record I changed it to the primary key field {studentid}


      appreciated your care and efforts


      Mike

      Comment


      • #4
        Ok,
        why not copy the records first and than load the form (multiple records/editable grid view)?

        In onScriptInit of your application:
        Code:
        sc_lookup(rs;"SELECT GROUP_CONCAT(primary_key_field) FROM student_table WHERE reviewdate = (SELECT MAX(reviewdate) FROM student_table) AND reviewdate < CURDATE()");
        if(count($rs) > 0)
        {
           $to_copy = "(".{rs[0][0]}.")";
           sc_exec_sql("INSERT INTO your_table SELECT Null, field2, field3, CURDATE(), field5 ... FROM your_table WHERE primary_key_field IN ".$to_copy); //the field list has to be in the same order as in the table
        }
        All you have to do now is go to your SQL WHERE CLAUSE and add reviewdate = CURDATE(). Now you can edit your records and just save them.

        Note:
        "WHERE reviewdate = (SELECT MAX(reviewdate) FROM student_table) AND reviewdate < CURDATE()" This is to make sure we don't copy twice and catch weekend/holiday.
        Also are you sure studentid is the primary key? In this case it has to be an auto increment field.
        In the example, field4 would be your reviewdate field. What this means is you list all the fields in the same order as in the table and substitute the primary key (auto increment) with NULL and reviewdate with CURDATE().

        jsb
        Last edited by jsbinca; 08-11-2014, 10:59 PM.

        Comment


        • #5
          alright, i've added the code to my multiple records form in the OnScriptInit event, and changed the values exactly as instructed (the primary key is "id", it is auto increment, so i replaced with NULL and also reviewdate replaced with CUREDATE().. ) - later added the SQL part as you said reviewdate = CURDATE()

          first got error that ";" was unexpected so checked the macro and replaced it with a coma "," - (first rs) - after that move the form opened once only as empty fields.. then didn't open again, it keeps saying

          Code:
          Error while accessing the database:
          You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
          note: i have 2 other date fields among this table fields, and one image upload BLOB field, so, when did not work, i thought to replace them by NULL, also no change (dates basically are set to datestamp the record update and the record insert) so i thought it may conflict with the copy and insert into the new fields... but also same SQL error is there...

          i removed back to code and it loads ok (but empty, for sure )

          btw: i did create a new multiple records form for this purpose.. i am thinking to add it to the menu later so if end user want to copy last day records and modify them before save then well and good... and there is the original multiple records form that to add new records from scratch or append some more records to the day if the end user want to do that...

          hmmmm, if this makes it simpler: can't we create a button in the form_add_batch_records that fills the data of the last date by clicking that button? something like: end user opens up this single form he has, to add 20 new records, he first clicks on the button above to fill the fields from last day records... then adjust the data, add new records if required, then, save the new batch as one date schedule... this will eliminate also the need of 2 forms, what you think?

          for me i just want the scenario to work, doesn't matter to create 2 forms or one... and also wanna thank you again for your care and support


          Mike

          Comment


          • #6
            Before getting hyper active, why don't we just find the typo. :-)

            On the left pane, open the Application submenu and click on Settings. Scroll down to Error Settings and set Debug Modus to Yes.

            Run the form and check/post the SQL command with the error.

            jsb

            Comment


            • #7
              Okay boss

              I figured out what is going on... when this multiple records form is loaded, for the first time, it works actually it copies last day's records to the database directly... i checked the db and seems the copy is ok...along with the reviewdate it comes as todays date! fantastic.

              now i assume you meant earlier by (all you have to do is to add reviewdate = CURDATE() to SQL... is to re-show those copied records in the form again so user can edit adjust and re-save updated version right? well, that seems perfect logic.. but the form is starting all empty fields.. blank fields (but, actually, copies the data in the background to the database...) AND, if started the form again, gives an error (there is no much in debug, simply because there is no max date that is not today's date,, this prevents the loop as wll, not to copy the records again... i like it) although, it will be great if didn't show this error, can't we add something like ( else if(count($rs) = anything else then ignore and continue...? ) or, can we customize this exact error message to say e.g. "there are no records to copy, or this is already done" - at least!?


              so i think the issue now is the load today's records in the form, so when the copy is done, and form is loading todays records from the database in the update mode, it can be adjusted and saved.. bingo... but couldn't make the form have todays records.. don't know why, it start in the insert mode like to add 20 new records... tired to make "records to insert: 0 but started as one record to insert, blank...


              Mike

              Comment


              • #8
                Oooops! It was late when i wrote the post. :-)

                Ok, what we are doing here is copying the records 'before' the form is loaded, hence onScriptInit.
                Now, to get the correct records loaded please click on SQL in the left pane.
                Go to Where Condition and put in: reviewdate = CURDATE()
                This ensures that whenever you open the form, you have always today's records regardless if there were something to copy or not.

                Here is the corrected code including a message that nothing has been copied

                Code:
                if(isset($rs) && {rs[0][0]} != null)
                {
                   $to_copy = "(".{rs[0][0]}.")";
                   sc_exec_sql("INSERT INTO your_table SELECT Null, field2, field3, CURDATE(), field5 ... FROM your_table WHERE primary_key_field IN ".$to_copy); 
                }
                else
                {
                   echo "<script>alert('Nothing to copy.');</script>";
                }
                jsb

                Comment


                • #9
                  As I said earlier, you are genius jsb, really, no compliments, it is exactly what I wanted. now it worked but i moved the code from onScrioptinIt to onApplicationInit because each time when you want to save anything from the list; it was giving the message "Nothing to coy"... also I've added the maxDate code part from your old advice to the the code you gave me at the end, it wasn't getting the records without the maxDate thing


                  amazing, thanks dude


                  cheers

                  Comment


                  • #10
                    jsb, dude i am little greedy, excuse me, all what we have done is perfectly working as required, data is being copied from max last day to today without problems, then the multiple records form is displaying the records for update them as required....


                    now, the challenge is i want to do this automatically! like at hour 00:00 copies all the records from last day to today alone... i know might sound crazy but this will solve the issue of (if one new record was added today already before making the copy, then the function will not work!? it will be considered the maxDay is today, then it will not work...) so if in anyway we could make this copy done at 00:00 or even 00:05 for example, hence, we will have the list ready for today automatically, and user can still add new record from another single record form, or to edit them through our previous multiple-records form....

                    does it require cronjob and how to do it? i am sure it is piece of cake for you guys, but for me is the first time dealing with such scenario

                    appreciated your time and efforts

                    Mike

                    Comment


                    • #11
                      [rant]
                      Why do you want to deal with things outside of your application when you are not comfortable with?
                      Is it really necessary to have it fully automated just because of a few records?
                      [/rant] :-D

                      In my opinion a much simpler approach would be: Go to Tools -> Libraries and create a new php library.
                      Create a function (i.e. copy_students) and move the stuff to copy the records over (get rid of the 'Nothing to copy' message) and save it.
                      In your forms go to Programming -> Libraries and check your newly created library.
                      In the onApplicationInit or onScriptInit event just call your copy_students() function.
                      Now whatever form you fire up checks for records to copy and if there are some it will do the job .

                      jsb

                      Comment


                      • #12
                        OH, that is really perfect dear, appreciated the idea, it is much better than i wanted loool you have all the right to [rant]

                        ok, what i have done is to copy the code to all applications manually... and it is doing the job perfectly (i removed the message part)...

                        Because when added to the library as "project" (also tried as "public") what i noticed is SC is adding automatically the <php and ?> --- then when i'm trying to select it in the application > programming > libraries > it gives me weird message Untitled.png

                        although, adding it in the library is much better for updating one code later on...


                        again, appreciated


                        cheers

                        Comment


                        • #13
                          Yeah, sometimes it's weird.
                          Nevertheless, try it again. Delete the library and create a new one. Copy/paste your stuff and save it.
                          That's how it should look like. Well, you have to replace ... with the proper code. :-)

                          Code:
                          <?php
                          function copy_students()
                          {
                          	sc_lookup(rs,"SELECT ...");
                          	if(isset($rs) && $rs[0][0] != null)
                          	{
                          	   $to_copy = "(".{rs[0][0]}.")";
                          	   sc_exec_sql("INSERT INTO ...");
                          	}
                          }
                          ?>
                          jsb

                          Comment


                          • #14
                            Perfect, it worked on OnScriptInit (not onApplicationInit)

                            Now the party starts, you are invited dear jsb, in fact you and Eric are the VIP guests for tonight loool



                            appreciated, cheers

                            Comment

                            Working...
                            X