Discussions

 View Only
  • 1.  Formula Date Field to Extract Date from Log Entry Field?

    Posted 09-01-2020 10:57
    Is there a formula, or a way, to have the date from a log entry field pulled into a separate field or into another formula? 

    Here's my setup and what I'm trying to accomplish:
    1. A hearing is completed >> Field Name: [ALJ Scheduled Date & Time]; Field Type: (Date/Time Summary)

    2. 60 days from the date of the hearing, the user should conduct follow up and add the status of that follow up in the follow-up field >> Field Name: [Follow Up Status]; Field Type: (Text-Multiple Choice)
    3. Every 30 days from the last follow-up status update, the user should be following up.

    I'm trying to insert a field that will calculate the next follow-up date each 60- or 30-day follow up, without the user having to manually enter a date of follow up. (New field name: [Outcome Follow Up Date])

    My thoughts were to try to base the new field [Outcome Follow Up Date] on the [ALJ Scheduled Date & Time] field for the first 60-day follow up; then the next 30-day follow up to be calculated from the date in the log edit field.

    I hope this makes sense and my explanation is thorough enough.


    ------------------------------
    Danaya Bryant
    Business Analyst
    The van Halem Group
    Atlanta GA
    ------------------------------


  • 2.  RE: Formula Date Field to Extract Date from Log Entry Field?

    Posted 09-01-2020 11:34
    I only have time right now to provide you with the formulas to extra out the three parts of an append only field.  These formula assume that the newest entries are at the bottom

    Parsing Append Only Fields

    so... if you have a text field set to Log Changes where the new entries act in the default manner which is to be at the bottom of the updates, here are some formuals to use to parse out the values from the most recent entry. I am also assuming that you are logging the date and not the date and time.

    Most recent value (formula text field type)

    Trim(Right([my update field],"]"))

    Date of most recent update (this needs to be a formula date field)

    ToDate(Left(Right([my update field],"["),9))

    Who did the most recent update (formula text field type)

    Trim(NotLeft(Left(Right([my update field],"["),"]"),9))

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Formula Date Field to Extract Date from Log Entry Field?

    Posted 09-01-2020 12:19
    Mark,

    Thank you so much, I was able to copy and paste the "Date of most recent update (this needs to be a formula date field)" and it worked.  In addition, you are correct, I was only looking for the date and not date and time.  

    So this did exactly what I needed it to do.

    ------------------------------
    Danaya Bryant
    Business Analyst
    The van Halem Group
    Atlanta GA
    ------------------------------



  • 4.  RE: Formula Date Field to Extract Date from Log Entry Field?

    Posted 12-01-2022 15:48
    What do you do if the most recent entry is at the top of the list?

    ------------------------------
    Bryan Helgeson
    ------------------------------



  • 5.  RE: Formula Date Field to Extract Date from Log Entry Field?

    Posted 12-01-2022 15:50
    This formula will probably work to parse out the most recent entry. But I have not tested it in years.


    Trim(Right(Part(NotLeft([append at top],1),1, "["),"]"))

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 6.  RE: Formula Date Field to Extract Date from Log Entry Field?

    Posted 12-01-2022 16:10
    Where is a complete set of formula is when the append only newest entries are at the top is at the top.  

    Most Recent Entry
    Trim(Right(Part(NotLeft([append at top],1),1, "["),"]"))

    Date of Most Recent Entry
    ToDate(NotLeft(Left([append at top], " "),1))

    Name who did the most recent entry

    NotLeft(Left([append at top], "]"),11)

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------