Forum Discussion
This is a problem that I refer to as "The Date and the date of The Date."
- Move your dates out of the Projects table and into a Child Table.
- Never let anybody change a date. They can only add a new child record.
- Build Summary Fields in the Parent Table that will surface the correct date based upon the business logic.
Your architecture looks like this
When the User wants to change the T-30 date, he is actually adding a record to the Completion Dates table where the Related Date Type is set to the RID for T-30.
The date entered in the Completion Dates table is The Date
Date Created is the date of The Date
For the particular Type of Date your Summary Fields can now easily see if the value in Completion Dates is Null.
I am sure there is a lot more logic this impacts in your application so it needs some thought on how to deploy it.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
- PaulPeterson16 years agoQrew Assistant Captain
Don,
I don't think I described the situation well enough. The completion date is actually pulled from an external system that the users do have the ability to modify. The T-n dates are calculated fields based on the completion date. The logic we are striving for is at completion date T - n days, if the Completion at T - n days is null, then we want to write the date of Completion Date - n. But if the Completion Date at T - n days has a value, we do not want to change the date.
As an example, let's assume the current Completion Date is 4/15/2020. On 3/16/2020 the Completion Date at T - 30 is 3/16/2020. If the Completion Date at T - 30 IsNull, then the date of 3/16/2020 will be entered in Completion Date at T - 30. If it already has a value, then the date must not be changed.
The intent is to prevent the users from "gaming" the system to make it appear they are hitting a higher percentage of on time completion by changing the Completion Date after the Completion Date at T - n. Following the example above, suppose on 3/18 the user notices they missed the Completion Date at T - 30 and they change the Completion Date to 4/17/2020. If the Completion Date at T - n days can be overwritten, the users can manipulate the data to make the on time completion rate appear better than it really was.
------------------------------
Paul Peterson
------------------------------- DonLarson6 years agoQrew ElitePaul,
I believe I understand your business case now. To summarize, you want the permissions on individual fields to change based upon whether or not the individual field has data in it. I have two different ideas for you.
1) You can use a Form Rule to change permission to edit the field.
This is quick and easy but has number of limitations as this only protects the data from that form. If they can Grid Edit other reports then the User can game the results. Do the Projects have parent records where this data is at risk of being viewed or manipulated off the the form?
2) It is a bit more work but the architecture using child records lets you set permissions at a role level that would prevent them from editing the value.
Create a formula checkbox that is true when the value is set in the child table
Disable the Add Child Button when the formula check box is true
Make the formula check box a look up field in the child table
Set the modify permission in the child table based upon the value of the look up field
Here is sample on the button control
Here is a screenshot of the relationship
Here is a shot of the permissions
This will prevent them from adding another date to the child table and they cannot edit the existing date.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------- PaulPeterson16 years agoQrew Assistant Captain
Don,
Thank you for your suggestions. Since this is being used for a report that the users will not have access to change, I went with option 1. It appears to be working.
Thank you!
------------------------------
Paul Peterson
------------------------------