Project Document Control & Submittal Management System (PDCS) v1.0.0 Enterprise

Step 1 - Load your RFI log(s)

These are the same workbooks everyone has already agreed on. New rows are appended underneath your existing data - nothing is renamed, reformatted, or removed. Add as many packages as you like: each file you add joins the list rather than replacing it, so you can pick them up from different folders one at a time. The tool reads each file's package code (C1, B1, B2, C2 …) from the RFI numbers inside it, so the packages never overlap.

Pre-flight check (read-only - what the tool found in your log)

Step 2 - Drop RFI PDFs (optional - skip for a dashboard-only refresh)

Same contractor template (Request for Inspection). Only page 1 is read - attachments are ignored. Each PDF is automatically matched against your log as a new RFI, a new revision, or an already-logged status check.

This step is for submissions. A new RFI, or a new revision of one, has not been decided yet, so it is written UNDER REVIEW — decisions come back through Step 2b, from the signed copy. If a PDF you drop here really did arrive already stamped, set its status on the card below.

Step 2b - Update a status without a PDF (optional)

Dropping a stamped PDF whose RFI and revision are already in the log is the normal way to record a decision — the tool marks it ALREADY LOGGED and changes only the Status and Approval Date on the existing row, never adding a row. Use this panel for the same in-place update when the decision arrived without a PDF: by email, on a transmittal, or at a site meeting.

Read a folder of signed / returned PDFs (bulk status update — adds no rows)

Point this at the folder of stamped copies. Each PDF is matched to its RFI, the decision is read off the form, and the approval date and reviewer comment are lifted where the PDF carries them. Everything lands in the queue below for you to confirm — no row is ever added by this route, only Status and Approval Date on rows that already exist.

Easiest: open the folder in Explorer, select the PDFs (Ctrl+A) and drag them onto the box below — or use Choose files. The pick a folder route works too, but Windows’ folder dialog lists only sub-folders, so a folder of PDFs looks empty and says “No items match your search”. That is normal: stay in the folder you want and press Upload / Select folder.

Or set one status by hand (no PDF at all)

Step 3 - Review extracted fields

Step 4 - Save into your template

RFIs whose status was not updated (so you can finish them by hand)

Files ready (nothing downloads on its own — choose what you want)

Package comparison (every loaded package, whatever the filter)

Review Decisions & Turnaround Duration by Signatory

Weekly detail (ending on data date)

Repeated approvals & data-quality flags

RFI Number Structure & Coding Guide

The tool automatically decomposes RFI numbers into their engineering components to route them to the right discipline sheet, package, and location. It supports all standard formats:

Accepted Discipline Codes & Aliases:

• Civil: C, CIV, CIVIL, STR, STRUCTURAL → Routes to Civil - RFIs
• Electrical: E, ELE, ELEC, ELECT, ELECTRICAL → Routes to Electrical - RFIs
• Mechanical: M, MECH, MECHANICAL, PLUMB, HVAC, PIPING → Routes to Mechanical - RFIs
• ICT / Telecom: ICT, IT, TEL, TELECOM → Routes to ICT- RFIs
• HSE / Safety: HSE, SAFETY, ENV → Routes to HSE

Package & Location Intelligence:

• Packages: If you load a single log file (e.g. your main project log), all dropped RFIs route directly to it, regardless of whether the prefix is C1, P1, or omitted.
• Locations / Sites: The location segment can be any station code, room, or chainage (e.g. PS1, PS2, HPT1, SHAFT-3, KM14). If omitted in the number, the tool extracts it from the form text.

What gets extracted automatically

RFI Number, Revision, Discipline (from the RFI number, not the form's DISCIPLINE field - see note below), Area/Site, BOQ No, TOD No, Submittal Date, Inspection Date & Time, Item Description, Document Ref, Contact Person and Mobile.

How the tool matches each PDF to your log (the three cases)

Before writing anything, the tool indexes every RFI already in your workbook and sorts each dropped PDF into one of three cases, shown as a coloured badge on its card:

NEW RFI - the number isn't in the log at all. It's added as a new row at the bottom of the correct sheet, continuing the Sr.# numbering.

NEW REVISION - the RFI exists but this revision doesn't. On the light discipline/site sheets the new revision is inserted directly below that RFI's latest existing revision, so all revisions stay grouped for tracking; on the two heavy rollup sheets ("Civil - RFIs", "Electrical - RFIs") it's appended at the bottom with a cross-reference note instead (see below).

ALREADY LOGGED - this exact RFI + revision is already there. No new row is added; instead the tool updates only the Status (and, if you provide it, the Engineer Approval Date) on the existing row, and only if you actually change it. Everything else on that row is left exactly as-is. This is what stops duplicate rows.

Recording a decision - with or without a PDF

The everyday way to record an engineer's decision is to drop the returned, stamped PDF. Because its RFI number and revision are already in the log, it is classified ALREADY LOGGED and the tool changes only the Status and Engineer Approval Date on the existing row - it never adds a row for it.

When a decision arrives without a PDF - by email, on a transmittal, or at a site meeting - use Step 2b. Search for the RFI number, set the status and approval date, and queue it. Queued changes use exactly the same in-place update path as a stamped PDF, and show up in the preview and the save report the same way.

Status - auto-guessed from the stamp, confirmed by you before it is written

The engineer's decision (Accepted / Accepted with comment / Not Accepted) is a hand-marked checkbox with no text behind it, so the tool renders the stamp area and measures the ink to guess which box is marked, then pre-fills the status dropdown and shows its confidence. The mapping used is: Accepted → ACCEPT, Accepted with comment → B-Status, Not Accepted → REJECT.

A guess is never written on its own. Until you confirm it - by picking a value in the dropdown or pressing "Confirm this status" on the card - the status cell is left exactly as it was, and the number of unconfirmed guesses is shown above the Save button. A low-confidence reading (where the marked box barely stands out from the printed box outlines) is reported but not even pre-filled, so it cannot be written by simply not looking.

The status is written in the spelling that sheet already uses. This matters: your sheets count code-B with COUNTIF(J:J,"B-Status ") - with a trailing space - and Excel's COUNTIF is case-insensitive but not space-insensitive, so writing a tidier "B-Status" would produce a row the workbook's own summary silently does not count.

Dates and times - any common format

Dates are read as year-first (2026-09-02, 20260902), day + month name (02-Sep-26, 2 September 2026, 2nd Sep 26), month name + day (Sep 2, 2026), or all-numeric (02-09-26, day-first, auto-corrected when the second field can only be a day) - with any of - / . or spaces as separators. Impossible dates such as 31 February are rejected rather than rolled into March. Times are read as 09:00 AM, 9:00am, 9.00 A.M., 3;45 PM, 10 00 AM, 10AM, 14:30, 1430 or 1430 hrs. Nothing here is specific to one contractor's form.

The pre-flight check

Every time you load the log, the tool reports - read-only, before anything is added - the RFI numbers that don't parse, duplicate rows on the same sheet, the same RFI carrying different statuses on different sheets, status values it cannot classify, formulas containing #REF!, summary blocks that have labels but no COUNTIF behind them, and how many rows each sheet can still take before it would reach its summary block. These are findings for you to act on in the source data; the tool changes none of them.

Protecting the summary blocks

Several sheets keep a small COUNTIF status summary a few blank rows below the data - Civil has only 10 free rows before it, Mechanical 7, ICT 16. If a batch is bigger than that gap, the tool pushes the summary block down by exactly the shortfall and reflows every reference to it, rather than writing RFI data over live formulas (which would also break the Total sheet, since that reads those exact cells).

Version 8.0 — Inbuilt Standard Template & Reviewer KPIs

v8.0 introduces an inbuilt option to download the official Standard RFI Log Template (.xlsx) directly from Step 1. Any user can download this template, populate their project data, and immediately generate the full dashboard, analytics, and management workbooks.

A new Signed / Reviewed By column is included in Column K (and detected anywhere by header name). When filled, it unlocks international standard Reviewer / Signatory KPIs (FIDIC / CMAA / AIA): total reviews completed, average turnaround time (days), SLA compliance %, approval vs rejection rates, and active review backlog.

The parser features full fault tolerance: rows with missing reviewer names, missing inspection times, or non-standard RFI numbering formats are processed safely without dropping data.

Why revisions are appended (not inserted) on Civil & Electrical

The "Civil - RFIs" sheet carries about 9,100 conditional-formatting rules (the status colour-coding) and Electrical about 790, each with relative formulas scattered across fragmented ranges. Excel re-points all of those automatically when you insert a row; a browser tool editing the raw file cannot guarantee it re-points every one correctly, and a wrong shift would silently break the colouring across ~15,000 rows. So on those two sheets a new revision is appended with a "see row X" note both ways, keeping your colour-coding untouched; every other sheet (few or no such rules) gets a true insert-directly-below with full reference reflow that is verified not to disturb formulas, filters, print areas or the cross-sheet Total references.

Dashboard-only mode

You don't need any PDFs to see the dashboard. Load just your log and press the button (it reads "Generate / refresh dashboard only") - the tool builds the overall + weekly picture from your existing data and writes the refreshed "RFI Dashboard" sheet into the workbook, changing none of your RFI rows.

Why "Discipline" ignores the form's own DISCIPLINE field

On every ICT sample, the form prints "DISCIPLINE: ELECTRICAL" - a quirk of the contractor's template. The tool instead reads the discipline code embedded in the RFI number itself (the segment before the sequence number, e.g. the "ICT" in RFI-C1-PS1-ICT-0016), which was correct on all 41 sample RFIs checked.

How saving works without breaking your template

A plain "read the workbook, add a row, write it back out" via a JS spreadsheet library was tested against your file and confirmed to strip the two logo images on every sheet, every dropdown list (Status, Site, Location), and the print settings - while inflating the file from 2.7MB to over 20MB. This tool avoids that entirely: it opens your .xlsx as the zip archive it actually is and edits only the text of the one worksheet being appended to. Every other part of the file - images, dropdowns, print settings, other sheets, formulas, defined names - is carried through byte-for-byte, untouched.

New columns (Discipline, BOQ No, Submittal Date, Days Open, Breached SLA, State, etc.) are added starting at column Q, well clear of your template's own hidden dropdown-source columns in L:O. "Breached SLA" and "State" used to be a single "Overdue" column that said Yes/No for an open RFI but Closed late/No for a closed one - so "No" meant two different things and there was no way to filter for an RFI that breached the SLA but has since closed. They are now two columns that each answer one question.

By default these columns are filled only on rows the tool writes. Tick Back-fill the derived columns on existing rows in Step 4 to fill them for every RFI already in the log, so the columns can be used to analyse the whole project rather than just today's batch. It reads what is already in the sheet (inspection date, approval date, status, sheet name) and writes only into columns Q onward.

Before you save: preview, and keep a backup

Preview changes lists every sheet, row and column a save would touch, and the current value it would replace, without writing anything. Save a backup first (on by default) downloads an untouched, timestamped copy of the file you loaded before any write happens.

After a save the tool reloads the file it just wrote and clears the reviewed batch, so you can drop another set of PDFs immediately. Without that, a second save would re-apply the first batch to the original bytes and classify the new batch against a stale index - which is how duplicate rows get created.

Saving in place vs. downloading a copy

If you open this file by double-clicking it, the page runs from file:// and no browser will let it write back to disk - so Save always produces an updated copy in your Downloads folder which you then put over your working file. The tool detects this and says so in Step 4. Direct in-place saving needs the tool to be served from http://localhost.

To check a save before you trust it, run verify_rfi_save.py (in this folder) against the original and the updated file. It confirms the logos, print settings, every conditional-formatting rule and dropdown, and the COUNTIF summary blocks all survived, and that no duplicate rows or numbering reversals were introduced.

Where new rows land

New RFIs are written right after the last real entry already in that sheet - or right after the header row if the sheet has none yet - never after a stretch of empty formatted rows further down, however far those extend. If your template already has blank pre-formatted rows waiting there (some do, with the Sr.# column already pre-numbered), the tool fills those in place, keeping their existing formatting and dropdown eligibility; it only creates brand-new rows once it runs out of those. Sr.# always continues from whatever numbering already exists in that sheet - real or pre-filled - and never regresses.

The "RFI Dashboard" sheet

Every Save also refreshes a sheet named "RFI Dashboard" inside your workbook - KPI tiles, a status/discipline breakdown, and a weekly submitted-vs-closed trend, backed by real native Excel charts (not pictures). It's fully regenerated from your combined log on every Save, so don't hand-edit it - your edits would be overwritten next time. Every revision of an RFI still counts as one RFI here, exactly like the Dashboard tab in this tool.

One assumption worth knowing about

This tool is built to work unmodified across your other packages - it detects sheet names and target-sheet matching from the RFI number itself, nothing about a specific site/area code is hardcoded. The one place a specific contractor's PDF layout is assumed is the Contact Person/Mobile line and a company-name line stripped out of the Item Description - both keyed to the contractor name printed on the sample forms. If another package's RFI PDFs come from a different subcontractor with different wording there, those two fields (only) may need re-tuning; everything else reads from generic field labels.