Missing custom windows from Smartlist Builder

I was adding a Smartlist to GP for a custom feature I added using Dexterity.  When I went to create a Go To for this Smartlist so it could open my Dex Inquiry form for the feature I could not find the form.

Fortunately I had a test environment and the feature worked there.  Using a SQL debug in GP I was able to isolate the issue to missing entries in the EXT90103 table.  This is an Extender table, but we don't use the Extender product with GP.

I was able to add the entries that were missing from the dev system and this resolved the issue - my inquiry form was then available.

I don't understand the reason for the issue but am glad I was able to find a work-around.

Vendor Lookup in Dexterity

Spent the better part of this day on this one so here's hoping you don't have the same.

I was trying to add a vendor lookup to my custom Dexterity window.  Using the traditional script of "open window ... return to" and "run redisplay button" was getting me nowhere.  The vendor lookup window opened but there were no vendors in the scrolling window.

The win in this was to set the sort option on the window.  Here is my code that worked.

open form Vendor_Lookup return to 'Vendor ID';
'Vendor ID' of window Vendor_Lookup of form Vendor_Lookup = 'Vendor ID';
'Vendor Sort By' of window Vendor_Lookup of form Vendor_Lookup = 1;
run script 'Redisplay Button' of window Vendor_Lookup of form Vendor_Lookup;
Bottom line is if you're trying to open an existing GP lookup and getting no data try to set the "Sort By" field.

GP Dexterity open PO Receiving Inquiry window

Recently I developed a window to ease invoice/shipment matching in AP. I thought it would be nice if I could give them a link to open the Receiving Inquiry window so they could view the entire transaction. You see I was just giving them a list of select fields.

Took me a bit of work but I figured this out. I first did a debug from the Purchase Order Documents Inquiry window, that opens the Receiving Inquiry window. I found the following lines.

Historical shipment transactions:
'OpenWindow() of form POP_Inquiry_Receivings_Entry', 0, "0022708", 2, 3, 1

Open shipment transactions:
 'OpenWindow() of form POP_Inquiry_Receivings_Entry', 0, "0053442", 2, 4, 1

The closed parenthesis means OpenWindow is a function in the form. 

Further I found this in the SDK:
------------------------------------------------------------------------
PURCHASING FORM FUNCTION:  OpenWindow
 of form POP_Inquiry_Receivings_Entry
------------------------------------------------------------------------
function returns         integer                  nStatus;
in                       'POP Receipt Number'     sRcptID;
in                       integer                  nMenu;
in                       integer                  nOrigin;
optional in              integer                  nCurrencyView=CURRENCYVIEW_ORIGINATING; 

I have no clue what nMenu is for.  Further research and testing is required.

To call this function and open the Inquiry window you would do something like this in Dexterity:

local long fnresult;
local integer posted;

clear table POP_Receipt;
'POP Receipt Number' of table POP_Receipt = receiptnumber;
get table POP_Receipt;
if err() = OKAY then
  set posted to 4; {work}
else
  set posted to 3; {history}
end if;

fnresult = OpenWindow(ReceiptNumber, 2, posted, CURRENCYVIEW_FUNCTIONAL) of form POP_Inquiry_Invoice_Entry;

There are other nOrigin values, I am sure.  If anyone figures those out or what nMenu is please add in comments.  I will update as I find those other values.

SQL code to check for open GL period in Dynamics GP

In Dynamics GP periods can and should be closed to prevent users from posting data where they should not.  To determine if the period is closed use the following code in SQL Server:


DECLARE @I_sSeries SMALLINT,
        @I_cOrigin CHAR(50),
        @I_dDate DATETIME,
        @I_dUserDate DATETIME,
        @O_sPeriodID SMALLINT,    -- Period ID
        @O_tClosed TINYINT,  -- 1 if period is closed
        @O_sYear SMALLINT, -- fiscal year
        @O_iOUTErr INT,   -- 0=no errors; 2=date not in a fiscal year; 3=unknown
        @O_iErrorState INT;

-- Financial=2, PM=4, Sales=3, IV=5, PR=6
SET @I_sSeries = 2;
-- SELECT DISTINCT odesctn FROM sy40100 ORDER BY odesctn
SET @I_cOrigin = 'General Entry';
-- date to check if period is closed
SET @I_dDate = '11/1/17';
SET @I_dUserDate = CAST(GETDATE() AS DATE);

EXEC Glcalculateglperiod @I_sSeries, @I_cOrigin, @I_dDate, @I_dUserDate,
 @O_sPeriodID OUTPUT, @O_tClosed OUTPUT, @O_sYear OUTPUT, @O_iOUTErr OUTPUT, @O_iErrorState OUTPUT;
SELECT @O_sPeriodID, @O_tClosed, @O_sYear, @O_iOUTErr, @O_iErrorState
PRINT CASE WHEN @O_tClosed = 1
THEN 'Period is Closed'
ELSE 'Period is Open' END;

Smartlist Builder misbehaving on Go To

Using Dex in Dynamics GP 2015R2 I created a custom table and have a Smartlist created for this data.  There is an unique ID field for each record in the table and an index on that field.

When I created the Smartlist for this I wanted a GoTo that would open the inquiry window I created in Dex for this table.

When the form opened it sometimes displayed the right record, and sometimes did not.

I sorted for the form by a different column and found that it was returning the first in the sequence by that column.

I realized that it was likely the key of the Smartlist table was probably defined incorrectly.  When I adjusted that I received the correct result.

Presenting addresses in SSRS

If you need to print forms in SQL Reports I have an easy way to do it so that blank address lines are removed.

Many systems, for example Dynamics GP, have as many as 3 lines of addresses, in addition to name, contact name, city, state and zip.  I rarely see anyone use line 3 but for the rest here is the method I developed to print all this in SQL Reports.

My method uses text concatenation of the separate address fields into a single field using SSRS expressions.  Whether you use a text box or a table the result is the same.

=TRIM(address1)+
TRIM(address2)+
IIF(TRIM(address2)="","",vbcrlf)+
TRIM(address3)+
IIF(TRIM(address3)="","",vbcrlf)+
TRIM(city)+", "+TRIM(state)+"  "+TRIM(zip)


SQL Reports from GP - a date problem

Just tried to update one of my SQL Reports by adding a date column from GP.  The column rendered fine in the SQL source, showing the date as expected.  However no matter what I did in SSRS I could only get #ERROR for the new column.

After banging my head some, and some more googling, I realized that perhaps the way GP stores the "no date" value, 1/1/1900, could be the problem.

So I changed my source to convert 1/1/1900 to nulls and like magic the dates started to show.

CASE WHEN datecol = '1/1/1900' THEN NULL ELSE datecol END

I believe 1/1/1900 is the start for SSRS dates.  So it might make sense.

Anyway the moral is if you are using GP date columns that have 1/1/1900 for a SQL Report you might want to do the conversion of 1/1/1900 to null.

** Update 12/18/17 **
Found another possible cause - using the "c" format code on a date field.  Made this typo mistake and was miffed why my dates showed #error - until I check and saw the "c" there.  Once I switched it to "d" the dates showed just fine!

DefaultDblClick in Dexterity

Took me a while to figure this one out, being the Dex genius I am.  Also there is almost nothing searchable about this so here goes.

If you have a scrolling region in your custom window in Dexterity and you want to double click on a row and have your app do something here is what you do.

Set the DefaultDblClick property of the scrolling window to True.  This is done from the Object properties tab of the open scrolling window.  According to the docs I've seen the scrolling window needs to be WindowType=BrowseOnly.

On the main form you need a button to take action based on the double click.  This button needs to have the property Default=True.  To be safe I set Hyperspace=True also.

The action button I want clicked is not located visibly on my form (Visible=False).  The following script is run as the Change property of this button:

open form <my form>;

<field> of window <my window> of form <my form> = <field> of window <scroll window>;
{repeat above for all fields needed in the opening form}

run script 'Display Existing Record' of window <my window> of form <my form>;

That did it for me.  Hope it works for you!

FetchXML aggregate data source

If you need to create reports for CRM Online you need FetchXML.  Here is a sample of some FetchXML code that aggregates data.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
  <entity name="phonecall">
    <attribute name="modifiedon" groupby="true" alias="modifiedon" dategrouping="day"/>
    <attribute name="statecode" groupby="true" alias="statecode"/>
    <attribute name="statuscode" groupby="true" alias="statuscode"/>
    <attribute name="activityid" aggregate="count" alias="counter"/>
    <filter type="and">
      <condition attribute="modifiedon" operator="on-or-after" value="2016-12-01" />
    </filter>
  </entity>
</fetch>

In this case we are totaling the status and state code of phone calls that were updated on or after 12/1/2016.

Normally FetchXML is extracted directly from CRM's Advanced Find, but aggregating data is not.

Note that if you have more than 500 records and want results in BIDS you will need the COUNT="50000" clause to the <fetch> line.  If there are more than 50000 records in the data set you will have to upload the report to CRM Online for it to work.