# Dinner invitation mail — what is already done, and what is left for you 4th IMHF, Evening Reception and Dinner, 30 October 2026, USI Lawns. 28 guests: Council, CDS, the three Chiefs, the Governor, the Defence Secretary. Everything below the line marked **YOUR PART** is a Power Automate job and has to be done in the browser — there is no connector on this box that can create a flow. --- ## What the match found | | | |---|---| | Guests on the dinner list | **28** (the sheet has 28 rows, not 29) | | Already on the 9,424 member roll | **17** — they keep the link already sitting in their inbox | | Not on the roll | **11** — new tokens, no membership number | The 17 get **the same link** the main campaign sent them. No second token was minted: a new one would silently kill the link already in their mail. One correction was applied, as agreed: the sheet spells Air Mshl Rajesh Kumar's address `ajeshkumar1111@gmail.com`; the roll has `rajeshkumar1111@gmail.com` (LM/73022). Treated as the same person, so he keeps his existing link and the dinner card goes to the address that works. The 11 non-members are filed under the category **Dinner Invitee**, not `USI Member`, so the member counts on the approvals page and the dashboard stay honest. The 17 who ARE members keep `USI Member` — moving them would drop them out of the dashboard's "USI Members" approvals button and into the public bucket. --- ## What the link now does | Who opens it | What they see | |---|---| | An ordinary member (9,396 of them) | **Exactly what they see today.** Nothing changed. | | A dinner guest who has not registered | The normal form, then a final step that asks the **Dinner Yes/No first**, then the attendance preference. | | A dinner guest who already registered | **Only** the dinner question. No second registration, no second QR pass. | | A guest who already replied | Their answer shown back to them, read-only. | Guests with no membership number never see the "Membership No." box. Both answers land in MySQL immediately and are written onto the SharePoint row by the existing 5-minute cron, matched by Token — **the registration flow is not touched at all.** That is deliberate: a Parse JSON that stops matching does not fail loudly, it loses registrations, and that mail is the only record a registration ever existed. --- ## Files | File | What it is | |---|---| | `out/USI_IMHF_Dinner_Invite.xlsx` | The recipient sheet. Table name **`Dinner`**. Columns: `Salutation \| Name \| Email \| Dinner Timing \| link \| Sent At` | | `Word File/Dinner-Mail-Merge.html` | The mail body, with four `{{PLACEHOLDER}}` markers | | `Word File/Dinner-Mail-PREVIEW.html` | The same thing filled in for the Governor — open this in a browser first | | `out/dinner-manifest.json` | Which token belongs to whom. Keep it: re-running the importer reads it so nobody is re-issued a link | | `out/dinner-manifest-test.json` | Test rows (batch 0). Kept out of every dinner count on the dashboard, so a test link can be clicked right through without moving the 28 | --- # YOUR PART ### 1. Add two columns in SharePoint (2 minutes) Lists → **Events Registration** → + Add column → **Single line of text**: | Internal name | Display name | |---|---| | `DinnerAttendance` | Dinner | | `DinnerTiming` | Dinner Time | The internal name has to match exactly. Graph refuses to create these with an app-only token (403) — same as `MembershipNo` and `Panels`, which you added by hand too. Until they exist the cron keeps approving members as normal and just **holds** the dinner replies; it writes them on the first run after the columns appear. Nothing is lost. Check it took: ``` php /home/admin/tools/dinner-make-columns.php # should print DinnerAttendance=YES DinnerTiming=YES php /home/admin/tools/invite-autoapprove.php --dry-run ``` ### 2. Upload the workbook Put `out/USI_IMHF_Dinner_Invite.xlsx` on **events@usiofindia.org**'s OneDrive, in the same folder as `USI_IMHF_Invite_Batch1.xlsx`. The cron finds it by filename — nothing to configure. It already has an empty `Sent At` column, so the flow has somewhere to write from the very first run. ### 3. Copy the flow **Test Bulk Mail 2** → ⋯ → **Save As** → name it `Dinner Invite 2026`. Then change four things and nothing else: 1. **List rows present in a table** → File: `USI_IMHF_Dinner_Invite.xlsx`, Table: `Dinner` 2. **Filter** — keep the resume guard exactly as it is: empty `Sent At` is what makes a restart skip the guests already mailed. Excel escapes dots but **not** spaces, so the key stays `Sent At`. 3. **Send an email** → To: the `Email` column. It holds `a@x;b@y` for the guests with two addresses; Office 365 takes the `;` list as-is. 4. **Update a row** → writes `Sent At` back onto the `Dinner` table. 5. **Repoint the control-sheet steps at `Dinner-Update-Mail.xlsx` / `Table1`** — do NOT leave them on `Live_Bulk-MailUpdate.xlsx`. That workbook is the member campaign's run log; both flows write a control row whose ID starts at 1, so sharing it would have the dinner run collide head-on with the member campaign's run ids and fork the history the dashboard reads. That is exactly what produced the phantom "run #8". `Dinner-Update-Mail.xlsx` is already uploaded, with the same columns (`ID | Date | Subject | Total Mails | Sent | Pending | Status`) and the subject `[START-BULK-DINNER]`. The cron mirrors it into its own `dinner_mail_runs` table and the dashboard shows it as **Dinner run #n** above the guest list. ### 5. The body — read this before you paste Open the **Send an email** body, switch to code view ``, and paste `Dinner-Mail-Merge.html` in one go. **Do not Ctrl+A over the body in design view and paste** — that is what killed the RSVP token chip last time. Then replace these four markers with the matching dynamic content: | Marker | Replace with | Where it shows | |---|---|---| | `{{SALUTATION}}` | `Salutation` | `Dear Hon'ble Governor,` | | `{{NAME}}` | `Name` | the guest's name on the dinner card | | `{{DINNER_TIMING}}` | `Dinner Timing` | `…30 October 2026 at **1945** hrs` — date fixed, time per guest | | `{{LINK}}` | `link` | the `href` of **Confirm Your Attendance** in paragraph 5 | `{{LINK}}` sits **inside** an `href="…"`. Put the chip between the quotes and leave the quotes alone. Search the finished body for `{{` before you save. If anything is left, that guest gets a mail with `{{NAME}}` printed on the invitation card. ### 6. Send Trigger it **once**. One `[START-BULK-DINNER]` mail = one run. Five taps = five runs = five copies to every guest — these are the Chiefs and the Governor, so that one really matters here. Watch it on **Bulk Mail Dashboard → Dinner — 30 October 2026**: Attending / Not attending / No reply yet / Dinner card sent, and a line per guest showing their arrival time and whether the reply has reached the sheet yet. --- ## Two things worth your eye first 1. **Open `Dinner-Mail-PREVIEW.html` in a browser.** The card name is 30pt. The original said `Maj Gen Ian Cardozo` (19 characters); the longest on this list is `Lt Gen Syed Ata Hasnain, PVSM, UYSM, AVSM, SM, VSM & Bar (Retd)` — 63 characters, which wraps to two lines. If you want it on one line, say so and I will drop the font size. 2. **The main campaign is live right now** (run #9, ~1,540 of 4,709 sent). Six of the 17 members have already had the festival invite. Sending the dinner card now means they get two mails — which is correct, they are two different invitations — but it is your call whether to wait for run #9 to finish. ## If you need to re-run the importer ``` python3 /home/admin/tools/dinner_import.py build # re-reads the sheet, keeps every token mysql -u usiuser -p usi_db < /home/admin/tools/out/dinner.sql python3 /home/admin/tools/dinner_import.py export # rewrites the workbook ``` Safe to repeat. Tokens are minted once and the SQL never writes the token column on a row that already exists.