DynDeveloper.com

DynDeveloper.com

Sign Up Now!Log In

Archives

 

This is an actual requirement that I got from a customer today, so I'm challenging you to see if you can figure out how to code it in SQL. 

Give it a careful though before you read my answer. 

Leave a comment if you have something that will work better, or is more elegant.

So, we start with this:

declare  @Order table(RowID int identity,Invoice varchar(20), CustomerID varchar(20), CustomerEmail varchar(100), DocAmount numeric(19,2) )
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00100','001','Joe@Acompany.com',100)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00101','002','Sam@Bcompany.com',110)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00102','003','Bob@Ccompany.com',120)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00103','004','Tom@Dcompany.com',130)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00104','005','Flo@Fcompany.com',140)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00105','006','Tim@Gcompany.com',150)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00106','006','Tim2@Gcompany.com',160)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00107','008','Sal@Hcompany.com',170)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00108','008','Sal2@Hcompany.com',180)
insert into @Order (Invoice, CustomerID, CustomerEmail, DocAmount) values ('INV00109','008','Sal3@Hcompany.com',190)
 
select * from @Order

This will give us an ORDER table that looks like the below. 10 lines, from 8 companies. The requirement is this:

Use the order table to update the email address field in the customer table. If the same customer has two email address, the newest one should be the main email address, the second oldest one should be the CC, and discard any older email addresses. 

The final result should look like the second screen shot. 

How would you code that?

 

 

TFS: The path xxx is already mapped in workspace xxx

Grrr.

I'm moving from one TFS server to another, and I'm having to remap quite a few projects. On each machine I run into the above error. 

I found a fix today:

Today we're talking about granting access to a table or view in SmartListBuilder. 

If you look on the menu where this article is located, you'll see several other articles on security, and those are more complete than this one. Today we're solving on simple problem. 

Error 7115 The Price Method passed is Currency Amount (PRICMTHD = 1), you can not have a Rounding Amount > 0

Error 7116 The Price Method passed is Currency Amount (PRICMTHD=1) you can not have a (ROUNDTO <> 1)

I... don't exactly have a solution, but I have the piece of code that raises the error. This should help. 

Is there a way to create a notification or something that tells an ap user when a check batch is done building? Currently, they have to rely on the batch itself updating and some of our batches can take several minutes to build. 

Just wondering if anyone has done anything like this before. 

I am currently using GP2016 and like to import GL Transactions with AA using econnect, VB or C#. 

The examples I found ate for version 2010/2013.

Good morning,
  I have the follow situation Customer Invoices not showing all invoices in the All-In-One View Option I verify that all options has been marked and no filter has been setup. All invoices showing correctly in the Trial Balance and in the Customer Inquiry. I run check link and reindex script I don't work.
Any suggestion is welcome.
Thanks,

 prcomputer

GP2016 version 16.00.0579


Good morning,
 I have the follow situation i enter to Sales Transactions-Orders and select a Document No. and select the right arrow
to see the orders and invoices screen, the order is correct but show two invoices that not corresponding to this customer.
Any suggestion is welcome.
Thanks,
 prcomputer

GP 2016 Version 16.00.0579

 

 


I'm writing a sales order import tool for GP 2018. I'm needing to know if the logic below is right, or if I'm missing something.
Import Logic

read headers
          |
does customer exist?
    NO                                    YES
|                                      |
|                                      |
create new customer    |
        |                                       |
insert records into                   |
RM00101, RM00102,               |
RM00103                                 |
        |                                       |
check if order already exists
(export systems allow duplicates)  
      NO                YES
  |                     |
        |             exit 

        |

check for batch
or create new - lock batch
SOP00500
        |
insert record into
SOP10100  
SOP10101
SOP10102 - 2 entries, one with 
account reference for Sales
and one with the reference for
accounts receivable
SOP10103
SOP10106
SOP10112
        |
loop through line items
insert records into
SOP10200
SOP10105
        |
update batch totals and unlock
SOP00500  


This SQL Query:

Select CUSTNMBR as 'Customer', Convert(numeric(10,2),sum(ORTRXAMT)) as 'Revenue' as 'Revenue', Format(DOCDATE,'MMM-yy') as 'Period' FROM [SIZE].[dbo].[RM20101] 
  WHERE CUSTNMBR ='ACFLOR220' AND DOCDATE between '2/1/2019' and '2/28/2019' AND RMDTYPAL=1  
  GROUP BY CUSTNMBR, DOCDATE

Produces this:

Customer Revenue Period
ACFLOR220      337.25 Feb-19
ACFLOR220      310.65 Feb-19

But I want it to produce this:

ACFLOR220       647.90 Feb-19

What should I do?

We are using a product that depends on the Batch Number to start with a certain character string.   The Batch was created in error with the wrong character string.   Batch Status is currently "110" which appears to be = Printing Computer Checks. 

Question, through SQL can I safely rename the Batch or do I need to create another Batch. 

Thanks! 

   

 

Hey all,

I would like to run a SQL Job every day which runs a SQL command which deletes from a table where records are 60 days older than LSTORDDT. I also want to exclude certain CUSTNMBR fields in TEMPLATES (the same as in RM00101) that I have setup in a new table.

Any new CUSTNMBR added to the new table gets excluded also.

I have a Table called 'EXCLUDE' with one column called 'DebtorID' (char15) which is populated by 4 records.

The Table I wish to delete the records from is called 'TEMPLATES'

If I create a SQL job with this code:

DELETE FROM dbo.TEMPLATES WHERE LSTORDDT < DATEADD(d,-60,GETDATE()) AND NOT EXISTS ( SELECT NULL FROM dbo.EXCLUDE m WHERE m.DebtorID = CUSTNMBR )
GO

Is this correct for a SQL Job?

Thanks

What table and column tells me that a customer is an EFT customer?    

You have the Customer EFT Bank Maintenance screen, but not every customer is EFT, so what's the indicator/flag ??

 

 

I have a PO invoice with an amount variance on the invoice.  When I manually enter the invoice, the amount calculates correctly.  When pushing the transaction through econnect, the calculated variance amount is wrong.

In this XML, there is 1 line, PO'd at Unit Cost 90, Ext Cost 900.  The invoice is at 80/800, and the variance is 1500 instead of 100.  I can not seem to find where the calculation is going wrong... 

<?xml version="1.0" encoding="utf-8"?>
<eConnect xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<POPEnterMatchInvoiceType>
<eConnectProcessInfo xsi:nil="true"/>
<taRequesterTrxDisabler_Items xsi:nil="true"/>
<taUpdateCreateItemRcd xsi:nil="true"/>
<taUpdateCreateVendorRcd xsi:nil="true"/>
<taCreateVendorAddress_Items xsi:nil="true"/>
<taPopRcptLineTaxInsert_Items/>
<taPopDistribution_Items xsi:nil="true"/>
<taAnalyticsDistribution_Items xsi:nil="true"/>
<taPopEnterMatchLotInsert_Items xsi:nil="true"/>
<taPopEnterMatchSerialInsert_Items xsi:nil="true"/>
<taPopEnterMatchInvLine_Items>
<taPopEnterMatchInvLine>
<POPRCTNM>RCT1266</POPRCTNM>
<PONUMBER>PO2098</PONUMBER>
<QTYINVCD>10</QTYINVCD>
<ITEMNMBR>3-C2924A</ITEMNMBR>
<VENDORID>ADVANCED0001</VENDORID>
<UNITCOST>80.00</UNITCOST>
<EXTDCOST>800</EXTDCOST>
<POLNENUM>16384</POLNENUM>
</taPopEnterMatchInvLine>
</taPopEnterMatchInvLine_Items>
<taPopEnterMatchInvToShpMultiLine_Items>
<taPopEnterMatchInvToShpMultiLine>
<POPRCTNM>RCT1266</POPRCTNM>
<POPMtchShpRcpt>RCT1264</POPMtchShpRcpt>
<PONUMBER>PO2098</PONUMBER>
<QTYINVCD>10</QTYINVCD>
<VNDITNUM>3-C2924A</VNDITNUM>
<ShipRCPTLNNM>16384</ShipRCPTLNNM>
<VENDORID>ADVANCED0001</VENDORID>
<IVCLINNO>16384</IVCLINNO>
<VarianceAccount>000-4750-01</VarianceAccount>
<Revalue_Inventory>1</Revalue_Inventory>
</taPopEnterMatchInvToShpMultiLine>
</taPopEnterMatchInvToShpMultiLine_Items>
<taPopEnterMatchInvHdr>
<POPRCTNM>RCT1266</POPRCTNM>
<VNDDOCNM>INV1106</VNDDOCNM>
<receiptdate>3/12/19 12:00:00 AM</receiptdate>
<BACHNUMB>GLOBAL SEARCH</BACHNUMB>
<VENDORID>ADVANCED0001</VENDORID>
<SUBTOTAL>800</SUBTOTAL>
<PYMTRMID>Net 30</PYMTRMID>
<TAXSCHID>ALL DETAILS</TAXSCHID>
<USINGHEADERLEVELTAXES>1</USINGHEADERLEVELTAXES>
<CURNCYID>Z-US$</CURNCYID>
</taPopEnterMatchInvHdr>
<taMdaUpdate_Items xsi:nil="true"/>
</POPEnterMatchInvoiceType>
</eConnect>

 

 

I have a smartlist that I wish to see the underlying SQL that pulls back the data.

 

How does one do this?  I have not had much luck.

I did look on the ASITAB20 table and it states the ASI_Table_Technical_Name is ASI_RM_Document_Explorer, but cannot find that table.

 

Any help would be greatly appreciate.

 

Will

Dear Sirs

I have a situation creating an ODBC 32bit connection in SQL Server 2017 with sp1.

I'm using SQL Server Native Client 11 (11.00.6540) with diferent server names (Sql Server Name, IP, and localhost) and none of them works.

Firewall is off, 

Have anyone has come up with issue that can help me.

I'm trying to connect Dynamics GP 2018 in Sql Server 2017. 

I'll appreciate the help

Thank You

Richard Rivera

I'm a .NET developer and am largely a novice with the GP table structures, so hopefully this makes sense..

All of our invoices are processed through the Bank Rec tables - specifically CM20202.  Payments are batched into that table and then an external, third-party application reads and processes the records for payment.  I have another application - an app that I'm writing - that needs to pull the record of some of those payments, tie them back to their corresponding Purchase Order, and send that information to another third-party site.

So as that third-party application processes transactions, I would like to read something from that CM20202 record - probably DOCNUMBR, since it appears to be unique - and work backwards.  I believe the data I need for the Purchase Order lines is stored in POP10500, and I believe it contains the line items and receipts for each transaction processed against a PO.

What I need to know is this...is there a way to build a join back from CM20202 to POP10500?

If this doesn't make sense, ask a question and I'll try to clarify.  I'm as much trying to understand how stuff works as solve my problem.

Thanks in advance!!

Joel

Hi

I need to run a SQL script to identify users who have access to Fiscal Period Set Up [Tools | Set up | Company | Fiscal Periods] in GP 10. Can someone help with the script?

Thanks

Paul