DynDeveloper.com

DynDeveloper.com
Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now! Log In
Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Archives

 
SOPTransactionType, Multiple lines with one tax line

Props to Steve Massey at DataMasons for sending this in, it shows a SOP transaction with multiple lines and one tax node. We show that the LNITMSEQ in the lines is not related to the LNITMSEQ in the taSOPLineIvcTaxInsert.

 
All Vendors that do not have a record in the transactional tables

I received a request yesterday to write a query that would find all vendors that did not have a record in any of the transactional tables; so I thought I'd talk for a little bit about that.

The first thing is that you should not be doing this if you don't understand SQL or the Dynamics GP table structure. Take a backup first.

Second, there is a little bit of selectivity in use here. I started with a query of all the tables that use the VENDORID field, in my install there are 188. Too many for me to want to include all of them in my searching. But, I know that I can eliminate the SVC tables and the JC tables because this client doesn't use them.

So, you would use the script below as a template and modify it to your needs.

DELETE into an OUTPUT table

Did you know that it is possible to OUTPUT a table from a DELETE statement? This OUTPUT table would contain all the rows that you deleted, and you can then use that table in other processes.

Sample code below.

dd_BonusCalc

<deep breath>

This query is a data source for a report to calculate overtime pay on bonuses. I don't really understand the accounting, but the general idea is that if you pay a bonus and the employee has overtime in that period, the bonus needs to be increased because of the overtime.

This script handles that and produces the extra amount for the overtime bonus.

It may very well be company specific (it may only work for the company that I wrote it for) but I'm posting it in case it comes up.

If you use it, I'd be really interested in knowing that it worked or didn't. There is a comment area at the bottom of the article. Thanks!
POPTransactionType
Sample POPTransactionType document
FORMATMESSAGE (SQL equivalent to String.Format)

I really love the .NET String.Format function, and you can use the SQL FORMATMESSAGE function to achieve the same thing.

Copy the code below into SQL and run it so see all the possibilities
Is it possible to restore specific integrations from a IM backup

I am now receiving a few requests for integrations to be restored to their previous working order, I have a few backups.

Is it possible to selectively choose what to restore or is it only the entire DB? If only the entire DB is it possible for me to view the integrations in the database and try to reverse engineer what I am being asked about?
eConnect taPoLine CMMTTEXT field size

The eConnect taPoLine CMMTTEXTfield size is only 500 characters, however, the CMMTTEXTcolumn in the POP10550 table is a text datatype. 

Is there a way to pass comments that are beyond the 500 character limit when the PO is created?  If not, is there a work around to accomplish that?
eConnect Issue with Project Creation.

Hello,

I am having a hell of time trying to get projects inserted into GP 2015.  We have a VB script that uses the eConnect nodes to insert our projects into GP.  It has been working for 3 years until now.  I have no idea what and where the issue could be. 

The eConnect error is shown below. 

I bolded the error that confuses me because THERE is no where in the script and all the tables where 'ADMIN' would exist. 

I have debug the script and it fails at this point

e.CreateEntity(sConnectionString, scontractDocument)

I looked at the XML and I also do not see any 'ADMIN' in it.  I have included our XML below.

Any insight would help.  Thanks

-------------------------------------------------------------------

Event[0]:
  Log Name: eConnect
  Source: Microsoft.Dynamics.GP.eConnect
  Date: 2016-11-03T12:55:22.000
  Event ID: 0
  Task: N/A
  Level: Error
  Opcode: Info
  Keyword: Classic
  User: N/A
  User Name: N/A
  Computer: ACC-GP01.accretivesolutions.com
  Description:
Action:
Create

Current User Name:

Input parameters:

Exception type:
System.Data.SqlClient.SqlException

Exception message:
Conversion failed when converting the varchar value 'ADMIN                ' to data type int.

Stack Trace:
   at Microsoft.Dynamics.GP.eConnect.ServiceProxy.HandleSqlException(FaultException`1 ex)
   at Microsoft.Dynamics.GP.eConnect.ServiceProxy.CreateEntity(String connectionString, String xml)
   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.EntityImportImplementation(String connectionString, String sXML, Boolean isTransaction)
   at Microsoft.Dynamics.GP.eConnect.eConnectMethods.CreateEntity(String connectionString, String sXML)

------------------------------------------------------------------

XML

 

<eConnect>
    <PAContractsType>
        <eConnectProcessInfo xsi:nil="true"/>
        <taRequesterTrxDisabler_Items xsi:nil="true"/>
        <taPAContracts>
            <CUSTNMBR>FF40559     </CUSTNMBR>
            <PAcontid>FF94981</PAcontid>
            <PAcontname>ERA</PAcontname>
            <PACONTNUMBER>FF94981</PACONTNUMBER>
            <PAcontclassid>8010</PAcontclassid>
            <PAProjectType>1</PAProjectType>
            <PAAcctgMethod>1</PAAcctgMethod>
            <PAMCCURNCYID>Z-US$</PAMCCURNCYID>
            <PAContMgrID>080159</PAContMgrID>
            <PABusMgrID>080159</PABusMgrID>
            <ADRSCODE>C144032</ADRSCODE>
            <SLPRSNID> </SLPRSNID>
            <PABILLFORMAT>T&M MULTIWK SUMMARY</PABILLFORMAT>
            <PRBTADCD>C144032</PRBTADCD>
            <PATRKCHGORDS>1</PATRKCHGORDS>
            <PATRKCOBDGADDFLY>1</PATRKCOBDGADDFLY>
            <USRDEFND1>12/12/2016</USRDEFND1>
            <USRDEFND2>1/27/2017</USRDEFND2>
        </taPAContracts>
        <taPAContractBillingCycle_Items xsi:nil="true"/>
        <taPAAccountsSetup_Items xsi:nil="true"/>
        <taPAProjects_Items>
            <taPAProjects>
                <CUSTNMBR>FF40559     </CUSTNMBR>
                <PACONTNUMBER>FF94981</PACONTNUMBER>
                <PAprojid>FF94981</PAprojid>
                <PAprojname>ERA</PAprojname>
                <PAPROJNUMBER>FF94981</PAPROJNUMBER>
                <PAProjectType>1</PAProjectType>
                <PABBeginDate>12/12/2016</PABBeginDate>
                <PABEndDate>1/27/2017</PABEndDate>
                <PAprjclsid>8010</PAprjclsid>
                <PADepartment>08     </PADepartment>
                <PALabor_Rate_Table_ID>Finan94981</PALabor_Rate_Table_ID>
                <PALabor_RateTable_Type>1</PALabor_RateTable_Type>
                <PABILLFORMAT>T&M MULTIWK SUMMARY</PABILLFORMAT>
                <PA_RestrictCustomerList>0</PA_RestrictCustomerList>
                <PATRKCHGORDS>1</PATRKCHGORDS>
                <PATRKCOBDGADDFLY>1</PATRKCOBDGADDFLY>
            </taPAProjects>
        </taPAProjects_Items>
        <taPAProjectBillingCycle_Items>
            <taPAProjectBillingCycle>
                <PAPROJNUMBER>FF94981</PAPROJNUMBER>
                <PABILLCYCLEID>BIWEEKLY</PABILLCYCLEID>
                <PABILLFORMAT>T&M MULTIWK SUMMARY</PABILLFORMAT>
            </taPAProjectBillingCycle>
        </taPAProjectBillingCycle_Items>
        <taPAProjectAccess_Items xsi:nil="true"/>
        <taPAProjectEquip_Items xsi:nil="true"/>
        <taPAProjectFeeSchedule_Items xsi:nil="true"/>
        <taPAProjectFee_Items xsi:nil="true"/>
        <taPAProjectBudgetMaster_Items>
            <taPAProjectBudgetMaster>
                <PAPROJNUMBER>FF94981</PAPROJNUMBER>
                <PACOSTCATID>SERVICES</PACOSTCATID>
                <PAProfitType>1</PAProfitType>
                <PAbllngtype>1</PAbllngtype>
                <PABBeginDate>12/12/2016</PABBeginDate>
                <PABEndDate>1/31/2017</PABEndDate>
                <PABQuantity>158.40</PABQuantity>
                <PABUnitCost>80.290000</PABUnitCost>
                <PABProfitAmt>185.000000</PABProfitAmt>
                <PABProfitPcnt>35.000000</PABProfitPcnt>
                <PABaselineOvrhdPct>35.000000</PABaselineOvrhdPct>
            </taPAProjectBudgetMaster>
        </taPAProjectBudgetMaster_Items>
    </PAContractsType>
</eConnect>
GP Tasks

I would like to know more about GP tasks.   What can initiate them, and how do you remove an entire recurring series of tasks?

We had over 5000 recurring task entries for one task that appeared one day last week on most of our GP users' home pages.

The only way we found to remove the task from view was to complete it, one by one - very time-consuming.

If I know what table(s) is involved, I could delete the entries easily.

 

 

 

 
Consulta

existe alguna tabla que contenga un registro de saldo pendiente de pago asociado al numero de cliente?

Is there a table that contains an outstanding balance record associated with the customer number
Dexterity not showing new fields assigned to a variable.

I have a form with variables on it. I have an array of 6 long types, the fields are on the form. At the end of a script, I have added these values to be modified. The values get modified but the new values do not show on the form. Is there any way to refresh the form to show these changed fields. Dex newbie here FYI. Bewlos is the code, as you can see from commented code, i check the values if they are assigned and the assignment works fine but new values do not show up on the form. Redraw field does not work either. I have Formula_Market on the form and need to show values from Formula_Market_Actual. Please help.

for i = 1 to 6 do
{warning str(Formula_Market[i]) + " BEFORE : " + str(Formula_Market_Actual[i]);}
Formula_Market[i] = Formula_Market_Actual[i];
[redraw field Formula_Market[i];]
{warning str(Formula_Market[i]) + " AFTER: " + str(Formula_Market_Actual[i]);}
end for;
Trying to create a Vendor import integration in IM

I am trying to create a Vendor import integration for new vendors in GP 2010 using IM in lieu of me importing using .csv and SQl for end users.

Is it possible to use a Source file that loads into SQL tables or is that frowned upon? So far I only see default integration sources, can these be created custom or is this a limitation of IM?

 

I was able to select Destination Vendor Under AP, looks to be okay to use for a basic vendor integration, but what If I wanted to setup a complete vendor, banking info, emails, ACH/ EFT details, etc. If this is a separate integration fro EFT banking information how would I go about creating a new EFT Vendor destination.  I am not understanding how to create something this custom.

 

Any help would be great from the IM experts out there!

 

Thank you

 

Mark

 
Integration Manager IM

acabo de instalar IM pero me pide registrarlo, he puesto el site name y los key iguales a los de GP pero dice que son inválidos.

Como lo registro?

Puedo utilizarlo sin Registrar?

I just installed IM but it asks me to register it, I have put the site name and the same key as the GP but it says they are invalid.
How do I register it?
Can I use it without registering?

 


 
Integration Manager 14.0

I'm trying to use IM to move Project transfers(part number, qoh, Serial/lot) from one company into an Inventory Adjust transactions in another company.  I'm getting an error that says:

DOC1 WARNING: The number of selected lot numbers does not equal the extended
quantity for item 'xxxxx-xxx'.

 

The part has been created in the second company and I can see the transfer but the Serial./Lot data does not import.  I've verified that the qty being transferred matches the Lot and or Serial QOH.    I'm using standard Integration destination because the eConnect version does not even have the serial/lot destination fields.

Thanks you for your help,

Cathy
Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
Archives | Table Listing | eConnect Error Database | eConnect Schema | Anchored Marriage
3