is there a way to query first transaction date within a QuickBooks file with the QuickBooks SDK?

Question

Is there a way to identify the first transaction date within a QuickBooks file through the QuickBooks SDK?

Answer 1

There's not a specific way to do this using the SDK, but you could come up with some code that would look at each month at a time to determine this information. The earliest date you can use in QuickBooks is 01/01/1901. I would probably just do a MetaDataOnly query of an ITransactionQueryRq to check if there are transactions, then do a NoMetaData query to get the actual dates of the transactions in the month. You may even want to narrow down to the week and/or day to limit the time a NoMetaData query will take.

int count = 0;
DateTime fromDate = new DateTime(1901, 01, 01);
DateTime toDate = new DateTime(1901, 01, 31);

while(count <= 0)
{
    ITransactionQuery query = MsgSetRequest.AppendTransactionQueryRq();
    query.metaData.SetValue(MetaDataOnly);
    ITransactionQuery query = MsgRequest.AppendTransactionQueryRq();
    query.metaData.SetValue(ENmetaData.mdMetaDataOnly);
    query.ORTransactionQuery.TransactionFilter.TransactionDateRangeFilter.ORTransactionDateRangeFilter.TxnDateRange.FromTxnDate.SetValue(fromDate);
    query.ORTransactionQuery.TransactionFilter.TransactionDateRangeFilter.ORTransactionDateRangeFilter.TxnDateRange.ToTxnDate.SetValue(toDate);

    IResponse response = SessionManager.DoRequests(MsgRequest).ResponseList.GetAt(0);

    if (response.StatusCode == 0)
    {
        count = response.retCount;
    }
    else if (response.StatusCode == 1)
    {
        fromDate = fromDate.AddMonths(1);
        toDate = new DateTime(fromDate.Year, fromDate.Month, DateTime.DaysInMonth(fromDate.Year, fromDate.Month));
    }
    else throw new Exception("TransactionQuery returned error: " + response.StatusMessage);
}

// fromDate and toDate contain the first month that has transactions.

Answer 2

The code has a few issues, below is a VB,net version. The important difference is the addition of msgSetRequest.ClearRequests() between every API call.

This code also runs much faster and gets within a day of the first transaction. I thought about starting with 10 year jumps to make it even faster.

Public Sub GetFirstTransactionDate(ByRef QuickBooksSessionManager As IQBSessionManager, ByRef fromDate As Date, ByRef toDate As Date)
    Dim msgSetRequest As IMsgSetRequest = QuickBooksSessionManager.CreateMsgSetRequest("US", 13, 0)

    Dim count As Integer = 0
    fromDate = New Date(1901, 1, 1)
    toDate = New Date(1901, 12, 31)
    ' make sure we do not have any old requests still defined
    msgSetRequest.ClearRequests()

    ' set the OnError attribute to continueOnError
    msgSetRequest.Attributes.OnError = ENRqOnError.roeContinue

    While count <= 0
        msgSetRequest.ClearRequests()
        Dim query As ITransactionQuery = msgSetRequest.AppendTransactionQueryRq()
        query.metaData.SetValue(ENmetaData.mdMetaDataOnly)
        query.ORTransactionQuery.TransactionFilter.TransactionDateRangeFilter.ORTransactionDateRangeFilter.TxnDateRange.FromTxnDate.SetValue(fromDate)
        query.ORTransactionQuery.TransactionFilter.TransactionDateRangeFilter.ORTransactionDateRangeFilter.TxnDateRange.ToTxnDate.SetValue(toDate)

        Dim response As IResponse = QuickBooksSessionManager.DoRequests(msgSetRequest).ResponseList.GetAt(0)

        If response.StatusCode = 0 Then
            count = response.retCount
        ElseIf response.StatusCode = 1 Then
            fromDate = fromDate.AddYears(1)
            toDate = toDate.AddYears(1)
        Else
            Throw New Exception("TransactionQuery returned error: " + response.StatusMessage)
        End If
    End While
    toDate = New DateTime(fromDate.Year, fromDate.Month, DateTime.DaysInMonth(fromDate.Year, fromDate.Month))
    count = 0
    While count <= 0
        msgSetRequest.ClearRequests()
        Dim query As ITransactionQuery = msgSetRequest.AppendTransactionQueryRq()
        query.metaData.SetValue(ENmetaData.mdMetaDataOnly)
        query.ORTransactionQuery.TransactionFilter.TransactionDateRangeFilter.ORTransactionDateRangeFilter.TxnDateRange.FromTxnDate.SetValue(fromDate)
        query.ORTransactionQuery.TransactionFilter.TransactionDateRangeFilter.ORTransactionDateRangeFilter.TxnDateRange.ToTxnDate.SetValue(toDate)

        Dim response As IResponse = QuickBooksSessionManager.DoRequests(msgSetRequest).ResponseList.GetAt(0)

        If response.StatusCode = 0 Then
            count = response.retCount
        ElseIf response.StatusCode = 1 Then
            fromDate = fromDate.AddMonths(1)
            toDate = New DateTime(fromDate.Year, fromDate.Month, DateTime.DaysInMonth(fromDate.Year, fromDate.Month))
        Else
            Throw New Exception("TransactionQuery returned error: " + response.StatusMessage)
        End If
    End While
    toDate = fromDate.AddDays(1)
    count = 0
    While count <= 0
        msgSetRequest.ClearRequests()
        Dim query As ITransactionQuery = msgSetRequest.AppendTransactionQueryRq()
        query.metaData.SetValue(ENmetaData.mdMetaDataOnly)
        query.ORTransactionQuery.TransactionFilter.TransactionDateRangeFilter.ORTransactionDateRangeFilter.TxnDateRange.FromTxnDate.SetValue(fromDate)
        query.ORTransactionQuery.TransactionFilter.TransactionDateRangeFilter.ORTransactionDateRangeFilter.TxnDateRange.ToTxnDate.SetValue(toDate)

        Dim response As IResponse = QuickBooksSessionManager.DoRequests(msgSetRequest).ResponseList.GetAt(0)

        If response.StatusCode = 0 Then
            count = response.retCount
        ElseIf response.StatusCode = 1 Then
            fromDate = fromDate.AddDays(1)
            toDate = fromDate.AddDays(1)
        Else
            Throw New Exception("TransactionQuery returned error: " + response.StatusMessage)
        End If
    End While
    ' fromDate and toDate contain the first month that has transactions.
End Sub