VALUE OF ONE FIELD IS ALWAYS EQUAL TO VALUE OF ANOTHER IN SAME TABLE

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
I have an automation that is dependent on a record being modified.  My problem is the trigger field (it is a date formula field), when changed, doesn't update the "date modified" field.  I created a new field (type=date) that I need to be always equal to the value in the current trigger field.  This field does update the modified date which would trigger the automation.  Any help is appreciated! 
Photo of Alecia Gipson

Alecia Gipson

  • 90 Points 75 badge 2x thumb
  • confused

Posted 2 months ago

  • 0
  • 1
Photo of Danie Grabe

Danie Grabe

  • 172 Points 100 badge 2x thumb
Hi Alecia, it makes sense that the formula would not change - only the result of the formula. If you make another field equal to this one, it will also be a formula - so same problem. Can you give us more information about your business case/ challenge? I am not 100% sure that I understand your question.
(Edited)
Photo of Eric Mohlman

Eric Mohlman, Employee

  • 500 Points 500 badge 2x thumb
There are three types of fields, formula, summary, and lookup that are "derived" at the moment they're used in a form, report, notification, etc. and therefore don't change values. Since your formula date field is based on some other information on the record however, would triggering your automation on its source data changing work for your situation instead?
Photo of Alecia Gipson

Alecia Gipson

  • 90 Points 75 badge 2x thumb
So the challenge I am facing is we have several tables that house different information about each employee.  Then all of those records come together in the "personnel" table that serves as an employee record and shows all information from different tables in one place.  Example:  John Smith has a record in personnel.  He has one event in the "citations" table, one event in "collision" table, etc..  All of those are displayed on the record form for "John Smith" in the personnel table.  We have a monitoring process built based on all of those summary fields from the different table (this was built prior to my coming on board).  In order to prevent the need to rebuild everything for time being I just need to "trigger" a training session (in training table) when criteria is met in the summary fields.  The only automation functions I can find are when a record is modified or added.  Since the personnel table record is basically a summary record I just need to figure out how to trigger that automation to trigger.  I tried setting an automation to update the current date field on a scheduled daily basis but there are too many records and it errors out.  Sorry for the novel it is just a complicated and inefficient build and I don't have the time to rebuild the entire thing right now. 
Photo of Eric Mohlman

Eric Mohlman, Employee

  • 500 Points 500 badge 2x thumb
Would an automation based on the details in your child table(s) be sufficient? i.e. a Citation is marked as complete could use an automation to create the training record elsewhere that's linked to the Personnel record, or a certification is expiring in the next 4 weeks could create the training record for recertification.

If you truly want it based on the Personnel record's summary fields, you could also setup formula fields to contain the details to send to the training session record, then create a table-to-table import targeting the correct information, and ultimately have a scheduled automation to send those details once the correct condition is met.
Photo of Alecia Gipson

Alecia Gipson

  • 90 Points 75 badge 2x thumb
After more testing I did find a solution.  Quickbase has a 25MB limit on automations (ugh..).  However I was able to create a date field and then set an automation to run daily to simply update the date to "today" based on records meeting the filter criteria (based on my summary fields).  This "modifies" the records and then triggers all the automations connected to run.  It isn't pretty but it works for now.