Archives

 
This is example XML of a working SOP document with taxes and payments

There's a feature in SQL that allows you to get the week number of a date. This is usefull in grouping all the detail in a query by week.

So, now you need to report on the first day of the week. This snippet will give you the first day of the week for a given week number.

In the example we're using the Project Cost transaction table... but any table with a date will do.

 

Writing reports in Visual Studio can be a challenge. We've decided to do a full set of tutorials on how to get up and running using different types of reporting tools This article will be the entry point
This article will cover how to create an RDLC Winforms report and how to deploy it.
This article will discuss how to deploy reports written with Microsoft Winforms RDLC Reports
This is a 101 level article that will take you through installing DevExpress reporting and writing your first report. We also talk about deployment.

Here's the deal. I'm just tired of writing this script. I have to write it  several times a week.

I'll blog it here and be done with writing it. <smiles>

This is a script that will produce an insert statement for any table in SQL 2008 +

Sure, you can just right click on a table and get this... but my solution is ready to use and requires only minimal editing.

And (for all you OCDers like me) the commas line up.

Earlier, we had blogged on how to create a refreshable report using VBA. Frequent GPUG contributor Steve Erbach pointed out that Victoria Yudin has a blog posting on how to do the same thing using Microsoft Query.

Having coded it both ways now I'm not sure which way is better. This technique below is longer to do (17 screen shots), but it avoids VBA which is bound to be a new technology for some people. I'd appreciate your comments.

This technique also queries the database and returns a list of all the tables and views so you have to sit and wait for a while to get that list... and then you don't need it. If you get the 'please wait' window just sit and wait it out. It might be a few minutes.

Victoria's original article is here. Our article here is written in our typical RAD style. Victoria's is more personable <smiles>.  

Our article was written against Office 2010.

I want to thank Steve for his input. You'd be surprised how few people take the time

Steve's words:

Victoria Yudin has addressed the use of stored procs with just one or two parameters without VBA. I also did an overly-long video series on YouTube showing how to implement the RM Historical Aged Trial Balance stored proc in a refex (REFreshable EXcel workbook).

Editors note:

If you're not a GPUG member, you're missing out.

Upon opening Dynamics GP, you receive this error "An error has occurred in the script on this page".

This is not a very impressive function, but it'll save me from having to write it again in the future.

Hi , I have an application that generates a file in the eConnect xml format, they are invoices, and an application that passes the information to gp10.0 .

When an invoice has different amounts of xml 0.000000 in the header TAXAMNT node, or has distita amount of 0.000000 in the field line MRKDNAMT it raises error and not load the transaction in GP . For these cases you must manually edit the xml and so achievement that is loaded into GP .. you can correct this situation?

 

 

Hola, tengo una aplicación que me genera un xml en formato econnet, son facturas. y una aplicación que pasa la información a gp10.0.

ocurre que cuando una factura en xml tiene cantidad distinta de 0.000000 en el nodo TAXAMNT,  del header, o tiene cantidad distita de 0.000000 en el campo MRKDNAMT de line me genera error y no se carga la transacción en GP. Para estos casos debe editar manualmente el xml y asi logro que se cargue en GP.. 

es posible corregir esta situación?

 

In the SOP10200 and SOP30300 tables there are fields labelled COMMNTID and ITEMCODE. Where in Dynamics can I read information on these fields?

En las tablas SOP10200 Y SOP30300 existes dos campos uno llamdo: "COMMNTID" y el otro: "ITEMCODE". Mi consulta es, ¿desde que ventana de GP10.0 puedo escribir información en esos campos?

Hi All :

Can anyone suggest SQL or the table name to get next receipt number for bank transaction entry screen?

Thank You ,

Jaswanth

Hello,

We would like to stop journal entries from posting through to the GL while performing cost adjustments in inventory series.  I can accomplish this by un-checking the Post Through General Ledger File in the Posting Setup for Series: Inventory, Origin: Transaction Entry.  The problem with that, is that all other inventory transaction journal entries stop at the door to the GL.  Is there any way to control ONLY the cost adjustment journal entries?

Thank you in advance,

Michal

Error: The purchase order line has been Received, Canceled, or Closed

Error: Invalid PO Status (POLNESTA), partially matched items cannot be closed

I have been asked to generate a query that will allow for all PO's received but not invoiced. to be displayed.

I have most of the logic I think but am struggling to cut the code, could one of the experts give me a hand on this possibly? Or perhaps there is a better way to do this.

I believe the logic would be something like find POs in the POP30100 Purchase Order History table that donothave a matching record in the POP30300 Purchasing Receipt History table where POP30300.POPTYPE = 2 (Invoice) or 3 (Shipment/Invoice). You’d also need to look at the POP10300 Purchasing Receipt Work table too: find all POs in the POP30100 table that do not have a matching record in the POP10300 table where POP10300.POPTYPE = 2 or 3.

Likewise with the related work tables: find all POs in the POP10100 Purchase Order Work table that do not have a matching record in the POP10300 Purchasing Receipt Work table where POP10300.POPTYPE = 2 or 3.

Playing in SQL I get

 

SELECT  A.[PONUMBER] ,
        CASE A.[POSTATUS]
           WHEN 1 THEN 'NEW'
           WHEN 2 THEN 'RELEASED'
           WHEN 3 THEN 'CHANGE ORDER'
           WHEN 4 THEN 'RECEIVED'
           WHEN 5 THEN 'CLOSED'
           WHEN 6 THEN 'CANCELED'
         END AS POSTATUS ,
        CASE A.[POTYPE]
           WHEN 1 THEN 'STANDARD'
           WHEN 2 THEN 'DROP-SHIP'
           WHEN 3 THEN 'BLANKET'
           WHEN 4 THEN 'BLANKET DROP-SHIP '
        END AS POTYPE ,
        A.[DOCDATE] AS Date ,
        A.[PRMDATE] AS PromiseDate ,
        A.[REQDATE] AS RequestedDate ,
        A.[REMSUBTO] AS RemainingSubTotal ,
        A.[SUBTOTAL] AS SubTotal ,
        A.OREMSUBT AS OriginatingRemainingSubTotal ,
        A.ORSUBTOT AS OriginatingSubTotal ,
        A.[VENDORID] AS VendorID ,
        A.[VENDNAME] AS VendorName ,
        A.[DUEDATE] AS DueDate ,
        A.[CURNCYID] ,
        A.[BUYERID] ,
        CASE B.[POLNESTA]
           WHEN 1 THEN 'NEW'
           WHEN 2 THEN 'RELEASED'
           WHEN 3 THEN 'CHANGE ORDER'
           WHEN 4 THEN 'RECEIVED'
           WHEN 5 THEN 'CLOSED'
           WHEN 6 THEN 'CANCELED'
        END AS POLineStatus ,
        B.[ITEMNMBR] AS ItemNumber ,
        B.[ITEMDESC] AS ItemDescrption ,
        B.[VENDORID] AS LineVendorID ,
        B.[VNDITNUM] AS VendorItemNumber ,
        B.[LOCNCODE] ,
        B.[UOFM] ,
        B.[QTYORDER] AS QuantityOrdered ,
        B.[QTYCANCE] AS QuantityCancelled ,
        B.[UNITCOST] ,
        B.ORUNTCST AS OriginatingUnitCost ,
        B.[EXTDCOST] AS ExtendedCost ,
        B.OREXTCST AS OriginatingExtendedCost ,
        B.XCHGRATE AS ExchangeRate ,
        ISNULL(C.POPRCTNM, ' ') AS POPRCTNM ,
        ISNULL(C.QTYSHPPD, 0) AS QuantityShipped ,
        ISNULL(C.QTYINVCD, 0) AS QuantityInvoiced ,
        ISNULL(C.QTYREJ, 0) AS QuantityRejected ,
        ISNULL(C.QTYMATCH, 0) AS QuantityMatch ,
        ISNULL(C.QTYRESERVED, 0) AS QuantityReserved ,
        CASE C.POPTYPE
           WHEN 1 THEN 'SHIPMENT'
           WHEN 2 THEN 'INVOICE'
           WHEN 3 THEN 'SHIPMENT/INVOICE'
           ELSE ' '
        END AS DocumentType ,
        ISNULL(C.UOFM, ' ') AS UOFM,
        ISNULL(C.DATERECD ,'') AS DateReceived,
        ISNULL(C.PCHRPTCT, 0) AS PURCHASERECEIPTCOST ,
        ISNULL(D.ORUNTCST, 0) AS ORGRECUNITCOST ,
        ISNULL(D.OREXTCST, 0) AS ORGRECCOST
        FROM    dbo.POP10100 AS A
        INNER JOIN dbo.POP10110 AS B ON A.[PONUMBER] = B.[PONUMBER]
        LEFT OUTER JOIN dbo.POP10500 AS C ON B.[PONUMBER] = C.[PONUMBER]
                                         AND B.[ORD] = C.[POLNENUM]
        LEFT OUTER JOIN dbo.POP30310 AS D ON C.[PONUMBER] = D.[PONUMBER]
                                         AND C.[RCPTLNNM] = D.[RCPTLNNM]
                                         AND C.POPRCTNM = D.POPRCTNM
        LEFT OUTER JOIN dbo.POP30300 AS E ON D.[POPRCTNM] = E.[POPRCTNM]

order by DATE DESC

 

I need to sort by status 2 and 3 I think to filter.

Hi All : 

Is there any way I can post a Bank Transfer Entry from my .net application to GP through web service or econnect  ?
I went through all the list of methods ,but couldn't find any .

Thank You ,

Jaswanth

Como modifico la definición de un formato para que sea numérico y no Moneda. 

Como creo uno nuevo y que reportwriter lo reconosca, ya que hice uno y no logro utilizarlo.

favor ver imagen

As I change the definition of a format to be numerical and not Monetary. As I think ReportWriter new one and it reconnoitre , as I made one and I can not use it.   Please see picture

En la programacion vb de la ventana de ventas tengo algunos sendKeys como ejemplo: sendKeys "^{l}" que representa el Ctrl + L

pero esto no funciona en windows 10. 

Como puedo resolver esto?

 

VB programming in the sales window I have some Sendkeys as an example: SendKeys "^ { l } " that represents the Ctrl + L but this does not work in Windows 10.

Hi:

I added a couple of custom fields (a textbox and a combobox) to the Purchase Requisitions Entry screen in GP 2015.  After running the dag.exe utilitity, generating the Application.Dynamics.ModifiedForms.dll and its xml file sucessfully, and importing it in my project, I am able to see the textbox in Intellisense, and use it, but I cannot see the combobox.  I tried adding a dropdown list, but it doesn't work either.  

Am I doing something wrong, or VST just doesn't support comboboxes and dropdown lists?

i have a problem in general ledger with the number of the transaccion the final number i have is 11 but in the automatic number of transaction have a diferent.

how can i fix the problem

i remember gp save this number in a table in the data base but i not remember.

 

 

Hi,

I need an example code of how to set an SOP on hold.

Hi All:

I am using "CreateGLTransaction" GP web service method to post from DOTNet Application, after posting in order to complete the transaction I am using series posting.

And when I am checking the current Balance in both the accounts I am unable to find any updates relating to the latest transaction.

I am unable to figure out where the problem is.

Thanks,

I have a batch of sales that have bills and when I try to open them said that the document was historical. to consult with sql query invoices are all in SOP10100 And SOP10200.
Apparently during the posting process the connection to the server is interrupted, that damage the loye and then changed to a new batch bills, but you can not count and I need to post the batch.

anyone has any idea that can help me?

 

 

Performing the SQL queries that attached discover that the invoices are in the GL10000 tables, GL10001 but are not on the table GL20000, transactions in the SOP10100 table has the PSTGSTUS = 14 field and field TRXSORCE = SLSTE00010061. 
I have two questions:
1. There is some additional table where you should look.
2. I can delete the damaged invoices batch of GL10000 tables, GL10001; update PSTGSTUS = 0 field and field TRXSORCE = ''  and make a new posting?

We are in the process of upgrading GP from version 9 to 2015 R2, the problem is that in GP 9 when I run a full inventory reconcile it takes about 11 hours, however the same process in GP 2015 R2 with the same dataset takes almost 40 hours.  The infrastructure in place for GP 2015 is much more robust hardware and storage.

 

Any ideas?

Hello,

I am trying to update  statuses for old purchase order documents en masse.  The web service call works fine when cancelling PO lines which are New, Released, or Change Order.  However, when trying to push a status of Closed to PO lines with the status of Received, the call does not work.  eConnect does not throw any errors at this point, it is like the call is being ignored.  Below I have pasted the entire web method, and any help/insight is appreciated!

[WebMethod]
        public void changePOLineStatusSTG(string PONumber, string Vendor, string ItemNumber, int LineSeq, int POStatus)
        {
            eConnectType eC;
            POPTransactionType po = new POPTransactionType
            {
                taPoLine_Items = new[]
                {
                    new taPoLine_ItemsTaPoLine {
                    PONUMBER = PONumber,
                    POLNESTA = (short)POStatus,
                    POLNESTASpecified = true,
                    VENDORID = Vendor,
                    ITEMNMBR = ItemNumber,
                    ORD = LineSeq,
                    ORDSpecified =true,
                    UpdateIfExists = 1
                    }
                },

                taPoHdr = new taPoHdr
                {
                    PONUMBER = PONumber,
                    VENDORID = Vendor,
                    UpdateIfExists = 1
                }
            };

            eC = new eConnectType { POPTransactionType = new[] { po } };
            SendEconnect(eC, Settings.Default.CS_STG, TrxType.UPDATE);
        }

 

Code snippet to change the back color of a RadTextBox

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3