How to Create a Form That Retrieves Employee Details by ID
Question details
The user wants to create a dynamic form that automatically fills in employee information, such as name and position, immediately after an employee ID is entered.
- Product
- Microsoft Forms and Power Apps
- Device & OS
- not provided
- Scenario
- Creating an internal data-entry or lookup tool for HR or business operations where entering a unique identifier pulls related records.
- Observed behavior
- The user needs dynamic data retrieval based on input, but standard static form tools do not support live data population during form completion.
Ensure you have access to Microsoft Power Apps and that your employee records are stored in a supported cloud data source, such as a SharePoint list or an Excel table hosted on OneDrive.
Use Microsoft Power Apps to Create a Dynamic Lookup Form
Since Microsoft Forms cannot dynamically populate fields during completion, building a canvas app in Power Apps is the best approach to retrieve real-time data.
Microsoft Forms is designed strictly for static surveys and quizzes, meaning it cannot pull live data from an external database while a user is typing. Power Apps solves this by connecting directly to live data sources like Excel, SharePoint, or Dataverse to perform dynamic lookups instantly.
Create an Excel file containing columns for Employee ID, Name, and Position. Format this data as a Table (Insert > Table) and save the file to SharePoint or OneDrive for Business.
Open Microsoft Power Apps, select 'Create', and choose 'Blank app'. Select 'Blank canvas app' and give your application a name.
In the left-hand menu, click the 'Data' icon, select 'Add data', and search for the connector where your Excel file is stored (e.g., OneDrive for Business or SharePoint). Select your file and the specific table inside it.
Go to the 'Insert' tab and add a 'Text input' control. This is where users will type the Employee ID. Clear its default text and name the control 'txtEmployeeID'.
Insert a 'Text label' control to display the Employee Name. In the formula bar for its 'Text' property, use the LookUp function: LookUp(YourTableName, EmployeeIDColumn = txtEmployeeID.Text, EmployeeNameColumn).
Create an Auto-Populating Form in WPS Spreadsheet
If you do not need a standalone web application, you can easily build an interactive form layout directly within WPS Spreadsheet using the VLOOKUP function to automatically retrieve employee details instantly.
- 1. Set Up Your Database Sheet: Open WPS Spreadsheet and create a sheet named 'Database'. Enter your employee data with dedicated columns for ID, Name, and Position.
- 2. Design the Form Layout: Create a second sheet named 'Form'. Designate specific cells for user input (e.g., cell C3 for the Employee ID) and output fields (e.g., C5 for Name, C7 for Position).
- 3. Apply the VLOOKUP Function: Click on the output cell for the Employee Name (C5) and enter the formula: =VLOOKUP(C3, Database!A:C, 2, FALSE).
- 4. Retrieve Additional Details: Click on the output cell for Position (C7) and enter the formula: =VLOOKUP(C3, Database!A:C, 3, FALSE). Now, typing an ID in C3 instantly populates the rest of the form.
Frequently Asked Questions
Can I use Microsoft Forms to auto-populate employee names?
No, Microsoft Forms is a static data collection tool. It does not support dynamic field population or live database lookups during form completion. You must use Power Apps or a spreadsheet-based solution.
What function do I use to retrieve data in a spreadsheet form?
The VLOOKUP, HLOOKUP, or XLOOKUP functions are ideal for this task. They allow you to search for a specific identifier, such as an Employee ID, and return related information from another column in your dataset.
Where should I store my employee data for Power Apps?
To ensure seamless connectivity, you should store your employee data in a cloud-based source such as a SharePoint List, Microsoft Dataverse, or an Excel table hosted on SharePoint or OneDrive for Business.
Is it possible to protect my spreadsheet form from accidental edits?
Yes. In WPS Spreadsheet, you can unlock only the input cells (like the ID field) via 'Format Cells' > 'Protection', and then go to the 'Review' tab to 'Protect Sheet'. This ensures users can only enter an ID without breaking the lookup formulas.
