Need to update value of a choice field based on whether people field is blank or not

[Nuke_It_Newport]

Good morning everyone!

I have a form with the following:

• A data card 'DC_AssignedTo', that contains a combo box 'CB_AssignedTo', which updates a people column in the SharePoint list.
• A data card 'DC_Status', that contains a combo box 'CB_Status', which updates a choice column in the SharePoint list.

  I have the following code in the OnChange event of CB_AssignedTo:

// Store the value of CB_AssignedTo before the OnChange event
UpdateContext({
    PreviousAssignedTo: ThisItem.AssignedTo
});

// Store the value of CB_Status before the OnChange event
UpdateContext({
    PreviousStatus: ThisItem.Status
});

// OnChange event of CB_AssignedTo
If(
    IsBlank(PreviousAssignedTo) && !IsBlank(ThisItem.AssignedTo), // If CB_AssignedTo was blank before and now contains a value
    Patch(
       SRC, // Name of your SharePoint list
        ThisItem, // Patch the current item
        {
            Status: {Value: "In Progress", Result: "In Progress"} // Set the choice column to "In Progress"
        }
    ),
    If(
        !IsBlank(PreviousAssignedTo) && IsBlank(ThisItem.AssignedTo), // If CB_AssignedTo was not blank before and now is blank
        Patch(
            SRC, // Name of your SharePoint list
            ThisItem, // Patch the current item
            {
                Status: {Value: "Not Assigned", Result: "Not Assigned"} // Set the choice column to "Not Assigned"
            }
        )
    )
);

What I need to happen is as follows:

•  If CB_AssignedTo was blank before the OnChange event, and now contains a value, set the value of CB_Status to "In Progress".
• If CB_AssignedTo was not blank before the OnChange event, and now is not blank, do not change the value of CB_Status.
• If CB_AssignedTo was not blank before the OnChange event, and now is blank, set the value of CB_Status to "Not Assigned".

Currently, this code is not changing the value of the Status field regardless of which of the three conditions above is true. Can anyone help me with this? Thank you!!

[LaurensM]

Hi @Nuke_It_Newport,

The issue was caused due to the If condition checking the previous person value twice:

ThisItem.AssignedTo is evaluated twice - directly via ThisItem.AssignedTo and indirectly via the variable storing the same value. If you want to check the currently selected combobox value, we can use Self.Selected instead.

Additionally I have made some adjustments to improve readability & avoid code duplication:

// OnChange event of CB_AssignedTo
Patch(
    DATASOURCE, // Name of your SharePoint list
    ThisItem, // Patch the current item
    If(
        IsBlank(ThisItem.AssignedTo) && !IsBlank(Self.Selected), // If CB_AssignedTo was blank before and now contains a value
        {
            Status: {Value: "In Progress"},  // Set the choice column to "In Progress"
            AssignedTo: Self.Selected //Patch person column
        },
        !IsBlank(ThisItem.AssignedTo) && IsBlank(Self.Selected), // If CB_AssignedTo was not blank before and now is blank
        {
            Status: {Value: "Not Assigned"}, // Set the choice column to "Not Assigned"
            AssignedTo: Self.Selected //Patch person column
        }, //Else the status does not change & only patch the person column
        {
            AssignedTo: Self.Selected //Patch person column
        }
    )
)

Alternatively, you could also change the status during Form submission instead of a separate Patch during the Person combobox OnChange. This approach would require an adjustment in the Status DataCard Update property:

If(
    IsBlank(ThisItem.AssignedTo) && !IsBlank(CB_AssignedTo.Selected),
    {Value: "In Progress"},
    !IsBlank(ThisItem.AssignedTo) && IsBlank(CB_AssignedTo.Selected),
    {Value: "Not Assigned"},
    //Change to correct combobox name
    StatusCombobox.Selected
)

If this solves your question, would you be so kind as to accept it as a solution.
Thanks!

[Nuke_It_Newport]

Thank you! When I implement your first solution, I get the following error:
"The function 'Patch has some invalid arguments.

Invalid argument type. Expecting  a Record value, but of a different schema.

Missing column. Your formula is missing a column 'Claims' with a type of 'Text'."

I'm going to try your second solution and see if that works for me.

[Nuke_It_Newport]

I am still attempting to use your first code snippet. I should add, I have a button on the form, IC_SubmitFormButton, with the following code in the OnSelect property:

If(
   !EditForm.Error && 
    SubmitForm(EditForm),
    Navigate(SelectScreen, ScreenTransition.Fade)
);

With that, do I need to patch, or can I update the values for 'Status' and 'AssignedTo' on the form and then submit the form? Perhaps something like this? (I know this code isn't correct, but it's the closest I could come up with.

UpdateContext({
    SelectedUser: {
        DisplayName: CB_AssignedTo.Selected.DisplayName,
        Claims: "i:0#.f|membership|" & Lower(CB_AssignedTo.Selected.Mail),
        Department: "",
        Email: Lower(CB_AssignedTo.Selected.Mail),
        JobTitle: "",
        Picture: ""
    },
    StatusValue: If(
        IsBlank(ThisItem.AssignedTo) && !IsBlank(CB_AssignedTo.Selected),
        "In Progress",
        If(
            !IsBlank(ThisItem.AssignedTo) && IsBlank(CB_AssignedTo.Selected),
            "Not Assigned",
            ThisItem.Status
        )
    )
});

[LaurensM]

Hi @Nuke_It_Newport, 😊

Thanks for the additional info.

Using 'Self.Selected' in the first code block, we expect that the AssignedTo Items property has not been changed. In other words, the Items property should still be original Choices(Datasource.AssignedTo) which has the expected person record schema.

Would it be possible to share the AssignedTo combobox Items property.

Thanks!

[Nuke_It_Newport]

Thank you. Here's the 'Assigned To' combo box Items property:

Office365Users.SearchUser(
    {
        searchTerm: CB_AssignedTo.SearchText,
        top: 5
    }
)

[LaurensM · Accepted solution]

@Nuke_It_Newport,

Thanks for the code snippet. The Office365Users API returns a record with a different schema, causing the error.

Since you are using SubmitForm, I think the best approach would be (1) adjusting the AssignedTo Datacard Update property to create the correct record schema & (2) prefilling the Status field based on the AssignedTo selection.

(1) AssignedTo Datacard Update property

If(
    !IsBlank(CB_AssignedTo.Selected),
    {
        DisplayName: CB_AssignedTo.Selected.DisplayName,
        Claims: "i:0#.f|membership|" & Lower(CB_AssignedTo.Selected.Mail),
        Department: "",
        Email: Lower(CB_AssignedTo.Selected.Mail),
        JobTitle: "",
        Picture: ""
    }
)

(2) Status combobox DefaultSelectedItems property

If(
    IsBlank(ThisItem.AssignedTo) && !IsBlank(CB_AssignedTo.Selected),
    {Value: "In Progress"},
    !IsBlank(ThisItem.AssignedTo) && IsBlank(CB_AssignedTo.Selected),
    {Value: "Not Assigned"},
    Parent.Default
)

With this approach the AssignedTo OnChange can be left blank and the Status Datacard Update property remains:

StatusCombobox.Selected

It is important to note that you would preferably set the status combobox displaymode to view. This would avoid users overwriting the prefilled status.

I hope this helps!

[Nuke_It_Newport]

Thank you. I've changed these properties. The following code throws a 'circular reference' error:

If(
    IsBlank(ThisItem.AssignedTo) && !IsBlank(CB_AssignedTo.Selected),
    {Value: "In Progress"},
    !IsBlank(ThisItem.AssignedTo) && IsBlank(CB_AssignedTo.Selected),
    {Value: "Not Assigned"},
    Parent.Default
)

Any ideas how to correct this?

I can set the status combobox displaymode to view. There's a third choice for this field of "Completed". The end user will populate the form, and when they submit the form, I will need to automatically change the status to "Completed" and set the current time and date on the field SRCCompDate.

[LaurensM]

@Nuke_It_Newport 

The circular reference may be caused due to other code within your form, normally the error will guide you to the circular reference. Could you share the exact error message?

With the current code, the combobox will be prefilled with either status and that respective status will be patched to the Datasource. The data will only be saved once the Form is submitted - does this mean the status should always be set to 'Completed' when submitting the Form?

If this is correct, why are the other statuses needed since the data is only saved on form submission? (As I understand it currently: the other statuses will never exist in your list)

[Nuke_It_Newport]

Thanks for your help, and your patience! I am still deciding how the end user will interact with the app and the edit form. My thoughts are as follows: The end user selects the record, which will initially have a status of "Not Assigned", then navigates to the edit form. The user assigns the record to themself with the field "AssignedTo". When this action happens, the status is set to "In Progress". There may be a period of days before the user completes the form. Once the user completes the form and acknowledges completion of the form through some method such as a checkbox control, then clicks the "Submit Form" button, the field "SRCCompDate" will populate with the current date and time, and the "Status" field will be set to "Completed".

I've attached screenshots of the code that is throwing the error, as well as the error message.