Filter values on SharePoint List and edit if value is found and if not found add a new record

[klllmmm]

I want to search based on text input within the SharePoint list's unique text value field (Employee Number).

If the value is found on the SP list, populate the entire row to the form and let the user edit/update the same.

And if such a value is not there in the SP list, then I would like to let the user insert a new record into the SP list through the same form.

Is this double?
Appreciate your kind input on doing this and any reference to a resource, youtube video is really appreciated.

Screen shot

[AJ_Z]

Hi @klllmmm 

From your description, it appears you are trying to perform a lookup in a SharePoint list based on a unique employee number, and then depending on the outcome of the lookup, either populate a form with existing data to be edited/updated or create a new entry if no matching record is found.

Here are a few steps you can do this with:

Set up a Lookup:
You can use the LookUp function to search for the employee number that matches the user's input. The syntax will look like this:

LookUp(YourSPList, EmployeeNumber = TextInput1.Text)

I assumed the name of the textinput and your sharepoint list please do update them.

Set up the Form:
Use the above lookup function in the Item property of your form. This will populate the form with the data from the found item. Make sure he forms datasource is still the sharepoint list.

Switching Form Modes:
You can set up the form mode dynamically based on whether the lookup finds a matching item. If it does, set the form to edit mode. If it doesn't, set the form to new mode. This can be done in the OnSelect property of a button, or directly in the form's DefaultMode property.

Here's an example of doing it in the DefaultMode property:

If(
  IsBlank(LookUp(YourSPList, EmployeeNumber = TextInput1.Text)),
  FormMode.New,
  FormMode.Edit
)

Updating or Creating the Record:
When the user submits the form (OnSelect property of the submit button), Power Apps will automatically handle either creating a new record or updating an existing one based on the form mode. Make sure the onselect of the button uses the SubmitForm Function.

I hope this is helpful

[AJ_Z]

Let me know if this worked and helped 🙂

[klllmmm]

1. I get an error in LookUp Function, as Data type: Record 

The sharepoint list employee number field is a single-line text type. And Text Input field is also a text field.
Appreciate any further input..

[v-mingkz-msft]

Hi @klllmmm,
Do you want to search the record based on the value of text input, and according to if the record is blank or not to decide the mode of form is edit or new?

@AJ_Z has provided a very correct solution

According to your screenshot, I assume that you set the Lookup formula to a Text Lable, however, this is expected to the Item property of Form.

Best Regards,
Kyrie

[klllmmm]

@AJ_Zand @v-mingkz-msft Thanks for your kind inputs!
@v-mingkz-msft, Final objective is correct.

Let me add a simplified example.
1. My SharePoint list is something similar to this;

2. And I have created a Canvas app with text input field "TextInputEmpNo_2"and also added a form using "Emp_List"

3. Then I tried to create a "LookUp" function to identify the EMP_Name value of the record that matches to EMP_Number of "A1001". but get errors
 
LookUp([Emp_List], EMP_Number.Value = TextInputEmpNo_2.Text ).EMP_Name

LookUp([Emp_List], EMP_Number.Value = "A1001" ).EMP_Name

What could be the reason for this error in LookUp function?

[v-mingkz-msft]

Hi @klllmmm,

Do you want to display the EMP_Name value of the record that matches to Lookup function?

If the column type is a single-line-of-text, you could try the formula:

1. Set the Text property of Label as:

LookUp([Emp_List], EMP_Number = TextInputEmpNo_2.Text ).EMP_Name

Best Regards,

Kyrie

[klllmmm]

Thanks @v-mingkz-msft !

I tried the formula you mentioned. Still, an error pops out.

Appreciate any further help on this

[v-mingkz-msft]

Hi @klllmmm,

Thanks for your detailed case description. I completely understand the situations and your feelings.

I have tested according to your description in my environment.

1. If add the '[]', it displays the similar error message.

2. You can try to remove it.

Best Regards,

Kyrie

[klllmmm]

Thanks, @v-mingkz-msft , LookUp function works now!!

Now I add a function to change the default mode of the form according to @AJ_Z ;

If(
  IsBlank(LookUp(Emp_List, EMP_Number = TextInputEmpNo_2.Text ).EMP_Name),
  FormMode.New,
  FormMode.Edit
)

1. When I enter an employee number already available in SP list the form shows as;

 
I want to show/populate values from sharepoint to the form so users can edit the same. How can I do this?

2. When I enter a new employee number that is not in SP list, it shows as;

By adding a button I can update the new values to SP list. So this part is fine. Only want to clear the value already in the "TextInputEmpNo_2" input field and pop in a new form.

SubmitForm(Form2_1);Navigate(Form2_1);NewForm(Form2_1)

Appreciate it if you can give any further input...