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

Applications Forms
#1

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.


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

#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 :slight_smile:

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

First, Run button. I created a new button in the multiple records form (this is what I understood), type: PHP and 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…

first i got the variable prompt for [selected] - so I put it as out from 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_batch\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 this same multiple form will start in insertion mode, then there will be a button that will pull yesterdays data… 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 then change the field {reviewdate} either manually or automatically and change some values then save to the database as new records…
  • after all, if the {reviewdate} is changed, i have a grid that shows CURDATE() so i can always see todays 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 :slight_smile:

appreciated your care and efforts

Mike

#4

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 :o

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

#5

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

In onScriptInit of your application:



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

#6

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

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 :slight_smile: )

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

#7

Before getting hyper active, why don’t we just find the typo. :slight_smile:

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

#8

Okay boss :slight_smile:

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… :slight_smile: 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

#9

Oooops! It was late when i wrote the post. :slight_smile:

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


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

#10

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 :slight_smile:

amazing, thanks dude

cheers

#11

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 :smiley:

appreciated your time and efforts

Mike

#12

[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] :smiley:

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

#13

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

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

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

again, appreciated

cheers

Untitled.png

#14

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. :slight_smile:


<?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

#15

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