Level 3

Database Maintenance

Forum|Forum|4 years ago
December 21, 2021
1 reply
0 views

I have decades of employer/bank/broker data in my current database and I would like to clean that up. Particularly I would like to somehow get a list of all the employers currently in my 700 client database so I can get rid of all the old/obsolete employer data using Table Editor. (I currently have thousands of entries.)

Are there any utilities out there that I can run against my data to determine the "real" employers?

This topic has been closed for replies.

D

DatabaseRobert

Level 6

Are you referring to the saved lists that quick-fill one or more fields for you, on various pages? (10/wages, 11/interest, 12/dividend, 13/pensions, for example.)

Those files are stored in text files inside of a ZIP file, but it is not *named* a zip file. Adjust for your own drive letter, and the year that you are looking for, and they are in:
J:\20 Install\20tax\Option20\TABLES.IW0
(File extension is "?W#", where ? is the letter of the module [i for Individual, F for Fiduciary, C for Corporate, N for Gift, and so on] and # is the final digit of the year. This file, "iW0", is for the individual module of "some year ending in 0", in this case 2020.)

That will show you "which ones you have saved."

.

However, in order to see "which ones are you actually using," the answer is not nearly so clean-cut.

To do that, the best way is to establish your ODBC connection to the data directory, and then iteratively open EACH client file and parse out the data to a common file. (Make sure to identify the records with CLIENT #, so that you know what file they came from!) In addition to the fields that the Lacerte detail file uses--input page/series, property #, code, value, description, Client/Spouse/Joint, state Sourcing, and so on--I add three fields for Year, Module, and Client #.

Then you do your "show me all series11/page10/Wages code800/employer name" query against THAT table, and see which ones are actually being used.

Note that you can NOT just "query for employer name" against a detail file, because you will get ONLY the records that match from that one particular file. This is why you first need to extract everything out to the main data table.
For comparison purposes, my extracted data for ~2300 individual client files runs to just under 718k records.

Robert Kirk
LTUGtools

P

pitstaxAuthor

Level 3

Very useful starting point. Thank you very much.

It does turn out that the tables are in TABLES.W0 (for 2020) rather than .IW0. I copied out TABLES.W0 to another location, renamed the extension as .ZIP, and that allowed me to extract the files in which I found, among dozens of files, @I_14_800 which was a text file containing all the employer data within my client universe and @F_12_800 which was a text file containing the bank/broker data.

Is there a document directory for these table files?

Now on to trying to parse out my current data!

D

DatabaseRobert

Level 6

"Now on to trying to parse out my current data!" -- PitsTax

First establish an ODBC connection to your data.
https://proconnect.intuit.com/community/permissions/help/lacerte-software-developer-s-kit/00/102313
https://developer.intuit.com/app/developer/lacerte-sdk/docs/lacerte-get-started

If you--or any other readers--are open to installing a new program, you can probably find a freeware version of "Paradox Runtime v10" after a brief websearch. I have a custom set of tools written as Paradox scripts, one of which is "extract tax detail".

You can reach me at "ParadoxForLacerte" at "Yahoo.com", if you are interested.

I initially wrote most of the utilities in the early 2010s, as a result of reading the Yahoo!Group "LTUG" (Lacerte Tax User Group) complaints of several years about "this should be changed or updated"; for TY2008 I was the notional 'recording secretary' keeping that list, which we as a group then forwarded on to the Intuit staffer who was the primary contact on the group.

At the time, Lacerte was just using plain DBase files, without encryption, so between TY1999 and TY2017 I had free access to read/write the data tables. Many of the things that we were asking for on the usergroup, could be done externally with access to the data tables, so I did.

With the advent of the changed tables in TY2018 and going forward, several of my utilities are outdated/no longer work, and I honestly have not bothered to go back and update them to work with the ODBC connection instead.

However, pulling the detail out works just fine.

Robert Kirk
LTUGtools

Sign up

Login with SSO

Let's get you signed in

Login with SSO

Scanning file for viruses.

This file cannot be downloaded