1. 

   ajdjackson, Member

   Hi

   I wish to create a report that would allow me to enter a new populated row into a table - enter a new Supplier invoice.

   The fields I wish to enter for the new invoice are:

   Supplier_Ref picked from a select list
   Site - picked from a select list
   Invoice Date - from datapicker
   Details - text box
   Invoice Net - keyed in
   Invoice VAT - keyed in
   TimeStamp - entered date and time

   I've looked though all the demo reports but they all refer to editing existing records not creating new ones.

   Is what I'm looking to possible and if so do you have an example I can look at?

   I'm ok at writing queries etc but no experience with javascript/JQuery so the easier the better.

   Now that I have got to grips with mydbr for reporting my next tasks will be writing several of these data inputting reports :(

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   Jake,
   easiest way to add new rows is just to use a linked report (with dbr.report) and have the the report parameters handle the data input.

   The linked report then does the insert into the table and refreshes the original report if you have set the linked report target to be inside the original report.

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   Thanks for getting back but I'm still kinda stuck at the beginning :)

   I understand doing the insert into table via a linked report bit but I haven't clue how to setup the invoice entry grid.

   When the initially run the user would see a blank grid with column labels as per above. The user would then in the first column select the supplier id for a select list then move to next column to select site again from a select list. The user would then input a date using the datepicker and then move on to entering invoices amount.

   Then the user would then run the linked report that would do the db table insertion.

   I could carry this out fine if I was editing an existing record but at this stage there is no record in table so how do I enter data into grid?

   Hope you follow that

   Thanks

   Jake
4. 

   myDBR Team, Key Master

   Jake,
   attach the "Create new"-linked report to a button placed on top/bottom of the list. The insert would then be independent from the list showing the existing rows.

   select 'dbr.button', 'Create new', 'dynamic_div[]';
   
   select 'dbr.report', 'sp_DBR_invoice_new';
   
   select 'dummy result set for the button;
   
   

   sp_DBR_invoice_new has necessary parameters to ask all data and will do the insert. It will return 'dbr.refresh' to refresh the original report with new inserted data.

   --
   myDBR Team
5. 

   ajdjackson, Member

   Hi

   I've tried to follow what you you suggest but I don't get a visible button. I haven't created the sp_DBR_invoice_new report yet.

   Any help much appreciated.

   Jake

   DROP PROCEDURE IF EXISTS sp_DBR_Materials_Input
   $$
   CREATE PROCEDURE sp_DBR_Materials_Input(inPlot_ID varchar(25))
   BEGIN

   select 'dbr.parameters.show';

   SELECT
   c.tblMaterials_ActRef as 'Supplier Ref[SuppID]',
   b.tblContractXidJobNo as 'Plot[PlotID]',
   e.tblSupplier_Name as 'Supplier[SuppName]',
   date(c.tblMaterials_Date) as 'Inv Date[InvDate]',
   c.tblMaterials_InvRef as 'Inv Ref[InvRef]',
   c.tblMaterials_Details as 'Details[InvDet]',
   case
   when c.tblMaterials_Type = '7' then (c.tblMaterials_Net)*-1
   else c.tblMaterials_Net
   end as 'Net[InvNet]',
   case
   when c.tblMaterials_Type = '7' then (c.tblMaterials_vat)*-1
   else c.tblMaterials_vat
   end as 'VAT[InvVAT]',
   case
   when c.tblMaterials_Type = '7' then (c.tblMaterials_Net+c.tblMaterials_vat)*-1
   else (c.tblMaterials_Net+c.tblMaterials_vat)
   end as 'Gross[InvGross]',
   f.tblCostCode_Description as 'Type[MatType]',
   c.tblMaterials_DateTimeStamp
   FROM
   hilmark.tblsitesplotsjake b
   INNER JOIN hilmark.tblmaterials_j c
   ON b.tblContractXidJobNo = c.tblMaterials_XIDJob
   INNER JOIN hilmark.tblsuppliers_j e
   ON c.tblMaterials_ActRef = e.tblSupplier_Ref
   INNER JOIN hilmark.tblcostcodes_j f
   ON c.tblMaterials_XIDCostCode = f.tblCostCode_ID

   where
   b.tblContractXidJobNo = inPlot_ID

   order by
   c.tblMaterials_ActRef asc,
   c.tblMaterials_Date desc;

   select 'dbr.button', 'Create new', 'dynamic_div[]';
   select 'dbr.report', 'sp_DBR_invoice_new','inSupp_ID=SuppID','inPlotID=PlotID','inSuppName=SuppName','inInvDate=InvDate','inInvRef=InvRef','inInvDet=InvDet','inInvNet=InvNet','inInvVat=InvVAT','inType=MatType','inEntDate=EntDate';

   SELECT
   null as 'Supplier Ref[SuppID]',
   inPlot_ID as 'Plot[PlotID]',
   null as 'Supplier[SuppName]',
   curdate() as 'Inv Date[InvDate]',
   null as 'Inv Ref[InvRef]',
   null as 'Details[InvDet]',
   0 'Net[InvNet]',
   0 as 'VAT[InvVAT]',
   1 as 'Type[MatType]',
   now() as 'EnterDate[EntDate]';

   END
   $$
6. 

   myDBR Team, Key Master

   Hi,
   use the code provided in the previous post. If you want to pass parameters to the insert report, pass only those that user cannot change, all the others are handled by the report itself as report parameters. Non changing parameters would include possible ID's etc. Columns like EnterDate you can insert in the insert clause if they are not user changeable.

   The button is not showing up because you have not created (and attached the sp_DBR_invoice_new report).

   --
   myDBR Team
7. 

   ajdjackson, Member

   Hi

   Limping forward now.

   I've added the following:

   select 'dbr.button', 'Create new', 'dynamic_div[]'; select 'dbr.report', 'sp_DBR_New_Mat_Inv'; select 'dummy result set for the button';

   I now get a 'create new' link (not a button). However when I click this the New_Mat_Inv report opens in the same window but the original report is gone ie all I see is the output of the sp_DBR_New_Mat_Inv report.

   Managed to edit the fields once I found that I have to create a dummy edit report to edit the fields.

   Also I notice that the column formatting disappears after editing the field ie InvNet was set as %0.2F but when edited the decimal places are gone.

   Is there any way to 'tab' across the input-grid to next column?

   Sorry for so many questions and no doubt there'll be more but mydbr is awesome.

   Cheers

   Jake
8. 

   myDBR Team, Key Master

   I now get a 'create new' link (not a button). However when I click this the New_Mat_Inv report opens in the same window but the original report is gone ie all I see is the output of the sp_DBR_New_Mat_Inv report.

   Sorry about an error, the code should look like (this caused both problems with the link):

   select 'dbr.button', 'b1';
   
   select 'dbr.report', 'sp_DBR_New_Mat_Inv', 'createx[]';
   
   select 'dummy result set for the button';
   
   

   Managed to edit the fields once I found that I have to create a dummy edit report to edit the fields.

   What do you mean by "dummy edit report". The edit functionality would be different from the "new row" functionality.

   Also I notice that the column formatting disappears after editing the field ie InvNet was set as %0.2F but when edited the decimal places are gone.

   What is the datatype you are using for the edited value in the editing report?

   Is there any way to 'tab' across the input-grid to next column?

   Not in the in-cell editing, no.

   --
   myDBR Team
9. 

   ajdjackson, Member

   Hi

   Great that fixed it. How do you control where the create[] div appears?

   what I meant about dummy edit report is to change/enter the data in the entry grid I made each of the fields editable and to actual edit them I found I need a report which is empty for the dbr.editable to work (not give errors. My idea is that when I have populated the entry grid I will have a further linked report running off a button to actual insert the data into table. Is this the best way to do it?

   A couple of the fields I'm editing are Double. When I initially call the report I use the%0.2F to display 0.00. However when I enter actual number say 100 the value is displayed as 100 instead of 100.00

   One other query I have is a couple of the editable fields are defined by using type=select. I have it working ok but it would be nice if some form of autocomplete could be used especially as the select lists can be long. I couldn't find in the manual or demo an example of editing a field with a autocomplete list. Is it possible?

   Thanks again

   Jake
10. 

    myDBR Team, Key Master

    Great that fixed it. How do you control where the create[] div appears?

    The [] at the end of the embed element name means that it is dynamic, myDBR will create it for you at the end of the DOM tree if it does not exist. This is fine if you just use it to pass non-visible items like JavaScript. If you want to show the content, you can use dbr.embed_object command to place the div.

    what I meant about dummy edit report is to change/enter the data in the entry grid I made each of the fields editable and to actual edit them I found I need a report which is empty for the dbr.editable to work (not give errors. My idea is that when I have populated the entry grid I will have a further linked report running off a button to actual insert the data into table. Is this the best way to do it?

    In order to make fields editable you need to have the editing report attached to it. If you mean that the code is not there yet when you say "dummy edit report" that is ok. To handle insert separately is the way to go.

    A couple of the fields I'm editing are Double.

    Please note that in MySQL double alone is treated as an integer. In order treat double as floating number, you need to define the decimal places or just use float-datatype.

    I couldn't find in the manual or demo an example of editing a field with a autocomplete list. Is it possible?

    There is no autocomplete in-cell editing capability. We could take a look if a searchable select list could be supported.

    --
    myDBR Team
11. 

    ajdjackson, Member

    Thanks for that.

    Sorry for taking up your time but this is where I've got to.

    I have the following report called from the 'create new' button:

    select 'dbr.javascript', 'function mycallback()
    
    {
    
    // Value of A in edited row
    
    var A = col_value_get( this, 6 );
    
    // Value of B in edited cell
    
    var B = col_value_get( this, 7 );
    
    // Set the value of 8th 6+7 column
    
    col_value_set( this, 8, 0, A+B);
    
    }';
    
    select 'dbr.report', 'sp_DBR_Insert_Mat_Inv','[Save]','inSuppID=SuppID','inPlot_ID=PlotID','inInvDate=InvDate','inInvRef=InvRef','inInvDet=InvDet','inInvNet=InvDet','inInvVAT=InvVAT','inType=MatType';;
    
    select 'dbr.calc', 'Gross', '[InvNet]+[InvVAT]';
    
    select 'dbr.editable', '[SuppID]','sp_DBR_dummy_edit_input','type=select','select=select hilmark.tblsuppliers_j.tblSupplier_Ref, hilmark.tblsuppliers_j.tblSupplier_Name FROM hilmark.tblsuppliers_j where hilmark.tblsuppliers_j.tblSupplier_isSubbie = 0 order by hilmark.tblsuppliers_j.tblSupplier_Name';
    
    select 'dbr.editable', '[PlotID]','sp_DBR_dummy_edit_input','type=select','select=select tblsitesplotsjake.tblContractXidJobNo,tblsitesplotsjake.tblContractXidJobNo FROM hilmark.tblsitesplotsjake';
    
    select 'dbr.editable', '[InvDate]','sp_DBR_dummy_edit_input','type=datepicker';
    
    select 'dbr.editable', '[InvRef]','sp_DBR_dummy_edit_input','type=textarea';
    
    select 'dbr.editable', '[InvDet]','sp_DBR_dummy_edit_input','type=textarea';
    
    select 'dbr.editable', '[InvNet]','sp_DBR_dummy_edit_input',"options={'callback':mycallback}";
    
    select 'dbr.editable', '[InvVAT]','sp_DBR_dummy_edit_input',"options={'callback':mycallback}";
    
    select 'dbr.editable', '[MatType]','sp_DBR_dummy_edit_input','type=select','select=select tblcostcodes_j.tblCostCode_Id,tblcostcodes_j.tblCostCode_Ref from hilmark.tblcostcodes_j order by tblcostcodes_j.tblCostCode_Ref';
    
    select 'dbr.colstyle', 1, '[width:200px]';
    
    select 'dbr.colstyle', 2, '[width:150px]';
    
    select 'dbr.colstyle', 3, '[width:100px]';
    
    select 'dbr.colstyle', 5, '[width:200px]';
    
    select 'dbr.colstyle', 6, '[width:100px]%.2f';
    
    select 'dbr.colstyle', 7, '[width:100px]%.2f';
    
    select 'dbr.colstyle', 8, '[width:100px]%.2f';
    
    SELECT
    
    	null as 'Supplier Ref[SuppID]',
    
    	null as 'Plot[PlotID]',
    
    	curdate() as 'Inv Date[InvDate]',
    
    	null as 'Inv Ref[InvRef]',
    
    	null as 'Details[InvDet]',
    
    	0 as 'Net[InvNet]',
    
    	0 as 'VAT[InvVAT]',
    
    	0 as 'Gross[Gross]',
    
    	null as 'Type[MatType]',
    
    	'Save' as 'Save[Save]';

    The 'sp_DBR_dummy_edit_input' report called when editing the fields above is:

    DROP PROCEDURE IF EXISTS sp_DBR_Dummy_Edit_Input
    
    $$
    
    CREATE PROCEDURE sp_DBR_Dummy_Edit_Input()
    
    BEGIN
    
    END
    
    $$

    On the face of it this works great in that I can change/enter the fields as designed. However when I click 'Save' to call the Insert Report I hit a problem.

    The 'sp_DBR_Insert_Mat_Inv' report has the following temporary code which at present should just display the passed parameters.

    DROP PROCEDURE IF EXISTS sp_DBR_Insert_Mat_Inv
    
    $$
    
    CREATE PROCEDURE sp_DBR_Insert_Mat_Inv(inSuppID varchar(25),inPlot_ID varchar(25),inInvDate date,inInvRef varchar(25),inInvDet varchar(50),inInvNet DOUBLE,inInvVAT DOUBLE,inType int(3))
    
    BEGIN
    
    select 'dbr.pageview';
    
    select 
    
    inSuppID as SuppRef,
    
    inPlot_ID as Plot,
    
    inInvDate as invDate,
    
    inInvRef as Ref,
    
    inInvDet as Detail,
    
    inInvNet as Net,
    
    inInvVAT as VAT,
    
    inType as Type,
    
    now() as EnterWhen;
    
    END
    
    $$

    Nothing gets displayed other than the report's Title, Parameter User Input Column and a Run Report button. Not what I was expecting.

    I've noticed that the parameters are not being sent in the URL other than the date which is set by default - /mydbr/report.php?r=60&p1=&p2=&p3=25%2F08%2F2014&p4=&p5=&p6=&p7=0.00&p8=&m=4&h=43c6351c29f133a2d423285f9cf43d4a034aea9b&i=1

    Any reason why this is not working?

    Cheers and thanks again

    Jake
12. 

    myDBR Team, Key Master

    Jake,
    the code could be bit simpler.

    When you create the create main report the code looks something like this:

    create procedure sp_DBR_show_invoices()
    
    begin
    
    /* Creating new rows. When user clicks this button a dialog is shown to ask for the data */
    
    select 'dbr.button', 'Create new';
    
    select 'dbr.report', 'sp_DBR_New_Mat_Inv', 'dynamic_div[]';
    
    select 'dummy result set for the button';
    
    /* Editing the existing data */
    
    select 'dbr.editable', '[InvDate]','sp_DBR_edit_invoice_date','type=datepicker';
    
    /* The actual list you want to show */
    
    select SuppID, Plot_ID, InvDate, InvRef, InvDet, InvNet, invVAT, inType
    
    from mytable;
    
    end
    
    

    
    The actual procedure doing the insert would just do a insert. You would attach the popup's and other selections to report parameters.

    CREATE PROCEDURE sp_DBR_Insert_Mat_Inv(
    
    inSuppID varchar(25),
    
    inPlot_ID varchar(25),
    
    inInvDate date,
    
    inInvRef varchar(25),
    
    inInvDet varchar(50),
    
    inInvNet float,
    
    inInvVAT float,
    
    inType int(3)
    
    )
    
    BEGIN
    
    insert into mytable ( SuppID, Plot_ID, InvDate, InvRef, InvDet, InvNet, invVAT, inType )
    
    values ( inSuppID, inPlot_ID, inInvDate, inInvRef, inInvDet, inInvNet, inInvVAT, inType );
    
    select 'dbr.refresh';
    
    END
    
    

    
    --
    myDBR Team
13. 

    ajdjackson, Member

    Hi

    I think I've managed to confuse you.

    My New Invoice Report looks like this:

    select 'dbr.parameters.show';
    
    select 'dbr.title', '';
    
    select 'dbr.subtitle', 'Enter New Invoice Details';
    
    select 'dbr.javascript', 'function mycallback()
    
    {
    
    // Value of A in edited row
    
    var A = col_value_get( this, 6 );
    
    // Value of B in edited cell
    
    var B = col_value_get( this, 7 );
    
    // Set the value of 8th 6+7 column
    
    col_value_set( this, 8, 0, A+B);
    
    }';
    
    select 'dbr.report', 'sp_DBR_Insert_Mat_Inv','[Save]','inSuppID=[SuppID]','inPlot_ID=[PlotID]','inInvDate=InvDate','inInvRef=InvRef','inInvDet=InvDet','inInvNet=InvDet','inInvVAT=InvVAT','inType=MatType';
    
    select 'dbr.calc', 'Gross', '[InvNet]+[InvVAT]';
    
    select 'dbr.editable', 'SuppID','sp_DBR_dummy_edit_input','type=select','select=select hilmark.tblsuppliers_j.tblSupplier_Ref, hilmark.tblsuppliers_j.tblSupplier_Name FROM hilmark.tblsuppliers_j where hilmark.tblsuppliers_j.tblSupplier_isSubbie = 0 order by hilmark.tblsuppliers_j.tblSupplier_Name';
    
    select 'dbr.editable', 'InvDate','sp_DBR_dummy_edit_input','type=datepicker';
    
    select 'dbr.editable', 'InvRef','sp_DBR_dummy_edit_input','type=textarea';
    
    select 'dbr.editable', 'InvDet','sp_DBR_dummy_edit_input','type=textarea';
    
    select 'dbr.editable', 'InvNet','sp_DBR_dummy_edit_input',"options={'callback':mycallback}";
    
    select 'dbr.editable', 'InvVAT','sp_DBR_dummy_edit_input',"options={'callback':mycallback}";
    
    select 'dbr.editable', 'MatType','sp_DBR_dummy_edit_input','type=select','select=select tblcostcodes_j.tblCostCode_Id,tblcostcodes_j.tblCostCode_Ref from hilmark.tblcostcodes_j order by tblcostcodes_j.tblCostCode_Ref';
    
    select 'dbr.colstyle', 1, '[width:200px]';
    
    select 'dbr.colstyle', 2, '[width:150px]';
    
    select 'dbr.colstyle', 3, '[width:100px]';
    
    select 'dbr.colstyle', 5, '[width:200px]';
    
    select 'dbr.colstyle', 6, '[width:100px]%.2f';
    
    select 'dbr.colstyle', 7, '[width:100px]%.2f';
    
    select 'dbr.colstyle', 8, '[width:100px]%.2f';
    
    SELECT
    
    	null as 'Supplier Ref[SuppID]',
    
    	inPlot_ID as 'Plot[PlotID]',
    
    	curdate() as 'Inv Date[InvDate]',
    
    	null as 'Inv Ref[InvRef]',
    
    	null as 'Details[InvDet]',
    
    	0 as 'Net[InvNet]',
    
    	0 as 'VAT[InvVAT]',
    
    	0 as 'Gross[Gross]',
    
    	null as 'Type[MatType]',
    
    	'Save' as 'Save[Save]';
    
    select 'dbr.hdr',1;
    
    select 'dbr.sum',6,7,8;
    
    SELECT
    
    	e.tblSupplier_Name as 'Supplier[SuppName]',
    
    	b.tblContractXidJobNo as 'Plot[PlotID]',
    
    	date(c.tblMaterials_Date) as 'Inv Date[InvDate]',
    
    	c.tblMaterials_InvRef as 'Inv Ref[InvRef]',
    
    	c.tblMaterials_Details as 'Details[InvDet]',
    
    	c.tblMaterials_Net as 'Net[InvNet]',
    
    	c.tblMaterials_vat as 'VAT[InvVAT]',
    
    	(c.tblMaterials_Net+c.tblMaterials_vat)as 'Gross[InvGross]',
    
    	f.tblCostCode_Description as 'Type[MatType]'
    
    FROM
    
    	hilmark.tblsitesplotsjake b
    
    	INNER JOIN hilmark.tblmaterials_j c
    
    	 ON b.tblContractXidJobNo = c.tblMaterials_XIDJob
    
    	INNER JOIN hilmark.tblsuppliers_j e
    
    	 ON c.tblMaterials_ActRef = e.tblSupplier_Ref
    
    	INNER JOIN hilmark.tblcostcodes_j f
    
    	 ON c.tblMaterials_XIDCostCode = f.tblCostCode_ID
    
    where
    
    	b.tblContractXidJobNo = inPlot_ID
    
    order by
    
    	c.tblMaterials_ActRef asc,
    
    	c.tblMaterials_Date desc;
    
    

    The user selects the plot he wishes to enter a new invoice for. The above report displays 1 row into which the new invoice details are to be entered. It is pre-populated with Plot_ID and today's date.

    The next bit of the report shows invoices from suppliers for that Plot which have been previously entered - not to edited at this time.

    The top part of the code is where I am entering the new data which at this time is not in the db. I'm using the 'dbr.editable' statements to allow the user to enter the new invoice data. Once the user has entered the data into the grid he then clicks Save which calls the 'sp_DBR_Insert_Mat_Inv' report. The code I have at present is to show me that the parameters have been sent correctly. I will once I'm happy that the parameters have been passed correctly change the code to what you suggest above to actually insert the data into the db.

    My issue is that the data changed/entered via the 'dbr.editable' statements are not being forwarded to the 'sp_DBR_Insert_Mat_Inv' report the URL calling that report doesn't contain any of the changed/entered data.

    Hope you follow that - sorry for taking up so much of your time.

    Jake
14. 

    myDBR Team, Key Master

    Jake,
    what we are saying that creating new rows in that way is too complex. You do not need to create editable fields withing the dialog that is shown when you click the 'create new' button.

    Just use the code from our previous post to make the insert.

    --
    myDBR Team

Reply

You must log in to post.