another blog

Icon

About Microsoft Dynamics GP :-)

SQL Fiddle

http://sqlfiddle.com/about.html

A tool for easy online testing and sharing of database problems and their solutions.http://sqlfiddle.com/

 

Filed under: Good Findings, SQL

How to find table in all sql stored procedures

How to find table in all sql stored procedures

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ‘%Sop10100%’

Filed under: SQL,

Extracting payroll data from GP – Microsoft Dynamics GP / Great Plains Development Forum – Dynamics User Group

Extracting payroll data from GP – Microsoft Dynamics GP / Great Plains Development Forum – Dynamics User Group.

Filed under: Good Findings, SQL, ,

How to find, due to which Dynamics GP User sql stored procedure is calling?

Often we need to pass user id and intercompany id to sql stored procedure to do some data selection from custom tables or to maintain the log regarding user activity. This happens with us with one custom window of Dynamics GP, in the beginning our stored procedure didn’t need user id and intercompany id parameters and was working fine but with the client requirement changes at some point we needed to pass these two parameters, adding parameters means creating chunk and deployment to all machines, which was not possible as users were connected and updated was necessary as it was hindering there routine work.

We can get intercompany id easily by using   select dB_name() but we also needed to find the Dynamics GP user who is calling the stored procedure. To find that user we use this function inside our stored procedure and in this way  avoid updating chunk.

SUSER_SNAME()

This will returned the login dynamics gp user id when stored procedure will be called from GP.

Filed under: Dexterity Development, SQL,

Quick Tip: Using Third Party Tables to fill a Scrolling Window – Developing for Dynamics GP – Dynamics User Group

Quick Tip: Using Third Party Tables to fill a Scrolling Window – Developing for Dynamics GP – Dynamics User Group.

Good informative article. There are ways of doing things and important is what to use when what is needed. Although it is a little advanced but always workable techniques. There were times when I use to accessed third party tables and what I did is not very different. I usually do this.

1. Create SQL view for the table which I need to access, just take an example of this FA General Information Master (FA00100) table, with some other unique name.

create view V_dynFA00100
as
select * from FA00100
Go

grant select on V_dynFA00100 to DYNGRP
Go

2.Then create  table in dictionary with same view name, and fields (with same data types and dexterity field’s format). The number of keys is my choice but the Primary key will always be same as of FA00100 table.

and now I can use it like any GP table, get/previous and next. range or no range to show data on my form. And I don’t need to bother to create this table V_dynFA00100 from SQL Maintenance window.

 

almas

 

 

 

 

Filed under: Dexterity Development, Dynamics GP, dynamics-gp, SQL, ,

DEX_ROW_TS

To insert value in DEX_ROW_TS from back end  GETUTCDATE() will be used in sql.

 

Filed under: Dexterity Development, SQL, , ,

How to insert CR(Carriage Return) from sql in dexterity text field.

update POP10150
set CMMTTEXT=’Almas’+char(13)+’Mahfooz’
where POPNUMBE=’PO2071′

Filed under: Dexterity Development, SQL, , ,

Finding SQL table names with specific column.

Select TABLE_NAME
From INFORMATION_SCHEMA.COLUMNS
Where COLUMN_NAME =’ITEMNMBR’
and table_name like ‘IV%’

Filed under: Code Snippit, SQL, , ,

Categories

Enter your email address to follow this blog and receive notifications of new posts by email.

Enter your email address to follow this blog and receive notifications of new posts by email.

almas

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Blog Stats

  • 8,101 hits

RSS dynamics-gp

  • VSTools and database triggers.
    In SanScript coding we have database triggers, Below is a detail of what database triggers are as explained in dexterity manuals. Database triggers Database triggers are activated by successful table operations, such as a record begin read, saved, or deleted. The procedure that runs in response to a database trigger has access to the same […]
    Almas
  • Getting the current active window in Dexterity.
    Using dexterity there is not a direct way of getting current active window information, but with set of available GP functions and triggers can make this task happen. Below I am sharing a trigger and function which can tell the active window name. Below code needs to be changed according to the needs of developer, […]
    Almas
  • Hints and Tips… On going Post
    This is on going post and points will be added from time to time. 🙂 .. with Field Level Security we can not restrict document access on Sales transaction entry Window. Means we can not make user to choose only ‘Invoices’ in SOP Type drop down. .. we can not add custom table in table […]
    Almas
  • Reversing the sort order of Receivable Transaction Inquiry – Customer by Document Date
    Last few months we got more request on changing sort orders of Inquiry windows and lookups and to cover them all in a one place I have been working on a small project which will provide a simple sorting without much effort. In Dynamics Community there was a post having similar situation, I have already […]
    Almas
  • Getting the current open window in .net GP Add In
    There are many ways of getting information of active window in GP, but it changes according to the developing tool we are working on. 1.Dexterity.(the best tool 🙂
    Almas
  • Not all RW_Function are meant for reports.
    There are lots of RW functions available in a report writer Calculated Field Definition window and almost all of them are so handy while working on report writer, but it is a matter of fact that some of them are not actually meant for report writer. Usually RW functions are associated with report writer and […]
    Almas
  • Dexterity Can Wait and Respond.
    https://evspakistan.wordpress.com/microsoft-dexterity-can-wait-respond/Filed under: Can i, Dexterity Development, Dynamics GP, dynamics-gp, Uncategorized Tagged: Delay, dexterity, Wait
    Almas
  • Cross Dictionary Development.
    https://evspakistan.wordpress.com/cross-dictionary-development/    Filed under: Dexterity Development, Dexterity Triggers, Dynamics GP, dynamics-gp, Uncategorized
    Almas
  • Calling Sales Document Numbers Lookup
    Often developers need to open available lookup on their custom form from dynamics GP.Sales Document Number lookup is a very common and much used lookup and every dynamics technical person needs to call it in once (or more) in their lifetime. 🙂 Dynamics GP SDK provide a prototype of coding on how to open various […]
    Almas
  • This datatype name is already in use.
    This datatype name is already in use.
    Almas
Coding Notes

Code, News, Tech & Stuff

Dynamics GP Builders & Reporting

How to use MS Dynamics SmartList Builder, Excel Report Builder and other Reporting tools to get the data and analysis you need.

%d bloggers like this: