SQL to JOIN EFStatus to Client Info
With the latest SDK, some changes in 21 Lacerte tax have affected certain columns and they have been removed from the SDK access. In particular, the C1_G9 and C1_G10 columns contain some EF status info. (see p35 of the SDK Instructions for all removed fields. I'd recommend reading it all over this year as there are some changes highlighted, including new requirements for the connection string, among other things)
There are actually 2 new tables that have been around for a few years - EFilings and EFStatuses that contain all of the EF info, but it's a little harder to parse if you just want the most recent Fed EF Status.
I took it as a challenge to write it into one SQL query, and here you have it:
SELECT
ci.C1_0,
ci.C1_2,
ci.C1_3,
ci.C1_4,
ci.C1_5,
ci.C1_6,
b.EFStatus,
b.EFStatusDate
FROM
[DATA1-Client Info] as ci
LEFT OUTER JOIN (
SELECT
ef.*,
efsd.*
FROM
[EFilings] AS ef
LEFT OUTER JOIN (
SELECT
efs.*
FROM
[EFStatuses] AS efs
INNER JOIN (
SELECT
EFilingId,
MAX(EFStatusDate) AS date
FROM
[EFStatuses]
GROUP BY
EFilingId
) AS b ON efs.EFilingId = b.EFilingId
AND efs.EFStatusDate = b.date
) AS efsd ON (ef.Id = efsd.EFilingId)
WHERE
(ef.EFID LIKE 'ind.us')
) AS b ON (ci.C1_0 = b.ClientId)ci.C1_? etc are all of the columns you want to include from the DATA1-Client Info database, so adjust as needed. b.EFStatus is the most recent EF Status for an "ind.us" return for that particular client, and b.EFStatusDate is the date that particular status happened. You could probably remove the WHERE (ef.EFID LIKE 'ind.us') if you wanted to show a line for each filing a client has instead of just the federal.
Hope this helps someone.
