Custom Fields (Or what my options are)

[TravisJack]

I'd like to sub-categorize Clients:

• Provide several custom fields. E.g. A unique ID relating to third-party software.
• Allow users to add/edit the field from the Lacerte front end.
• Gather the data at the back end using the ODBC connection.

I found a previous post that asked for something similar, but none of the replies answer that question (3).

I've been toying around with the Notes feature with some success but this would involve searching for/parsing the Note file, and leaves me wondering if this is the best approach.

I understand that I can achieve this outside of Lacerte but I want to investigate my options within Lacerte.

[DatabaseRobert · Accepted solution]

No real surprise that I had replies in both of the threads you linked to.  (I am nothing if not opinionated.)

NOTES:
 - You can *read* the files externally.  (They are renamed .ZIP files, what I do is make a copy/expand them with WinRAR in a temp directory/parse each of the resulting files/move on to the next client.)
 - NO changes that you might make to those files will actually appear in Lacerte.  (So the ONLY thing you can do to affect them, is to make all of your "writes" in Lacerte.)  My recollection is several years old, but I believe trying to make changes/save new files into what Lacerte creates corrupts/destroys everything visible inside the tax program on the Notes page for that module/client #.

CUSTOM FILTER:
 - Has a hard character limitation.  If you want to record more than one piece of information, you may need to use delimiters or consistent spacing.  ("code1 | code2 | code3", or "First 8 characters for ODBC, blank, next 5 characters for <something>, blank, next 6 characters for <other>, blank...")
 - Does NOT appear in any built-in Lacerte printout, nor can it be added to them.  (Such as "on the General Information" page, or on a Database Report/client list.)
 - CAN be added to your Client List, and of course F3/Filtered upon.
 - CAN be Exported by selecting on Client List.

MULTI-SORT:
 - Doesn't really work.  Lacerte programs up to TY2018 all sorted by the left-most column.  TY2019 sorts by column header clicks (up/down), just like you see in Outlook email.  But pretty much, inside Lacerte, you can sort by one (1) column.

.

With that said...  we actually already use the Custom Filter field for external info.  (We have a custom object in SalesForce, "TR-######", for "Tax Returns".  We have a similar object for "TP-#####"/Tax Projects.)
 - We instruct all of our Preparers to BLANK that field out whenever they create a copy of a return (such as for ESTimates, TST/test situations, X/amended returns, or whatever); this ensures that a given TR exists ONLY for the tracked return that is actually being filed/billed for.
 - If the return becomes additional work for some reason--Amended, CP2000--then a TP/tax project gets created in SalesForce, so the copy in Lacerte gets the TR# replaced with the TP#.
 * I very much *HIGHLY* recommend adding a "LacerteNum" field into your SalesForce (or other external software) object build.  Preferably force it to be unique.  This is simply the 8-character client number in Lacerte.  You will ALSO need a "module" indicator, because there could easily be a SMITH123 in INDividual, GIFt, Fiduciary, and PARtnership, and the ODBC connection needs to know which one to follow.

You will need to do all of the cross-pollination work yourself, externally.  (Because Lacerte does not reach out to anything.)  So you will need to take your known TR-# in SalesForce, use the LacerteModule and LacerteNum fields to find the correct record in Lacerte, "do ODBC magic" to communicate to Lacerte about it (adding detail over the ODBC, or reading each W-2 on page10/Wages, or whatever), and so on.

Robert

[TravisJack]

You're a legend.

The Custom Filter field is exactly what I was looking for... with the delimiter/spacing workaround(s), that is.

The ability to view this in the Client List is a bonus - which I overlooked. And I've added our version of "LacerteNum" to our third-party application (formatted as ClientNo_TaxType_Year).

So I am now able to use the Custom Filter field as you mentioned, and if needs be; append to the Custom Filter string that an additional Client Note exists, and read/update the Note file if necessary (somewhat risky, I know).
Side note: the Note file that I am referring to is in the \xDATA\NOTES\ClientNo.IG9 file - in this example, opened in Windows 10 - Notepad.exe:

Thanks very much!

[DatabaseRobert]

Thanks for the kind words!  I'm glad you arrived at something that will work for you.
 * Make sure you have your people input those delimited items/spacing formats in EXACTLY the same way.

I am intrigued by your screenshot, because I do not even *HAVE* any files with the extension ".?G#" in my Notes subdirectory.
  ("?" == the module letter, so "i" for IND, "f" for FID, "n" for GIF, and so on.)
  ("#" == the terminal year digit.)

The file you showed there should be in the individual module.  I have iC9, ii9, a very small number iJ9, iK9, iN9, io9 [letter "oh", not digit "zero], iU9, iX9, precisely one iY9, and iZ9.  No iG9 files for me at all.

The data that you showed (including one line of X= and Y=) makes me think that it is talking about window location, or some such, and NOT the actual content of the 'Notes' page in whichever module you want to be in.

At this point I do not recall WHICH of the files inside of the Notes directory contains the actual documents, but it shouldn't take you long to find it.
  (Pick a client that you know has text in Notes.  Copy all of his files to your test location.  Change the extension of one file at a time to ".ZIP".  Look into the ZIP file for things like "Statement" or "Worksheet" (which are your choices when inputting a Note).  Open one and read it to confirm that it is the one you think.  Now you know the extension you need to parse for each client.)

Robert