ReportSQL - Census Tract Numbers

Census Tracts are often referenced by 11 digit numbers. LegalServer often has words, spaces, periods, etc in the census tract name (and only has the field for the census tract when GIS is enabled. To get it to show as an 11 digit number use the following:

Add a Custom Text Formula Field from the Address subtable. Preferably from the Person -> Primary Home Address -> Address subtable.

(
  SELECT
    lpad(split_part(sub.val, '-', 2), 5, '0')
    || lpad(
         split_part(substring(split_part(sub.val, '-', 1) FROM 'Census Tract\s+([0-9.]+)'), '.', 1),
         4, '0'
       )
    || rpad(
         COALESCE(
           NULLIF(split_part(substring(split_part(sub.val, '-', 1) FROM 'Census Tract\s+([0-9.]+)'), '.', 2), ''),
           '0'
         ),
         2, '0'
       )
  FROM (
    SELECT public.lookup_census_tract.name || '-' || public.lookup_county.fips AS val
    FROM address
    LEFT JOIN public.lookup_census_tract ON address.census_tract_id = public.lookup_census_tract.id::int
    LEFT JOIN public.lookup_county ON address.county::int = public.lookup_county.id
    WHERE address.id = %tableAlias%.id
  ) sub
)