Note: dates in the following post are in the following format: mm/dd/yyyy.
I have a SharePoint list called Contracts for storing employee contracts. In that list, there are two columns StartDate and EndDate which hold the starting date and ending date of a contract.
I customized the Contracts list form using PowerApps.
Here is my scenario:
I create a new contract from 12/01/2018 to 12/31/2018 for John Smith
When I save this contract, in the OnSave event of the form I use a LookUp function to search in the Contracts datasource for a duplicate contract (meaning: another existing contract for John Smith that would overlapp the period from 12/01/218 to 12/31/2018).
Let's say the contract being created in step 1 is the first one. Since there are no duplicates, the contract is saved.
So now in my Contracts list I have one item for John Smith from 12/01/2018 to 12/31/2018.
Let's say that right after creating this contract (I do not refresh the list page) I click the New button again to create another contract for John Smith from 11/01/2018 to 12/15/2018 (which obviously overlapps the previous one)
When I click on the Save button, no duplicate contract is detected by my LookUp function because the Contracts datasource (probably cached) does not contains the first contract created at step 1 (even though it HAS been created in the list) -> OUCH !
Generally speaking, what I want to point out here is that, as far as you don't refresh a list page, all operations made on the list items are not updated in the datasource "variable" in PowerApps. This can lead to real problems like the one described above.
My question is:
Should I use the Refresh function on the Contracts datasource to make sure it is up-to-date before checking for any duplicates ? If so, this could lead to performance problem since my customer creates 700 contacts a month; so my list will grow significantly fast and performance will drop as time goes by.
Shouldn't PowerApps itself update the Contracts datasource after submitting it with success ?
Anyone has any idea how I could address this issue ?
How do you submit your form data into your SP list data source? Using SubmitForm function or Patch function?
Could you please share a bit more about your LookUp function within your app?
In default, after you submit the form data, the data source would be updated automatically. I think your thought is proper, the Refresh() function could achieve your needs, but it may cause a Performance issue (due to large data).
In addition, you could also consider take a try to create a Copy of your Contracts list. In other words, you could consider create a Contracts-Copy list (which has same data structure with your Contracts list) on your side, then when you submit your form data into your Contracts list, you should also save the form data into the Contracts-Copy list using Patch function. The standard formula format of Patch function as below:
'Contracts-Copy', /* <-- a Copy list of your Contracts list */
EmployeeName: DataCardValue1.Text, /* <-- EmployeeName column used to store the employee name, e.g. 'John Smith'*/
After that, you could use the Contracts-Copy list as a data source within your LookUp function instead of your Original Contracts list, check if there is already one contract created for the employee in the Contracts-Copy list. If yes, delete the previous contract within both SP list (Contracts list and Contracts-Copy list), and create the new contract within both SP lists for that employee.
More details about the Patch function, please check the following article: