Automatic Incrementing of Row ID Numbers
john.ellis@tribridge.com asked 10 years ago
Hello:
A client and I were very successful in SmartConnect 2012 Node Maintenance, in adding the "Windows" Extender node to a Sales Order (invoice) map.
But, we could not find a way to have the Extender node's mapping automatically increment the Line Item Sequence Number of one of its required fields.
So, we simply hard-coded the line item sequence numbers in the source file as a column. Then, we were able to successfully import the data into both GP Sales Transaction Entry and eOne's Extender window for Sales Transaction Entry.
The client is OK with having to hard-code those row ID numbers, in the source file. But, it would have been nice if I could have figured out a way to add some sort of calculation or some value within the mapping of the field that would simply auto-increment those line item sequence numbers.
We tried both Custom Rolling Column and GP Rolling Column. Neither worked.
I can't remember the exact sequence number. But, similar to the "16384" line item sequence of sales line items in GP, the Extender window has a value like that where each succeeding line item is the preceding line item plus some constant value.
is there a calculation or something like that that can be put in place in the mapping?
Thanks!
John
A client and I were very successful in SmartConnect 2012 Node Maintenance, in adding the "Windows" Extender node to a Sales Order (invoice) map.
But, we could not find a way to have the Extender node's mapping automatically increment the Line Item Sequence Number of one of its required fields.
So, we simply hard-coded the line item sequence numbers in the source file as a column. Then, we were able to successfully import the data into both GP Sales Transaction Entry and eOne's Extender window for Sales Transaction Entry.
The client is OK with having to hard-code those row ID numbers, in the source file. But, it would have been nice if I could have figured out a way to add some sort of calculation or some value within the mapping of the field that would simply auto-increment those line item sequence numbers.
We tried both Custom Rolling Column and GP Rolling Column. Neither worked.
I can't remember the exact sequence number. But, similar to the "16384" line item sequence of sales line items in GP, the Extender window has a value like that where each succeeding line item is the preceding line item plus some constant value.
is there a calculation or something like that that can be put in place in the mapping?
Thanks!
John
In my data source I have a column named "Line" (which is 1,2,3,4 per distribution line) and then I add the following to my query:
SELECT [Line] * 16384 AS [DistSeq], * FROM [Sheet1$]
Then on the distribution I put in the DistSeq as a source. Works for the GL integrations where I have mulitple distribution lines.
Thanks, Sue!
But, is there a way of getting around having to put in a column for the source file?
John
It was the only way I could come up with a way to group the distributions and stil have the correct seq line numbering come up without the users having to remember how to increment. I don't know if anyone else has been able to come up with anything else. If anyone else has a different way to do it, would sure like to see it. Surprised that they don't have a function that would do the incrementation for distribution lines for GP.
I have tried making my source as ODBC text file, and tried using a select statement which works as a SQL Query in Mgmt Studio, but not in smartconnect. ERROR [42000] [Microsoft][ODBC Text Driver] Syntax error (missing operator) in query expression 'rownumber() over (order by a.[TxnDate] )' Does anyone know what the missing operator is?
SELECT rownumber() over (order by a.[TxnDate] )
FROM [AP_GLtrx.csv] a
Alyson,
While some SQL Commands are available for an ODBC Connection, some are not, such as the rownumber(). That type of function as well as other more complex functions are only available with certain ODBC Drivers.
The best option is to include the row number in the data source or use Global Variables to store the next number and reset the number when certain row values change.
Lorren