Skip to main content
Question

Populating Date field if two fields match

  • December 1, 2023
  • 0 replies
  • 207 views
Forum|alt.badge.img+9

I am wondering if I can populate a date field if two other fields match?  I have a form where there is a checkbox if a "Coaching session" occured.  There is also a field for the "Topic" of the coaching session.  When the checkbox is checked the date for this record is populated in a Date/Time field.  

When a new record is created and the same Topic is selected I would like the date that the record (previous record) with the "Coaching session" checkbox checked populated in the new records "Coaching" date field.

Is this possible?

Thanks,

Brian



------------------------------
Brian Dunk
------------------------------
    This topic has been closed for replies.

    Forum|alt.badge.img+9
    • ChayceDuncan
    • Quickbase Alumni
    • December 1, 2023

    Couple different ways potentially depending on your structure, assuming that its just a simple text dropdown for Topic and not a table setup, I can think of (2) that would be the easiest. 

    Option 1: Use a Pipeline when the new record is saved and check to see if there was a prior record checked with coaching and the same topic. You can modify the query to match a specific person if these are for individuals, or whatever else you need. If you find a match, update the date on the new record. This will only work after the new record is saved, but same end result. 

    Option 2: Use a formula query to search for other records in the table for the same topic / coaching session / other values and get the value of the prior date. The query would search once all of your fields are filled out, and if it returns a value - you can have a form rule that copies the value from your query into the date field. The catch with this one is that you'll have to make sure you account for finding the most 'recent' date, in the event that you have 2 or more prior events, the query will return all of the dates that match. You can pluck out the first but would need to confirm/make sure that's the right date. One option in the formula would be to do something like: 

    Max(

    ToDate(Part($queryResult, 1,";")),

    ToDate(Part($queryResult, 2,";")),

    ToDate(Part($queryResult, 3,";")),

    ToDate(Part($queryResult, 4,";")),

    ...

    )

    Just cover enough iterations of the result to find the Max that way. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    Terms & ConditionsAccessibility statement