U.S. flag

An official website of the United States government

Official websites use .gov
A .gov website belongs to an official government organization in the United States.

Secure .gov websites use HTTPS
A lock ( ) or https:// means you’ve safely connected to the .gov website. Share sensitive information only on official, secure websites.

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?

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?

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

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

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
...