Query if a Client uses a Form

[TGDonovan]

In Lacerte, you can build a filter based on whether or not her return has a form. Is there a way to do that through the SDK?

thank you

[Steffan]

You can query table [DATA6-Display Info] and [DATA7-Form Info]. They have all the same information as you're able to add via filter or a column in Lacerte.

For DATA6 you can use the references at the paths such as "C:\Lacerte\23tax\ind\usdbdef.i23" to find out what each column means, but for DATA7 I haven't found a reference for what values mean what, so I've found all mine via trial and error.

If you let me know what form you're trying to query I might have already figured it out.

Best,

Steffan

[TGDonovan]

Thank you so much. First, I appreciate you and everyone for commenting this week that they were broken, too, it makes me feel better that I wasn't doing something wrong.

Second, thanks for responding here. I've never played with Data7 and will. I'm looking for an individual that used form 8606.

[Steffan]

No problem at all, happy to help! 

I have a tool for trial-and-error finding DATA7 fields. Create two clients, make the form appear in one, and compare the DATA7 tables... You're looking for C7_39 for Form 8606 (2023).

I must warn you though, DATA7 has NOT been consistent between years for me. Lacerte changes things up from year to year and you can't trust that C7_39 will still be Form 8606 in 2024, and it might not be that in 2022, etc.

Best,

Steffan

[TGDonovan]

Thanks I'm trying 2022, and it is C7_38 it appears, but I have a client that has an 8606, but that field doesn't have a 1. I'm still testing, but it sure looks like C7_38 is 8606. When I try and build a filter in Lacerte I am having the same problem.

[Steffan]

Ah, then the column is just bugged. This has been happening for as long as I have been working with Lacerte. The DATA6 and DATA7 tables will always match what Lacerte shows in-product. If the column isn't showing in Lacerte, but they DO have Form 8606, try to unlock the return and go to Client > Update Client Database.

That should update the column in Lacerte and when you query via the API.

Don't know why it happens. Tried to contact Lacerte about it in the past, but didn't get it fixed. Sometimes the columns just don't get updated when the return is calculated.

[TGDonovan]

Thank you for the reminders. I've been using Lacerte for quite a while and it is amazing what one can forget. I unlocked all clients, ran an updated client database, relocked, and then ran my filters and found more clients, and all that I expected.

To find out what column a form number is (now I remember), I create a filter in Lacerte with a distinctive name, use the form number I'm looking for, exit Lacerte, and then open the CTS###.IW3 (or equivalent) file where ### is your workstation number (which shows up on the bottom of the system information screen when you press F10). Search for the filter you just created (search twice to get to the actual definition of the filter), and you will see the C7_## that the filter is using. 

[DatabaseRobert]

It is not that the column is buggy, and TGDonovan has the correct track: the issue is that if Lacerte adds a form, they are likely adding it IN THE MIDDLE OF where other forms already exist.  So <the form that USED TO BE tracked in column '23'> is now shifted to the right, into column '24', and the NEW form just added is recorded in column 23.
  YES, this means that EVERYTHING downstream from 'column 23' is now incorrect.
  YES, this means that you have clients (who *did* have the previous 'form 23') recorded as having <whatever this new form is>, because it is recorded in 'column 23'.
  NO, if you re-process (including just going to Alt+F[orms view]) a Locked return, NOTHING WILL UPDATE (because 'locked').
  So YES, you have to do as TGDonovan did and UNlock all of your files, and re-process your entire data directory, to take into account the new form.
  (If you have a USE for having files locked, I highly recommend making a copy of your data directory [call it like "\FormMetrics\" or some such], and figuring out your form usage from there instead of your primary, 'live' data.)
  NO, none of this lasts through the time when Lacerte updates THE NEXT FORM, because that may well be now in "column 7" (so, even before this one that was added), and then the next time, "column 12" (in between them) before finally adding one down in "column 58".
  YES, you then have to re-duplicate your data directory and re-process your entire client list, yet again, for each of those releases.
  NO, this is not in fact an intelligent way to run a railroad.

.

As others have said, data table 6 is for the numbers themselves (what appears on the two-year comparison Tax Summary, which is fundamentally "most of the numbers from the 1040") and data table 7 is for the forms.  See above for "issues with dealing with forms".

For figuring out the forms...  look at "x:\##tax\option##\USforms.?W#" (where "?" is the module letter, and "#" is the terminal digit of the year, and "##" is the last TWO digits of the year); adjust for your own installation drive & pathing.
 - The file has the forms STORED in, I believe, the order in which a paper return is to be constructed.  (The small bold-face "Attachment Sequence Number" that the IRS uses in the top right corner.)
 - Each form has its variable number (left side), and its form referent (right side).  "60168=Form 5405"

 * After I solved this once to make my database show them to me in the correct order, I stopped thinking about it.  I may be mis-remembering how this works, because it was such a PITA to work with.

 - You need to sort them ALPHABETICALLY by variable number.  Note that "61" comes AFTER "60168" just displayed above, because "60 which happens to be followed by 168" is less than "61", alphabetically.
 - You need to FIND the one to use, by its column position.  YES, this means that "some of the first twenty that you see" may very well NOT BE IN THE FIRST TWENTY COLUMNS YOU DEAL WITH, because they sort (alphabetically by variable name) out to beyond what you can see right away.

So I am looking at my local machine current install (I may or may not have pulled all updates down from the Hosted environment yet), and I see that I am on the top-line version number (variable 0000) is 44.0125.  As we can see, "Form 1040" is #1661, and "1040-NR" is 19; "1040-X" is #588.
  The sorting order will be "1661, then 19, then 588."  If we add "Schedule A (Dis Std)"/238981 and "Schedule D"/2186 to the mix, they sort like this:
 1661
 19
 2186
 238981
 588
(Remember to sort ALPHABETICALLY.)

This means that "10429", which is "Form 8834", is actually THE FIRST FORM recorded in the data table.  1040 doesn't happen until 16th.

(While you are looking at these variables, I also highly recommend search-and-replace removing the word "Form " [including the space at the end], so that you get JUST the form # itself.
 * NOTE * that when you do that, you need to remember NOT TO CHANGE THE ACTUAL USFORMS FILE ITSELF; highlight/copy/paste your list of forms & variables, and work with them externally!)

[TGDonovan]

I thank you for this information. I looked at it really quickly, and I was unable to sort it in an order that would tell me any valid information, but I just did it very quickly. I made a copy of the file you referenced, replaced the = with a comma, imported it to Excel, and created a column that appended zeros so it would sort appropriately, but I'm not getting numbers that I believe to be correct. Most likely, I'm doing something wrong and will play with this later. To have to do some tax returns. Again I appreciate the information.

Tom

[DatabaseRobert]

I would not append 0s (That just turns "16" into "000016", and you still have things sorting in numerical order.)

What you need to do is:
 - replace "=" with "tab" (will put the forms into their own column);
 - bring your two text columns over into a column to the RIGHT of "A";
 - copy the left-side column (the Lacerte variable) AND THE COLUMN TO ITS LEFT back into your clipboard over to your text file;
 - replace "tab" with tab' (tab followed by an apostrophe, forcing that cell to be "TEXT");
 - re-copy those columns back over to your spreadsheet.

You now have your blank column that you skipped when pasting in the first time, and you have TEXT ENTRIES for the variables, and then you have the form names.

Now when you sort, you should get them in alphabetical order, not numerical.



Robert