Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
5 years ago

Log Date a file attachment changes for expiration

I have a file attachment under a company record for Certificate of Insurance.  I need to log a Certificate of Insurance for each of my installers.  I need an expiration date which I want to be 1 year from the date we upload the COI.

So lets say Installer A has a COI uploaded today (7/2/19).  I want to auto set a field titled COI Expiration 7/2/20 this way I can generate a report via subscription on that to see overdue COI's and work on getting them resolved.

But in the dynamic form rules I cannot find any triggers for when a file attachment changes.
Forms
Formulas and functions
Quickbase Automations

7 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    5 years ago
    You can make a formula text field called File Name with the formula

    ToText([My file attachment field])

    But if the actual name of the file attachment does not change when a new file is uploaded, I don�t think that the form rule would fire.
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      5 years ago
      I am trying to modify a date field, not text.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      5 years ago
      I understand that you are trying to modify a date field. 

      But you were saying that the file attachment field does not seem to be an option to use on form rules.  

      So I suggested making a formula text field and using that formula text field in the form rule.
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      5 years ago
      Understood, but how would I update the based on this?
  • DanLadner1's avatar
    DanLadner1
    Qrew Trainee
    5 years ago
    Can you do anything with form rules and file attachments? I think you'd want to use an automation in this case.
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain
      5 years ago
      I tried this but the field is in the same table.  Automations seems to focus on child tables
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    5 years ago
    If you were to store your COI's as child-table records against a parent record; you could eliminate all these problems.  Store documents as a child table; and have them tagged by a multiple choice field. Then use summary fields to pull back to the parent expiration dates.  You can calculate the expiration date with a formula like this:

    Case(true,
    [Type] = "COI", AdjustYear(ToDate([Date Created]),1)
    )

    You would then build a Summary field to pull back the Maximum [Expiration Date] field value where [Type] = "COI".  This way you can have different document types; but leverage the summary fields to pull back dates into dedicated-fields for use with your subscription reports.

    Alternatively; each time you upload a file; populate a date field for the date it was uploaded and then use the same AdjustYear() function in another formula-field to use in subscriptions.