1. 

   john, Member

   join in select so my insert doesn't insert row into result set.
   inserting a row into joined table but i need to update two columns, the id and the edited value so that i can then edit the same cell again without reloading page.
   on inserts i usually use dbr.refresh (and something other than a text area) but dbr.refresh doesn't work here so trying with javascript

   troubles with what i have tried
   if the stored procedure returns a result set I don't know how to access the array of results
   this only accesses first one col_value_get( this )
   Changing a column "id2" value with below call back, new value displays on screen but dbr.editable uses the old value

   dbr.editable code
   select 'dbr.editable', '[col1]', 'sp_DBR_update_insert_col1', 'insecond=id2','inmainid=id', 'type=textarea',"options={'height':50,'onblur':'','callback':mycallback}

   call back code
   select 'dbr.javascript', 'function mycallback() { var B = col_value_get( this ); col_value_set( this, 16, 0, B); }';

   notes-

   change/ update
   works- if column "id2" is not null on report load
   sort of works - column "id2" is null, edit col1, use call back to change column "id2" value. but now my edited cell shows my result set in json because that is what i returned from stored procedure
   does not work- edit same cell again - dbr.editable does not not send the value set in sort of works for column "id" instead passes the value the report loaded with

   Insert
   works- saving to db
   does not work- I am having trouble manipulating the two values received back from stored procedure

   Thanks
2. 

   myDBR Team, Key Master

   John,
   can you show the actual query, the editing procedure (sp_DBR_update_insert_col1) and the structure of the table you are editing? Easier to see what the problem is.

   --
   myDBR Team
3. 

   john, Member

   Thanks very much for having a look, i changed the column names in original post to make it easier to read, these are unchanged

   report
   select 'dbr.javascript', 'function mycallback() { var B = col_value_get( this ); col_value_set( this, 16, 0, B); }';

   select 'dbr.editable', '[workperformed]', 'sp_DBR_update_insert_workperformed', 'inid=id2','inproject1id=id', 'type=textarea',"options={'height':50,'onblur':'','callback':mycallback}";#, "select=sp_DBR_TEST_selectactivity [Equipid]";

   select p.id, p.ProjectName as 'ProjectName', p.`Change Order` as 'Change Order', p.Notes1 as 'Notes1', p.ClientCode as 'ClientCode', p.Description as 'Description', p.FieldA as 'FieldA', p.Complete as 'Complete', p.tcref as 'Tcref', p.Qty as 'Qty', p.`Unit Price` as 'Unit Price', p.Total as 'Total', p.UOM as 'UOM', p.ProjectNumber as 'ProjectNumber', p.projectid as 'Projectid', t2.id as 'id2', t2.project1id, t2. workperformed, t2.commentswo, t2.hourswo from test.project1 p left join test.tbl_tcpiptime2 t2 on p.id = t2.project1id where projectid=test.getprojectid( inproject); END $$

   stored procedure - the ifnull part is a work in progress, i originally had an if exists with a select on the id

   CREATE DEFINER=`root`@`%` PROCEDURE `sp_DBR_update_insert_workperformed`(inid int, inproject1id int, invalue text )

   BEGIN

   if (ifnull(inid,0) ) then

   update test.tbl_tcpiptime2 set workperformed = invalue where id=inid;

   else

   INSERT INTO test.tbl_tcpiptime2 ( workperformed, project1id,commentswo) VALUES ( invalue, inproject1id, inid); #note just inserting inid into commentswo so i can see what is being passed,

   select id, workperformed from test.tbl_tcpiptime2 where id = LAST_INSERT_ID();

   end if; END

   table i am edititing

   CREATE TABLE `tbl_tcpiptime2` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `workperformed` TEXT NULL, `commentswo` TEXT NULL, `project1id` INT(11) NULL DEFAULT NULL, `equipid` INT(11) NULL DEFAULT NULL, `ticpipheadid` INT(11) NULL DEFAULT NULL, `hourswo` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`id`), INDEX `ticpipheadid` (`ticpipheadid`), CONSTRAINT `tbl_tcpiptime2_ibfk_1` FOREIGN KEY (`ticpipheadid`) REFERENCES `tbl_tciphead` (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=288 ;
4. 

   myDBR Team, Key Master

   John,
   do you expect just one or more than one row in tbl_tcpiptime2 against a row in test.project1 (tbl_tciphead?)?

   --
   myDBR Team
5. 

   john, Member

   yeah my table names suck
   the way it is working now that is possible but no i will limit to one row in tbl_tcpiptime2 per row in tbl_tciphead
6. 

   myDBR Team, Key Master

   If you just have zero or one row in tbl_tcpiptime2 against a row in tbl_tciphead, you can use tbl_tciphead.id as a key in the editable procedure. In the procedure you can check if the row exist and do update / insert. You can then use the callback to get back the tbl_tcpiptime2.id (if you really need to show it in the report).

   If you have more than one row in tbl_tcpiptime2 against a row in tbl_tciphead, you need to separate the insert and update so that you can create multiple tbl_tcpiptime2-rows against a id in tbl_tciphead. For insert you can attach a dbr.report to the project name which will do the insert and refreshing to show the added row. The editable field would be just simple editable field.

   --
   myDBR Team
7. 

   john, Member

   thanks i will incorporate a button for inserts. I don't know enough about javascript to move forward on using callback - will see what i can learn

Reply

You must log in to post.