Add Date only if the field is null and prevent further changes

Question

I have fields that track the project completion date starting original completion date and tracking any date changes between one checkpoint and the next.&nbsp; The logic for the original completion date is fairly straightforward. What I want to do at each of the other times is:
Compare the current completion date to the completion date at the previous checkpoint.&nbsp;&nbsp;If the dates are different, enter the new completion date in the field relating to that checkpoint (Ex. Completion Date at T-n days)This date should only be updated once and not overwritten.&nbsp; For example, if the T - 30 date at 3/5 is 4/5 and on 4/6 the date is changed to 4/8.&nbsp; When we get to the new T - 30 on 4/8, the date entered on 3/5 cannot be overwritten.&nbsp; I tried adding a checkbox field to see if the completion date field is null and only allow an update if the checkbox is false.&nbsp; That doesn't work as QuickBase will not allow a formula with an indirect reference to the current field.Thank you in advance for your assistance------------------------------Paul Peterson------------------------------

donlarson · Answer

Paul,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.&nbsp; &nbsp;
Never let anybody change a date.&nbsp; 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 DateDate Created is the date of The DateFor 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 LarsonPaasporterWestlake OH------------------------------

paulpeterson1 · Answer

Don,
I don't think I described the situation well enough.&nbsp; The completion date is actually pulled from an external system that the users do have the ability to modify.&nbsp; The T-n dates are calculated fields based on the completion date.&nbsp; 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.&nbsp; 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.&nbsp; On 3/16/2020 the Completion Date at T - 30 is 3/16/2020.&nbsp; If the Completion Date at T - 30 IsNull, then the date of 3/16/2020 will be entered in Completion Date at T - 30.&nbsp; If it already has a value, then the date must not be changed.&nbsp;
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.&nbsp; 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.&nbsp; 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------------------------------

donlarson · Answer

Paul,I believe I understand your business case now.&nbsp; &nbsp;To summarize, you want the permissions on individual fields to change based upon whether or not the individual field has data in it.&nbsp; I have two different ideas for you.1)&nbsp; 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.&nbsp; &nbsp;If they can Grid Edit other reports then the User can game the results.&nbsp; 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.&nbsp;&nbsp;Create a formula checkbox that is true when the value is set in the child tableDisable the Add Child Button when the formula check box is trueMake the formula check box a look up field in the child table&nbsp;Set the modify permission in the child table based upon the value of the look up fieldHere 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 LarsonPaasporterWestlake OH------------------------------

paulpeterson1 · Answer

Don,
Thank you for your suggestions.&nbsp; Since this is being used for a report that the users will not have access to change, I went with option 1.&nbsp; It appears to be working.Thank you!------------------------------Paul Peterson------------------------------

donlarson · Answer

Paul,Your welcome.By all means use the simplest solution to meet the requirements.------------------------------Don LarsonPaasporterWestlake OH------------------------------

Forum Discussion

Add Date only if the field is null and prevent further changes

Related Content

Preventing Budget Overrun

Preventing Scheduling Conflicts

Prevent save a record - New forms

Changing/setting record owner

Historical Date Change Tracking