flyingturtle13/detailing_status-check_folder_method

Power BI Reporting for Construction Detailing Model Status

Detailing Model Files in Focus Zone Folders Status Checker

     At its fundamental purpose, this subroutine checks a folder if a file exists and last updated date per column and row header name. It requires a user to input root folder path to where files reside.
     For this application, detailers are to post model files to associated level folders when ready for coordination and appending to Navisworks (NWF) federated model. The subroutine automates checking if the detailing model files exist in the level folders and reports last posting date. The Excel file is then consumed in Power BI as a visual report showing detailing status (Up to what level trade detailers are at and tooltip indicating last posting date).
Note: Information regarding updating the Power BI report data visuals will not be discussed. Refer to https://synoptic.design/ for information about updating report visuals as synoptic panels are used to visualize data.

Getting Started

Environment setup required to implement subroutine

• Repository Items:

  • Subroutine .cls file
  • Associated Excel workbook (.xlsm) implementing subroutine
  • Power BI report template (.pbit) referencing Excel spreadsheet

• IDE:

  • Excel Macros

• Language:

  • VBA (Microsoft Visual Basic)

• Output Type:

  • Class File (.cls)

Subroutine Development

Subroutine features and specs

• User Interface

  • Button (Update Detailing Status) to activate auto update feature
  • Paste root folder path to where files reside when prompted
  • Refresh file in Power BI to update report data
  • Power BI report tooltip indicates date of model file posting

• Subroutine Specifications

  • By default, if file does not exist cell value of level model by trade is "0"and date posting is empty
  • When file is found, cell value of level model by trade is set to "1" and date is copied to another column cell
  • Create folders for levels/focus zones for model file posting
  • Cell values in orange highlighted cells should match folder names where model files to be checked are/to be posted.
  • Revise range if number of levels (row) and disciplines (column) change. (See worfklow structure below)
  • Revise column number for recording file posted date if range is revised.
  • Model file name should use underscore ( _ ) as delimiter.
  • Cell values in blue highlighted cells should match discipline code used in the model file name. (See worfklow structure below)
    • Ex: MD ==> ProjectName_LXX_MD_CompanyCode.nwc, will use MD to match with file name

Workflow Structure Implementing Subroutine

See below for the flow chart and map depicting overall structure and flow of information.

Legend

1. Button to activate auto update.
2. Window prompt for user to paste folder path to Level/Focus Zone folders where model files are posted.
3. Level/Focus Zone folders
   • Folder name is used to map to corresponding row
4. Model files in Level/Focus Zone folders
   • Discipline Code in file naming convention used to map to corresponding column. ( _ ) used as delimiter.
   • The date file is posted is also mapped to another column in the same row.

Installing and Running Application

1. Clone or download project.
   
2. Open FileStatusCheck_Template.xlsm. Check if macros already loaded. If not, import included CLS file.
   
3. Create level folders are created for model files to be posted.
   
4. Ensure level cell values in column B match folder names.
   
5. Revise Discipline Code in row 1 to match that of the model file names based on standardized file naming convention.
   
6. Check subroutine code and update parameters in code as needed.
   
   • Discipline check range if correct

     

   • check if column is correct where model file date posted is stored.

     

7. When setup is complete, click "Update Detailing Status" button to activate.
   
8. When prompted, paste root folder path to where level folders are located.
   
9. Update the report with correct data visual image per https://synoptic.design/.
   
10. Ensure Data Source path is pointing to excel file: FileStatusCheck_Template.xlsm
    
11. Refresh report to update data visuals.
    

Power BI Report

• Mapping Data to Power BI Report

• Power BI Tooltip displays date of model file posted