Confirmation Dialogue « myDBR Forums

Confirmation Dialogue

(6 posts) (2 voices)

Started by ajdjackson
Latest reply from ajdjackson

Tags:

ajdjackson, Member

I've created the following report that takes user inputs and eventually inserts this input into a table. I have the report working fine but I would like to have a dialogue where the user confirms the Save or can Cancel and reload the page.

This is the report that take the user input:



DROP PROCEDURE IF EXISTS sp_DBR_Subbie_Cert_Input

$$

CREATE PROCEDURE sp_DBR_Subbie_Cert_Input(inPlot_ID varchar(25), inSubbie varchar(25), inCertDate date, inCertDetails varchar(50), inCertGross float )

BEGIN

declare Max_Cert_Id int(11);

set Max_Cert_Id=0;

select max(y.tblCert_ID) into Max_Cert_ID from

	hilmark.tblcertificates_j y

where y.tblCert_XIDJob=inPlot_ID and y.tblCert_XID456=inSubbie

group by y.tblCert_XIDJob, y.tblCert_XID456 ;

select 'dbr.colstyle',6,'%0.0F';

select 'dbr.colstyle',7,'%0.0F';

select 'dbr.colstyle',8,'%0.0F';

select 	inPlot_ID as 'Plot[Unit]',

	inSubbie as 'Subcontractor[inSub_ID]',

	inCertDate as 'Date[inC_Date]',

	case

	when Max_Cert_Id=0 then 1

	else (select a.tblCert_Number from hilmark.tblcertificates_j a where a.tblCert_ID=Max_Cert_ID)+1

	end as 'Cert#[inC_No]',

	upper(inCertDetails) as 'Details[inC_Det]',

	inCertGross as 'Gross[inC_Gross]',

	case

	when Max_Cert_Id=0 then 0

	else (select a.tblCert_Gross from hilmark.tblcertificates_j a where a.tblCert_ID=Max_Cert_ID)

	end as 'Prev. Gross[inC_Prev]',

	case

	when Max_Cert_Id=0 then inCertGross

	else inCertGross-(select a.tblCert_Gross from hilmark.tblcertificates_j a where a.tblCert_ID=Max_Cert_ID)

	end as 'Change in WIP[inInvNet]';

END

$$

And this is the rpeort that does the insertion:



DROP PROCEDURE IF EXISTS sp_DBR_Insert_New_Sub_Cert

$$

CREATE PROCEDURE sp_DBR_Insert_New_Sub_Cert(inC_No int(11), inPlot_ID varchar(25), inSub_ID varchar(25), inC_Date date, inC_Det varchar(50), inC_Gross float, inC_Net float, inC_Prev float, inC_WIP float)

BEGIN

insert into hilmark.tblCertificates_j ( tblCert_Number, tblCert_XID456, tblCert_XIDJob, tblCert_Date, tblCert_Details, tblCert_Gross,tblCert_NetAmount,tblCert_Previous,tblCert_DateTimeStamp,tblCertificates_AllocateWIP )

values ( inC_No, inSub_ID, Unit, inC_Date, inC_Det, inC_Gross, inC_Gross, inC_Prev, now(),inInvNet);

select 'dbr.refresh';

END

$$

I first thought that I could do this with a button but I couldn't figure out how to ask the Yes/NO question and how do I pass the value to that button?

Many thanks again

Jake

myDBR Team, Key Master

Jake,
how does sp_DBR_Subbie_Cert_Input and sp_DBR_Subbie_Cert_Input relate to eachother? You do not call sp_DBR_Insert_New_Sub_Cert from as one would expect.

What do you mean by "report that takes user inputs"? There is no need for separate procedure for user input and insert?

What do want the dialog for? What has user done before your Yes/No dialog should appear?

--
myDBR Team

ajdjackson, Member

This is a 3 report sequence.

The first report 'sp_DBR_SubContractor_Certs' allows the user to select the Plot_ID and then displays any certificates entered for that plot.

This report has a button which allow the user to enter a new certificate with the only parameter being passed is the Plot_ID resulting in the user being prompted to enter the new certificate details in 'sp_DBR_Subbie_Cert_Input'. The new certificate details are displayed for the user to review.

What I haven't been able to work out is how to actually trigger the update by calling 'sp_DBR_Insert_New_Sub_Cert' giving the user the opportunity to cancel and refresh 'sp_DBR_SubContractor_Certs'. Therefore I haven't any code in 'sp_DBR_Subbie_Cert_Input' yet to execute the update.

Hope you follow that.

Below is the report code for the 1st report in the sequence:

Thanks

Jake

DROP PROCEDURE IF EXISTS sp_DBR_SubContractor_Certs

$$

CREATE PROCEDURE sp_DBR_SubContractor_Certs(inPlot_ID varchar(25),inLogin varchar(30))

BEGIN

select 'dbr.parameters.show';

select 'dbr.title',concat('SubContractor New Certificate Entry for ',inPlot_ID);

select 'dbr.embed_object','createx';

select 'dbr.button', 'Enter New Certificate';

select 'dbr.report', 'sp_DBR_Subbie_Cert_Input','inPlot_ID=[Unit]','createx';

select x.tblContractXidJobNo as 'Unit[Unit]' from hilmark.tblsitesplotsjake x where x.tblContractXidJobNo = inPlot_ID;

select 'dbr.hdr',1;

select 'dbr.sum',8;

select 'dbr.colstyle',5,'%0.0F';

select 'dbr.colstyle',6,'%0.0F';

select 'dbr.colstyle',7,'%0.0F';

select 'dbr.colstyle',8,'%0.0F';

select 'dbr.colstyle',9,'%0.0F';

SELECT

	v.tblSupplier_Ref as 'SubCon.[Subbie]',

	u.tblCert_Number as 'Cert.#[Cert]',

	date(u.tblCert_Date) as 'Date[CDate]',

	u.tblCert_Details as 'Details[Cdet]',

	u.tblCert_Gross as 'Gross[Gross]',

	u.tblCert_Dayworks as 'DayWorks[DWorks]',

	u.tblCert_Variations as 'Vars[CVars]',

	u.tblCertificates_AllocateWIP as 'Total[CWIP]'

FROM

	hilmark.tblsitesplotsjake t

	INNER JOIN hilmark.tblcertificates_j u

	 ON t.tblContractXidJobNo = u.tblCert_XIDJob

	INNER JOIN hilmark.tblsuppliers_j v

	 ON u.tblCert_XID456 = v.tblSupplier_Ref

where (t.tblContractXidJobNo = inPlot_ID)

order by

	t.tblContractXidJobNo ASC,

	v.tblSupplier_Ref ASC,

	u.tblCert_Number ASC;	

END

$$

myDBR Team, Key Master

So what you are doing is that you show user a list of certificates and user can add a new one from "Enter New Certificate" button. The button asks user the required parameters and then user is shown details from the certificate. If the parameters look good, user should be able to add the certificate and for this you need the confirmation dialog.

You can add a confirmation dialog with callback function when you add dbr.report call to sp_DBR_Insert_New_Sub_Cert to the sp_DBR_Subbie_Cert_Input.

Btw, the sp_DBR_Subbie_Cert_Input has some errors and inefficies in it.
1) max(y.tblCert_ID) will produce NULL if no certificate is found. Yet you seem to expect it to be 0.
2) The group by is incorrect as the column in group by do not appear in select-clause.
3) You seem to repeat queries from hilmark.tblcertificates_j when you could do this in one query.

--
myDBR Team
ajdjackson, Member
Hi

Thanks for getting back.

1) max(y.tblCert_ID) will produce NULL if no certificate is found. Yet you seem to expect it to be 0.

Yes I expect it to return NULL when the supplier is new to the Plot. After declaring the variable I set it to 0 -
```
set Max_Cert_Id=0;
```
2) The group by is incorrect as the column in group by do not appear in select-clause.
```
select max(y.tblCert_ID) into Max_Cert_ID from hillmark.tblcertificates_j y where y.tblCert_XIDJob=inPlot_ID and y.tblCert_XID456=inSubbie
```
I assume you mean this. I reckon I have to do this as I'm looking for the max cert_id for the combination of Supplier By Plot. It seems to work. Is there another way to do this?

3) You seem to repeat queries from hilmark.tblcertificates_j when you could do this in one query.

Yes I agree - I'll rework.

I would like to know how to do the confirmation via a callback function but I've no idea how to do. I've looked at the example in the manual but I couldn't get it to work.

My fall-back position, although not as elegant, is as follows:
```
.......,

'Save' as 'Save[Save]',

'Cancel' as 'Cancel[Cancel]'
```
and link the Insert report of the [Save] column and the 'dbr.refresh' report of the cancel.

In 'sp_DBR_Insert_New_Sub_Cert' after the insert I've added the following line:
```
select 'dbr.report','sp_DBR_SubContractor_Certs','inPlot_ID=[inPlot_ID]';
```
I was hoping this line would reload the 'sp_DBR_SubContractor_Certs' report with the Plot_ID and now show the existing list of certs with new one included. I just get a blank page! Any thoughts on why the above is not working?

However I would like to learn how to do it via the callback function.

Cheers and again many thanks

Jake
ajdjackson, Member

Hi

Sorted the 'blank page' issue by targeting the insert report in a predefined div using dbr.embed_object :)

Jake

Reply

You must log in to post.