Patchy patching - some data not getting through

[Anonymous]

Morning all,

Hope you are all well.

I have built an app within which operators record their work. They log into the app, start a job, sometimes pause that job, and then finish it. I have two data tables in sharepoint, one which holds data they capture on the job 'Production Database' and one which records a time stamp at the start and end, and each time they pause 'Production Data Times' which allows me to see the amount of hours actually spent working on a job.

To start a job I use the following:

NewForm(Form_job_running);
SubmitForm(Form_job_running);
If(IsBlank(Form_job_running.Error),
     UpdateContext({JobStatus:"Running"});
     UpdateContext({JobRunning:true});
     Patch('Production Data Times',
	     {   Job_MO: label_jobmo_1.Text,
	         Start: Now(),
             Job_ID: DataCardValue20.Text,
             job_pause_count: DataCardValue5.Text
	     }
         );
     Patch('Production Database', First(Filter('Production Database',ID=Value(DataCardValue20.Text))),
	{Job_Start: Now()
	}
          );
Refresh('Production Database'))

The creates a new form, checks that theres no errors in submission and then patches some data to the 'Production Data Times' table. this function works perfectly as expected every time.

To pause a job, I use the following:

UpdateContext({JobSaving:true});
UpdateContext({JobStatus:"Paused"});
Patch('Production Data Times', First(Filter('Production Data Times',Job_ID=DataCardValue20.Text, job_pause_count=DataCardValue5.Text,End=Blank())),
	{End: Now(),
    Operator: dropdown_user.Selected.Value
	}
);

SubmitForm(Form_job_running);
UpdateContext({ReloadJob:false});
Refresh('Production Database');
Patch('Production Database',First(Filter('Production Database',ID=Value(DataCardValue20.Text))),
    {
    job_pause_count: DataCardValue5 +1
    }
    );
UpdateContext({JobSaving:false});
Navigate(Screen_mainmenu,ScreenTransition.Fade)

For some reason, the two patch functions fail to run sometimes. The first is supposed to use the unique record ID from 'Production Database' which I pull into powerapps to find the unique start record ascociated with this job and patch an end time. The second patch function is supposed to add one to the pause count (allowing the next resume to have a unique identifier). These functions work fine most of the time but there are a few instances where they simply dont seem to do anything even though the submit form goes through.

Looking at the two tables, all other data is there excpet the end time and operator. When the job is resumed it goes ahead and creates a new record but this old orphaned one remains. I cannot recreate the issue and cannot find anything in the forumulas to suggest there is anything wrong.

I wondered whether sometimes powerapps gets a bit carried away and skips functions, or whether maybe the submit form function stops the patch function before its finished. I wondered about adding some sort of delay function inbetween to rule this out but can't find anything.

Do you have any ideas?

Thanks in advance and apologies for the long post. If I had a potato I'd offer one.

[v-xida-msft]

Hi @Anonymous,

I think this issue maybe related to the SubmitForm() function. When the SubmitForm() function executes successfully, all Data Cards values reset to their default values within the EditForm of your PowerApps app. 

You could consider take a try to initialize a variable to store the Job_ID value and job_pause_count value within your PowerApps app.

The Onselect property of the Start job button set to following formula:

NewForm(Form_job_running);
UpdateContext({Job_ID_variable:DataCardValue20.Text});
UpdateContext({job_pause_count_variable:DataCardValue5.Text})
SubmitForm(Form_job_running);
If(IsBlank(Form_job_running.Error),
     UpdateContext({JobStatus:"Running"});
     UpdateContext({JobRunning:true});
     Patch('Production Data Times',
	     {   Job_MO: label_jobmo_1.Text,
	         Start: Now(),
             Job_ID: DataCardValue20.Text,
             job_pause_count: DataCardValue5.Text
	     }
         );
     Patch('Production Database', First(Filter('Production Database',ID=Value(DataCardValue20.Text))),
	{Job_Start: Now()
	}
          );
Refresh('Production Database'));

The OnSelect property of the Pause job button set to following formula:

UpdateContext({JobSaving:true});
UpdateContext({JobStatus:"Paused"});
Patch('Production Data Times', First(Filter('Production Data Times',Job_ID=Job_ID_variable, job_pause_count=job_pause_count_variable,End=Blank())),
	{End: Now(),
    Operator: dropdown_user.Selected.Value
	}
);

SubmitForm(Form_job_running);
UpdateContext({ReloadJob:false});
Refresh('Production Database');
Patch('Production Database',First(Filter('Production Database',ID=Value(DataCardValue20.Text))),
    {
    job_pause_count: DataCardValue5 +1
    }
    );
UpdateContext({JobSaving:false});
Navigate(Screen_mainmenu,ScreenTransition.Fade)

Please take a try with above solution I provided and let me know if it could solve you problem.

Best regards,

Kris

[Anonymous]

Hi Kris,

Thank you very much for taking the time to read through my post and suggest a solution.

The Job_ID fields on the form read directly from sharepoint so their default value is correct and not changed within powerapps. I like your suggestion of using a context variable, however the operators sometimes have more than one job running at a time and therfore the context variable would be overwritten with potentially the wrong data. Also, given this only happens rarely, if there was a problem with the submitform function, wouldn't it effect every submit?

I have a feeling that the submit form function is taking too long to complete and the app is giving up execuing the rest but I could be totally wrong.

Do you have any ideas?

[Anonymous]

bump?

[Anonymous]

Any other ideas?

[KroonOfficeSol]

Sorry, I didn't bother to read all the text, but I looked at your code. I do not understand the combination NewForm, SubmitForm and Patching in you're solutions why not just do only the Patch() formula. 

Further I would do the validating before the patch, instead off handeling errors on submitting wrong data. See my code below for an example.

UpdateContext({ValidateForm:true});

//Here your checks. Where errors, wil set the ValidateForm to false

If(ValidateForm,
    UpdateContext({JobRunning:true});
	Patch('Production Data Times',
		,Defaults('Production Data Times'),
		 {   Job_MO: label_jobmo_1.Text,
			 Start: Now(),
			 Job_ID: DataCardValue20.Text,
			 job_pause_count: DataCardValue5.Text
		 }
	);
	Patch('Production Database', LookUp('Production Database',ID=Value(DataCardValue20.Text)),
		{Job_Start: Now()
		}
	);
	Refresh('Production Database'))
)

Also I would skip the Jobstatus variable and just make a variable called JobPaused and set this to true/false. You then do you're logic on the JobRunning, JobPaused and other booleans. If there are to many options you should replace tehe text by a number, so 1 = New, 2 = Running, 3 = Paused, simple because is easier to check against an integer.

Other reasons for not patching:

• The ,Default(), is missing from the first Patch()
• Job_ID: DataCardValue20.TexT , Job_ID takes a integer I presume so then this should be 

  Job_ID: Value(DataCardValue20.Text)

• job_pause_count: DataCardValue5 +1, Here the ,Text is missing. Should be like 

  job_pause_count: Value(DataCardValue5.Text) +1

So Basically you should make the formula leaner to start with. What you can do to test is to split the formula up in part and place them under other buttons, which you then call with a select() formula. Know that when one line in you're formula breaks then the rest is not runned and by splitting it up you can adress this (so basically only a part off you're tasks will not run on an error)

Hope this helps.

Greatings,

Paul

[KroonOfficeSol]

I saw those two after my post and probably you want to set them on creating, which is the reason to newform and submitform. Maybe this works (add it after the patch):

UpdateContext({Job_ID_variable:Last('Production Data Times').ValueToStore});
UpdateContext({job_pause_count_variable:Last('Production Data Times').ValueToStore})

When it is possible that multiple records are patch on the same time, then you could check for the creator. Like:

UpdateContext({Job_ID_variable:Last(Filter('Production Data Times', CreatedBy = 0365ID).ValueToStore});
UpdateContext({job_pause_count_variable:Last(Filter('Production Data Times, CreatedBy = 0365ID').ValueToStore})

[Anonymous]

Hi Paul,

Thank you very much for taking the time to read through.

You are correct in your assumptions. The newform followed by a submit form create a unique entry in the sharepoint table. Each unique entry has a sharepoint ID, I am using this as my job ID to ensure each job is traceable across both tables.

The context variables cannot be combined as it exists that many different combinations of true/false may occur, it is not one or the other.

I will try adding defaults to the patches and then also adding the value function to my formulas and report back. Breaking the formula down won't work as it only fails 1/20 times (ish) and breaking the formulas apart it works every time.