Saving value from a child field to parent record

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
For our projects, it is common that completion dates change.  To control the process we have a change approval form linked to the parent record where user indicates the new comp date and depending on whether this is approved, this date is then looked up back from the child table into the parent.  This works fine but it is not an elegant solution as I have now ended up with two completion dates and the new completion date is only valid when it has a value in it.  This will cause an issue for me to use this in various reports.  Ideally, if a new date has been approved, I would like to over write the existing parent field.  Unable to figure out how to achieve this? Will appreciate some help. 





Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Are you saying that you only want to have a maximum of 1 Approved record at any time?
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Yes please.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
That sounds difficult to do, given that that during the approval process seems to require that the 2nd unapproved record needs to co-exist with the previous approved record until it is approved.  Is there is good business reason why you only need to have 1 record?  Can't you do a reverse lookup of the Completion adjustment record with the highest record ID# which is Approved?
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Sorry I misunderstood your question.  In fact there are a number of requests made by the user for approvals  for changing dates.  Some were approved some were rejected.  So I can see in my sub form all these records.  It is not just one record.  At the moment I am looking up the max of records to show the new date in my main form. But this would be ideal if I could amend the completion date.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
If I have your email address, I can send you a screenshot to assist you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
You can post a screen shot in your original Question, if you like.  But if you want to amend the Completion date field in the Parent record just rename your existing field on the Parent record to be called [Original Completion Date]  and then make a new field to display in view mode called [Completion Date].  It will be a formula field
IF(not IsNull([Most Recent Approved Completion Date Adjustment]), [Most Recent Approved Completion Date Adjustment],
  [Original Completion Date])
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks. I have added a screenshot. This is a good solution but  as I thought more about this, this becomes complicated.  As I will need to show a rolling situation in my sub form.  If a new date was approved, it then becomes the current date and when yet another date is added to the sub form for approval.   Not too sure if I have the approval process right.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
I'm not sure if that was a question or just thinking out loud. :)
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Sorry I hit the send button too soon.  I was meaning to ask if a rolling situation can be achieved whereby my current date is saved in change approval form each time a new approval is given. Any any new approved date becomes the current date.  In the process the change approval process form retains the history.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Yes, you can lookup the calculated value for value for the [Completion Date] down to the Completion Date Adjustment child records.  But call it [Completion Date lookup]. Then create a new date field called [Completion Date snapshot of Completion date before this change request].  At the bottom of the field properties set it to be a snapshot of the [Completion date lookup] field.
Here is the help text on snapshot fields http://www.quickbase.com/user-assistance/Default.html#setting_up_snapshot_fields.html
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot.  This is a great feature.  I successfully managed to create the field. Now I will use your formula to create a field to show orig date or new date in case no change has occurred.  Formula looks straight forward if these are the name of fields you have used to explain it.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Sorry to be back.  Did you mean formula date field?  If so, I took the option of Edit formula using a text field and entered the following formula but this apparently has errors.  IF(not IsNull([New Comp Date), [New Comp Date], [Project Comp Date]). If I take the other option, Use the End Date formula builder, then I am not too sure how to use the formula.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
IF(not IsNull([New Comp Date]), [New Comp Date], [Project Comp Date])
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot for kindly persevering with me.  It works great.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
glad to help