Setting GP Batch ID to Current Date with Sequence

When running “transaction type” integrations to Microsoft Dynamics GP for everything but Bank Reconciliation, those records require a “Batch ID” to be supplied.

Often times this is hard-coded in the map to whatever value is desired. Another popular method is “set the Batch ID to today’s date” in whatever format desired using a SmartConnect calculated field.

If you would rather watch a video explaining this, click here.

In this example, we want to set the batch date with a prefix – SC – and then today’s date. Notice that in both we format the date using the string format of “yyyyMMdd” for 4 digit year, 2 digit month (M is month, m is minute), and 2 digit day.

'CALC_DATE_AS_BATCH

Dim mydate as DateTime = DateTime.Now
return "SC_" & myDate.ToString("yyyyMMdd")

// CALC_DATE_AS_BATCH

DateTime myDate = DateTime.Now;
return "SC_" + myDate.ToString("yyyyMMdd");

We have seen a twist on the “today’s date” a few times before and once again recently. The customer did want to use today’s date in the Batch ID such as this, but the twist was that they wanted to sequence that value to keep multiple map runs as separate batches. With the code above, on every map run for the day the same Batch ID would be used. That’s fine if that’s what you want – but it wasn’t what was requested in this instance.

So, for example, we would want the batches to be created as SC2024082801, SC2024082802, SC2024082803 etc. Then the next day would be SC2024082901, SC2024082902, etc.

This does present a design challenge is that perhaps we’d have to keep track of the map runs for the day. We could create our own SQL table to write to and keep track of this – but that seems a bit overkill.

It seems easier that since successful batches would be created in GP – we would use that data to keep track of it for us. We just have to get it.

Because we want all the transaction run on each map run into the same batch – the easiest way to handle the batch calculation would be a map pre-task to run a SQL Script Task. We let SQL do the query to GP, find out of any have been created, and then increment the sequence number.

The SQL Task Script for this isn’t terribly complicated and the “incrementing” can be done easily using a GP SQL Function, so we don’t need to write it on our own. The commented SQL code for this is below.

declare @batchid varchar(15)
declare @maxbatch varchar(15)
declare @querybatch varchar(15)

/*batch id will be in form of SCYYYYMMDD*/
SELECT @batchid = rtrim('SC' + CONVERT(varchar(8), GETDATE(), 112))
select @querybatch = @batchid + '%'
select @maxbatch = max(rtrim(BACHNUMB)) from SY00500 where BCHSOURC = 'Sales Entry' and BACHNUMB like @querybatch

/* if we didn't find a batch at all then default to SCYYYYMMDD01 and return that result*/
if (@maxbatch is NULL)
begin
	select @batchid = rtrim(@batchid) + '01'
	select @batchid
end
else
begin 
	/* There was a batch already created for today and @maxbatch is set to that last value
	call the GP proc to increment it and then return the result */
	DECLARE	@return_value int,
			@IO_vDocNumber varchar(21),
			@O_iErrorState int

	SELECT	@IO_vDocNumber = @maxbatch

	/*use a GP function to sequence that returned "last batch for today" value */
	EXEC	@return_value = [dbo].[ivNumber_Inc_Dec]
			@I_tInc_Dec = 1,   /* increment the value */
			@IO_vDocNumber = @IO_vDocNumber OUTPUT,
			@O_iErrorState = @O_iErrorState OUTPUT

	SELECT	 @IO_vDocNumber
end

After creating a new User Defined Global Variable named GBL_BATCHID, I then edit the map to add the new “Before Integration” SQL Task.

Because we need the results of the “select”, we can choose the “update global variable with SQL command result” and choose our variable – GBL_BATCHID.

After saving this SQL Task – the last thing we need to do is create the Script Calculation that will get the global variable.

This is the VB.Net version, add a semicolon if using C# – that’s the only difference.

Map this new calculation to the Batch ID fields wherever required and test by running the map.

I ran the map three times and examined the output of the failed records so I could easily review. Or I could have queried SQL or ran the map to the eConnect XML destination for review.

We can see from the XML in the error for the 3rd run of this map and the new SQL task added (Run #1 was testing the map without this SQL code) that the BATCHNUMB is SC2024082803 which would be correct and validating that this is functioning as desired.