Automatic status change and disable form fields

[joshieboy]

Hello,

I have a form that has two status fields - one called "Form Status" and another for "Approval Status".  Form Status can be either Draft or Submitted and can automatically set to Draft when a user clicks the Save and Continue later button on the form.  This works well as the form is very involved and may require a user to take days or even weeks to complete.  Once its saved, they get a url of the form so they can continue from where they left off and Save and continue later for any number of times.  Only when all mandatory fields are filled can they click Submit form.  When they click Submit form, the form status changes from Draft to Submitted.  This also works well and sends an email to the parties for review of the submitted form.   

The Approval Status field has the following options:

• Prequalification Information submitted ( this is the default option when a form is saved on the system) so Form Status will be Draft and Approval Status will be Prequalification Information submitted
• Review in Progress (I would like the system to set this status automatically once the Form Status field is = Submitted, and also prevent the user from making further edits to the form.  I am not sure if there is a way to disable all fields from the user if the statuses are If Form Status = Submitted and Approval Status = Review in Progress then disable all fields from the user, or if there can be a screen that loads to let them know the form is currently under review and no further edits can be made at this time.  
• Request additional information -(The approver should be able to manually set this from the approval status field) If the approver sets the status to this, it should reopen the form for editing.  It would be nice to also have a field where they can enter what is required and that be stored as a treaded discussion for auditing purposes.  Hoping this is not too much to ask here
• Buyer Successful (The approver should be able to manually set this from the approval status field)
• Buyer Unsuccessful (The approver should be able to manually set this from the approval status field)

The system should also disable the form from further edits once the status is Buyer Successful or Buyer Unsuccessful.  

Would be very grateful how to approach the above.  Thanks in advance.

[SolTeferi]

Hi @joshieboy ,

To enable and disable the form and the fields based on your requirements, please use the following. 

If((LookUp(YourDataSource,ID=Value(IdFieldOnForm.Text)).'Form Status'="Submitted" && LookUp(YourDataSource,ID=Value(IdFieldOnForm.Text)).'Approval Status'="Review in Progress"  && CountRows(Filter(ApproversList,ApproverPersonColumn.Email  =  User().Email))<1) || LookUp(YourDataSource,ID=Value(IdFieldOnForm.Text)).'Approval Status'="Buyer Successful"  || LookUp(YourDataSource,ID=Value(IdFieldOnForm.Text)).'Approval Status'="Buyer Unsuccessful"     ,    FormMode.View,FormMode.Edit)

This assumes that your data source is a SharePoint list. Please do the following before using the above:

• Replace "YourdataSource" by the actual name of your list name.
• Add a field on your form and set it to the ID of the item
• Create a new list with a list of your approvers. One of the columns should be a person field for the approvers. Then replace "ApproversList" in the formula above by the name of the list you created. And, replace "ApproverPersonColumn" by the name of the person field holding the approvers

I did not include anything for your question on creating a history/log of conversation between the approvers and users. However, if you create a new topic with that question, I can give you a detailed method on how to achieve that a log containing the date, the name of the user and the content of the conversations. I think you asked multiple questions on a single post here. So, please post your other question on a different post.

[joshieboy]

Hi @SolTeferi - really appreciate the feedback.  I will post the other question now, and also try the above.  I do have a separate list of approvers so based on your steps above things are looking good.  Will go through this step by step now and update this feed with my progress.  Thanks again.

[joshieboy]

@SolTeferi - hello, exactly where am I putting the code above?  Thanks.

[SolTeferi]

Hi @joshieboy ,

I forgot to mention that. The above code goes to the DefaultMode property of the form.

[joshieboy]

Thank you @SolTeferi 

I created a text field but am struggling to know how to set it to the form ID.  All other areas above completed but this.

[SolTeferi]

Hi @joshieboy ,

All you have to do is insert the ID field from the "Fields" pane. Here are the steps to do that:

1. Make sure the form is selected (One way to tell the form is selected is by looking at the control name at the top right corner. You should see the name of the form there)
2. Click on "Edit Fields" on the right pane. A "Fields" pane opens.
3. Select or Check the "ID" field from the list of choices and click "Add". This will add the "ID" field/data card to your form.
4. Once the ID field/data card is added, Copy the name of the ID field (not the data card) you just added
5. Replace the "IdFieldOnForm" in the formula by the name you copied in step 4
6. You can then hide the ID field. To do that, set the visible property of the ID field data card to false. That is it.

[joshieboy]

Thank you @SolTeferi - I thought it was that but was not sure.  Got through with this piece.

I completed the formula as per below and as per attached got one error message with the email part I believe.  Would appreciate some guidance here.:

If((LookUp('Buyer Pre-Qualification Questionnaire',ID=Value(DataCardKey142.Text)).'Form Status'="Submitted" &&
LookUp('Buyer Pre-Qualification Questionnaire',ID=Value(DataCardKey142.Text)).'Pre-Qualification Status'="Review in Progress" && CountRows(Filter('EMT Portal Pre Qual Admins',Admin.Email = User().Email))<1) ||
LookUp('Buyer Pre-Qualification Questionnaire',ID=Value(DataCardKey142.Text)).'Pre-Qualification Status'="Buyer Successful" || LookUp('Buyer Pre-Qualification Questionnaire',ID=Value(DataCardKey142.Text)).'Pre-Qualification Status'="Buyer Unsuccessful" , FormMode.View,FormMode.Edit)

[SolTeferi]

Hi @joshieboy ,

That is because, you are using the "Data card key". You should use "Data card value". So, replace the DataCardKey142 in all 4 places by DataCardValue142. But, make sure the spellings match what you have on the form. Then, check if there is any other error.

If you still see any error, that may be due to  some of your sharepoint columns being choice fields which require a ".Value" in the formula. So, if that is the case, use the following.

If((LookUp(YourDataSource,ID=Value(IdFieldOnForm.Text)).'Form Status'.Value="Submitted" && LookUp(YourDataSource,ID=Value(IdFieldOnForm.Text)).'Approval Status'.Value="Review in Progress"  && CountRows(Filter(ApproversList,ApproverPersonColumn.Email  =  User().Email))<1) || LookUp(YourDataSource,ID=Value(IdFieldOnForm.Text)).'Approval Status'.Value="Buyer Successful"  || LookUp(YourDataSource,ID=Value(IdFieldOnForm.Text)).'Approval Status'.Value="Buyer Unsuccessful"     ,    FormMode.View,FormMode.Edit)

Also make sure the spellings of the following exactly match your records:

• Your data sources (Buyer Pre-Qualification Questionnaire and EMT Portal Pre Qual Admins)
• Your column names (Form Status, Pre-Qualification Status, Admin)
• Your column values (Submitted, Review in Progress, Buyer Successful, Buyer Unsuccessful)
• Your form field names (DataCardValue142)

If you still see any error, click in side the formula to show the red underlines and send me a screenshot of that.

[joshieboy]

Hi @SolTeferi - apologies for the delayed response.  I thought that "value" was supposed to be the option but hesitated.  Since making the change, those errors are gone.  Regarding the choice fields, both "Form Status" and "Pre-Qualification Status" are choice fields in the SharePoint list so your suggestion of .value to the formula also works.  Thanks.    

As per attached, in a previous application I built, somehow and I don't know if its how our O365 is configured, but .email always gives an error so I usually would use .DisplayName.  I did so as per attached changing up the .email from your original formula and there is probably one more error based on that.  There are no more errors in the App Checker but a warning as per attached warning.jpg.  I believe once this is resolved it should work, as I did try opening a completed form as a guest user, and was still able to edit.  Also, please note:  The formula was included only on the Edit form, as this needs to be triggered when a user tries to edit a submission.

Below is my revised formula:

If((LookUp('Buyer Pre-Qualification Questionnaire',ID=Value(DataCardValue142.Text)).'Form Status'.Value ="Submitted" && LookUp('Buyer Pre-Qualification Questionnaire',ID=Value(DataCardValue142.Text)).'Pre-Qualification Status'.Value ="Review in Progress" && CountRows (Filter('EMT Portal Pre Qual Admins',Admin.DisplayName = varUser.FullName, Admin.DisplayName))<1) ||
LookUp('Buyer Pre-Qualification Questionnaire',ID=Value(DataCardValue142.Text)).'Pre-Qualification Status'.Value ="Buyer Successful" || LookUp('Buyer Pre-Qualification Questionnaire',ID=Value(DataCardValue142.Text)).'Pre-Qualification Status'.Value ="Buyer Unsuccessful" , FormMode.View,FormMode.Edit)