Populate DataCard with Date (point in time) based on another DataCard selection

[simms7400]

Hi Folks -

I have a need to populate a DataCard with the date at the time another DataCard has a value of "Terminated" selected.  My issue is if i use the Now() function, anytime I open the Form it will revert the current date, rather than when "Terminated" was selected.

Is there a way around this? Thanks!

[mdevaney]

@simms7400 

Put this code in the onChange property of your Status combobox.  Replace your_current_id with a reference to the unique id number of the record.

If(
    DataCardValue_Status.Selected.Value="Terminated",
    Patch(
        your_datasource_name,
        ID = your_current_id
        {your_field_name: Now()}
    )
);

// refresh of datasource may be necessary
Refresh(your_datasource_name);

Then use this code in the Default property of the Card.

ThisItem.your_field_name

That should be it!  Let me know if you have questions.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[v-xida-msft]

Hi @simms7400 ,

Could you please share a bit more about your scenario?

Do you set the Default value of the Date time field Text Box or the DefaultDate of the Date time field DatePicker to Now() formula?

I have made a test on my side, please consider take a try with the following workaround:

Set the Default property of the Date time field Text Box or the DefaultDate of the Date time field DatePicker to following:

If(
   EditForm1.Mode = FormMode.New,
   If(
      AnotherFieldDropdown.Selected.Value = "Terminated",
      Now()
   ),
   ThisItem.DateTimeField
)

Note: The "DateTimeField" represents the date time field from your data source.

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[simms7400]

@v-xida-msft  Dont think that would work as the Now() function would return the current date anytime I opened the form if the value was "Terminated"...

@mdevaney 

Thank you!  This seems logical to me. However, I'm getting some errors and not sure why:

If(
    DataCardValue35.Selected.Value="Terminated",
    Patch(
        rdAssets,
        ID=DataCardValue53.Text,
        {Termination_Date: Now()}
    )
);

[v-xida-msft]

Hi @simms7400 ,

I think you have some misunderstanding with the solution I provided. The If(

EditForm1.Mode = FormMode.New,

,

..) formula would check current Edit form is in New mode (create entry) or Edit mode (exit existing entry).

When the current Edit form is in New mode, it would continue detecting if the DataCardValue35 selected value is "Terminated", it yes, execute Now() function, if no, do nothing (remain blank within the Termination_Date Box).

When the current Edit form is in Edit mode, it would retrieve stored Termination_Date value from your data source, and display it within the Termination_Date data card rather than execute the Now() function.

In addition, you should modify your Patch formula as below:

If(
    DataCardValue35.Selected.Value = "Terminated",
    Patch(
        rdAssets,
        LookUp(rdAssets, ID = Value(DataCardValue53.Text)),   // Modify here
        {
           Termination_Date: Now()
        }
    )
);

Best regards,

[simms7400]

@v-xida-msft  Oh I get it, I will try that, thank you!

Your suggestion to my patch function worked!  I am seeing a weird behavoir though.  When I select "Terminated", my Terminate_Date field populated correctly...However, after the patch runs it reverts it back to the Default value. Why?

On my  DataCardValue53, I have the DefaultSelectedItems set to this:

Coalesce(Parent.Default,First(Choices(rdAssets.Portfolio_x0020_Status)))

Could this be affecting it in that way?  Thank you!

[v-xida-msft]

Hi @simms7400 ,

Based on the issue that you mentioned, I think it is related to Patch formula execution. When you run your Patch formula successfully in your app, the rdAssets data source (SP List) would be refreshed in your app, so the following formula would be re-calculated:

Coalesce(Parent.Default,First(Choices(rdAssets.Portfolio_x0020_Status)))

As an fixed solution, you could consider save the selected 'Portfolio Status' value into your SP List along with the Terminate_Date value. So please modify your Patch formula as below:

If(
    DataCardValue35.Selected.Value = "Terminated",
    Patch(
        rdAssets,
        LookUp(rdAssets, ID = Value(DataCardValue53.Text)),   // Modify here
        {
           Termination_Date: Now(),
           'Portfolio Status':  {
                                   Value: "Terminated"
                                }     
        }
    )
);

Note: I assume that the Portfolio_x0020_Status column is a Choice type column in your SP List.

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[simms7400]

@v-xida-msft  Thank you.

Unfortunately, that didn't work.  IN fact, when I added the new piece of code, Terminate_Date no longer gets populated and it still refreshes to the default value.

Any ideas?

Thanks!

[v-xida-msft]

Hi @simms7400 ,

Do you mean that the Terminate_Date field value would be reset to Default value when your Patch formula executed successfully?

Do you change the status value when you edit an record or create a new record?

Could you please share a screenshot about your Edit form's configuration?

Please consider take a try with the following workaround:

Please modify your formula as below:

If(
    DataCardValue35.Selected.Value = "Terminated",
    Set(
      StoredTerminatedValue,
      Patch(
           rdAssets,
           LookUp(rdAssets, ID = Value(DataCardValue53.Text)),   // Modify here
           {
            Termination_Date: Now()
           }
    )
   )
);

Set the Default property of the Date time field Text Box or the DefaultDate of the Date time field DatePicker to following:

If(
   EditForm1.Mode = FormMode.New,
   If(
      AnotherFieldDropdown.Selected.Value = "Terminated",
      Now()
   ),
   If(
      !IsBlank(StoredTerminatedValue),
      StoredTerminatedValue.Termination_Date,
      ThisItem.Termination_Date
   )
)

Set the OnSelect property of the "New" button (click it to create a new record) to following:

Set(StoredTerminatedValue, Blank());
NewForm(EditForm1);
Navigate(EditScreen)

Please share a screenshot or GIF about the issue within your app, then we could understand the issue that you mentioned better.

Best regards,

[simms7400]

@v-xida-msft 

Hi!  The first slide of the attached PowerPoint is a screen recording of issue #1.  Where when I specify "Terminated", the "Terminate_Date" fields populates but then reverts "Terminated" back to the Default.

The second slide of the attachment happens with your suggestion to then reset the Status field to Terminated. But as you can see, it doesn't even populate "Termination_Date" and then it still reverts the Status field back.

And again, the DefaultSelectedItems property of the Status field is as follows:

Coalesce(Parent.Default,First(Choices(rdAssets.Portfolio_x0020_Status)))

Please let me know your thoughts, thank you!!