{"id":1549,"date":"2025-07-28T08:10:00","date_gmt":"2025-07-28T13:10:00","guid":{"rendered":"https:\/\/automatethemundane.com\/index.php\/2025\/07\/28\/copilot-studio-and-ai-builder-better-together-post-2-building-the-core-dataverse-table-relationship-design-and-model-driven-app\/"},"modified":"2025-07-28T08:10:00","modified_gmt":"2025-07-28T13:10:00","slug":"copilot-studio-and-ai-builder-better-together-post-2-building-the-core-dataverse-table-relationship-design-and-model-driven-app","status":"publish","type":"post","link":"https:\/\/automatethemundane.com\/index.php\/2025\/07\/28\/copilot-studio-and-ai-builder-better-together-post-2-building-the-core-dataverse-table-relationship-design-and-model-driven-app\/","title":{"rendered":"Copilot Studio and AI builder – Better together. Post 2 \u2013 Building the Core: Dataverse Table Relationship Design, and Model Driven App"},"content":{"rendered":"\n

Introduction<\/h1>\n\n\n

Before automating anything or designing a user interface, I needed to make sure the data model was solid. Since the Talent Intake System relies on capturing and relating candidates, resumes, and job applications, I started by designing a structure in Dataverse that could support flexible workflows, easy querying, and clean integration with forms and flows.<\/p>\n\n\n

In this post, I\u2019ll walk through the four key tables that make up the foundation of the solution, how they relate to one another, and some of the decisions I made to keep the model lean but extensible.<\/p>\n\n\n

\n\n\n

Core Tables and Relationship Design<\/strong><\/h1>\n\n\n

The solution uses four main Dataverse tables:<\/p>\n\n\n

Table<\/strong><\/th>Purpose<\/strong><\/th><\/tr><\/thead>
Candidate<\/strong><\/td>Stores applicant information like name, email, and contact details.<\/td><\/tr>
Resume<\/strong><\/td>Manages uploaded resume files and links back to the candidate.<\/td><\/tr>
Application<\/strong><\/td>Represents a submitted application tied to a specific job posting.<\/td><\/tr>
Job Posting<\/strong><\/td>Contains open positions visible on the public Power Pages form.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n

🧑‍💼 Candidate<\/h3>\n\n\n
Field Name<\/strong><\/th>Type<\/strong><\/th>Purpose<\/strong><\/th><\/tr><\/thead>
Full Name<\/td>String<\/td>Candidate\u2019s full name.<\/td><\/tr>
Email<\/td>String<\/td>Candidate\u2019s email address (for contact or login).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n
\n\n\n

📄 Resume<\/h3>\n\n\n
Field Name<\/strong><\/th>Type<\/strong><\/th>Purpose<\/strong><\/th><\/tr><\/thead>
Candidate Name<\/td>Lookup (Candidate)<\/td>Links this resume to a candidate.<\/td><\/tr>
Resume Title<\/td>String<\/td>Descriptive title for the resume.<\/td><\/tr>
Resume<\/td>File (Virtual)<\/td>The uploaded resume document.<\/td><\/tr>
Resume_Name<\/td>String<\/td>Internal or uploaded file name.<\/td><\/tr>
Submission Date<\/td>DateTime<\/td>When the resume was submitted (e.g., via portal or form).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n
\n\n\n

📝 Application<\/h3>\n\n\n
Field Name<\/strong><\/th>Type<\/strong><\/th>Purpose<\/strong><\/th><\/tr><\/thead>
Job Title<\/td>Lookup (Job Posting)<\/td>The job that this application is associated with.<\/td><\/tr>
Resume<\/td>Lookup (Resume)<\/td>The resume being submitted for this application.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n
\n\n\n

📢 Job Posting<\/h3>\n\n\n
Field Name<\/strong><\/th>Type<\/strong><\/th>Purpose<\/strong><\/th><\/tr><\/thead>
Job Title<\/td>String<\/td>Name\/title of the open position.<\/td><\/tr>
Department<\/td>String<\/td>Department within the organization.<\/td><\/tr>
Location<\/td>String<\/td>Where the job is based (or remote).<\/td><\/tr>
Security Clearance Requirement<\/td>String<\/td>Any security clearance required for the job.<\/td><\/tr>
Job Listing<\/td>Memo<\/td>Rich text job description body.<\/td><\/tr>
Skills<\/td>Memo<\/td>List of skills required or desired.<\/td><\/tr>
Work Experience<\/td>String<\/td>Required prior work experience.<\/td><\/tr>
Hiring Manager<\/td>Lookup (User)<\/td>Person overseeing the hiring process.<\/td><\/tr>
HR Coordinator<\/td>Lookup (User)<\/td>Person supporting the application process.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n
erDiagram\n    Candidate ||--o{ Resume : "submits"\n    Resume ||--o{ Application : "is used in"\n    JobPosting ||--o{ Application : "is for"\n    JobPosting }o--|| User : "Hiring Manager"\n    JobPosting }o--|| User : "HR Coordinator"\n\n    Candidate {\n        string FullName\n        string Email\n    }\n\n    Resume {\n        string ResumeTitle\n        file ResumeFile\n        string ResumeFileName\n        datetime SubmissionDate\n    }\n\n    JobPosting {\n        string JobTitle\n        string Department\n        string Location\n        string SecurityClearanceRequirement\n        string Skills\n        string WorkExperience\n    }\n\n    Application {\n        string PlaceholderField\n    }\n\n    User {\n        string Name\n    }\n<\/code><\/pre>\n\n\n
 <\/p>\n\n\n
 <\/p>\n\n\n
This setup reflects the natural flow of a typical applicant experience: the candidate enters their details, uploads a resume, and then applies for a specific position.<\/p>\n\n\n
Relationship Design<\/strong><\/h2>\n\n\n
Here\u2019s how the tables are connected in the Talent Intake System:<\/p>\n\n\n
    \n
  • Candidate (1) \u2192 (N) Resume<\/strong>\n
    Each candidate can have multiple resumes over time. This allows for versioning or updates without altering the original candidate record.<\/p>\n<\/li>\n\n\n\n
  • Resume (1) \u2192 (N) Application<\/strong>\n
    A single resume can be used for multiple applications. This supports scenarios where a candidate submits the same resume to different job postings, or where applications are versioned without re-uploading files.<\/p>\n<\/li>\n\n\n\n
  • Application (N) \u2192 (1) Job Posting<\/strong>\n
    Multiple applications can be submitted to the same job posting. This supports both single and recurring job openings.<\/p>\n<\/li>\n<\/ul>\n\n\n
    Why Resume and Application Are Separated<\/h3>\n\n\n
    The separation between Resume<\/strong> and Application<\/strong> is intentional and strategic:<\/p>\n\n\n
      \n
    • Resumability<\/strong>: A candidate might begin an application, revise their resume, or use the same resume across multiple applications.<\/li>\n\n\n\n
    • Auditability<\/strong>: It preserves submission history and allows tracking how many times a resume was submitted and to which postings.<\/li>\n\n\n\n
    • Flexibility<\/strong>: Future enhancements like tagging resumes or attaching multiple documents (e.g., cover letters) can be isolated at the resume level without cluttering application logic.<\/li>\n<\/ul>\n\n\n
      This design keeps the data model normalized and scalable\u2014ideal for a job portal or recruiting process.<\/p>\n\n\n
      Design Considerations<\/strong><\/h3>\n\n\n
      While keeping the schema straightforward, I added a few thoughtful touches to support future enhancements:<\/p>\n\n\n
        \n
      • Optional Fields<\/strong> \u2013 Fields like \u201cResume Status\u201d and \u201cApplication Source\u201d provide hooks for future automation or reporting without overcomplicating the initial build.<\/li>\n\n\n\n
      • Custom File Handling<\/strong> \u2013 Resumes are stored as attachments in the Resume table, not embedded in the Candidate table. This keeps the file logic isolated and easier to manage in flows.<\/li>\n\n\n\n
      • Lean First, Expand Later<\/strong> \u2013 I avoided overengineering at this stage. The goal was to create a flexible, functional structure that could support the initial workflow but grow with future needs like approvals, interview tracking, or feedback.<\/li>\n<\/ul>\n\n\n
        \n\n\n
        Model Driven App<\/h1>\n\n\n
        The Model-Driven App serves as the primary interface for HR professionals to manage job postings and oversee candidate applications. While the app provides full access to create and update postings, most day-to-day interactions\u2014such as conducting interviews or reviewing applicant data\u2014are streamlined through integrated Teams Adaptive Cards and Power BI dashboards. This approach keeps the app focused on administrative setup while shifting operational tasks to more natural, role-specific tools.<\/p>\n\n\n
        App and Form Design<\/h2>\n\n\n
        The MDA is a simple app with the four tables added. \n<\/p>\n\n\n
        \"\"\/<\/figure>\n\n\n
        Job Posting<\/h3>\n\n\n
        The job posting form consists of two tabs. The first is what allows HR professionals to enter in the job details. The second are the prompt outputs of three AI builder prompts, breaking down the job posting for future use. <\/p>\n\n\n
        \"\"\/<\/figure>\n\n\n
        \"\"\/<\/figure>\n\n\n
        Candidates<\/h3>\n\n\n
         The Candidate<\/strong> table uses a straightforward, single-tab form. Since the candidate\u2019s Name<\/strong> and Email<\/strong> are captured at the time of record creation\u2014typically from the public Power Pages form\u2014these fields are set to read-only<\/strong> to preserve data integrity. The only editable field on the form is Phone Number<\/strong>, which allows internal users to add or update contact information if needed.<\/p>\n\n\n
        This approach ensures that once a candidate is created, their core identity remains unchanged, while still allowing HR staff the flexibility to complete missing details or make necessary updates.<\/p>\n\n\n
         <\/p>\n\n\n
        \"\"\/<\/figure>\n\n\n
         <\/p>\n\n\n
        Resumes<\/h3>\n\n\n
        This table and its form are used to connect the Candidate to a Resume. <\/p>\n\n\n
        \"\"\/<\/figure>\n\n\n
        Application<\/h3>\n\n\n
        The Application table and its main form are the core of the MDA. It has four tabs and a Business Process Flow (BPF) to manage the process.<\/p>\n\n\n
        The first tab has all the information about the Resume being submitted, when it was submitted, the resume, and the overall assessment of the applicants resume. <\/p>\n\n\n
        \"\"\/<\/figure>\n\n\n
         \nThe Resume<\/strong> tab captures all evaluations tied to the submitted resume. These fields are automatically populated by Copilot Studio agents as part of the application submission workflow. This approach ensures consistency in how resumes are assessed and minimizes the need for manual data entry by HR staff.<\/p>\n\n\n
        \"\"\/<\/figure>\n\n\n
         The Job Posting<\/strong> tab on the form serves as a snapshot of the job details at the time of submission. This design choice ensures that if the original job posting is later updated or modified, the application retains a historical record of what the position looked like when the candidate applied. This is especially useful for audit scenarios or when reviewing application context after a job has evolved.\n<\/p>\n\n\n
        \"\"\/<\/figure>\n\n\n
        \nThe Assessment<\/strong> tab is where Copilot Studio agents evaluate the resume against the job posting criteria. As part of this automated process, the agent assigns a score from 1 to 5 based on the alignment between the applicant\u2019s qualifications and the job requirements.<\/p>\n\n\n
        \"\"\/<\/figure>\n\n\n
        \nThe Interview<\/strong> tab captures feedback from all stages of the human interview process. It\u2019s organized into three sections\u2014HR<\/strong>, Hiring Manager<\/strong>, and Overall Assessment<\/strong>. The HR and Hiring Manager sections are filled out during their respective interviews, while the Overall Assessment<\/strong> is automatically generated by an agent based on those inputs. This ensures the process remains efficient without losing the value of human insight.\n<\/p>\n\n\n
        \"\"\/<\/figure>\n\n\n
         <\/p>\n\n\n
        \n\n\n
        Lessons Learned<\/strong><\/h2>\n\n\n
        One early lesson was the importance of modeling around the user experience<\/em>, not just the data. For example, separating Resume from Candidate helped reduce complexity when handling file uploads and made it easier to validate submissions on the front end. It also improved flow performance by minimizing the size of the Candidate record payload.<\/p>\n\n\n
        Additionally, designing with Power Pages in mind meant I had to pay close attention to relationships and permissions\u2014especially since the site is anonymous and needed to map submissions without requiring sign-in.<\/p>\n\n\n
        \n\n\n
        Up Next: Power Pages<\/strong><\/h2>\n\n\n
        With the data model in place, the next step was making it accessible to applicants. In the next post, I\u2019ll walk through how I used Power Pages to build a multi-step, user-friendly application form\u2014complete with anonymous access, file upload handling, and clean navigation between stages.<\/p>\n\n","protected":false},"excerpt":{"rendered":"
        Introduction Before automating anything or designing a user interface, I needed to make sure the data model was solid. Since the Talent Intake System relies on capturing and relating candidates, resumes, and job applications, I started by designing a structure in Dataverse that could support flexible workflows, easy querying, and clean integration with forms and […]<\/p>\n","protected":false},"author":1,"featured_media":1550,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,38,6],"tags":[],"class_list":["post-1549","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dataverse","category-design","category-model-driven-app","entry","has-media"],"jetpack_featured_media_url":"https:\/\/automatethemundane.com\/wp-content\/uploads\/2025\/07\/photo-1545987796-200677ee1011-scaled.jpg","_links":{"self":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/1549","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/comments?post=1549"}],"version-history":[{"count":0,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/posts\/1549\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media\/1550"}],"wp:attachment":[{"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/media?parent=1549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/categories?post=1549"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/automatethemundane.com\/index.php\/wp-json\/wp\/v2\/tags?post=1549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}