SQL to JOIN EFStatus to Client Info

[JasonAtIntuit]

With the latest SDK, some changes in 21 Lacerte tax have affected certain columns and they have been removed from the SDK access. In particular, the C1_G9 and C1_G10 columns contain some EF status info. (see p35 of the SDK Instructions for all removed fields. I'd recommend reading it all over this year as there are some changes highlighted, including new requirements for the connection string, among other things)

There are actually 2 new tables that have been around for a few years - EFilings and EFStatuses that contain all of the EF info, but it's a little harder to parse if you just want the most recent Fed EF Status.

I took it as a challenge to write it into one SQL query, and here you have it:

SELECT
  ci.C1_0,
  ci.C1_2,
  ci.C1_3,
  ci.C1_4,
  ci.C1_5,
  ci.C1_6,
  b.EFStatus,
  b.EFStatusDate
FROM
  [DATA1-Client Info] as ci
  LEFT OUTER JOIN (
    SELECT
      ef.*,
      efsd.*
    FROM
      [EFilings] AS ef
      LEFT OUTER JOIN (
        SELECT
          efs.*
        FROM
          [EFStatuses] AS efs
          INNER JOIN (
            SELECT
              EFilingId,
              MAX(EFStatusDate) AS date
            FROM
              [EFStatuses]
            GROUP BY
              EFilingId
          ) AS b ON efs.EFilingId = b.EFilingId
          AND efs.EFStatusDate = b.date
      ) AS efsd ON (ef.Id = efsd.EFilingId)
    WHERE
      (ef.EFID LIKE 'ind.us')
  ) AS b ON (ci.C1_0 = b.ClientId)

ci.C1_? etc are all of the columns you want to include from the DATA1-Client Info database, so adjust as needed. b.EFStatus is the most recent EF Status for an "ind.us" return for that particular client, and b.EFStatusDate is the date that particular status happened. You could probably remove the WHERE (ef.EFID LIKE 'ind.us') if you wanted to show a line for each filing a client has instead of just the federal.

Hope this helps someone.

[qbteachmt]

Thanks. Is there anyway to make the link from here:

https://developer.intuit.com/app/developer/lacerte-sdk/docs/lacerte-get-started

to this community? There is no Lacerte SDK Community in the Developer Community. Or, this one should go there? It just seems schizo.

[MikeCodesStuff]

Just stumbled the SDK and this community after manually doing daily exports for longer than I'd care to admit!

Thanks so much for this labyrinthine query in particular; curious if there are any plans to expose the "eSignature Status" field to the SDK in the future

it is terribly helpful for my clients!

thank you again

Mike

[rkamp]

Hi Mike,

I'm new to the Lacerte SDK Group and saw your post.   I have tried to use the SDK for both ODBC and .Net access, but running into some problems.   I have lots of experience using the Quickbooks toolkits, but since there are no examples in the Lacerte SDK, it is a difficult to get started.    I'm interested in either interface, but I have been trying to use the .Net interface with Visual Studio 2019, with little success using the 32-bit SDK.     Any suggestions and/or small example in either VB .Net or C#?   

Best regards,

Rick Kamp

[MikeCodesStuff]

Yeah Rick

I'm still mostly baffled but have been able to automate exports using python and its pyodbc library which has been incredibly helpful for my tax preparer clients

What are you trying to accomplish?

[rkamp]

Thanks for your reply, Mike.   I'm trying to develop a small tool to read certain fields in the tax return to export to an Excel-based questionnaire.    This questionnaire then gets sent to our tax clients to simplify the gathering of information for tax preparation.    For example, we'll list the W-2 companies from last year's tax return, the bank accounts for Schedule B, etc.  This also makes it easier to see if we are missing any information.

Currently, we cut and paste fields into an Excel template from Lacerte.    Automating the process would save us lots of time.    The .Net interface would be ideal if I could get it working, but the interface looks like it was developed quite a while ago and the libraries don't work with the current version of Visual Studio.

The interface is very simple and just uses a form number and field number.   Have you tried to use their Dll's?

Thanks for any advice.

Best regards,

Rick Kamp

[JasonAtIntuit]

This is a small C# console program I wrote which uses the ODBC connection to query the Employer name for every client in a database and print it out. This could be modified to do something else with the data. I don't have an example using the .Net library but hopefully this helps show how one could use it. You would probably want to add some error catching with a Try ... Catch at minimum.

using System;
using System.Collections.Generic;

namespace lacerte21sdktest2
{
    class Program
    {
        static void Main(string[] args)
        {
            //define data path containing clients
            var dataDir = @"C:\Lacerte\22tax\DEMODATA"; 
            // connection string - ReturnType and TaxYear can be omitted if the DataDir contains only one type and year of data, it should figure it out
            var connectionString = $"Driver=LacerteDSIIDriver; ReturnType=Individual; TaxYear=2022; DataDir={dataDir}";
            
            // create a connection to the database
            using (var connection = new System.Data.Odbc.OdbcConnection(connectionString))
            {
                // first query the [DATA1-Client Info] table to get a list of clients by client number - field C1_0
                var queryString = "SELECT C1_0 FROM [DATA1-Client Info]";
                
                System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(queryString);
                command.Connection = connection;
                connection.Open();

                var clientList = new List();

                // execute the query on the connection and loop through the result
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // add all of the client numbers to a list for later use - a Try Catch would be good here
                        clientList.Add(reader.GetString(reader.GetOrdinal("C1_0")));
                    } 
                }

                // now loop over the list of clients to get the employer name for each - need to check if clientList is empty
                foreach (var client in clientList)
                {
                    // client details is in the [Client-Detail] table for each client, where Client is the client number
                    // W2 employer is Series 11, Code 800 and the value is stored in the Description field
                    var clientQueryString = $"SELECT Description FROM [{client}-Detail] WHERE Series=11 AND Code=800";
                    System.Data.Odbc.OdbcCommand clientCommand = new System.Data.Odbc.OdbcCommand(clientQueryString);
                    clientCommand.Connection = connection;

                    // execute the command and open a reader
                    using (var reader = clientCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            // read all of the results and just print the client number along with the employer name.
                            // here is where you could do something else with this result, store it in a variable, etc.
                            Console.WriteLine($"{client} employer: {reader.GetString(reader.GetOrdinal("Description"))}");
                        }
                    }
                }
            }
            Console.WriteLine("Press any key to exit");
            Console.ReadLine();
        }
    }
}

 This produces this output:

02SAMPLE employer: His Employer
02SAMPLE employer: Her Employer
03SAMPLE employer: His Employer
03SAMPLE employer: Her Employer
05SAMPLE employer: Employer1
06SAMPLE employer: His Employer
06SAMPLE employer: Her Employer
09SAMPLE employer: His Employer
09SAMPLE employer: Her Employer
10SAMPLE employer: Taxpayer New York Employer
10SAMPLE employer: Taxpayer California Employer
10SAMPLE employer: Spouse Both State Employer
11SAMPLE employer: Taxpayer New York Employer
11SAMPLE employer: Taxpayer California Employer
11SAMPLE employer: Spouse Both State Employer
12SAMPLE employer: His Employer
12SAMPLE employer: Her Employer
14SAMPLE employer: Employer Name
16SAMPLE employer: His Employer
16SAMPLE employer: Her Employer
18SAMPLE employer: Employer
21SAMPLE employer: Company
Press any key to exit 

[rkamp]

Hi Jason,

Thanks so much for your assistance!   This is very helpful.

Best regards,

Rick

[rkamp]

Hi Jason,

In trying your program, I seem to have the connection working, but there seems to be something simple I have wrong.   I have added a try/catch block and have shown the error I'm getting:

"ERROR [42S02] [Lacerte][SQLEngine] (31740) Table or view not found: LACERTEDATA..DATA1-Client Info"

I have tried using both the dataDir set to @"N:\Lacerte\21tax\IDATA" and @"N:\Lacerte\21tax\DEMODATA", but am getting the same error.

I have attached a screen shot.    I'm using the 64 bit driver on a 64 bit system.   It's crashing on the "using" statement.

Almost there...

Thanks for your assistance!

Rick

[rkamp]

Hi Jason,

It looks like I'm able to read some from the client detail table (using Excel), but I get this error when I try to access DATA1-Client Info table:

DataSource.Error: The table has no visible columns and cannot be queried.
Details:
DATA1-Client Info

Maybe this is something about 21tax data?

Any help is greatly appreciated, but I'm making some progress 🙂   

Best regards,

Rick Kamp

[JasonAtIntuit]

I'm not sure why you'd get those errors - can you try a different tax year and see if you get the same thing?

You could also try adding these lines above where the ExecuteReader() happens - line 32 for my code - to see if it's able to even get the table names from the database:

 var schema = connection.GetSchema("tables");
foreach (System.Data.DataRow row in schema.Rows)
{
Console.WriteLine(row["TABLE_NAME"]);
}

 Might want to try uninstalling and reinstalling the Lacerte ODBC driver as well.

[rkamp]

Hi Jason,

I uinstalled, rebooted and reinstalled the 64 bit ODBC driver from the 1.0.0.347 SDK.

Your schema code works fine and displays all the tables.

I still was not able to access the DATA1-Client Info table.   I have no trouble accessing any of the client tables.    Also, I get errors when I access EFilings and EFStatuses.   There must be something incompatible on my server.      For now, I can manually map the client numbers by scanning the files in the IDATA directory.    I have attached the error when I access EFStatuses.

Thanks for your assistance, but I need to finish my program in the next few days to create the client questionnaires.

Best regards,

Rick Kamp

[rkamp]

Jason,

Here's the error when accessing EFStatuses

Regards,

Rick

[JasonAtIntuit]

Can you check in the properties of your project that the target framework is .NET Framework 4.6 or greater?

[rkamp]

Hi Jason,

I'm using .Net 4.6.    It seems to be an issue with the ODBC driver.   I'm running WIndows 11, Version 22H2, Visual Studio 2019, Version 16.11.23

I'm able to access the Detail and SubClientStateInfo table for all clients.    When I try to access Detail-SubClientInfo , I get the error:

DataSource.Error: The table has no visible columns and cannot be queried.
Details:
2376-Detail-SubClientInfo

When I try to access EFilings, i get the error:

DataSource.Error: ERROR [HY000] [Lacerte][CLIDSI] (90) Unhandled managed exception caught: System.BadImageFormatException: This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded. (Exception from HRESULT: 0x8013101B) (Stack Trace: at Interop.ClientDataAccess.ITableAccess.MoveToNextRow()
at f.MoveToNextRow()
at Simba.CLIDSI.CLIDSIResultSet.Move(CLIDSIResultSet* , DSIDirection in_direction, Int64 in_offset))

All of this can be duplicated by using Microsoft Excel for Office 365, Version 2301 and accessing the tables via ODBC.

Fortunately, the data I need is in the client Detail table, but it would be nice to access all the tables.

Again, I really appreciate your assistance.

Regards,

Rick Kamp