Adding a Copy Multiple Records button to a Grid

Applications Grid / Reports
#1

I have a code testing table in my database called multi_add. It has 3 columns, MA_ID, Name, List. I’m trying to create a button that will allow me to select 1 or more records on a grid, copy all of their data and make new records using that data into the same table for editing later. I’ve successfully been able to copy the records in SQL and through your SQL builder with this code:

SQL:
INSERT INTO multi_add(Name, List)SELECT Name, List FROM multi_add

SQL Builder in Scriptcase:
INSERT INTO multi_add(Name, List)
SELECT Name, List
FROM multi_add

Unfortunately when I try to build a RUN button using scriptcase 7 and add this code to the OnFinish it does not work.

After a lot of review I found that using the OnRecord code for inserting data into a different table in the OnFinish area of the run button, seems to work for this scenario, however, I can’t seem to get the data to populate with what I need it to. It will create a new record, with a new ma_id but Name and List are either blank or whatever I type into the array. Here is the code:

$insert_table = ‘multi_add’; // Table name
$insert_fields = array( // Field list, add as many as needed
‘Name’ => ‘Name’,
‘List’ => ‘List’,
);

// Insert record
$insert_sql = ‘INSERT INTO ’ . $insert_table
. ’ (’ . implode(’, ‘, array_keys($insert_fields)) . ‘)’
. ’ VALUES (’ . implode(’, ', array_values($insert_fields)) . ‘)’;

sc_exec_sql($insert_sql);

I have tried creating global variables, adding brackets and a whole lot more with no luck. I know I’m missing something and it’s probably going to be one of those duh moments but could someone please help? I need a fresh set of eyes.
Thanks in advance!

#2

Hi,
have a look at this post, it gives you an idea what to do.

http://www.scriptcase.net/forum/showthread.php?6738-clone-or-copy-records-from-previous-day-to-today-a-hint-please&highlight=copy

jsb

#3

Hi jsbinca! Thanks so much for the post! :slight_smile: I actually did review that post earlier today, along with a variety of other posts, videos and tutorials lol. Tried to figure it out before I posted. I did copy and paste the first set of basic code information you gave him. Not sure what I did wrong with it earlier but I’ll show you what I tried with your post. Maybe you can make some sense out of the error:

OnRecord:
[selected][] = {MA_ID};

OnFinish
if(count([selected]) > 0
{
$to_copy = “(”.implode(’,’,[selected]).")";
sc_exec_sql("INSERT INTO multi_add SELECT Null, Name, List FROM multi_add WHERE MA_ID IN ".$to_copy);
}
sc_redir(form_multi_add.php);

I too had the application start me with an input field called selected and a button to continue to grid_multi_add below it. When I select the button I get the following error: Parse error: syntax error, unexpected ‘{’ in D:\Program Files\NetMake\v7\wwwroot\scriptcase\app\Food_Inspection_Demo\grid_multi_add\index.php on line 1818

I don’t see any extra { characters in the code you provided so I’m not sure where to take it out. I don’t need to auto copy and auto change values everyday like the gentleman you assisted so the next sets of code were a little more complex than what I needed. I’m just looking to have the end user select multiple items at random to copy when necessary and they can make their own changes. The initial code you provided would work great but I’m not sure where the error is coming from.

Thanks!!

#4

I also tried to modify the next example that you gave, took out the references to his date changes and this is what I ended up using:
sc_lookup(rs, “SELECT GROUP_CONCAT(ma_id) FROM multi_add FROM multi_add”);
if(count($rs) > 0)
{
$to_copy = “(”.{rs[0][0]}.")";
sc_exec_sql("INSERT INTO multi_add SELECT Null, Name, List FROM multi_add WHERE ma_id IN ".$to_copy);
}

I received an error so I took the steps you suggested of turning on the debug mode, ran it again and this is the error I was shown.

ERROR Message:

(mysqlt): SELECT ma_id, Name, List from multi_add LIMIT 0,12 (mysqlt): SELECT GROUP_CONCAT(ma_id) FROM multi_add FROM multi_add 1064: 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 ‘FROM multi_add’ at line 1

  ADOConnection._Execute(SELECT GROUP_CONCAT(ma_id) FROM multi_add FROM multi_add, false) % line 1085, file: adodb.inc.php

ADOConnection.Execute(SELECT GROUP_CONCAT(ma_id) FROM multi_add FROM multi_add) % line 1808, file: index.php
grid_multi_add_apl.SC_btn_0() % line 1355, file: index.php
grid_multi_add_apl.controle() % line 2723, file: index.php

(mysqlt): INSERT INTO multi_add SELECT Null, Name, List FROM multi_add WHERE ma_id IN () 1064: 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

  ADOConnection._Execute(INSERT INTO multi_add SELECT Null, Name, List FROM multi_add WHERE ma_id IN (), false) % line 1085, file: adodb.inc.php

ADOConnection.Execute(INSERT INTO multi_add SELECT Null, Name, List FROM multi_add WHERE ma_id IN ()) % line 1836, file: index.php
grid_multi_add_apl.SC_btn_0() % line 1355, file: index.php
grid_multi_add_apl.controle() % line 2723, file: index.php

Error
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
INSERT INTO multi_add SELECT Null, Name, List FROM multi_add WHERE ma_id IN ()

Now in my database I have the primary key ma_id set to not null. Since we call for Null I’m wondering if this is where I’m running into a problem? I’m still hacking at it though trying everything I can think of lol. Any thoughts? Thank you so much for your help!!

#5

WooHoo! Persistence pays off lol. I was able to modify the code and figure out my errors and got it to copy the data. Here’s the code:

sc_lookup(rs,“SELECT GROUP_CONCAT(ma_id) FROM multi_add”);
if(isset($rs) && {rs[0][0]} != null)
{
$to_copy = “(”.{rs[0][0]}.")";
sc_exec_sql(“INSERT INTO multi_add SELECT Null, Name, List FROM multi_add WHERE ma_id IN”.$to_copy);
}

Now I just have to try to adjust it to only copy the items the user selects. I’m assuming this is done where I have the select group concat code. If you have any pointers while I rack my brain looking for the right selection code I sure could use them.

Thanks again!!

#6

Ugh, no matter how I change the sc_lookup the code duplicates everything in the table no matter how many records I select. :frowning: Quitting for the night, been at it all day. At one point 4 records turned into 400 because it held the criteria. Even tried adding a reset to 0 for the count and it said NO and gave me more data lol. Oh well, any suggestions you guys could share would be greatly appreciated! Thanks!

#7

Hi,

onScriptInit: [selected] = array(); /initialize the array where you collect the selected rows. Set to OUT

Run Button onRecord:
[selected][] = {ma_id};

Run Button onFinish:
if(count([selected]) > 0
{
$to_copy = “(”.implode(’,’,[selected]).")";
sc_exec_sql("INSERT INTO multi_add SELECT Null, Name, List FROM multi_add WHERE MA_ID IN ".$to_copy);
}
sc_redir(form_multi_add.php);

jsb

#8

You are a doll! Initially I was getting the parse error with unexpected { again. I was able to fix the error and ya know what, YOU ROCK! This is perfect!!! Thank you sooooo very much for all of your help! :smiley:

For anyone else needing to do the same process, here is the code thanks to all the help from jsbinca!!

Step 1:
onScriptInit: [selected] = array(); /initialize the array where you collect the selected rows. Set to OUT in application/global variable.

Step 2:
Create a new run button

Step 3:
Run Button onRecord:
[selected][] = {ma_id}; /replace ma_id with your primary key

Step 4:
Run Button onFinish:
if(count([selected]) > 0)
{
$to_copy = “(”.implode(’,’,[selected]).")";

sc_exec_sql("INSERT INTO multi_add SELECT Null, Name, List FROM multi_add WHERE ma_id IN ".$to_copy); /replace multi_add with your table name and replace name, list with a listing of your table entries that you need to copy. Must match order of your database
}
sc_redir(form_multi_add.php); /replace form_multi_add.php with whatever form you would like to redirect to, if any

Save and run! Good luck!

1 Like