Microsoft Wonderful Plains SQL Reporting and Information Correcting

This is newbie stage SQL scripting posting for DB Administrator, Programmer, IT Expert

Our and Microsoft Business Alternatives objective in this article is to educate database administrator, programmer, program developer to empower them help Microsoft Excellent Plains for their businesses. In our opinion self help is the purpose of Microsoft to facilitate implementation of its items: Fantastic Plains, Navision, Solomon, Microsoft CRM. You can do it for your corporation, remaining conscious on basic data maintenance procedures and attractive to Microsoft Organization Solutions Techknowledge databases. This will allow for you to avoid highly-priced consultants visits onsite. You only will need the assist from professional when you plan on intricate customization, interface or integration, then you can appeal to any person who specializes in these responsibilities and can do cheap country-broad remote help for you.

Let us seem at very simple info mend techniques.

1. How to delete the user:

If person, shuts down computer system, prior to signing off Wonderful Plains – she/he will get the message that she/he is currently signed. Then you will need to delete the consumer from the activity desk. Run the query beneath in opposition to DYNAMICS databases – it will unlock the consumer:

delete Action where by USERID=’MARIE’

2. How to unlock the batch:

From our knowledge – this is 2nd FAQ we are listening to from Great Plains administrator. Operate this script towards organization databases to unlock hanging batch:

update SY00500 set BCHSTTUS= where by BACHNUMB = ‘BATCH0023’

In some cases you also have to run this script towards DYNAMICS database (if initial script does not do the position)

delete SY00800 the place BACHNUMB = ‘BATCH0023’

3. Some Reporting Queries – Before we go there, we would like you to be ready to obtain the table with the data you are wanting for. Start Excellent Plains and go to Applications->Resource Description->Tables. Find the desk in the good series. If you are wanting for the consumers – it ought to be RM00101 – consumer grasp file.

Now lets question Buyer Grasp desk:

select CUSTNMBR, CUSTNAME from RM00101 the place Condition=’CA’

This question will return Buyer Variety, Consumer Name for all the prospects in California

Next query will give you top rated 10 Stock Things with the most effective Sales Success

decide on a.ITEMNMBR, a.ITEMDESC, b.SALESNUMBER from IV00101 a be part of (pick out leading 10 ITEMNMBR, depend(ITEMNMBR) as SALESNUMBER from SOP30300 the place SOPTYPE=3 group by ITEMNMBR get by count(ITEMNMBR) desc) b on a.ITEMNMBR=b.ITEMNMBR

Content querying and supporting! if you want us to do the assistance you – give us a simply call 1-866-528-0577! [email protected]