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------------------------------

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------------------------------

Hi Chayce, lets try the pipeline option, however not too familiar with using pipelines. Few Questions: I added three fields from the table: Topic, Coaching Counsel Check, Coaching Counsel Date. Are there others I should include? What would the value be? If I am doing this correctly. What would next step in process be? ------------------------------Brian Dunk------------------------------

So the trigger would be the creation of the new record. You would probably do it on 'Record Added' and in it, you would need to include all of the fields that you intend to query / search on. Your next step would be a 'Search Records' - you want to check to see if there is a previous record that has the same criteria/match. You would set up the search to look for records that have the same topic/coaching, and then include another criteria that the Record ID# is not equal to the ID from Step A- the idea being that you want to omit the current record from being matched too. You will want as part of this search to return the 'date' field that you're wanting to copy as one of the fields to use in later steps. Pipelines will automatically give you a loop associated with the search, and inside that loop you will do an Update Record action, where you are going to point it at Step A (the new record). The only field you need to map the the date from your search - so something like this as a mockup: *your field is named something different I imagine but the gist is there. Then that's it - your matched record will update your new record with the prior date. ------------------------------Chayce Duncan------------------------------

Hoping you can read this: I feel like I am missing something: ------------------------------Brian Dunk------------------------------

I feel like I am missing the specifics of the Search: ------------------------------Brian Dunk------------------------------

Populating Date field if two fields match

19 Replies

ChayceDuncan
Qrew Captain
8 months ago
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
------------------------------
- BrianDunk
  Qrew Cadet
  8 months ago
  Hi Chayce, lets try the pipeline option, however not too familiar with using pipelines. Few Questions:
  
  I added three fields from the table: Topic, Coaching Counsel Check, Coaching Counsel Date. Are there others I should include?
  
  What would the value be? If I am doing this correctly.
  
  What would next step in process be?
  
  ------------------------------
  Brian Dunk
  ------------------------------
  - ChayceDuncan
    Qrew Captain
    8 months ago
    So the trigger would be the creation of the new record. You would probably do it on 'Record Added' and in it, you would need to include all of the fields that you intend to query / search on.
    
    Your next step would be a 'Search Records' - you want to check to see if there is a previous record that has the same criteria/match. You would set up the search to look for records that have the same topic/coaching, and then include another criteria that the Record ID# is not equal to the ID from Step A- the idea being that you want to omit the current record from being matched too. You will want as part of this search to return the 'date' field that you're wanting to copy as one of the fields to use in later steps.
    
    Pipelines will automatically give you a loop associated with the search, and inside that loop you will do an Update Record action, where you are going to point it at Step A (the new record). The only field you need to map the the date from your search - so something like this as a mockup:
    
    *your field is named something different I imagine but the gist is there.
    
    Then that's it - your matched record will update your new record with the prior date.
    
    ------------------------------
    Chayce Duncan
    ------------------------------

Forum Discussion

Populating Date field if two fields match