|
|
This blog entry does two things - first, it shows a view that brinks in 401K detail for a company. The view (obviously) could be altered to bring in different pay codes.
Second, it shows a cool CTE technique that can be modified to work for any view; and it's worth reading the code just to see how it's done.
We talk multiple data sets in a CTE, all comma separated, and then merge them into the final output.
Normally I'd use temp tables, but that's not available in the simple view format (but would be in the more complicated view format)
|
|
|
|
| This is a working snippet from an Integration Manager Payroll integration. I'm posting it mainly as an example of data access, but it also shows how to access fields in a Payroll integration |
|
|
|
| Below is a line to the VS Tools Programmers Guide for GP10 |
|
|
|
| This is the ddf_ColumnType script, needed in other scripts |
|
|
|
The Manufacturing Automated Data Collection form opens with the time field set to midnight. This mod will put a time in there.
|
|
|
|
BACK UP YOUR DATABASE FIRST
This article represents several days work. I'm sure that you won't want to read through the code, but read enough that you'll remember where you saw it. That way when you need it a year from now you'll remember where you saw it.
This week I encountered a Dynamics GP SQL server set to 'BINARY' collation. I tried to use it but as I loaded my library code onto the box to do an integration, none of it was running. The stored procedures wouldn't even create. Grrrr.
The resulting solution is below. There are three basic steps:
- Set the SERVER collation
- Set the DATABASE collation
- Set the TABLE FIELD collation (each text field in each table is assigned its own collation)
I didn't need to do the first, the consultant on the job had already fixed the SERVER collation; so the code below does not do that. But you'll need to do that first.
So, my task was to set the DATABASE and FIELD collation. There is a method that I see described that involves using BCP to export all the data, then fixing the install and then BCPing the data back in. I came across this solution before I found help with the BCP method, so that's what I used.
I got the method (and most of the code) from
http://sqlmag.com/database-performance-tuning/seven-step-process-changing-database-collation
I fine tuned it for use with Dynamics GP, and my resulting code is easier to use for a GP db. But props to SQLMAG, their code is great, and the concept came from them.
The work is not in the 'change collation' commands, they're easy. The work is in scripting all the indexes, constraints, views, and table valued functions that need to be dropped and recreated to be able to run the 'change collation' commands.
One final note. The code below consists of the first main script and a number of following scripts that handle various 'drop and recreate' tasks. The scripts are numbered 020, 030, 040, etc. They each produce two resulting scripts: a drop script and a create script. I named all those '020 drop' and '020 recreate', etc. Make sense?
Ready? Here we go. HTH.
|
|
|
|
Hi,
We have a Smartlist builder problem - user wants to add a column from a table on Server A to an existing Smartlist that uses tables on Server B.
Is this possible ? We don't see where it lets you cross servers. |
|
|
|
We have installed the Econnect service reference on a C# solution. The C# application will only build if it is run from the server where GP is installed and the /mex library exists. We are developing on local machines and uploading the code to the server. However, when we try to build on the local machine, it is unable to reach the Service Reference since it is not available to the local machine. Any ideas on how to connect to this reference from a local machine? Any other workarounds to be able to continue to develop on a individual machine and build for testing?
|
|
|
|
I have a method in a Dynamics GP Addin. The method is suppose to substitute accounts based on a the client's account pattern.
When I issue a ChangeNext() command, I get back "DoubleLock" as the return value; but only the second time I issue the command. I understand that this means that I have attempted to lock a record that has already been locked. How do I unlock that record? Issuing Release() on the table prior to executing the command does nothing. How do I unlock a record in GP
private void UpdateInvoiceLineWork(string invoiceNumber, Dictionary < int, int > replacements) {
if (!String.IsNullOrEmpty(invoiceNumber)) {
IvcLineWorkTable _lineWork = Dynamics.Tables.IvcLineWork;
_lineWork.Release();
_lineWork.Key = 5; //select key group five, which allows us to use just the invoice number
_lineWork.Clear();
_lineWork.InvoiceNumber.Value = invoiceNumber;
_lineWork.RangeStart();
_lineWork.Clear();
_lineWork.InvoiceNumber.Value = invoiceNumber;
_lineWork.RangeEnd();
TableError _result = _lineWork.ChangeFirst();
if (_result == TableError.NoError) {
do {
string _itemNumber = _lineWork.ItemNumber.Value;
if (replacements.ContainsKey(_lineWork.CostOfSalesIndex.Value)) {
_lineWork.CostOfSalesIndex.Value = replacements[_lineWork.CostOfSalesIndex.Value];
_lineWork.Save();
}
_result = _lineWork.ChangeNext();
}
while (_result == TableError.NoError);
}
_lineWork.Close();
}
} |
|
|
|
I am creating a new Smartlist in GP, and I want to use a new SQL view and table.
Both the view and table are in the same database where my other GP tables are located, yet they do not show up in the list of tables or views that are available when I try to select a table for the Smartlist.
I'm assuming this is a permissions issue - with me as a user or the table and view?
|
|
|
|
Hi everyone,
When I attempt to post a Fixed Assets Batch, I get the message "You can not distribute to an inactive account"
So I ran an edit list [Fixed Assets Posting Edit List] to identify the inactive accounts but there are no indication of whether the account is active or not in the report. So then I edited the report in Report Modifier and added "Active" field from Accounts Master but when I run the report, it shows just an 'X' next to the account string. The whole report is 418 pages and it's not easy to identify them all.
So I exported the report into Excel as CSV but the report is messy and no way to sort them.
Any ideas how I can identify the accounts from the edit list whether they are active or inactive?
I am running GP 2013 R2
Thanks
Paul Chacko |
|
|
|
existe alguna ventana para hacer listas de empaque en GP 10.0?
existe alguna ventana para hacer listas de empaque en GP 10.0? |
|
|
|
I need to develop a join that can get me data from the 20K tables onto my 30K tables. I can't use a UNION to pull in the data because then I'll get duplicates.
On another note. Since the 20K tables are for the sub ledgers (still learning these terms so forgive me if I call them by the wrong names), then does that mean that all the transactions in the 20K tables should be encompassed in the 30K tables? I'm facing a situation where I believe that there is a record in my 20K table that isn't in the 30K table.
Lastly, do 20K tables have a history table? |
|
|
|
Hi ,
I am new to econnect. I have two sites for example with multiple bin in it. Example I have site Returns with R1 BIN, Another Site NJ305 with RCV bin.
I want to transfer inventory from 'Return' site 'R1' bin to 'NJ305' site 'RCV' bin. How will be the xml structure for ecconect .Can you please provide an example for that.
Thanks,
Priya |
|
|
|
Hello,
I am developing bin to bin transfer module using econnect. I checked the xml nodes and saw that taIVMultibinBinToBinTransfer fulfills my requirements.
I mostly understand the elements (inputs) of that node but Bin_XFer_Doc_Number is something that I don't understand.
According to Microsoft documentation, it is required one, however It also seems like a field which is a primary key of a table.(maybe IV30004 )
I set a value and It throwed "Transfer Doc Number already exists" error.
When I tried to set a value which is different than the ones stored in IV3004, I got this exception "The conflict occurred in database \"F", table \"dbo.IV30004\", column 'Bin_XFer_Date'.\r\nThe statement has been terminated"
Can somebody help me what information should I pass this field ?
On the other hand,
Bin_XFer_Date = DateTime.Now.ToString();
is this correct usage for C#.
Sincerely |
|
|
|
Saludos,
Necesito saber si existe alguna forma de extraer el código que se encuentra encapsulado en un arcivo .CNK..
debo resolver un problema y para poder comprenderlo debo conocer cono es funciona el código encapsulado en un archivo .CNK..
I need to know if there is any way to extract the code that is encapsulated in a .CNK ..
I have to solve a problem and to understand it, I must know how the encapsulated code works in a .CNK file
Saludos,
Gracias |
|
|
|
I am trying to add a new field within Purchasing Check Remittance for EFT within Report Writer.
Since this is not a reprint this will need to be PM OPEN TRANS HDR FILE(tbl) is that correct? then associate as a relationship to the remittance report, is that correct?
Are there any alternate methods to Report Writer?
Thank you
mark |
|
|
|
Hi all,
I have noticed on my sql logs that something is constantly trying to login to the GP sample database called "TWO". the database doesn't exist anymore on our database. it is putting about 4 entries in the log PER SECOND! I sure would like to clear this up.
9/19/2017 6:05:25 PM
Log SQL Server (Current - 9/19/2017 4:54:00 PM)
Source Logon
Message
Login failed for user 'ROI\sysservices'. Reason: Failed to open the explicitly specified database 'TWO'. [CLIENT: 127.0.0.1]
for the life of me, I cannot find what the heck Is trying to login. I can see here what user, but what process?
thanks!
george
|
|
|
|
Saludos,
Existe algun xml para econnect que me permita usar los procedimientos almacenados de conteo stock?
Is there any xml for econnect that will allow me to use stock count stored procedures?
saludos, |
|
|
|
Hi,
I am posting this question again as I did not get any answer yet.
We cannot use the Lot transfer as we are not implementing any Lot in our system.We do not have any lot number. I tried the below xml for Site Transfer and Bin Tranfer. It did not throw any exception but also did not do any transfer.
I want -ve adjustment OF QTY 1 in "Return"Site "R1" BIN and
+ve adjustment OF QTY 1 in "NJ_305VET" Site "RCV" BIN
Nothing changed in my database THE QTY IV00102 AND IV00112 :
But addded row in IV10000 and IV10001
Below is the XML we are sending:
<?xml version="1.0" encoding="UTF-8"?>
<IVInventoryTransferType>
<taIVTransferLineInsert_Items>
<taIVTransferLineInsert>
<IVDOCNBR>00000000000000502</IVDOCNBR>
<ITEMNMBR>SPX3000</ITEMNMBR>
<LNSEQNBR>16384</LNSEQNBR>
<TRXQTY>2</TRXQTY>
<TRXLOCTN>RETURNS</TRXLOCTN>
<TRNSTLOC>NJ_305VET</TRNSTLOC>
</taIVTransferLineInsert>
</taIVTransferLineInsert_Items>
<taIVTransferMultiBinInsert_Items>
<taIVTransferMultiBinInsert>
<IVDOCNBR>00000000000000502</IVDOCNBR>
<ITEMNMBR>SPX3000</ITEMNMBR>
<LNSEQNBR>16384</LNSEQNBR>
<BIN>R1</BIN>
<TOBIN>RCV</TOBIN>
<QUANTITY>1</QUANTITY>
</taIVTransferMultiBinInsert>
</taIVTransferMultiBinInsert_Items>
<taIVTransferHeaderInsert>
<BACHNUMB>Trans</BACHNUMB>
<IVDOCNBR>00000000000000502</IVDOCNBR>
<DOCDATE>Thursday, September 14, 2017</DOCDATE>
</taIVTransferHeaderInsert>
</IVInventoryTransferType
</eConnect>
Thanks
Priya
|
|
|
|
| I am using eConnect to create a Contract, Project, Fee Schedule and Fees. Just discovered that the field pabillnoteidx is set to 0 for all Fee entries in the PA Project Fee Master File (PA02101) when using eConnect to insert/create the fees. This is causing any Billing Note added for these projects to appear on all the projects. Why is this happening? Should this not be automatic as is the noteidx fields in the Projects table when adding a project via eConnect? |
|
|
|
I am getting an error trying to build the SmartList files.
Dynamics GP 14.00.1059
SqlServer Express 2012
The attached ZIP file contains the ridiculously long Dexsql.log and a word document with the errors.
I was originally getting the ASI_MSTR_Explorer... error for columns don't match. I ran the Drop and Create SQL Maintenance steps discussed in a different article found here. Now I get this error when logging into Dynamics and trying to build the SmartList.
Thanks for any help
Bill |
|
|
|
Error: Budget status is invalid - budget status needs to be open
|
|
|
|
Hello,
We are new to econnect and are trying to write the code to create the billing entries for project accounting. We would like to create both Fee and expense billings but are starting with the Fee billing... Transactions >> Project >> Billing >> Billing Entry. We have found that we can use PAProjectFeesType class and use the xml reference given at https://msdn.microsoft.com/en-us/library/ff623740.aspx, but it does not have all the fields for the header. Is there another class for this type of transaction?
Thanks,
Phil |
|
|
|
Hi
I need to set up audit trails to track any changes made to the posting set up in GP 10 [Tools | Set up | Posting | Posting | Sales | All] and the Selection is "Include Multi Currency Info". Picture attached
Someone keeps changing it and I want to find out who.
How will I set this up in the Audit Trail? Is it under Company? Sales? System?
Thanks
Paul Chacko |
|