Triggering Sanscript to run anywhere in Dynamics GP

This is perhaps the best kept secret about Dynamics GP. You can use eXtender to trigger small pieces of code from anywhere you like, just like you can trigger an eXtender window to open. The hardest part of an implementation is usually the last 10% of functionality and this is made easy by eXtender Logic.

This example adds logic to an existing Great Plains form. In this scenario, we need to add logic to the Great Plains Sales Order Processing screen that checks to see if duplicate Customer PO number has already been entered for the specific customer. In this example we use the Logic Routine to create a trigger on an existing Great Plains form. In the screenshot we can see that the trigger has been placed on the Sales Order Processing – Customer PO Number – Field change event.

After the Customer PO number has been entered, the script runs which executes a query of the SOP work, open, and history tables to see if that particular Customer PO Number combination has been entered previously. If it finds it, it will open a dialog box asking if you want to “Open Inquiry, Continue, or Delete”.

If the user selects “Open Inquiry” it will open the Sop Document Inquiry window with the Invoice number that contains the duplicate PO Number. If the user clicks “Continue” it will allow the user to continue to enter the duplicate Customer PO Number. If the user hits delete, it will actually delete the current SOP document being entered.

The code window looks like this.

The actual code is posted below so you can see exactly what happens. Again I repeat that if you do not understand the code below - then don't try and write logic scipts. If this makes perfect sense then there is so much you can do within GP with eXtender logic. The best part is you can deploy this code by sending a script to be imported, and you never need to cnk up or upgrade dictionaries again for small customisations.

local string ls_po_number, ls_customer, ls_sop_number;
local integer li_sop_type;
local text lt_sql;
local long ll_sql_connection, ll_status;
ls_customer = 'Customer Number' of window SOP_Entry of form SOP_Entry;
ls_po_number = 'Customer PO Number' of window SOP_Entry of form SOP_Entry;

if empty(ls_po_number) or empty(ls_customer) then
abort script;
end if;
li_sop_type = 'SOP Type' of window SOP_Entry of form SOP_Entry;
ls_sop_number = 'SOP Number' of window SOP_Entry of form SOP_Entry;
SQL_Connect(ll_sql_connection);
lt_sql = "USE " + 'Intercompany ID' of globals;
SQL_Execute(ll_sql_connection, lt_sql);
{check if customer and po combination exists in work table}

lt_sql = "select SOPTYPE, SOPNUMBE from SOP10100 where ";
lt_sql = lt_sql + "CUSTNMBR = " + SQL_FormatStrings(ls_customer) + " and ";
lt_sql = lt_sql + "CSTPONBR = " + SQL_FormatStrings(ls_po_number) + " and ";
lt_sql = lt_sql + "not (SOPNUMBE = " + SQL_FormatStrings(ls_sop_number) + " and SOPTYPE = " + str(li_sop_type) + ")";
ll_status = SQL_Execute(ll_sql_connection, lt_sql);
if ll_status = 0 then
ll_status = SQL_FetchNext(ll_sql_connection);
if ll_status = 0 then
SQL_GetData(ll_sql_connection, 1, li_sop_type);
SQL_GetData(ll_sql_connection, 2, ls_sop_number);

case ask("A document has already been entered for this customer with this purchase order number.", "Open Inquiry", "Continue", "Delete")

in [ASKBUTTON1]

open form SOP_Document_Inquiry;
if isopen(form SOP_Document_Inquiry) then
'Sort By' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script 'Sort By' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;

'All Or Range' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script 'All Or Range' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
'Start SOP Number' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;

'End SOP Number' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;
'Include GB' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 0;
run script 'Include GB' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
run script 'Redisplay Button' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
end if;

in [ASKBUTTON3]
run script delayed 'Delete Button' of window SOP_Entry of form SOP_Entry;
end case;

abort script;
end if;
end if;

{check if customer and po combination exists in history table}

lt_sql = "select SOPTYPE, SOPNUMBE from SOP30200 where ";
lt_sql = lt_sql + "CUSTNMBR = " + SQL_FormatStrings(ls_customer) + " and ";
lt_sql = lt_sql + "CSTPONBR = " + SQL_FormatStrings(ls_po_number);
ll_status = SQL_Execute(ll_sql_connection, lt_sql);
if ll_status = 0 then
ll_status = SQL_FetchNext(ll_sql_connection);
if ll_status = 0 then

SQL_GetData(ll_sql_connection, 1, li_sop_type);
SQL_GetData(ll_sql_connection, 2, ls_sop_number);
case ask("A document has already been entered for this customer with this purchase order number.", "Open Inquiry", "Continue", "Delete")

in [ASKBUTTON1]
open form SOP_Document_Inquiry;
if isopen(form SOP_Document_Inquiry) then
'Sort By' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script 'Sort By' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
'All Or Range' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script 'All Or Range' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
'Start SOP Number' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;

'End SOP Number' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;
'Include GB' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
run script 'Include GB' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
run script 'Redisplay Button' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
end if;

in [ASKBUTTON3]
run script delayed 'Delete Button' of window SOP_Entry of form SOP_Entry;
end case;
abort script;
end if;
end if;
SQL_Terminate(ll_sql_connection);

Triggering SmartConnect from an eXtender Object

It is great to be able to capture custom data inside of eXtender. What is truly powerful is taking this data and pushing it through the GP business logic (eConnect) and creating new records.

This is example is going to look at Triggering a SmartConnect map from a form created in eXtender. Using the same form that was created in the previous Blog entry (Tenant Management), use a combination of eXtender and SmartConnect in order to create a new Customer in Great Plains whenever a new tenant is created in our eXtender form.

In this case, a real time datasource was created in SmartConnect using our Tenant Management form as the source.

A map was created in SmartConnect that mapped the fields from our Tenant Management form into the Customer Maintenance form. The left side of the map window shows the values from the Tenant Management form.

When the map is saved, a real time Dexterity trigger is created on our Tenant Management form. When any record in the table is updated, it will run the SmartConnect map that will create/update the customer record in Great Plains.

This shows how every GP consultant can not build screens, and create transactions without ever writing a single line of code. Now that is powerful.

Executing Sanscript from an eXtender object

This article looks at how to include sanscript code on an eXtender form from any of the following trigger points:

• Field Entry of any field
• Field Change of any field
• Field Exit of any field
• On opening the object
• On clicking on an extra window (button)

We will specifically explore here the way to embed sanscript code into an eXtender form to open a regular GP window and pass some parameters.

In this example, a Property Management application was created using eXtender. A tenant form was created to allow tenant information to be tracked. The tenants are setup as customers in Great Plains.

An extra window was added at the bottom of the window that is labeled Rent Payment. The Rent Payment button has a few lines of sanscript code that will automatically open the Cash Receipts Entry window, and automatically populate the Customer Number with the Tenant ID. This is ideal to give the work flow process that is ideal for the customer.

The code that performs this operation is repeated here. If you are a developer you will understand this perfectly. If you do not understand what is below then you should not be writing logic code and should either contact us at eOne for consulting help or contact you local dex guru who can whip up scripts in no time.

local string ls_tenant_id;
call with name "GetString" in dictionary 3107,ls_tenant_id,"Form","PM_TENANT_CARD",CurrentID,1,"Header",0;

open form RM_Cash_Receipts;
set 'Customer Number' of window RM_Cash_Receipts of form RM_Cash_Receipts to ls_tenant_id;

run script 'Customer Number' of window RM_Cash_Receipts of form RM_Cash_Receipts;

Happy Thanksgiving & Thank You!

During a quick run to the grocery store today, I found myself weaving in and out of carts piled high with the common commodities needed for a Thanksgiving feast. The store was chaotic, everyone gearing their efforts to accomplish one thing: make and eat a ton of food with friends and family. The site alone reminded me of the great abundance we enjoy and how important it is to be thankful.

With Thanksgiving feasts and festivities in mind, I want to express our heartfelt gratitude and appreciation for all you do for us as partners, customers, and supporters of eOne. We truly value your continued promotion and support of our products and services. It is because of people like you that we have experienced an abundance of success this year.

May your Thanksgiving be filled with joy and abundance!

GP Developers Get Extender

This week 263 of the Dynamics GP partner communities finest developers gathered in Fargo.

During the main stage presentation on day one, we were able to give this audience a 10 minute sniff of eXtender. Errol Shoenfish challenged the 'boys from eOne' to develop a random eXtender application in 45 minutes while the session was being presented. The audience picked the topic of 'professor classroom/schedule management', and Chris and I left the room to get started.

When we returned to stage we were able to show a brand new mini application that had 5 new eXtender forms, 1 eXtender window, 4 new SmartLists, and one live Excel Report Builder report. (I will record a video and post this up here in the next few days of the what the solution looked like.) From all reports people were generally amazed at what we could achieve in that time period.

The conclusion is that Dynamics GP eXtender must be part of every GP demo ever presented.

Now - what we could not show is the three things that the developers really wanted to see:

1. That they can embed sanscript code behind any of those forms or windows they built with eXtender. (Requires eXtender Enterprise)

2. That they can trigger a SmartConnect (eConnect) map real time using the data form any of those windows or forms. (Requires SmartConnnect)

3. That they can trigger SanScript code to run anywhere in Dynamics GP, based on the field entry, field exit or field change events of any GP screen. (Requires eXtender Enterprise)

Over the next three days I am going to explore each of these three topics further and in depth here on this blog.

Poppy Cock

Below is a replication of an extract from a recent newsletter from Accolade Publications. I could not share the message better, so will let it stand as below.

Reports on Extender Data

It seems that the most mis-understood part of Extender is the availability of the data entered in Extender objects (windows and forms) for use in reports. Report designers typically head straight for the data tables to find business information, build relationships, and place data on reports. When they hit those "named pairs" structured Extender tables, only the strong are successful. Many toss in the towel and complain about how difficult the data structure is and how hard it is to write reports against Extender.

Poppy Cock!

Browse down to the Views section on the Extender desktop. Take a minute and create a view for each window or form. These views are true SQL views that re-organize the window table into a more familiar format. Each window or form is represented as a single line of data and each field on the window or form will be represented in the data structure, as if each form or window had it's own table in the database. These views can then be used like any other view, and exactly like a table in the database, to create reports using any SQL reporting tool.

Much easier!

Tips like these are found in books available from
www.AccoladePublications.com

Airlift

I will be one of the 400 people heading into Fargo next week for the Technical Airlift. I am really looking forward to seeing how GP's new improved functionality is going to help 'airlift' sales of Dynamics GP in 2010.

What is a sales guy doing at a tech conference? A couple of really key reasons:

1. Technology=Sales

The connection between technical advances in GP - and sales has never been stronger. There was a time when we could rely on 'Microsoftism's' to sell GP, but that is not enough any longer. We need to have the best product, with the best road map. I am keen to have Microsoft show me how we can beat all the competition by having a technically superior solution to our competition.

2. This goes with That, and hooks to that?

GP has great modules, ISV's have great solutions - but how does everything work and talk together? As VAR's this is our job to eliminate this question for our customers and present a 'solution' that is made up of the best of the best. One of my aims at Airlift is to help the technical crew get a grip on how to hang things together.

For Example: When to use SmartList Builder, when to switch to Excel Report builder - or is Navigation List Builder a better option when working with the HR module?

Or if I am using Wennsoft, how can we integrate and automate the next steps?

Or how to best tie custom entities in CRM with GP.

Or how to populate eXtender fields in GP with the data captured in CRM?

Or I love Rockton's SmartFill addon - but does it work with eXtender and other third parties?

Or How do I make that V10 workflow stuff, fit with a real customer need?

3. eOne and V10 sp4 and V11

eOne has played a big part in the release of both SP4 and the upcoming V11. The first place to learn all the secrets of what is coming next from eOne/Microsoft is Airlift.

4. Understanding the power of SC

There are a few developers out there that still insist on writing directly to eConnect. I am going to discuss all the things SC can do as part of a full GP solution - and help reduce dev time while making more money for VAR's.

5. Talking up Excel Report Builder

ERB is really cool and i think the most under utilised module out there, that most customers already own. Come and talk to any Microsoft Rep or anyone from eOne to learn about where and when it will help you win a deal.

So there are a few reasons why I am flying right to the other side of the globe to spend 2 days with 400 technical guru's. It is the tech teams that bundle together solutions in a way that solve our customers business needs. If we do not get the tech components right then we are all out of business.

On a personal note.

This Saturday I competed in and completed the Hawkesbury Classic. This is a 111km overnight kayak race on Sydney's Hawkesbury River. This was physically the hardest thing I have ever done, by far. 16hrs of propelling a boat forward by hand. The last 2 hrs was a challenge of the mind as there was nothing left physically to give. I could draw all sorts of business parallels and metaphors but that would be demeaning to the 650 competitors. There is simply nothing quite as satisfying as setting a goal, and then achieving it.

Having data specific extender windows pop up! Impossible?

One of the well kept Microsoft Dynamics GP Extender secrets - is that you can have different eXtender windows pop up based upon the data you are working with. For Example, you may want to capture extra information about an inventory code but the data you need to capture is different depending on the inventory item. Lets say you sell both telephones and hard drives. For the telephone you need to track (make, model, color, hands free, number of lines, and a host of phone features such as call back and number storage) while for the hard drive you need different information (storage capacity, make, connection types, connection speed, Mac support etc)

Using Extender you do not need the user to be aware of the different data types. Users simply need to open the item extras window (or have it pop up automatically) and Extender will ask for the correct information based on the item currently open in the inventory screen.

So how do you do this?

1. Create two independent Extender windows with both linked to item maintenance

2. Use the window Groups function to link these two windows together. Call this group 'Item Details ' or a similar useful descriptor. You are able to group in here as many windows as you require, so it might be just two or you can have 50 different windows for inventory.

3. Use the conditions section to define under what circumstances you want this window to open. In this case we have said "if the item number = 'INSTALL'" open the Extender install window. You can use any field from the main GP screen in your condition. (e.g. you can use item class, price list etc).

4. Set this group (under options) to either auto open, leave it as a manual exercise or add a hot key CTRL E for Extra is good. (using a hot key is useful in that no matter where the user is they can hit CTRL E and be sure the extra window will open).

That's it. Next time you open the item window and select one of these items - the correct eXtender window will be available.

Using your imagination you can use this in many ways, and not just with inventory. You can have different windows pop up based on zip code, state, salesperson, customer class, vendor class, price list and so forth.

This functionality is available with both Microsoft Dynamics GP Extender as well as with Extender Enterprise.

When to Show it?

All the time. You can see how easy it is to build this scenario, and if you want to differentiate GP from AX, NAV, Sage, Accpac etc then this is a great way. Then have your prospect ask to see how Accpac can configure data specific user defined fields - during the demo?

In you pre-sales discovery find a 'need' for this functionality. It is something you know the opposition can not do, so find a need and change the customers expectations of an ERP.

Changing the ERP Game with eXtender

Fargo in November

If you're heading to Fargo in November, bring gloves and a hat, and more importantly, be sure to attend our SmartConnect training November 9th and eXtender Enterprise training November 12th!

We're holding classroom training for our partners before and after the Microsoft Dynamics GP Technical Conference event so you can make the most of your time in Fargo.

Please sign up with me (email abbey.heesch@eonesolutions.net) as soon as possible to reserve your spot as there is limited seating available. Each session will be held on the Microsoft campus and will include a full day of classroom training, as well as lunch & a snack. Pricing is $795/person for both sessions or $595/person for one session.

If you have any questions on the training, please don't hesitate to call(888.319.3663 ext 717) or email (see above address).

Looking forward to seeing you in November!



For more information the GP Technical Conference visit: http://www.microsoft.com/dynamics/fargodeveloperconference/