|
|
This very simple, yet very useful pivot table in Excel puts all the paycodes from Dynamics GP in a spreadsheet for a payroll expert to review.
We will take data directly from the UPR30300 paycheck detail table like this...
And turn it into this, one line per employee, showing all their aggregated payroll code data. Note that the pay codes are grouped, in the screen shot below you can see Benefits and Deductions
Start with copying this select statement into a SQL query window and run it.
Select
d.EMPLOYID as 'Employee_ID',
PAYROLCD as Payroll_Code,
case when PYRLRTYP = '1' then 'RegularPay'
when PYRLRTYP = '2' then 'Deductions'
when PYRLRTYP = '3' then 'Benefits'
when PYRLRTYP = '4' then 'Taxes'
when PYRLRTYP = '5' then 'LocalTax'
else '' end as Payroll_Type,
UPRTRXAM as Amount,
CHEKDATE as Check_Date
from upr30300 d
where year(d.CHEKDATE) = year(GETDATE())
|
|
|
|
We had an overseas client call for help with a failing eConnect integration, it was failing with essentially no error at all.
The last time this happened, it was because of an error in an Multi Currency trigger in the Dynamics database, today's error turned out to be a missing currency line in DYNAMICS..MC00200. The trick was finding the error, because it wasn't being reported.
We had the XML, and it had three call: PO Line, PO Header, and PO tax. We already have 'wrapper' code on this site for the first two so I wrote the wrapper below for the tax call and sent it to them.
We copied the XML into the three stored procedure wrapper calls and we got the error on the header: error on the MCValidate stored procedure. And... an error number. I looked up the number and it was 'missing MC00200 line'. Grrr.
Hope this helps. |
|
|
|
| This seems pretty simple, but on the server from IE it was pretty hard. I ended up loading Chrome and found it. IE has JavaScript restricted on servers. Screen shots below |
|
|
|
I need to be able to return a JSON string containing the results of an SQL query. I know there are 3rd party tools that can simplify this, but I wanted to not have to add more to my project.
I built a small WinForm project to determine how to convert text into a JSON-formatted string. Even though my source data would be from an SQL results set, for my test I just generated some data. You only have to change the Vendor.GetVendorInfo() to accommodate whatever means you use to supply your data.
It is important to note the decorations used in the Vendor and Vendors classes. This is needed for the JSON serialization.
The Form1 class is just a WinForm with a Start and Exit button and a text box for displaying the resulting JSON string. I first tried with a single Vendor instance, the v1 variable. Once that was working, I moved on to an array of Vendor types (vendors).
The GetVendorInfo() just creates the data to be serialized. The BuildJSON() method does the actual serialization.
NOTE: Make sure you add a reference to System.Runtime.Serialization to the project! |
|
|
|
|
|
This is a step by step walkthrough for a Sales Invoice integration into Dynamics 365/Business Central using SmartConnect
If you don't have any existing SmartConnect integrations, read this post first, it contains some setup items not covered here.
Please comment below if we can make this any clearer or helpful |
|
|
|
Seems like a silly thing, hiding the tabs on a .NET TabControl. but it's a little bit of a trick
 |
|
|
|
| Here's an interesting piece of code that adds a line to the 'Additional Items' menu in most any Dynamics GP form. Try it! |
|
|
|
This article contains two stored procedures that will insert/update data in Extender tables. The code is not fully flushed out for all of the data types, but it supports text and numbers.
If you use it and update it, email your code to support@ and we'll update it here.
EDIT 11/16/2018
In response to customer feedback, new proc below updated to handle dates and dropdown values
EDIT 9/29/2020
Rewritten to include suggested code from the comments below.
|
|
|
|
This piece of code is ALL the code behind for a standard .net 1 form application that generates a license key that can be used by your applications.
The form looks like this:
And, here is the code that consumes the key:
Private Sub ProcessLicenseKey()
' This subroutine reads the distribution key, decrypts it, extracts the
' expiration date, and warns as expiration is within 2 months, and exits
' if expiration is past.
Dim baseDir = Path.GetDirectoryName(Application.ExecutablePath)
Dim expirationDate As DateTime
Dim keyValid As Boolean = True
Try
Dim encryptedText As String = File.ReadAllText(Path.Combine(baseDir, "KEY.DAT"))
Dim licenseKey As LicenseKey = New LicenseKey()
expirationDate = licenseKey.GetExpirationDate(encryptedText)
If Not (ValidKey(expirationDate)) Then
Application.Exit()
End If
Catch ex As FileNotFoundException
Dim msg As String = "Unable to read distribution key." + vbCrLf
msg += String.Format("Error: {0}", ex.Message) + vbCrLf
msg += "Contact your Run-Biz representative." + vbCrLf
msg += "Program will be terminated."
MessageBox.Show(msg)
keyValid = False
Catch ex As Exception
Dim msg As String = "Unexpected error occurred." + vbCrLf
msg += String.Format("Error: {0}", ex.Message) + vbCrLf
msg += "Contact your Run-Biz representative." + vbCrLf
msg += "Program will be terminated."
MessageBox.Show(msg)
keyValid = False
End Try
If Not keyValid Then
Application.Exit()
End If
End Sub
End Class
|
|
|
|
So, here's a poser. A question. A mys-ter-y.
I took a support call yesterday from a client, their IM integration was locking up with no warning. Now, before I lose those that hate IM, the solution had nothing to do with IM, so stay with me.
Here's the deal. I'm going to describe the symptoms and then the steps that I took in troubleshooting.
*** I'm going to number the steps.
If you get the solution at a step before I did, please enter a comment below with your step. The winner gets absolutely nothing, save fleeting fame and admiration from your peers.
So, here we go.
Step #1 The client logs me into IM, and runs the RM Integration, and the document type is Finance Charge. It logs no ATTEMPTS, just locks up immediately. We have to end the task (both IM and Dynamics) to get control again.
Step #2 The integration has Before Integration, Before Document, After Document, and After Integration scripts. Suspecting the code, I put MSGBOX statements in the first three and determined that the scripts were fully executing (not locking) and that we never even started the After Document. This appeared to be a Dynamics issue, not Integration Manager.
Step #3...
|
|
|
|
Second error like this in a week. I blogged the last one too, but this one is attached to eConnect so I'll blog it just to record the error number.
If you remember the post, you should be able to guess the problem just from reading the error...
Sql procedure error
codes returned:
Error Number = 38 Stored Procedure= taCreateSopPaymentInsertRecord
Error Description = Unable to get payment number
Node Identifier Parameters: taCreateSopPaymentInsertRecord
SOPNUMBE = BR-285706
SOPTYPE = 2
PYMTTYPE = 3
<taCreateSopPaymentInsertRecord>
<SOPTYPE>2</SOPTYPE>
<SOPNUMBE>BR-285706
</SOPNUMBE>
<CUSTNMBR>B999999 </CUSTNMBR>
<DOCDATE>11/27/2018</DOCDATE>
<DOCAMNT>13.40000</DOCAMNT>
<CARDNAME>WEBSITE </CARDNAME>
<PYMTTYPE>3</PYMTTYPE>
<Action>1</Action>
<SEQNUMBR>16384</SEQNUMBR>
<USRDEFND1>SALE
</USRDEFND1>
</taCreateSopPaymentInsertRecord>
|
|
|
|
I'm just going to copy all the code from this project into an article. It was simple to code, and DAYS to configure for SSL. The issue was all with the web.config
https://docs.microsoft.com/en-us/dotnet/framework/wcf/feature-details/how-to-configure-an-iis-hosted-wcf-service-with-ssl
In the end, this article helped the most. |
|
|
|
The is in response to a question on the forum, a user asked how to restrict data in a GP scrolling window using VBA.
I this thread http://dyndeveloper.com/thread.aspx?threadid=630
RedSoxFred details how to do it, I'm copying it to an article so that I can put it on the VBA menu
http://dyndeveloper.com/Menus/VBA.aspx
|
|
|
|
| I need to view a users password in GP. I am using SSMS 12.0.5207.0 basic version. What query can I run, to decrypt the users password and view it? We use a third party application, that uses GP credentials to login. Without resetting the user's password, how can I view it from the SY01400 table? |
|
|
|
Good morning,
After install Manufacturing and Human Resources Features from control panel and run GP Utilities is necessary to install the feature like Manufacturing or Human Resources in each pc client even if it is not used?
Any suggestion is welcome,
Mr. García
|
|
|
|
I'm writing a Web Services integration for GP. I'm utilizing the UpdateSalesOrder method, pulling the existing Sales Order object using the Sales Order ID, and supplying a new set of SalesOrderLines, with Lots filled out.
The goal is that our system controls order/lot information, and we're exporting information to update the GP Sales Order and its lines. For an order we export an Item key, its lot keys, lot quantities, and an item quantity (total of lot quantities) and want to make these allocations in GP.
I have a basic test: Updating Sales Order with 1 Line Item and 1 Lot on the item. In my C# code I've set the SalesOrderLine Quantity, QuantityAllocated, QuantityFulfilled to 5, and SalesOrderLine's only Lot Quantity to 5 as well.
I'm getting “Lot Quantity Fulfilled does not match the Sales Line Fulfilled. AllocateLotBehavior = 1.”
UpdateSalesOrder Allocate Lots Behavior is set to Manual.
I checked a SQL Trace of taSopLotAuto and saw @I_vQUANTITY=5.00000; @I_vQTYFULFI=default (It looks like this might be the cause)
- If @v_QTYFULFI being sent needs to be the value on Line Item (5), how do I do that? (SalesLineLot object from web services only exposes Quantity)
XML from exception console:
<Lines>
<SalesOrderLine>
<Extensions/>
<Key>
<SalesDocumentKey>
<CompanyKey>
</CompanyKey>
<Id>DR1005</Id>
</SalesDocumentKey>
<LineSequenceNumber>0</LineSequenceNumber>
</Key>
<Quantity>
<Value>5.00000</Value>
<DecimalDigits>0</DecimalDigits>
</Quantity>
<IsNonInventory xsi:nil="true"/>
<IsDropShip xsi:nil="true"/>
<RequestedShipDate>2017-04-12T00:00:00</RequestedShipDate>
<DeleteOnUpdate xsi:nil="true"/>
<TaxBasis xsi:nil="true"/>
<IntegrationSource xsi:nil="true"/>
<WarehouseKey>
<Id>WAREHOUSE</Id>
</WarehouseKey>
<ItemKey>
<Id>TESTLOT</Id>
</ItemKey>
<ShippingMethodKey>
<CompanyKey>
</CompanyKey>
<Id>LOCAL DELIVERY</Id>
</ShippingMethodKey>
<SalesTerritoryKey>
<CompanyKey>
</CompanyKey>
<Id>TERRITORY 1</Id>
</SalesTerritoryKey>
<SalespersonKey>
<CompanyKey>
</CompanyKey>
<Id>PAUL W.</Id>
</SalespersonKey>
<PriceLevelKey>
<CompanyKey>
</CompanyKey>
<Id>RETAIL</Id>
</PriceLevelKey>
<ShipToAddressKey>
<CompanyKey>
</CompanyKey>
<CustomerKey>
<CompanyKey>
</CompanyKey>
<Id>AARONFIT0001</Id>
</CustomerKey>
<Id>WAREHOUSE</Id>
</ShipToAddressKey>
<ShipToAddress>
<Extensions/>
<Line1>11403 45 St. South</Line1>
<Line2/>
<Line3/>
<City>Chicago</City>
<State>IL</State>
<PostalCode>60603-0776</PostalCode>
<CountryRegion>USA</CountryRegion>
<Phone1>
<Value>31255501020000</Value>
</Phone1>
<Phone2>
<Value>31255501020000</Value>
</Phone2>
<Phone3>
<Value/>
</Phone3>
<Fax>
<Value>31255501020000</Value>
</Fax>
<Name>Aaron Fitz Electrical</Name>
<ContactPerson>Bob Fitz</ContactPerson>
</ShipToAddress>
<Taxes/>
<QuantityToBackorder>
<Value>0</Value>
<DecimalDigits>0</DecimalDigits>
</QuantityToBackorder>
<QuantityToInvoice>
<Value>5.00000</Value>
<DecimalDigits>0</DecimalDigits>
</QuantityToInvoice>
<QuantityCanceled>
<Value>0</Value>
<DecimalDigits>0</DecimalDigits>
</QuantityCanceled>
<QuantityFulfilled>
<Value>5.00000</Value>
<DecimalDigits>0</DecimalDigits>
</QuantityFulfilled>
<QuantityAllocated>
<Value>5.00000</Value>
<DecimalDigits>0</DecimalDigits>
</QuantityAllocated>
<FulfillDate xsi:nil="true"/>
<ActualShipDate>2018-11-07T00:00:00</ActualShipDate>
<Components/>
<Serials/>
<Lots>
<SalesLineLot>
<Extensions/>
<DateReceived xsi:nil="true"/>
<DateSequenceNumber xsi:nil="true"/>
<IsPosted xsi:nil="true"/>
<DeleteOnUpdate xsi:nil="true"/>
<LotNumber>TESTLOTDRAla1</LotNumber>
<Quantity>
<Value>5.00000</Value>
<DecimalDigits>0</DecimalDigits>
</Quantity>
<ManufacturedDate xsi:nil="true"/>
<ExpirationDate xsi:nil="true"/>
<UserDate>2018-11-07T00:00:00</UserDate>
<Key>
<CompanyKey>
</CompanyKey>
<SalesLineKey>
<CompanyKey>
</CompanyKey>
<SalesDocumentKey>
<CompanyKey>
</CompanyKey>
<Id>DR1005</Id>
</SalesDocumentKey>
<LineSequenceNumber>0</LineSequenceNumber>
</SalesLineKey>
<QuantityType>On Hand</QuantityType>
<SequenceNumber>0</SequenceNumber>
</Key>
</SalesLineLot>
</Lots>
<Bins/>
</SalesOrderLine>
</Lines>
|
|
|
|
| How do a create a sql query (not using a join or union) and search multiple companies in gp |
|
|
|
Need some SQL help here....
I have a table with the following columns:
ItemNumber, BinLocation, TransactionDateandTime
There are multiple entries in this table, based on what happens with Inventory.
Example:
Item Bin Date and Time stamp
123456 LOC A 2018-10-01 07:23:13.397
123456 LOC B 2018-11-06 10:19:01.540
123456 LOC C 2018-10-30 08:22:00.770
111222 LOC O 2018-11-07 06:54:45.373
111222 LOC P 2018-11-03 06:12:59.067
I want the record for each item with the most recent date to be extracted into another table I'm using for a Smartlist.
Desired result:
123456 LOC B 2018-11-06 10:19:01.540
111222 LOC O 2018-11-07 06:54:45.373
|
|
|
|
I am creating a calculated column to pull the GP PA Project ID from a segment of the GL string to build an PO PA Receipt import
I need to extract the Project ID if greater than 000 from the 6th segment of this account string XXX-15-XXXX-XXX-300-XXX where segment 2 equals 15 and segment 5 equals 300.
Note: Smartconnect requires that I write the calculated column code using .NET - I believe I need to use Substring, but cannot locate any documentation that shows me the .Net Syntax for Substring.
here's is what I wrote:
Dim mySegtwo as String =
_Account.ToString.Trim
Dim mySegFive as String =
_Account.ToString.Trim
Dim mySegSix as String =
_Account.ToString.Trim
If String.IsNullOrEmpty(mySegtwo)
Then
Return ""
Else If
mySegtwo.Substring(5,2) = “15” and mySegFive.Substring(17,3) = “300” and
mySegSix.Substring(21,3) > “000” Then
Return mySegSix.Substring(21,3)
End If
When I ran the code above - I got the message shown below?
Just seeking assistance with this or if someone could possibly direct me to a site with some example .Net syntax - that would be ideal and greatly appreciated
|
|
|
|
Good
morning,
I want to test Manufacturing in Fabrikam but when the user
enter to other company a Manufacturing Series Registration Window appear,
the user need select "Cancel" to continue. I don't have Manufacturing
registration key still because I want to test the Manufacturing module before
buy a license but the window is an inconvenience for the user. how to avoid
this window? see below
Any suggestion is welcome,
Mr. García
|
|
|
|
Does anyone have code that will help me add the 5 char DB name to all of the results of a Query of GP tables.
If I query the CMPANYID from SY00100 I get 1, then I go to Dyanmics SY01500 and I look up 1 and I can get the long Company Name. But then I have no common fields to join the name to any other selected items. Even if it worked, all I really want is the 5 Digit Database Name.
Any help would be appreciated.
Thank you
|
|
|
|
how to give access to multi-level query wizard to a user who is not poweruser
|
|
|
|
| Hi there is there a procedure or flag in GP to stop a sales order from being edited. right now our sales people often modify the sales order after it's sent to warehouse for fulfillment, causing lots of discrepancy at the time of invoicing. thanks! |
|
|
|
Hi, i'm testing an econnect 14 integration on SOP Modulu in which i have to send a lot of records (14K thousand orders total /18k thousand lines total).
In order to do this i'm sending one big econnect transaction, data is OK, however i'm getting this error:
"The operation is not valid for the state of the transaction."
Seems like some sort of timeout or disconnection, i just wonder if somebody had this in the past and how to it was solved, below you'll see the stacktrace.
at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateTransactionEntity(String connectionString, String xml)
at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction)
at Microsoft.Dynamics.GP.eConnect.eConnectMethods.ProcessEntityImport(String connectionString, String sXML, Boolean isTransaction)
at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateTransactionEntity(String connectionString, String sXML)
at SopConsApp.Program.Main(String[] args) in c:\Console Apps\SopConsApp\SopConsApp\Program.cs:line 266 |
|
|
|
Hello,
I am developing a web-based integration for Dynamics GP 2018 using .NET/MVC/C# and have run into an issue with eConnect. After using the 'UpdateEntity()' eConnect method, some of our Purchase Orders have their 'Payment Terms' values stripped. I have examined the value in the POP10100 table just before and after calling that method so I can safely say that it is not caused by another piece of the integration. I am also certain that we are not somehow setting the Payment Terms to empty. This error occurs even when the <PYMTRMID> Xml node is not included in the Xml passed to the 'UpdateEntity()' method.
I have done some research and compiled a list of Payment Terms values that do get dropped after updating, and others that do not.
Payment Term values that DO get dropped:
0.5%10 Net 25 - dropped
0.5%25 Net 30 - dropped
0.5%Net 10 - dropped
1.5%10 Net 30 - dropped
1.5%net 20 - dropped
Payment Term values that do NOT get dropped:
1.5% 10 Net 45 - not dropped
1%10 Net 30 - not dropped
1%10 NET 60 - not dropped
33% down / net 30 - not dropped
2% 35 ,Net 45 - not dropped
2%10Net45 - not dropped
I have not been able to identify a clear reason as to why some values do get removed and others do not. I have compared the 'SY03300' records and nothing stands out there either.
Anyone have any ideas? |
|
|
|
Using eConnect to import AUS dollar transaction into a GP company db which uses USD as the functional currency, I receive an out-of-balance error message (and the transaction does not post). The transaction clearly balances in AUS dollars.
Error Number = 944 Stored Procedure=
taGLTransactionHeaderInsert Error Description = Unbalanced journal entry,
the credits do not equal the debits
Node Identifier Parameters: taGLTransactionHeaderInsert
BACHNUMB = SSCASH
JRNENTRY = 215452
REFRENCE = SS Cash 10/31/2018
TRXDATE = 10/31/2018 12:00:00 AM
TRXTYPE = 0
When I hand key the transaction into GP, it goes in just fine. The system adds in a rounding line to accommodate a rounding difference when it translates to functional USD currency. In this transaction’s case, there is a penny rounding difference when it translates to USD. How can we get eConnect to perform the same rounding function for functional currency translation rounding that GP performs systematically when the transaction is hand keyed?
|
|
|
|
Multiple bins can't be enabled because there are unposted invoices transactions"
Is there a script to see if there any open batches. There are none unposted batches in the user interface.
|
|
|
|
| Is there any way to modify the underlying query that populates a Dynamics GP lookup window? I want to restrict the query that populates the PA Return Item Lookup window that auto-opens and populates when the user exits the Item Number field in the Project Inventory Transfer Entry window (Project > Transactions > Inventory Transfer Entry). Thanks. |
|