Great Plains Dexterity Dynamics eEnterprise Microsoft CRM Microsoft RMS Crystal Reports SQL .Net C# International Chicago Illinois Customizations Great Plains Programming Development Offshore Programming FRx ReportWriter Integration Manager POS Upgrade Data Recovery Conversion EDI Barcoding SQL Multicurrency Microsoft Business Solutions Great Plains Certified Master FAQ Forum Continuum for VB Btrieve Ctree Pervasive SQL.2000 Great Plains Business Portal eOrder eView eRequisition Intellisol Kampdata Mekorma Horizon Wennsoft Naperville Schaumburg Lombard Hinsdale Aurora Rockford Downers Grove Brazil Moscow Montreal Mexico USA Canada California
Question:
I have modified
the keyable length of a datatype on my own table in Dexterity. I can go into
Test Mode and use the table just fine. When I extract and test it in runtime, I
get an incorrect record length error.
Answer:
We renamed the
forms and reports dictionary for the product and the error still occured. We
then find that keyable length was changed by modifying an existing Dynamics
datatype in the developer dictionarty. This worked fine in Test mode, but the
extract process does not extract this change. Therefore, it used the default
size for the Dynamics data type at runtime and generated the error. The
multi-dictionary architecture does not allow changes to any core resources to be
extracted. To resolve this problem, you would have to create your own field or
data type for this field. This way, you can change the keyable length on your
own field and it will be extracted.
Question:
What might cause
an "Illegal Syntax Error" when trying to print a very simple report. There are
no restrictions or calculated fields on the report.
Answer:
This error message
was being caused by having a Big Text field on the report marked as Last
Occurrence. This is not supported by the Report Writer. In this case the Big
Text field was formatted in the report footer. Because of this limitation, Big
Text fields cannot be in the report footer.
Question:
I have added
button to a Dynamics window which when pressed by the user runs a script
statement (Dexterity - Extern (DLL) or Run Application (EXE)) (VBA - Call (DLL)
or Shell (EXE)) to launch and run a Crystal Report. I see Dynamics running the
script statement successfully when I watch in Debug Mode but the Crystal Report
does not run and I do not receive any error messages.
Answer:
Check with Seagate
support for Crystal
and determine what version of the Crystal OCX file should be loaded for the
version of Crystal Reports you are running. The times we have seen this problem
report it was due to the user having loaded another windows product which in
turn loaded an older OCX file which overlaid the newer version of the OCX file
Crystal Reports was using. (Example: Crystal Version 6 installs a Crystal
version 6 OCX file when the user loaded a product named GOLDMINE, GOLDMINE
loaded a Release 1 version of the OCX file. As soon as the user reloaded the
Crystal OCX version 6 file his script statements executed and launched Crystal
Reports correctly from the button added to the Dynamics window.)
Question:
My Dexterity Application contains a section of code which uses Pass-through SQL
statements to create a Stored Procedure on the SQL server. The Stored Procedure
will use SQL statements to create a new SQL table, and create a SQL procedure to
grant security for the new table. I then use the Dexterity Sanscript 'call
sproc' statement to execute the SQL Stored Procedure to grant access for the
table.
When my Pass-through SQL statements are executed I am receiving the Great Plains Error Message window displaying a message of "An error error occurred creating the pass-through SQL Connection:208."
I have enabled the DEX.INI statements SQLLogSQLStmt=TRUE and SQLLogODBCMessages=TRUE to create a DEXSQL.LOG file and I see the error listed as shown below:
/* Date: 12/08/2000
Time: 9:52:05
stmt(33313716):*/
grant execute on Dynamics.dbo.amAutoGrant to DYNGRP
/* Date: 12/08/2000 Time: 9:52:05
SQLSTATE:(S0002) Native Err:(208) stmt(33313716):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Dynamics.dbo.amAutoGrant'.*/
What does there error mean and how can I correct the problem?
Answer:
The error message in the dexsql.log file breaks down the following way: "Invalid
object name" says that SQL can't find the object you command is trying to
reference. "'Dynamics.dbo.amAutoGrant'." says that SQL is looking for an object
named amAutoGrant in the Dynamics database.
The error message you see
in the dexsql.log is being generated because of the combination of :
1) Your dexterity sproc procedure amAutoGrant is set to System series and
2) when you execute the SQL pass-through statements to create the SQL stored
procedure amAutoGrant, you had set a USE statement to set current SQL access for
the commands you were passing to use one of the SQL company databases instead of
Dynamics.
To resolve the error you need to either edit the Dexterity amAutoGrant global procedure and change it's series to something other than System or edit your code section which passes your SQL pass-through commands for creating the SQL amAutoGrant Stored Procedure and make the first statement you pass prior to the SQL create procedure statements a SQL USE statement specifying has the correct SQL Database.
When your Dexterity code uses the 'call sproc' for your global procedure amAutoGrant, Dexterity by default will set the SQL engine to look at the SQL Database specified for the SQL Series Pathname. For example if the series for the your Dexterity procedure is set to System all commands you pass to SQL will be executed against the DYNAMICS database. If the series for your Dexterity procedure is Purchasing, all commands you pass to SQL will be executed against the current company database(TWO, or one of your companies).
Modules: Dexterity - Scripting
When working with Dynamics C/S+ for SQL, if you need to create your own tables, you will need to use the Table_SetCreateMode() function to allow new tables to be created. In the SQL product, we have turned off the ability to create tables because of the problems that occur with permissions if tables are dropped and recreated.
We recommend that developers either create the tables through SQL scripting or create one Dexterity script that creates all tables by opening and closing them, and then calling a stored procedure to grant permissions to these tables and the table-generated stored procedures.
Here is an example of the Dexterity script to create the tables and the table-generated stored procedures.
local boolean l_result.
set
l_result to Table_SetCreateMode(true).
open table Dev_Table_One.
close table Dev_Table_One.
open table Dev_Table_Two.
close table Dev_Table_Two.
set l_result to Table_SetCreateMode(false).
{Call the prototype procedure for the stored procedure.}
call Stored_Proc_Set_Permissions.
{ This stored procedure should set select,update,delete,and insert permissions on the tables, and execute on all of the table-generated stored procedures. These permissions should be granted to the DYNGRP group so that any future users that are created automatically have the correct permissions.The Dynamics CS+ for SQL Server dictionary does not include a procedure named Stored_Proc_Set_Permissions. This is simply an example of what you would need to do at this point in the process. There is a stored procedure detailed in another TK called amAutoGrant that could be used to grant permissions to the table procedure. Also see examples in the Dexterity Samples area of Partnersource/CustomerSource}
Modules: Dexterity - Scripting
How can I set the key number to be used when a user opens a lookup window? Do Dynamics lookup windows accept parameters?
In the lookup button change script, after opening the form, set the value of the Sort_By field, then run the script attached to the Sort_By field. Example:
open form Customer_Lookup
return to 'Customer Number'.
set 'Sort By' of window Customer_Lookup of form Customer_Lookup to 1.
run script 'Sort By' of window Customer_Lookup of form C_Lookup.
Other lookups in Dynamics may also use an INITIALIZE or OPEN form global script on the Lookup form itself. See lookup documentation available in the SDK.
Question:
I currently have database triggers on the SOP_HDR_WORK and SOP_LINE_WORK
tables. I have delete triggers on these two tables so when a SOP transaction is
posted and the documents are moved to history and removed from the work tables,
that I know to do the same with my own custom tables. However, my database
triggers do not fire if I'm on a SQL platform. They work fine on PSQL and Ctree.
What is the problem here?
Answer:
The problem is that on 6.0 Dynamics/eEnterprise, if on the SQL platform, we use
stored procedures to move the data between tables for performance reasons.
Since Dexterity itself is not doing the table operation, the database triggers
don't fire. As a result, you will need to use procedure triggers instead to
verify posting has completed.
The best procedure to trigger on is the sopMarkPostingComplete procedure. This is the final call in the posting procedure and we can tell if the transaction successfully posted by a postingstatus parameter. We also know the SOP record posted from a SOP_HDR_WORK table buffer parameter. The parameters for this procedure are:
in integer PostingStatus;
inout table SOP_HDR_WORK;
The possible values for the PostingStatus parameter are POSTED, POSTED_WITH_ERR and FALSE.
Question:
On a Dexterity
modification that was developed, the report for a certain table runs quickly for
all the clients but one. It had previously been running at acceptable speeds
until recently. Running a SQL Profile on the server shows that it appears to be
doing a table scan of the records. This means that it reads and rejects many
records that don't print on the report which causes it to be slow. This client
does have a large database and the table is a high volume table that has data
flowing in an out constantly. The SQL Setup for this customer is typical. What
is causing the report to run slowly?
Answer:
In this case the problem was the zDP stored procedures for the table. To
optimize data access, the stored procedures in SQL "know" something about the
data in the table and this is stored in the query plans for the table. If the
data in the table changes quite a bit, the original query plans become out of
date and could cause this type of problem. This typically isn't a problem as
the stored procedures are recompiled automatically the first time they are run
after the SQL Server was stopped & restarted but the customer keeps the SQL
Server up for maxiumum uptime (they operate around the clock). Because the SQL
Server was never restarted, the zDP procs were not recompiled and this problem
occured. There are 2 solutions to this problem:
1. For the problem tables, periodically use the Dexterity sanScript functions Table_DropProcedures() and Table_CreateProcedures() to drop and recreate the zDP stored procedures. By dropping and then recreating, a new query plan would be generated that was up to date.
2. Use the native SQL Server stored procedure named sp_recompile. The customer had a standard set of maintenance queries that were run weekly and this procedure was now to be included in the set of maintenance. This should fix the performance problems.
Question:
I need to be able to change the Unit Cost for an item that is being posted in
SOP Entry. I have tried changing the window field but have not had much success.
Is there way to do this?
Answer:
Yes, there is. The best way is to put a procedure trigger on the
INVEN_Decrease_Receipt procedure, since this procedure returns the 'Extended
Posting Cost'. You could put an TRIGGER_AFTER_ORIGINAL procedure trigger on this
and then change the 'Extended Posting Cost' parameter in your trigger processing
procedure from an in parameter to an inout parameter. In your code, then just
retrieve your cost and set the 'Extended Posting Cost' to the value you
choose. Dynamics will return that cost to the SOP Line Work table. This Extended
Posting Cost does include any overrides that were encountered.
Keep in mind however, that the IV Purchase Receipt table would not actually be adjusted with your cost, since it has already retrieved the cost from that table by the time your procedure would run. As a result Inventory(IV) would never balance to General Ledger(GL).
This procedure will return the Extended Cost for the item and the Unit Cost would be figured out automatically for you. The distributions for the IV and COGS accounts are created after the extended cost is returned, so the distributions would be created with the cost that you are sending back. Serial numbered items use the INVEN_Update_Serial_Receipt to retrieve the cost and lot numbered items use the INVEN_Update_Lot Receipt procedure. So if you are tracking serials and lots, you would have to trigger off these procedures in a similar manner.
Question:
I am trying to
open a lookup form and enforce a range of records. How can I do this?
Answer:
The key here is
that a range is in effect only on the form that you are currently on. To make
this work successfully, you need to attach the range setting features to a
field, such as the 'Sort By' field, on the lookup form and then run that script
from the Lookup Button.
Lookup_Button_CHG
open form Lookup_Form return to Lookup_Field.
set 'Sort By' of window Lookup_Window of form Lookup_Form to 3.
set '(L) Class ID' of window Lookup_Window of form Lookup_Form to 'Class ID' of
window Maint_Window of form Maint_Form.
run script 'Sort By' of window Lookup_Window of form Lookup_Form.
Sort_By_CHG
set 'Class ID' of file Cust_MSTR to '(L) Class ID'.
clear 'Customer Number' of file Cust_MSTR.
range start file Cust_MSTR by number 'Sort By.
fill 'Customer Number' of file Cust_MSTR.
range end file Cust_MSTR by number 'Sort By'.
fill window Lookup_Scroll by number 'Sort By'.
Question:
It would be nice
to be able to add "restrictions" in the sense of Report Writer restrictions when
going into a scrolling window in addition to just being able to set a range.
Example: restrict 'Customer Type' to 'Customer Type'=1 or 'Customer Type' =4.
fill window Customer_Type_Lookup_Scroll. What do you think?
Answer:
You should be able
to accomplish this using the 'fill' script on the scrolling window. If you did
not want to put records with a 'Customer Type' = 4 in the scrolling window, you
could write a fill script to handle this. It utilizes the 'reject record'
statement to not put a line in the scrolling window.
EXAMPLE FILL SCRIPT :
if ('Customer Type' <> 1) and ('Customer Type' <> 4) then
reject record.
end if.
However, if you have a large file with many records but few of these 'Customer
Type's, the system will continue to read through the file one record at a time
until it finds a valid record or hits the end of file or the scrolling window is
full so performance could be severely degraded.
One option under SQL on Dexterity 6.0 and above is to use the range where
statement. In this case, after setting the normal range, use the range where
statment to further refine the SQL statement generated. The range where syntax
actually modifies the SQL statment by adding a new "where" clause to further
restrict our data. See the Dexterity help for more information on range where
statements.
Question:
When I register a procedure trigger on a Great Plains procedure, how are they
passed to me?
Answer:
This will depend on how the procedure is called by Dynamics or eEnterprise in
the first place. Let's use an example where DynProcA calls DynProcB in the
background and DynProcB calls DynProcC.
If the Great Plains(GP) procedure is called with the background or remote keyword, then my trigger processing procedure will get a "copy" of the parameters as they were sent into the GP procedure. For example, if my trigger is a TRIGGER_AFTER_ORIGINAL on DynProcB, then I will get a copy of the parameter values as they are passed into DynProcB.
If the GP procedure is called normally, then I will "share" the parameters with GP procedure. For example, if my trigger is a TRIGGER_AFTER_ORIGINAL on DynProcC, then I will get the values of the parameters as they are passed out of DynProcC back to DynProcB. In this particular case, I could actually change the parameter value that is sent back to the GP procedure and affect the outcome of that procedure.
Notice that in my second example, even though DynProcC is called from a procedure (DynProcB) that is running as a background task, I still "share" the parameters since the call to DynProcC does not use the background or remote keyword.
Additionally, one thing to remember is that if the GP procedure has a parameter defined as an 'out' parameter, you do have the ability to define these parameters as 'inout' in your trigger processing procedure. This will allow you to actually use and refer to that value, instead of just setting its value. This ability has existed since sometime during v5.5 of Dexterity.
Question:
In Dexterity, I plan on creating an Object (ADO,Outlook, etc) that will be
accessed often. The object, when created, will ask for login credentials so
therefore I don't want to keep creating the object every time I need to
reference it in my code. The object is typically destroyed when the script that
it was created in is closed. How can I keep my object "alive"?
Answer:
Store the object
reference as a field on an object form.
Create a form with a window that is not auto-open. On that window, create a local field (or make a global field and put it on the window) with type of generic reference. If you would like to have early binding, you can create a Library for the object type and set a datatype to COM Reference. Either option, will work fine.
Then, when you use the COM_CreateObject() function to create the reference to the COM Object, store the object reference on the window field of that hidden window on your form. To be able to reference that field, you will need to issue an 'open form' statement on that form and leave it open, so that the COM reference will remain "alive" and can be used anywhere in your application.
Question:
How do developers
use the "debug" statement?
Answer:
You use the debug
statement instead of warning statements in places where the only possible reason
to display a message to the screen is for debugging purposes. In Test Mode,
under the new scripts menu, you can turn the debug messages on or off. Under
DYNAMICS.EXE, the debug messages don't typically show. The syntax is identical
to the warning statement. Debug messages can show in runtime by using
ScriptDebugger=TRUE
This will cause the dexterity Debug menu to appear in runtime with the same options (except Edit Script) and will cause debug messages to show.
Question:
I am using Dexterity to create Payables Transactions that are entered in a
currency other than my functional currency. When trying to post or run an edit
list, I am receiving the following error:
"This transaction contains multicurrency
error(s)." What could be wrong, or how can I get more information on
this?
Answer:
Within Dynamics or eEnterprise, there is actually a setup option that can be of
some use to us here. From the Setup menu, select Posting. In the Posting Setup
window, select the appropriate Series and Origin for the transactions
(Purchasing and Payables Trx Entry, in this case). Then, mark the checkbox
titled "Include Multicurrency info". By marking this checkbox, you will now get
additional error messages on the Edit List that tell you the specific
multicurrency error.
In the case of this developer, we then received the additional message of "The exchange rate does not exist." What we found is that he was not updating the Time field in the MC_PM_Transactions table and therefore it could not read the exchange rate record.