Viewing Data with Lacerte SDK

[GameFire]

I am a developer working for a small CPA firm. I am trying to write a small C# console app that will help my firm price their returns. Right now the process is manual and time consuming. I've managed to get the Lacerte tax program installed with sample data, but I'm not an accountant and am having a hard time trying to figure out where the data that I need to check for lives.

I was able to set up an ODBC connector to read the data using a query string, but the data doesn't come back in a format that I'm able to interpret. I would appreciate any help you could provide, whether that be a class object, a mapping document, or something that could help set me on the right path.

Thanks!

[Steffan]

Hi GameFire,

I would check out these files as a starting point:

• "C:\Lacerte\24tax\2024.14.10.0\ind\usdbdef.i24"

Where "C:\Lacerte\" is your install path and "24tax" is the year of the program you're working with. Starting with 2024, there is a versioned subfolder here "2024.14.10.0", then the module "ind".

The usdbdef file contains all the database columns and a map to what they mean. The only exception is the DATA7 table, which seems to populate columns as those forms are encountered in the tax program, so there is no single reference to what they mean.

Hope this helps!

Best,

Steffan

[GameFire]

Thanks Steffan,

I had found the usdbdef.i24 file, converted it to as CSV and took a look at it but I think my lack of knowledge on accounting is doing me a disservice. I found a post here earlier pointing to that and giving a list of what each of the columns meant. (I've been trying to find it to link it here, but I haven't been able to) 

The columns I got were as follows

TableNumber

FieldNumber

Description

ShortDescription

FieldType

FieldLength

Column 1

Column 2

Column 3

Column 4

Column 5

Column 6

Column 7

Column 8

Column 9

ToggleType

Column 10

Some important things that I need to be able to query on are

Number of W2/1099R

Number of Sch B

Number of non-zero lines in the Current Year Depreciation Schedule

Are these things that I could find in the usdbdef.i24 file?

[Philip24]

This is a good starting point. 

What we use is the Client Detail table. this lists all the data written in input boxes. We use the code 800 to determine what proforma data exists in a return. You can print a blank input form from the Lacerte UI, which helps map input boxes in Lacerte UI to the backend Series Number and Code number.

Steffan, if you are aware of an easier way to fetch the input box to series/code combos using the config files I would love to hear it. 

[Philip24]

use the client detail table. I query how many prefix are used in the series need (11: w2, 12: interest, 55: k1 1065... and so on) Also if you fetch code number 800 for these, it will tell you the description used

[GameFire]

I'll take a look at this, thanks for the input!

[kamprich]

Another way to find the data you are interested in is to go into Lacerte and set the mode to "Show Codes".   You do this by right clicking in the gray Client Information tab at the top of the screen (see attachment) and Lacerte will show you the codes for all the data.   This combined with the other information in the SDK will help guide you through the tax data.

Regards,

Rick Kamp, EA

[Philip24]

We've used this to build a system that identifies what files are needed, organizes the files when the client provides them, and does some of the data prep for the most common forms. Happy to share more

[Steffan]

GameFire,

For those fields you'll need to query the Detail table for the client. It can be a pain, since you need to query each client individually, but as far as I am aware and a quick search through that definition file, those are not available in the main tables.

As @Philip24 mentioned, and as far as know, the best way to map known values/fields to the detail table is to print the corresponding input sheets. So for the # of W-2s query, I would go to Print > Input Sheets, hit "suppress all", go to Screen 10, Wages and Force all 4 of those pages. Print those, and the pages that print will have the Series at the bottom of the page, and each row will have the Code for that value. You can use these to query against the Detail table. You can also go to Screen 10 in Lacerte and right click the grey bar at the top - like where it says 10 Wages, Salaries, Tips, and hit "show codes" as @kamprich mentioned. This will give you the codes as well. For fields that can have multiple of something, like a W-2, the Prefix column is the ID for that specific item.

I have a tool I use to visualize the data (I maybe can share if you want) - I made a test client with two W-2s to give you an idea of what you would query to find the quantity or if you need more detail, the values (see screenshot attached). Depending on your needs, you might need to also query for the "delete this year" box, as this data may exist in the table but not the return if this field is "1".

Edit: Here's a link to the screenshot, it doesn't seem to be working through this forum: https://ibb.co/BXNV2kJ

Best,

Steffan