Help Recreating a Table Originally Written for MS Access

[SeanT]

I am taking advantage of the new dashboard Table element available with 11.1 by recreating a table originally scripted with sql in a MS Access database (not by me). 

I have a set of facilities (unique points in a feature layer) that I need to join imagery flights to from a related, one-to-many table. But what I need assistance with is that the table needs to have columns for the last 10 years from Now, and each facility would have an X in the year column for flight that occurred.

Desired output:

FacilityID	2013	2014	2015
1	X	X
2	X		X

I've got some aspects figured out from this community's amazing knowledge, but how to generate the year columns is escaping me.

//add portal connection
var p = portal('https://portal');
//add site_p as fs to table filtered to major training sites = Y
var fs = Filter(
    FeatureSetByPortalItem(
        p,
        'portalID1',
        15,
        ['joinfield1','namefield2','locationfield3'],
        false
        ),
    "big_site" = 'Y'"
);
//add imagery data table
var rel_fs = FeatureSetByPortalItem(
    p,
    'PortalID2',
    20,
    ['joinfield1','dt1'],
    false
)

// Empty feature array and feat object to populate output
var features = [];
var feat;

// Iterate over features in main layer
for (var f in fs) {

    // grab related records for feature
//     var rel = FeatureSetByRelationshipName(
//         f, 
//         'related_table', 
//         ['joinfield1', 'dt1'], 
//         false
// )

    // create pivot? of years from imagery flight data table related to site_p features
    // for(var r in )

    //add features to output array
    feat = {
        attributes: {
            JoinField: f['joinfield1'],
            Namefield: f['featurename'],
            LocationField: f['locationfield'],
            // loop through years to create columns for last 10 years using a rolling count to keep format as Now -10 
            // Year1-Year10: (year >= Year(Now()) - 10)
            
        }
    }
    Push(features, feat);
};
//create/populate output dictionary
var fs_dict = {
    'fields': [
        {name: 'joinfield1', type: 'esriFieldTypeString'},
        {name: 'Namefield', type: 'esriFieldTypeString'},
        {name: 'locationfield', type: 'esriFieldTypeString'},
    ],
    'geometryType': '',
    'features': features
};

//dashboard indicator has to process results as a FeatureSet
return FeatureSet(Text(fs_dict))


//sql statement from MS Access I'm trying to replicate:
//TRANSFORM IIf(Count([flight_year])>0,"X","") AS Expr1
//SELECT Installation_list.region, Installation_list.state_abbrev, Installation_list.installation_name
//FROM Installation_list INNER JOIN [_Gap Analyses part1] ON Installation_list.instln_id=[_Gap Analyses part1].instln_id
//GROUP BY Installation_list.region, Installation_list.state_abbrev, Installation_list.installation_name
//ORDER BY Installation_list.state_abbrev, Installation_list.installation_name
//PIVOT [_Gap Analyses part1].flight_year;