Help Recreating a Table Originally Written for MS Access

SeanT — Wed, 17 May 2023 17:26:47 GMT

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;

topic Help Recreating a Table Originally Written for MS Access in ArcGIS Dashboards Questions

Help Recreating a Table Originally Written for MS Access