Forum Discussion

BenjaminGolden's avatar
BenjaminGolden
Qrew Cadet
6 years ago

Running Action/Automation with a Formula Trigger

So, to pre-summarize and make a long story short, I'm looking for a way to get an Automation, Action, or whatever method may work to create a new record when the result of a formula changes. I know this is going to take some creativity, since these normally don't work unless the record is directly modified, but I figured at least one person may know a hack for it.

Basically, my situation is that I have an app with a bunch of interconnected tables, and have one table that calculates the next day a piece of equipment is in need of periodic maintenance based on a weekly, monthly, etc. schedule. Each time the next maintenance day is changed (e.g. when the day passes) I want to create a work ticket that shows the required task to be completed at the next maintenance day.

If y'all need more information I can certainly provide, but I'm a little crunched on time so please be patient. I know a workaround for this would help out with a lot of things such as Automated Notifications, but it won't hurt my feelings if there's not really a practical way to do so.

Thanks!
Formulas and functions
Quickbase Automations
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    For a fully automated solution you can try this but I don't know for sure if it will work.  I have other non fully automated solutions as plan B.

    Set up a saved table to table import  and an Automation run it daily to copy the formula date into a [last recorded formula date] field.  ie import the record into themselves merging on [Record ID#] filtered by where those two dates are not equal.

    Then set up another Automation to create a Work order when the 
    [last recorded formula date] changes.

    Let me know if that works or does not work.

    • BenjaminGolden's avatar
      BenjaminGolden
      Qrew Cadet
      6 years ago
      Currently, this seems to be exactly what I want. The import works, and as long as it runs like normal in the day-to-day this seems to be the right way to go. Best thing to do now is cross my fingers for the Automation timing improvements they said they'd implement in User Voice.

      Anyway, thanks, Mark! I think it's worth mentioning I've taken your advice on here more than a few times since I started using QB a few weeks ago and it's good to hear from you directly. Hope you're having a good day!
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      6 years ago
      Just a thought.  If you want the Automation to run more often than once a day in the middle of the night, you can make a new automation to do the same ting and trigger it off some other record being edited that you know if happening regularly during the day by regular users of ideally, triggering off a change into values that cause the formula to be changes, aside from the simple passage of time.

      It is very light weight Automation to run a saved table to table import to look for records where the formula is not matching the [last recorded formula date].  

      So you can set that to be triggered perhaps many times in the day even if mostly it does not change any records, but then at least sometimes it does, thus creating the Work Order(s) earlier than "tomorrow".




    • BenjaminGolden's avatar
      BenjaminGolden
      Qrew Cadet
      6 years ago
      Sounds pretty good to me. I'm going to be reworking a lot of aspects of the app in the meantime, but I'll most likely integrate something like what you described for when it comes down to creating schedules to order maintenance multiple times a day as opposed to every day or week or month. Thanks again!