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.  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------------------------------

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.   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 thisWhen 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------------------------------

Sign up

Login to the community