Database Maintenance

[pitstax]

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?

[DatabaseRobert]

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

[pitstax]

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!

[DatabaseRobert]

"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

[pitstax]

I downloaded and installed the Paradox V10 run-time.  If you have any scripts that you can pass along that I could utilize to pull data from an individual detail file that would be great.

It doesn't look like the run-time version allows much more than the ability to run scripts (not create them) so hopefully I can use whatever scripts you are willing to pass along and edit them off-line and then invoke them using the run-time system.

[pitstax]

Also, I understand the concept of iteratively scanning through all the ID0 files in the DETAIL folder, but what program did you use to do that?  I don't know how to read an ID0 file.  I can low-level program to an extent but I'm flying blind regarding how to open and parse the ID0 files other than some continuous character stream input.

Thanks for whatever you can provide in this regard.

[DatabaseRobert]

Yes, you are on the right track.  The ID0 files are what you need.  And you have correctly assessed the Runtime version as "only use, not create."  That is why they give it away for free...

You will need two things more:
 - setting up an ODBC connection (so that Paradox will have permission to actually work with the Lacerte files), and
 - something to run in Paradox to *do* that work for you.
 (Note that I refer to Paradox because that is what I use and am comfortable with.  I am certain that others who use Excel or Access or other spreadsheet/database software can achieve the same thing.  Someone else posting on the developer topics has been using Python, for example.)

.

I am out of the office today/Wednesday, so I will reach out to you tomorrow/Thursday 23 Dec 2021 in order to work with you on putting that in place.

Since there is no direct communication here, if you want to touch base with me at "ParadoxForLacerte" at "Yahoo.com", we can trade files directly.



Robert

[pitstax]

Maybe we can revisit next week.  I'm at pitstax_aol.com in Castro Valley CA.  Still unsure what to use to open an .ID# file directly.  I've renamed the extension to try to head fake the process with zip, paradox, access, excel and word with no luck.  I can write a BASICA low level program I guess that can input a character stream and try to parse that but it seems like a waste of time - someone must know how to open an .ID# outside of the Lacerte application.

Merry Christmas!

[DatabaseRobert]

Up until TY2017, yes: it was an unencrypted binary file, and an acquaintance had already cracked it with a VBA script that I took advantage of for many years.

TY2018, they completely re-wrote everything, so now you really do need to have the ODBC connection in place.

Once you do, then you open your program of choice--I use Paradox, I know others use Access, one guy up in NYC that I was trading emails with at one point has a VERY nicely blinged out Excel spreadsheet that largely duplicates (and expands upon) the old Lacerte tracking (showing return progress across ALL modules), someone here on these boards is using Python, others Javascript--and then you will be able to <do things> with the file.
  In order to do so, though, you still have to input a valid Lacerte license at the login.

So, no.  I think at this point, "ODBC" and then proceed is going to be your answer.



Robert