adding a timestamp, and authorized users to edit specific field in power app

[ryanz3653]

Hi community, I am new to Power Apps and I would like to ask two questions,

I am working on a MS form - SharePoint list - power automate - power apps project, I have built their link already.

There is a 'Date request' input field in MS form, when it comes to sharepoint list, I would like to add another column for 'Date request timestamp', it is the timestamp when users completed the form.

There is a 'status' field only in sharepoint list, when new records comes from MS form, it is automatedly 'New', unless users change it in sharepoint or power apps from 'New' to 'Completed', and a date field will be required for users to fill in when that happens - 'Date completed', and I want a column to show the timestamp for that - 'Date completed timestamp'

Overall I will need two timestamp columns, 'Date request timestamp' and  'Date completed timestamp', what is the workaround?

Here is a simple excel screenshot for explanation

----------------------------------------------------------------------------------------------------------------------------------------

Second question, as i have many columns in sharepoint and in power apps,  I want some users are only authorized to edit some specific columns. Logics explained as in screenshot below, it is a edit screen in power apps. Is there a way to do that?

---------------------------------------------------------------------------------------------------------------------------------------------

I hope above are all making sense, thank you in advance for your solutions!

[Robu1]

Hi @ryanz3653 ,

Welcome to power apps. 

Let’s address your questions step by step:

1. Adding Timestamps:

   • To create timestamp columns in your SharePoint list, follow these steps:
     1. Date Request Timestamp:
        • In your SharePoint list, add a new column (e.g., “DateRequestTimestamp”).
        • Set the default value of this column to Now() when a new item is created. This will automatically capture the current date and time when users complete the form.
     2. Date Completed Timestamp:
        • Similarly, add another column (e.g., “DateCompletedTimestamp”).
        • When the status changes from “New” to “Completed,” use Power Automate to update the “DateCompletedTimestamp” column with the current timestamp.

2. Column-Level Permissions:

   • To restrict user access to specific columns, consider the following approach:
     1. Create Security Groups:
        • In your Office 365 environment, create security groups (e.g., “Supervisors” and “Administrators”).
        • Add users to these groups based on their roles.
     2. Set Column Permissions:
        • In Power Apps, go to the table that corresponds to your SharePoint list.
        • Under the table schema, select the relevant column (e.g., “Comments” or “Priority”).
        • Expand the advanced options and enable “Enable column security.”
        • Configure the permissions for each group:
          • For “Comments”:
            • Supervisors: Edit
            • Submitters and others: View
          • For “Priority”:
            • Administrators: Edit
            • Submitters and Supervisors: View

Remember to replace the placeholder names (“Supervisors,” “Administrators,” etc.) with actual group names and adjust the permissions according to your specific requirements. This way, you can control who can edit specific columns while keeping others read-only.

Feel free to ask if you need further assistance or clarification! 😊🚀

If this post helps, then Click on the Thumbs Up below and Accept it as the solution to help others.

Thanks,

Robu1.

[ryanz3653]

Thank you for your reply @Robu1, I followed your instruction and created following calculated columns in sharepoint list:

for Date Request Timestamp, my formula is =TEXT(NOW(),"dd/mm/yyyy hh:mm")

for Date Completed Timestamp, my formula is =IF(Status="Reviewed",TEXT(NOW(),"dd/mm/yyyy hh:mm"))

but whenever I updated my records, whether its in sharepoint or power app (like changing status from New to Completed), the Date Request Timestamp get updated as well, and I do not wish that happen, I would like it to stay forever the moment that this record from MS form to sharepoint, is that a workaround? and could you be more specific about the power automate part please,

thank you in advance.

[Robu1]

I understand your concern.

Calculated columns in SharePoint don’t update dynamically; they only update when an item is created or edited. Unfortunately, using NOW() in your formula will cause the timestamp to update every time the item is modified, which isn’t ideal for your use case.

Here are a couple of workarounds you can consider:

1. Scheduled Flow to Update Timestamps:

   • Create a scheduled Power Automate (formerly Flow) flow that runs daily.
   • This flow should update the “Date Request Timestamp” and “Date Completed Timestamp” columns for all items in your SharePoint list.
   • You can use the “Update item” action in Power Automate to set the timestamp values based on your conditions (e.g., if the status changes to “Completed”).
   • This way, the timestamps will remain static once set during the initial form submission.

2. Separate Columns for Creation and Completion Dates:

   • Instead of using calculated columns, create separate columns for “Date Request” and “Date Completed.”
   • Use Power Automate to set the initial “Date Request” timestamp when a new item is created.
   • Similarly, set the “Date Completed” timestamp when the status changes to “Completed.”
   • This approach ensures that the timestamps remain fixed once captured.

Regarding the Power Automate part, here’s a more detailed explanation:

• Power Automate Flow for Date Request Timestamp:

  1. Create a new flow in Power Automate.
  2. Set the trigger to be when a new item is created in your MS Form responses.
  3. Add an action to create a new item in your SharePoint list.
  4. In the “Date Request Timestamp” field, use the utcNow() function to capture the current timestamp.
  5. Save and test your flow.

• Power Automate Flow for Date Completed Timestamp:

  1. Create another flow triggered when an item is modified in your SharePoint list.
  2. Add a condition to check if the status has changed to “Completed.”
  3. If true, update the corresponding item’s “Date Completed Timestamp” with the current timestamp using utcNow().
  4. Save and test this flow as well.

Remember to adjust the column names and conditions according to your specific setup. If you encounter any issues or need further assistance, feel free to ask! 😊

---

I’ve provided detailed instructions for both workarounds, emphasizing the use of Power Automate to manage the timestamps effectively. If you need further clarification or have additional questions, feel free to ask! 🌟

[ryanz3653]

Thank you @Robu1, at least now I know that I should be working toward PowerAutomate not the sharepoint,

I have just been working on the details of PowerAutomate following the guide of your previous two solutions for Date Request Timestamp and Date Completed Timestamp, do you mean I need to create two seperate flows? because in real case I will have other timestamp column as well (eg: Timestamp requested, timestamp reviewed, timestamp shipped, timestamp completed)

For Date Request Timestamp I created PowerAutomate flow as following screenshot 1, and formula in step 3 for Date Request Timestamp column is the screenshot 2. It seems working as per the purpose but just one glitch, I am always getting the result that their timezone is 3 hrs earlier, even though I have set up the correct timezone in the formula (In Toronto eastern time in my area) 

As for the Date Completed Timestamp, could you please provide the flow and formula details? (like how I did for timestamp for date request)

I believe the trigger point is 'When an item or a file is modified', and what comes after that?

Thank you again in advance

[Robu1]

 ok @ryanz3653 , Let’s address your questions step by step:

1. Multiple Timestamp Columns: You can create multiple timestamp columns in the same flow. Each timestamp column will have its own flow action. For example, if you need “Timestamp Requested,” “Timestamp Reviewed,” “Timestamp Shipped,” and “Timestamp Completed,” you can set up separate actions for each of these columns within the same flow.

2. Timezone Issue: The timezone issue you’re experiencing is likely due to the default behavior of Power Automate, which uses Coordinated Universal Time (UTC) by default. To adjust the timestamp to your local time zone (Eastern Time in Toronto), you can follow these steps:

   • In your existing flow, locate the “Compose” action where you’re using @utcnow().
   • After the “Compose” action, add a new action called “Convert time zone.”
   • In the “Convert time zone” action, set the input time to the output from the “Compose” action (your UTC timestamp).
   • Specify the source time zone as “UTC” and the target time zone as “Eastern Standard Time” (or “Eastern Daylight Time” if applicable).
   • The output from this action will be the timestamp adjusted to your local time zone.

   Here’s how you can set up the “Convert time zone” action: !Convert time zone action

3. Date Completed Timestamp: To create the “Date Completed Timestamp,” follow these steps:

   • Add a new action after the “Convert time zone” action (or after the “Compose” action if you skip the timezone conversion).
   • Choose the “Update item” action and connect it to your SharePoint list.
   • In the “Update item” action, map the “Date Completed Timestamp” column to the output from the “Convert time zone” action (or directly from the “Compose” action if you skip the timezone conversion).

4. Flow Trigger: You’re correct that the trigger point should be “When an item or a file is modified.” This ensures that the flow runs whenever any item in your SharePoint list is updated.

   Your flow should look something like this:

   1. Trigger: When an item or a file is modified.
   2. Compose: @utcnow()
   3. Convert time zone: Convert the UTC timestamp to Eastern Time.
   4. Update item: Map the “Date Completed Timestamp” column to the converted timestamp.

   Remember to save and activate your flow after making these changes.

Feel free to adjust the flow according to your specific requirements, adding more timestamp columns as needed. If you encounter any issues or need further assistance, don’t hesitate to ask! 😊