Hello,

Right now I am inserting estimatelines, and its working great.  I just need to insert a blank line will all null values after each estimateline I insert.  What would my "blank line" insert statement look like?

Thanks.

The blank estimate lines need to be added when creating the Estimate using a SQL Statement like this:

INSERT INTO "EstimateLine" ("EstimateLineDesc", "FQSaveToCache") VALUES ('', 1)

You cannot have any column values whatsoever, other than caching the actual transaction line. If the blank line is the last line in the Estimate, then FQSaveToCache should be a 0 (not 1).

See: How do I create Estimates? for more information.

What about entering blank lines into existing invoices?

I first execute a statement like this to insert the estimate line with the detail (with existing TxnID):

        strSQL = "INSERT INTO EstimateLine (" & _
                 "TxnID, " & _
                 "EstimateLineItemRefListID, " & _
                 "CustomFieldEstimateLineOther1, " & _
                 "EstimateLineDesc, " & _
                 "EstimateLineRate, " & _
                 "EstimateLineAmount) " & _
                 "VALUES (" & _
                 "'" & strTxnID & "'," & _
                 "'" & strEstimateLineItemRefListID & "'," & _
                 "'" & strInvoiceNumber & " " & "Domestic/Export" & "', " & _
                 "'" & strDate & "'," & _
                 "" & dblTotalCredits & ", " & _
                 "" & dblTotalCredits & ")"

I then tried to execute this statement to insert a blank line:

        strSQL = "INSERT INTO EstimateLine (" & _
                 "TxnID, " & _
                 "EstimateLineDesc, " & _
                 "FQSaveToCache) " & _
                 "VALUES (" & _
                 "'" & strTxnID & "'," & _
                 "'', " & _
                 "1)"

But now, it looks like the estimate lines from the first insert are somehow inserted twice (they are be duplicated).

Why?

You don't use FQSaveToCache when adding lines to an existing transaction. Try:

        strSQL = "INSERT INTO EstimateLine (" & _
                 "TxnID, " & _
                 "EstimateLineDesc, " & _
                 "VALUES (" & _
                 "'" & strTxnID & "'," & _
                 "'', " & _
                 ")"

Ok, for existing estimates, I tried:

       strSQL = "INSERT INTO EstimateLine (" & _
                 "TxnID, " & _
                 "EstimateLineItemRefListID, " & _
                 "CustomFieldEstimateLineOther1, " & _
                 "EstimateLineDesc, " & _
                 "EstimateLineRate, " & _
                 "EstimateLineAmount) " & _
                 "VALUES (" & _
                 "'" & strTxnID & "'," & _
                 "'" & strEstimateLineItemRefListID & "'," & _
                 "'" & strInvoiceNumber & " " & "Domestic/Export" & "', " & _
                 "'" & strDate & "'," & _
                 "" & dblTotalCredits & ", " & _
                 "" & dblTotalCredits & ")"

and then I immediate try and insert the blank line:

     strSQL = "INSERT INTO EstimateLine (" & _
                 "TxnID, " & _
                 "EstimateLineDesc) " & _
                 "VALUES (" & _
                 "'" & strTxnID & "'," & _
                 "'')"

The first insert works, but it does not insert the blank line.  Could it be some kind of setting I'm missing?

As for new estimates, I'm also having problems:

I insert the new estimate line:

        strSQL = "INSERT INTO EstimateLine (" & _
                 "EstimateLineItemRefListID, " & _
                 "CustomFieldEstimateLineOther1, " & _
                 "EstimateLineDesc, " & _
                 "EstimateLineRate, " & _
                 "EstimateLineAmount, " & _
                 "FQSaveToCache) " & _
                 "VALUES (" & _
                 "'" & strEstimateLineItemRefListID & "'," & _
                 "'" & strInvoiceNumber & " " & "Domestic/Export" & "', " & _
                 "'" & strDate & "'," & _
                 "" & dblTotalCredits & ", " & _
                 "" & dblTotalCredits & ", " & _
                 "1)"

and then immediately I insert the blank line:

     strSQL = "INSERT INTO EstimateLine (" & _
               "EstimateLineDesc, " & _
                "FQSaveToCache) " & _
                "VALUES (" & _
                "'', " & _
                "1)"

but this time, it inserts duplicates of the estimate line I just inserted, and does not insert blank lines.

Do you think in a later version of QODBC that for existing estimates, inserting a blank line will be available?

Thanks...