How to Automatically Add a Completion Date and Assign Items in Microsoft Lists
Question details
The user wants to automatically record the current date when an item is marked as archived and assign the item to a user when its status changes to waiting for approval.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Automating date entry and user assignment based on field changes in a list when Power Automate is unavailable.
- Observed behavior
- The user needs a practical setup to update dates and assignees dynamically based on the values of a Yes/No field and a Choice field.
Ensure you have permission to customize forms in Microsoft Lists using Power Apps, and verify the exact internal names of your Yes/No and Choice fields before starting.
Customize the Microsoft Lists Form Using Power Apps
Use Power Apps to set default values for date and assignee fields based on the conditions of other fields, bypassing the need for Power Automate.
By customizing the default properties of your data cards in Power Apps, you can build dynamic rules that automatically populate fields when users interact with the form.
Navigate to your list in Microsoft Lists, click on 'Integrate' in the top menu, select 'Power Apps', and then choose 'Customize forms'.
Select the Date field card (e.g., DataCardValue3) in the Power Apps studio and set its Default property to: If(DataCardValue2.Value=true,Text(Today(),DateTimeFormat.ShortDate),"") where DataCardValue2 is your Archive Yes/No field.
Select the Assignee field card (e.g., DataCardValue5) and set its Default property to: If(DataCardValue4.Selected.Value="Waiting For Approval","Administrator1","") where DataCardValue4 is your Choice field.
Select your Submit button and set its OnSelect property to: SubmitForm(SharePointForm1);NewForm(SharePointForm1). Then, select your Reset button and set its OnSelect property to: ResetForm(SharePointForm1);NewForm(SharePointForm1).
Click 'File' in the top left corner, then 'Save', and finally select 'Publish' to apply the customized logic to your Microsoft List.
Use a Calculated Column for the Completion Date
If you only need to automatically display a date without changing user assignment, a simple calculated column can achieve this directly in Microsoft Lists.
Boost Your Productivity with WPS Office
While Microsoft Lists requires tools like Power Apps for advanced form logic, WPS Office offers a straightforward, lightweight, and highly compatible suite for your daily document, spreadsheet, and presentation needs. Experience robust data management capabilities in WPS Spreadsheet without complex configurations.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open WPS Spreadsheet: Launch WPS Spreadsheet to create a new tracker or seamlessly open your existing Microsoft Excel files.
- 3. Apply Formulas and Validation: Use built-in Data Validation and IF formulas to automate data entry and manage task statuses easily.
Frequently Asked Questions
Why can't I use a calculated column to assign users in Microsoft Lists?
Calculated columns in Microsoft Lists are designed to perform mathematical operations or text manipulation on data within the same row. They do not have the capability to interact securely with the People and Group fields to reliably assign or look up system users.
Can I automatically assign users without requesting access to Power Automate?
Yes, if you do not have access to Power Automate, you can use Power Apps to customize your list's input form. By setting conditional IF formulas on the Default property of the assignee field card, the form can automatically populate the assigned user when specific criteria are met.
What happens to my customized Power Apps form if I add new columns to the list later?
If you add new columns to your Microsoft List, they will not automatically appear on your customized Power Apps form. You must open the form in Power Apps again, manually add the new data cards for the newly created columns, and publish the form.
