Forum Discussion

AaronSnow's avatar
AaronSnow
Qrew Cadet
6 years ago

Formula Help

I have a table that is driven by due dates and would like to tack / capture the number of lates in a report or table. does anyone have any suggestions on best practices and how i might auto generate this report using a formula?

thanks,
Formulas and functions
Relationships
reports and charts
  • AdamKeever1's avatar
    AdamKeever1
    Qrew Commander
    6 years ago
    You can use a formula numeric field to calculate the days overdue, [Late]:

    If(
    ToDays([Due Date] - Today())>0,0,
    Abs(ToDays([Due Date] - Today())))

    Then you can set up a notification to fire when [Late] is greater than 0.

    If you are wanting to automate this then you will have to have a table connection or sync in order to set up a refresh schedule.
  • AaronSnow's avatar
    AaronSnow
    Qrew Cadet
    6 years ago
    I have something like this setup now and what i would like to capture is the history of lates... sorry, i dont think i spelled that out very well in my description.

    thanks, 
    • AustinK's avatar
      AustinK
      Qrew Commander
      6 years ago
      How would you like to see the history? How far back do you want it to go? Are you wanting this info on the record itself(ie how many times they were late) or are you wanting to have these "late records" saved in another table somewhere, so you can associate them back to the related record and show the exact times they were late?
    • AaronSnow's avatar
      AaronSnow
      Qrew Cadet
      6 years ago
      currently i have a separate table setup with the necessary relationships... starting on today's date, i would like any late to show up in the new table (late history) once any record shows as late. I currently have the other table setup showing the name of the form owner and the related record id (shown as a link).

      I hope this answers all of your questions.

      thanks, 
    • AustinK's avatar
      AustinK
      Qrew Commander
      6 years ago
      As Adam said you can use an automation to copy records over there. That would be my suggestion as well. Since the records are likely not going to be modified first (to trigger a regular automation)the best solution I see would be to have the automation run every day on a schedule and copy records that are late as of that day([due date] before Today). You can even just do a few fields so keep it easy to read. Since it will be related back to the original record all you would really need is the due date, the record id of the record to be turned into the "related record" and whatever else you want to show.

      Does that make sense? Sorry I would have replied earlier but I got caught up in something.
  • AdamKeever1's avatar
    AdamKeever1
    Qrew Commander
    6 years ago
    You can create a Late History table and use an automation to copy the records to that table that are late.
  • AaronSnow's avatar
    AaronSnow
    Qrew Cadet
    6 years ago
    This is the way i have it setup and it does not seem to be working properly. i assumed it was because the late status is triggered through a formula and within the automation it states to "trigger" when a user makes a change. 

    If i go in and change the dates manually to drive a "Late" status, it will populate the other table as it is intended but it does not work on its own.

    to work this way i need a formula correct? or maybe i have something else that is not setup correctly?




  • AaronSnow's avatar
    AaronSnow
    Qrew Cadet
    6 years ago
    I see now. sorry i misunderstood... i had the automation triggered on a change as opposed to a schedule like you mentioned. thanks for your help. it should work now.