Knowledgebase: Data & Table
[QODBC-Desktop] Troubleshooting - How to find Estimate to Invoice conversion time
Posted by Jack - QODBC Support on 18 August 2017 01:10 PM

Troubleshooting - How to find Estimate to Invoice conversion time

Problem Description:

I am using MS Excel access QuickBooks Data through QODBC. I have Estimate & some of the Estimates are converted to QuickBooks. I want to know how many Estimates are converted to Invoice & what is conversion time.

I need Estimate RefNumber, Date, Customer, Value, Invoice RefNumber, Date, Value, Days to Convert

How Can I find Estimate to Invoice conversion time?

Solution:

Please click here to download the Sample Excel file.

You can find Estimate to Invoice conversion time by following below steps.

Please start Microsoft Excel & open new Workbook. An Empty sheet would open and you shall be on "Home" tab:

Please select the "Data" tab and click "From Other Sources->From Microsoft Query"

It will take some time to get the DSN list. And then in the "Choose Data Source" Window, please select a DSN to continue. (Here I choose the default data source "QuickBooks Data" with a sample company file open. And check Use the Query Wizard to create/edit queries.). :

Please Note: If you have 64-bit MS Office, then you need to use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote"

The first time use MS Excel to access QuickBooks, you will be asked to grant access permission for QODBC:

Select "Yes, always" option, and then click "Continue" button, in the next window, click "Done" button to finish authorizing.

After you grant permission for QODBC, you will be guided by Query Wizard. In Choose Columns window, Please click "Cancel" to write the query in Microsoft Query.

Please click "Yes" to write the query in Microsoft Query.

In Add Tables windows list of tables available, Please click "Close" to Open MS Query editor.

Click "SQL" button to write SQL Statement.

We will use below query for finding Estimate to Invoice conversion time & paste it in "SQL Statement" box and click "OK".

SELECT Estimate.RefNumber as "Estimate RefNo",Estimate.TxnDate as EstimateDate, Estimate.TotalAmount as EstimateValue , Estimate.CustomerRefFullName as CustomerName, InvoiceLinkedTxn.TxnDate as InvoiceDate, InvoiceLinkedTxn.RefNumber as "Invoice RefNo", InvoiceLinkedTxn.AppliedAmount as InvoiceValue, (InvoiceLinkedTxn.TxnDate - Estimate.TxnDate) As ConversionDay FROM Estimate Left outer JOIN InvoiceLinkedTxn ON InvoiceLinkedTxn.LinkedTxnTxnID = Estimate.TxnID

Please click "OK" to continue execution of the query.

The result is available in the Microsoft Query editor window.

Please select "File" >> "Return Data to Microsoft Excel" to export the result to Excel.

 

Please select "Existing Worksheet" and click "OK" to export the result to Excel.

 

The result is available in Excel sheet. As you see column alias is not working.

 

We will add alias by editing query by clicking "Data" & "Connection" button.

 

Please select the query & click "Properties..." button.

 

Please navigate to the "Definition" tab & paste below query in the "Command Text" box and click "OK".

 

SELECT Estimate.RefNumber as "Estimate RefNo",Estimate.TxnDate as EstimateDate, Estimate.TotalAmount as EstimateValue , Estimate.CustomerRefFullName as CustomerName, InvoiceLinkedTxn.TxnDate as InvoiceDate, InvoiceLinkedTxn.RefNumber as "Invoice RefNo", InvoiceLinkedTxn.AppliedAmount as InvoiceValue, (InvoiceLinkedTxn.TxnDate - Estimate.TxnDate) As ConversionDay FROM Estimate Left outer JOIN InvoiceLinkedTxn ON InvoiceLinkedTxn.LinkedTxnTxnID = Estimate.TxnID

Please click "Close" to close workbook connection window.

The result is available in Excel sheet with column alias.

 

Note: Case When statement is not working MS Query. Alternative of Case When statement is IIF statement, But IIF statements is not supported in QODBC.

Also, Refer:

Troubleshooting - Cannot use alias in MS Query

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).