Want to Create a View that shows the End date Alert message in the app

[AnshulKamboj]

I have a table Projects in which I have created 7 columns:-

1. ID -> UniqueID()
2. Employee Name - Ref
3. Project Name -Enum
4. Start Date - Date
5. End Date - Date
6. Utilization - Number
7. Status - Enum

I want to create a view:-
It should be refers the Employee Name, Project Name And a alert message when the project is near to End date.
The message should come in the view 7 days before from the end date.

[Suvrutt_Gurjar · Accepted solution]

Since you want to show the alert message in the view itself, you could create a show type column of "text" category with the column's "content" setting expression something like 

IFS([ End Date]<=TODAY()+7, CONCATENATE( "HI ", [Employee Name], "  the project ", [Project Name], "  is coming to an end on ", [End date]))

However please be aware that this message will get displayed only if the user opens and syncs the app on any day between 7 days of the project end date. The message will NOT auto recalculate till the user syncs the app once.

[AnshulKamboj]

Sir, Which view do I choose to display the message in list the view?

[AnshulKamboj]

Please Look into the Image the expression is not fetching the employee. What I'm doing wrong?

[Suvrutt_Gurjar]

In the detail and form view may be.

[Suvrutt_Gurjar]

You will need to add another column in the Projects table to pull Employee Name using dereference expressions  from your Employee table. Right now it is populating key of the Employee table.

https://help.appsheet.com/en/articles/1090811-dereference-expressions

[AnshulKamboj]

As suggested the problem of employee name referencing is resolved but, Please look once at the expression 

IFS([End Date]<=TODAY()+7, CONCATENATE( "Hi ", [Emp].[Employee Name], ", The Project ", [Project Name], " is coming to end on Date ", [End date]))

The expression is also showing the list of project those date are after 7 days. What needs to be changed in it?

for more clarification, I have assigned an end date to the project 28/02/2022. It is also showing into the list but the message is not showing with it.

[Suvrutt_Gurjar]

It should not show. Please show the test pane of test results for the said row.

[AnshulKamboj]

Please Check

[Suvrutt_Gurjar]

So for first row when the end date is 2/28 , there is no message displayed, for the second project when the end date is 2/22, the message is displayed. That is what is expected , is it not? Because that project ending on 2/28 is not finishing within one week from today , so no message.

Your mentioned in the beginning

"The message should come in the view 7 days before from the end date."

[AnshulKamboj]

"The message should come in the view 7 days before from the end date."  --IT is working fine.

Kindly refer the attached snapshot for more clarification

the highlighted record date is 28/02 so that I wish this record should not be present in the list.

[Suvrutt_Gurjar]

You mentioned about the message, not about the record no showing or showing in the view. 🙂

[AnshulKamboj]

My Mistake Sir, Kindly help what to be add in the expression so that it will filter the record also.

[Suvrutt_Gurjar]

I think that is not so straightforward. You could create a slice of records having [End Date] as next seven days. But you may still need to decide when you wish to display that slice.  A simpler approach will be to have such slice, create a view based on it and name it "Ending Projects" or something similar.  You could always display a view of that slice.

[AnshulKamboj]

Thank u Sir, Resolved!

[Suvrutt_Gurjar]

Nice, thank you for the update.