External uBAQ

Epicor ERP 10
1

I am trying to make an updateable external BAQ but its if failing to commit the updates.

what i have done so far:

  • create the external datasourse
  • create a BAQ and set it as updateable
  • in the update general properties set 2 fields to updateable.
  • on the Update processing tab i have selected advanced BPM update only option
  • then i clicked the BPM directive configuration button and created a base processing directive on the update method.
  • in the designer i added a field setter and set the field of the changed row to the ttfield value.
  • when i test the updateable section in the BAQ designer i can edit the fields and they change color to indicate they are dirty. I can press the update button and the fields change back to normal color and the new values stay there indicating the update was successful, however if i refresh the list the new values are gone and they are not in the extrenal DB either.

Has anyone got any hints?

Thanks
Brett

2

Does Epicor have rights to update the external datasource?

3

The user i setup the external datasource with can log into SQL and edit the data via SSMS so user rights is not an issue…

4

Epicor doesn’t have the built in logic to commit these changes you’ll have to write the C# code to go back lookup the record and update it. In the advanced BPM

-Jose

1 Like
5

OK got this working. Here’s how for future reference :slight_smile: And thanks to Jose for pointing me in the right direction.

On the BAQ Update tab set the General properties to select which fields are updateable.
In my case here it is RTP_STATUS and RTP_PO.

image

On the Update processing tab select Advanced BPM Update Only.

image
image.png733Ă—453 14.4 KB

Click on the BPM Directives Configuration… button. Add a base Processing BPM to the *.Update method.

image
image.png890Ă—431 22.2 KB

In the BPM add an Execute Custom Code node. Here is the code i used to find the updated rows and write the changed values to the external database.

try
{
	
	int rtpno=0,po=0, status = 0;
	string conStr = "user id=****;data source=***\\****;initial catalog=*****;password=*****";

	foreach(var row in ttResults.Where(r=>r.RowMod=="U"))
{
		// update the values in the database.
		using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(conStr))
		{
			conn.Open();
			using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("UPDATE RTP SET PO=@PO, STATUS=@STATUS  WHERE RTPNO=@RTPNO", conn))
			{ 
				cmd.Parameters.AddWithValue("@RTPNO", row.RTP_RTPNO);
				cmd.Parameters.AddWithValue("@PO", row.RTP_PO);
				cmd.Parameters.AddWithValue("@STATUS", row.RTP_STATUS);
				
				cmd.ExecuteNonQuery();
			}
		}
	}
}
catch (System.Data.SqlClient.SqlException ex)
{
	//Log exception
	//Display Error message
	 System.Diagnostics.Debug.Write (ex);
}

Cheers
Brett

EDIT: You will also need to add a using System.Data; to the code & import the System.Data assemble.

5 Likes
6

Have you tried deploying it as an updateable dashboard if it works there?