Paul,
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
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
Original Message:
Sent: 03-09-2020 10:16
From: Paul Peterson
Subject: Add Date only if the field is null and prevent further changes
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
Original Message:
Sent: 03-07-2020 22:25
From: Don Larson
Subject: Add Date only if the field is null and prevent further changes
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.
- 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
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
Original Message:
Sent: 03-06-2020 10:54
From: Paul Peterson
Subject: Add Date only if the field is null and prevent further changes
I have fields that track the project completion date starting original completion date and tracking any date changes between one checkpoint and the next. 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.
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. For example, if the T - 30 date at 3/5 is 4/5 and on 4/6 the date is changed to 4/8. When we get to the new T - 30 on 4/8, the date entered on 3/5 cannot be overwritten. 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. 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
------------------------------