Knowledgebase: QODBC
[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 an Estimate for Invoice conversion time

Problem Description:

I am using MS Excel to 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, and Days to Convert

How Can I find an Estimate for Invoice conversion time?

Solution:

Please click here to download the Sample Excel file.

You can find the Estimate for Invoice conversion time in the below steps.

Please start Microsoft Excel & open a new Workbook. An Empty sheet will open, and you shall be on the "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 to Use the Query Wizard to create/edit queries.). :

Please Note: If you have 64-bit MS Office, you must 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 the "Yes, always" option, and click the "Continue" button in the next window. Click the "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 the MS Query editor.

Click the "SQL" button to write SQL Statement.

We will use the below query to find the Estimate to Invoice conversion time & paste it into the "SQL Statement" box and click "OK."

SELECT Estimate.RefNumber as "Estimate RefNo," Estimate.TxnDate as EstimateDate, Estimate. the total amount 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 the 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 an Excel sheet. As you see column alias is not working.

 

We will add an alias by editing the query by clicking the "Data" & "Connection" buttons.

 

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

 

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

 

SELECT Estimate.RefNumber as "Estimate RefNo," Estimate.TxnDate as EstimateDate, Estimate. the total amount 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 the workbook connection window.

The result is available in an Excel sheet with a column alias.

 

Note: Case When a statement is not working MS Query. Alternative to Case When information is an IIF statement, But IIF statements are 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).