Home API resources Rollup fields

FAC API rollup tutorial

The FAC API follows the historical data format distributed by Census with a few exceptions. During the transition, we attempted to provide consistency for our partners as they migrate between system while streamlining the data wherever possible. This tutorial will look at some of the fields we no longer provide and how those fields can be recreated using the data already in the FAC API.

About this code

The code for this tutorial can be found in the fac-api-examples repository. These resources would not be possible without help from HHS, who provided feedback and documentation regarding many of these rollup fields. If you see space for improvement, please reach out via our help desk.

Rollups covered

Example: ALN (previously CFDA)

The ALN, or Assistance Listing number, is a two-part identifier with the shape 12.345. Sometimes it includes letters (12.RD), or even letters and numbers (12.U01). Historically, it is sometimes even messier. The FAC does not distribute the ALN. It distributes the agency prefix (the first part of the code) and the program number (the second part of the code).

The FAC collects two fields related to the ALN: the federal_agency_prefix and federal_award_extension. Both of these fields are required as part of the federal awards workbook and are distributed via the federal_awards endpoint.

To generate a single ALN, combine federal_agency_prefix and federal_award_extension with a . in the middle.

Code example output

If you run the sample code provided above, you should get:

2023-06-GSAFAC-0000000002 ['84.027', '84.027', '84.173', 
    '84.173', '84.010', '84.367', '84.424', '84.424', 
    '84.425', '84.425', '84.425', '84.425', '84.425', 
    '84.425', '84.425', '10.553', '10.555', '10.575', 
    '10.649', '10.555']
2023-06-GSAFAC-0000000688 ['14.155', '14.195', '14.018', '14.228']
2022-12-GSAFAC-0000001061 ['16.557', '16.524', '16.841', '16.526', 
    '93.497', '16.575']

Example: Given a report ID, calculate COGOVER

Every audit has an agency who is either cognizant or has oversight. The FAC populates one of two fields in every record: cognizant_agency or oversight_agency. Only one of the two fields will ever be populated.

Census included a field called cog_over that would be set to C if the audit had an agency that was cognizant, and O if the audit had an agency who had oversight.

Code example output

If you run the sample code provided above, you should get:

2023-06-GSAFAC-0000000002 O
2023-06-GSAFAC-0000000688 O
2022-12-GSAFAC-0000001061 O

Example: Are there questioned costs?

  • Rolled up Census field and table: QCOSTS
  • Criteria: If any of the auditee's findings have QCOSTS = Y (QCOSTS from Findings Table)
  • Output: Then QCOSTS in General table = Y, otherwise QCOSTS = N
  • Census data dictionary description: Indicate whether or not the audit disclosed any known questioned costs.
  • FAC API example code: https://github.com/GSA-TTS/fac-api-examples/tree/main/rollups/03-qcosts

The QCOSTS rollup did a bit more work. In the original table, this field looked at all questioned costs fields and if any of them were yes, then this field was yes.

IF the audit has any findings 
    AND one of the questioned cost fields are `true`
THEN this is true
ELSE false

To compute this using the new FAC data, you need to check each of those questioned costs fields.

Code example output

If you run the sample code provided above, you should get the following output. From the first 30 audits that come back, three of them have findings.

2023-01-GSAFAC-0000000854 N
2023-06-GSAFAC-0000000688 N
2022-12-GSAFAC-0000001061 N
2023-06-GSAFAC-0000000733 N
2023-06-GSAFAC-0000001206 N
2023-05-GSAFAC-0000001215 N
2021-06-GSAFAC-0000000998 N
2023-06-GSAFAC-0000002250 N
2022-12-GSAFAC-0000000861 N
2023-06-GSAFAC-0000000779 N
2023-06-GSAFAC-0000000800 N
2023-04-GSAFAC-0000000495 N
2023-02-GSAFAC-0000000773 N
2023-03-GSAFAC-0000000812 Y
2023-06-GSAFAC-0000000127 N
2023-06-GSAFAC-0000000050 N
2023-06-GSAFAC-0000000198 N
...

Example: Are there current year findings?

The CYFINDINGS field rolled up an audit's findings and, if any were found, reported True. To recreate the CYFINDINGS field, query the /findings endpoint for a given report ID. If there are findings, it is True. If there are no findings, it is False.

Code example output

If you run the sample code provided above, you should get the following output. From the first 5 audits that come back, none have findings.

2023-01-GSAFAC-0000000854 N
2023-06-GSAFAC-0000000688 N
2022-12-GSAFAC-0000001061 N
2023-06-GSAFAC-0000000733 N
2023-06-GSAFAC-0000001206 N

Example: Major program report type?

  • Rolled up Census field and table: TYPEREPORT_MP
  • Criteria: Combines all values (U, D, A, S) entered in the TYPEREPORT_MP field from CFDA
  • Output: "U" if all TYPEREPORT_MP = U, overwise all non-U values listed once
  • Census data dictionary description: Type of report issued on the major program compliance
  • FAC API example code: https://github.com/GSA-TTS/fac-api-examples/tree/main/rollups/05-typereportmp

The logic for this rollup is set-based.

"U" if all TYPEREPORT_MP = U or the non-"U" values listed once

In Python, we've unpacked this in the example code as follows:

    types = set()
    for art in json:
        if art["audit_report_type"] != "":
            types.add(art["audit_report_type"])
    if types == {"U"}:
        return "U"
    elif "U" not in types: 
        return set_to_string(types)
    else:
        return "ERR"

For each row in federal_awards, look at the report type and add it to a set. If the end result is that the entirety of the set is {"U"}, return "U". Otherwise, collapse the set to a single string (e.g. "AD"). Include the "ERR" case, but do not expect it to be possible to reach.

Code example output

If you run the sample code provided above, you should get:

2023-06-GSAFAC-0000000198 U
2022-12-GSAFAC-0000000165 U
2023-06-GSAFAC-0000000339 U
2023-06-GSAFAC-0000001644 U
2023-06-GSAFAC-0000000967 U

Example: Material weakness

  • Rolled up Census field and table: MATERIALWEAKNESS_MP
  • Criteria: If any of the auditee’s findings have MATERIALWEAKNESS = Y
  • Output: Then MATERIAL WEAKNESS_MP = Y
  • Census data dictionary description: Indicate whether any reportable condition/signficant deficiency was disclosed as a material weakness for a major program in the Schedule of Findings and Questioned Costs
  • FAC API example code: https://github.com/GSA-TTS/fac-api-examples/tree/main/rollups/06-materialweakness

If any of the auditee’s findings' MATERIALWEAKNESS fields equal a certain value (Y, for example), then MATERIAL WEAKNESS_MP equals that value.

If any findings have MATERIALWEAKNESS = Y
Then MATERIALWEAKNESS_MP = Y

The FAC stores this value in the general table under the key is_internal_control_material_weakness_disclosed. Note that the FAC stores yes values as "Yes", and no values as "No". The example code maps these back to Y/N to demonstrate mapping values to the old-style.

Code example output

If you run the sample code provided above, you should get:

2023-01-GSAFAC-0000000854 N
2023-06-GSAFAC-0000000688 N
2022-12-GSAFAC-0000001061 N
2023-06-GSAFAC-0000000733 N
2023-06-GSAFAC-0000001206 N
2023-05-GSAFAC-0000001215 N
2021-06-GSAFAC-0000000998 N
2023-06-GSAFAC-0000002250 N
2022-12-GSAFAC-0000000861 N
2023-06-GSAFAC-0000000779 N
2023-06-GSAFAC-0000000800 N
2023-04-GSAFAC-0000000495 N
2023-02-GSAFAC-0000000773 N
2023-03-GSAFAC-0000000812 Y
2023-06-GSAFAC-0000000127 N
2023-06-GSAFAC-0000000050 N
2023-06-GSAFAC-0000000198 N
2022-12-GSAFAC-0000000165 N
2023-06-GSAFAC-0000000339 N
2023-06-GSAFAC-0000001644 N

Example: Reportable condition

  • Rolled up Census field and table: REPORTABLECONDITION_MP
  • Criteria: If any of the auditee’s findings have SIGNIFICANTDEFICIENCY = Y
  • Output: Then the REPORTABLE CONDITION_MP = Y
  • Census data dictionary description: Whether or not the audit disclosed a reportable condition/significant deficiency for any major program in the Schedule of Findings and Questioned Costs
  • FAC API example code: https://github.com/GSA-TTS/fac-api-examples/tree/main/rollups/07-reportable

This is almost identical in construction to the previous example.

Code example output

If you run the sample code provided above, you should get:

2023-01-GSAFAC-0000000854 N
2023-06-GSAFAC-0000000688 N
2022-12-GSAFAC-0000001061 N
2023-06-GSAFAC-0000000733 N
2023-06-GSAFAC-0000001206 N
2023-05-GSAFAC-0000001215 N
2021-06-GSAFAC-0000000998 N
2023-06-GSAFAC-0000002250 N
2022-12-GSAFAC-0000000861 N
2023-06-GSAFAC-0000000779 N
2023-06-GSAFAC-0000000800 N
2023-04-GSAFAC-0000000495 N
2023-02-GSAFAC-0000000773 N
2023-03-GSAFAC-0000000812 Y
2023-06-GSAFAC-0000000127 N
2023-06-GSAFAC-0000000050 N
2023-06-GSAFAC-0000000198 N
2022-12-GSAFAC-0000000165 N
2023-06-GSAFAC-0000000339 N
2023-06-GSAFAC-0000001644 N

Example: Previous year findings?

This involves looking at the array of values in agencies_with_prior_findings in general, splitting on the string (and stripping any spaces for good measure) and asking whether or not any agencies had findings.

In the Python example provided, you can do this by converting the list to a set, then deciding if the set is non-empty.

    # If there is any number other than 00 listed
    # Then PYSCHEDULE = Y
    set_of_agencies = set()
    for find in json:
        numbers = list(filter(lambda n: n != "00", 
                         [n.strip() for n in find[AWPF].split(',')]
                         ))
        set_of_agencies.update(numbers)
    # An empty set is "false" in Python
    return bool(set_of_agencies)

Code example output

If you run the sample code provided above, you should get:

2023-01-GSAFAC-0000000854 N
2023-06-GSAFAC-0000000688 N
2022-12-GSAFAC-0000001061 N
2023-06-GSAFAC-0000000733 N
2023-06-GSAFAC-0000001206 N
2023-05-GSAFAC-0000001215 N
2021-06-GSAFAC-0000000998 N
2023-06-GSAFAC-0000002250 N
2022-12-GSAFAC-0000000861 N
2023-06-GSAFAC-0000000779 Y
2023-06-GSAFAC-0000000800 N
2023-04-GSAFAC-0000000495 N
...