I really wanted to make a super-nice version of Waldo’s form but I came up short in a couple of areas. Who knows, maybe someone reading this blog will be able to offer some advice as to how to overcome the problems I found.
First of all, let me give you my requirements.
I want the system to provide immediate visual cues to show the users which database or company they happen to be in. The required information is to provide something eye-catching, to show a large bit of text (like “TEST SYSTEM”) and to provide the database name, company name and finally give me a big area where I can put a support message. Oh and one more thing: when I copy my database from the live system and restore it to my test database, I want the messages and visual cues to stay the same – that’s right, I don’t want to have to edit the data to make my test system say it’s the test system every time I restore it from the live backup.
The solution I came up with is pretty much based on Waldo’s solution so I’m not taking credit for this. I just figured, it would be nice to share this with anyone that’s interested and maybe someone with a bit more time can iron out the imperfections. I should also say that I only ever intended this to work with SQL databases so there’s no need to point out that this will not work with a native database.
Before we get stuck in to the How, let’s take a look at the solution I came up with. Like Waldo’s solution, I have some code in my CompanyOpen() trigger in codeunit 1 ApplicationManagement that will launch a form.
My form will look for details in the setup table matching the current company and database and if it doesn’t find a record, it creates one and displays the following image.
I then have a setup form that allows me to edit the info that’s going to be displayed.
The Database and Company get filled in automatically. The Title field get’s displayed, the title Background Colour is an “unimplemented feature” :-)
The Image Type allows you to select from Live, Test and Unknown options which will show one of the following icons.
I downloaded these icons from http://tpdkcasimir.deviantart.com/ and made some adjustments to set the background colour to be the same as NAV’s background colour and resize them (when I say I did it, I mean someone with far more talent than me did it for me.)
Now this is where I hit the first weird NAVism – or maybe I was just doing something wrong. Whenever I put the icons on the form, they got stretched. Anyone know what I’m doing wrong? I thought at first that NAV had some kind of minimum size so I resized the Icons to fit the size of my NAV graphic and these got stretched again to look like this.
But at least you can see the idea – and the way the other data fields are being updated from the database.
The Support message appears at the bottom of the form, the Show Info tick allows you to suppress the message in certain company/database combinations – like maybe your live system. The custom background colour is another unimplemented feature.
I made a list form first and then decided a card form is a little easier to set up the data.
OK now let’s look at how I did this.
To start with, how am I going to solve the problem of my data not getting lost when I restore my live system? Well, there’s some other data that doesn’t get lost when you restore over your database in SQL and that’s the user license. The way the system keeps that is by storing it in a table called $ndo$srvproperty that lives in the master database. So I’m going to create a new table in the master database and use that to store details on my NAV databases/companies. Here’s the SQL to create the table and grant permission to public:
USE [master]
GO
/****** Object: Table [dbo].[IntergenNAVWhereAmI] Script Date: 05/09/2008 20:06:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IntergenNAVWhereAmI](
[timestamp] [timestamp] NOT NULL,
[Database] [varchar](30) NOT NULL,
[Company] [varchar](30) NOT NULL,
[Title] [varchar](50) NOT NULL,
[Title Background Colour] [int] NOT NULL,
[Image Type] [int] NOT NULL,
[Support Message] [varchar](250) NOT NULL,
[Title Background Custom Colour] [int] NOT NULL,
[Show Info] [tinyint] NOT NULL,
CONSTRAINT [IntergenNAVWhereAmI$0] PRIMARY KEY CLUSTERED
(
[Database] ASC,
[Company] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT, DELETE, INSERT, UPDATE ON IntergenNAVWhereAmI TO PUBLIC
GO
SET ANSI_PADDING OFF
OK. So now we have a table we can work with. The next thing is that in each database I’m going to use this with, I’m going to need a view that references this table so NAV can access it via a linked table (you know that NAV can have linked tables that are based upon views which point to tables in other databases right?)
Here’s my code to create the view:
USE [Demo Database NAV (5-0)]
GO
/****** Object: View [dbo].[Intergen NAV Where Am I] Script Date: 05/09/2008 20:04:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Intergen NAV Where Am I] AS
SELECT [timestamp]
,[Database]
,[Company]
,[Title]
,[Title Background Colour]
,[Image Type]
,[Support Message]
,[Title Background Custom Colour]
,[Show Info]
FROM [master].[dbo].[IntergenNAVWhereAmI]
If you don’t create this view you won’t be able to compile the table in NAV since the table is a linked table.
Now you can import my objects into the database. Sorry for the massive amount of code but at least you can paste this into a text file and import it (I decided to stick the code at the end of the post rather than in the middle of this text.)
So we’re nearly there. The next thing is to add this code to your codeunit 1 CompanyOpen() function:
IF GUIALLOWED THEN
FORM.RUN(FORM::"Where Am I");
You’ll probably need to hack the code to change the path for the bitmaps and create the bitmap files in order to get the form to compile and obviously you must have the view created in the database before you can compile the table.
That’s it. Obviously there’s a whole heap of other things to do like to make the setup form accessible from somewhere (menu option.)
Sadly I couldn’t address the problem of being able to click on the Form and press ESC. I really want the form to stay in the background and never come to the front but I don’t think I can do this either.
So if anyone knows how to solve the these problems or feels like finishing it off, feel free to have a go – just post a comment and a link to wherever you post your stuff.
This still isn’t a great solution and I think Microsoft really need to add this as a standard feature – and make it so we can change something really useful like the window colour, etc.
Oh yer, this code is posted without warranty or guarantees. If you decide to try and implement this code and you break something – don’t call me.
Here's the code...
OBJECT Table 50500 Intergen NAV Where Am I
{
OBJECT-PROPERTIES
{
Date=08/05/08;
Time=[ 2:37:12 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
DataPerCompany=No;
LinkedObject=Yes;
}
FIELDS
{
{ 1 ; ;Database ;Text30 }
{ 2 ; ;Company ;Text30 }
{ 3 ; ;Title ;Text50 }
{ 4 ; ;Title Background Colour;Option ;OptionString=[ ,Green,Red,Yellow,Custom] }
{ 5 ; ;Image Type ;Option ;OptionString=[ ,Live,Test,Unknown] }
{ 6 ; ;Support Message ;Text250 }
{ 7 ; ;Title Background Custom Colour;Integer }
{ 8 ; ;Show Info ;Boolean }
}
KEYS
{
{ ;Database,Company ;Clustered=Yes }
}
CODE
{
BEGIN
END.
}
}
OBJECT Form 50500 Where Am I Setup List
{
OBJECT-PROPERTIES
{
Date=08/05/08;
Time=[ 2:38:09 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
Width=16500;
Height=6710;
TableBoxID=1000000000;
SourceTable=Table50500;
}
CONTROLS
{
{ 1000000000;TableBox;220 ;220 ;16060;5500 ;HorzGlue=Both;
VertGlue=Both }
{ 1000000001;TextBox;0 ;0 ;4400 ;0 ;HorzGlue=Both;
ParentControl=1000000000;
InColumn=Yes;
SourceExpr=Database }
{ 1000000002;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000001;
InColumnHeading=Yes }
{ 1000000003;TextBox;0 ;0 ;4400 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr=Company }
{ 1000000004;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000003;
InColumnHeading=Yes }
{ 1000000018;CheckBox;8202;770 ;1700 ;440 ;ParentControl=1000000000;
InColumn=Yes;
ShowCaption=No;
SourceExpr="Show Info" }
{ 1000000019;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000018;
InColumnHeading=Yes }
{ 1000000005;TextBox;0 ;0 ;4400 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr=Title }
{ 1000000006;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000005;
InColumnHeading=Yes }
{ 1000000007;TextBox;0 ;0 ;3157 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr="Title Background Colour" }
{ 1000000008;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000007;
InColumnHeading=Yes }
{ 1000000013;TextBox;16333;0 ;4400 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr="Title Background Custom Colour" }
{ 1000000014;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000013;
InColumnHeading=Yes }
{ 1000000009;TextBox;0 ;0 ;1980 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr="Image Type" }
{ 1000000010;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000009;
InColumnHeading=Yes }
{ 1000000011;TextBox;0 ;0 ;4400 ;0 ;ParentControl=1000000000;
InColumn=Yes;
SourceExpr="Support Message" }
{ 1000000012;Label ;0 ;0 ;0 ;0 ;ParentControl=1000000011;
InColumnHeading=Yes }
{ 1000000015;CommandButton;9240;5940;2200;550;
HorzGlue=Right;
VertGlue=Bottom;
Default=Yes;
PushAction=LookupOK;
InvalidActionAppearance=Hide }
{ 1000000016;CommandButton;11660;5940;2200;550;
HorzGlue=Right;
VertGlue=Bottom;
Cancel=Yes;
PushAction=LookupCancel;
InvalidActionAppearance=Hide }
{ 1000000017;CommandButton;14080;5940;2200;550;
HorzGlue=Right;
VertGlue=Bottom;
PushAction=FormHelp }
}
CODE
{
BEGIN
END.
}
}
OBJECT Form 50501 Where Am I
{
OBJECT-PROPERTIES
{
Date=09/05/08;
Time=[ 7:52:40 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
XPos=0;
YPos=0;
Width=17270;
Height=5060;
Editable=No;
BackColor=11250603;
BorderStyle=None;
CaptionBar=None;
Minimizable=No;
Maximizable=No;
Sizeable=No;
SaveControlInfo=No;
SavePosAndSize=No;
SaveColumnWidths=No;
InsertAllowed=No;
DeleteAllowed=No;
ModifyAllowed=No;
SaveTableView=No;
OnOpenForm=BEGIN
SetDetails();
END;
OnQueryCloseForm=VAR
l_ApplicationManagement@1000000000 : Codeunit 1;
BEGIN
//MESSAGE(FORMAT(l_ApplicationManagement.CanInfoFormClose));
END;
}
CONTROLS
{
{ 1000000009;Frame ;0 ;0 ;17270;5060 ;Focusable=No;
ShowCaption=No;
Border=No }
{ 1000000001;Image ;0 ;0 ;2310 ;2310 ;Name=Tick;
ParentControl=1000000009;
InFrame=Yes;
Bitmap=C:\Users\davidr\Desktop\tips.bmp }
{ 1000000000;Image ;0 ;0 ;2310 ;2310 ;Name=Warning;
ParentControl=1000000009;
InFrame=Yes;
Bitmap=C:\Users\davidr\Desktop\Warning.bmp }
{ 1000000002;Image ;0 ;0 ;2310 ;2310 ;Name=Question;
ParentControl=1000000009;
InFrame=Yes;
Bitmap=C:\Users\davidr\Desktop\Help.bmp;
OnPush=VAR
l_IntergenNAVWhereAmI@1000000001 : Record 50500;
BEGIN
END;
}
{ 1000000005;TextBox;3520 ;2530 ;7260 ;660 ;Editable=No;
Focusable=No;
ParentControl=1000000009;
InFrame=Yes;
Border=No;
FontSize=12;
CaptionML=ENZ=Database;
SourceExpr=g_DatabaseName }
{ 1000000006;Label ;110 ;2530 ;3300 ;660 ;ParentControl=1000000005;
FontSize=12 }
{ 1000000003;TextBox;2310 ;0 ;14850;2310 ;Name=Title;
Editable=No;
Focusable=No;
ParentControl=1000000009;
InFrame=Yes;
BackTransparent=Yes;
Border=No;
FontSize=24;
SourceExpr=g_Title }
{ 1000000004;TextBox;3520 ;3300 ;7260 ;660 ;Editable=No;
Focusable=No;
ParentControl=1000000009;
InFrame=Yes;
Border=No;
FontSize=12;
CaptionML=ENZ=Company;
SourceExpr=g_CompanyName }
{ 1000000007;Label ;110 ;3300 ;3300 ;660 ;ParentControl=1000000004;
FontSize=12 }
{ 1000000008;TextBox;110 ;4290 ;17050;660 ;Editable=No;
Focusable=No;
ParentControl=1000000009;
InFrame=Yes;
Border=No;
FontSize=12;
CaptionML=ENZ=Database;
SourceExpr=g_SupportMessage }
}
CODE
{
VAR
g_Title@1000000000 : Text[50];
g_DatabaseName@1000000001 : Text[50];
g_CompanyName@1000000002 : Text[50];
g_SupportMessage@1000000003 : Text[250];
PROCEDURE SetDetails@1000000001();
VAR
l_IntergenNAVWhereAmI@1000000001 : Record 50500;
l_Session@1000000000 : Record 2000000009;
BEGIN
CurrForm.Tick.VISIBLE(FALSE);
CurrForm.Warning.VISIBLE(FALSE);
CurrForm.Question.VISIBLE(FALSE);
l_Session.SETRANGE("My Session", TRUE);
IF l_Session.FINDFIRST THEN
g_DatabaseName := l_Session."Database Name"
ELSE
g_DatabaseName := 'Database Not Found!';
g_CompanyName := COMPANYNAME;
IF NOT l_IntergenNAVWhereAmI.GET(g_DatabaseName, g_CompanyName) THEN BEGIN
l_IntergenNAVWhereAmI.Database := g_DatabaseName;
l_IntergenNAVWhereAmI.Company := g_CompanyName;
l_IntergenNAVWhereAmI."Image Type" := l_IntergenNAVWhereAmI."Image Type"::Unknown;
l_IntergenNAVWhereAmI.Title := g_CompanyName;
l_IntergenNAVWhereAmI."Title Background Colour" := l_IntergenNAVWhereAmI."Title Background Colour"::" ";
l_IntergenNAVWhereAmI."Image Type" := l_IntergenNAVWhereAmI."Image Type"::Unknown;
l_IntergenNAVWhereAmI."Support Message" := 'This database was automatically added to the ''Where Am I'' register.';
l_IntergenNAVWhereAmI."Show Info" := TRUE;
l_IntergenNAVWhereAmI.INSERT;
END ELSE
IF NOT l_IntergenNAVWhereAmI."Show Info" THEN
CurrForm.CLOSE;
CASE l_IntergenNAVWhereAmI."Image Type" OF
l_IntergenNAVWhereAmI."Image Type"::Live :
BEGIN
CurrForm.Tick.VISIBLE(TRUE);
END;
l_IntergenNAVWhereAmI."Image Type"::Test :
BEGIN
CurrForm.Warning.VISIBLE(TRUE);
END;
l_IntergenNAVWhereAmI."Image Type"::Unknown :
BEGIN
CurrForm.Question.VISIBLE(TRUE);
END;
END;
g_Title := l_IntergenNAVWhereAmI.Title;
g_SupportMessage := l_IntergenNAVWhereAmI."Support Message";
END;
BEGIN
END.
}
}
OBJECT Form 50502 Where Am I Setup Card
{
OBJECT-PROPERTIES
{
Date=08/05/08;
Time=[ 5:15:14 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
Width=9790;
Height=6160;
InsertAllowed=No;
DeleteAllowed=No;
SourceTable=Table50500;
}
CONTROLS
{
{ 1 ;Frame ;220 ;220 ;9350 ;4950 ;HorzGlue=Both;
VertGlue=Both;
ShowCaption=No }
{ 2 ;TextBox ;3850 ;440 ;5500 ;440 ;Editable=No;
ParentControl=1;
InFrame=Yes;
SourceExpr=Database }
{ 3 ;Label ;440 ;440 ;3300 ;440 ;ParentControl=2 }
{ 4 ;TextBox ;3850 ;990 ;5500 ;440 ;Editable=No;
ParentControl=1;
InFrame=Yes;
SourceExpr=Company }
{ 5 ;Label ;440 ;990 ;3300 ;440 ;ParentControl=4 }
{ 6 ;TextBox ;3850 ;1540 ;5500 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr=Title }
{ 7 ;Label ;440 ;1540 ;3300 ;440 ;ParentControl=6 }
{ 8 ;TextBox ;3850 ;2090 ;2750 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr="Title Background Colour" }
{ 9 ;Label ;440 ;2090 ;3300 ;440 ;ParentControl=8 }
{ 10 ;TextBox ;3850 ;2640 ;2750 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr="Image Type" }
{ 11 ;Label ;440 ;2640 ;3300 ;440 ;ParentControl=10 }
{ 12 ;TextBox ;3850 ;3190 ;5500 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr="Support Message" }
{ 13 ;Label ;440 ;3190 ;3300 ;440 ;ParentControl=12 }
{ 14 ;TextBox ;3850 ;3740 ;1700 ;440 ;ParentControl=1;
InFrame=Yes;
SourceExpr="Title Background Custom Colour" }
{ 15 ;Label ;440 ;3740 ;3300 ;440 ;ParentControl=14 }
{ 16 ;CheckBox ;3850 ;4290 ;440 ;440 ;ParentControl=1;
InFrame=Yes;
ShowCaption=No;
SourceExpr="Show Info" }
{ 17 ;Label ;440 ;4290 ;3300 ;440 ;ParentControl=16 }
{ 18 ;CommandButton;7370 ;5390 ;2200 ;550 ;HorzGlue=Right;
VertGlue=Bottom;
PushAction=FormHelp }
}
CODE
{
BEGIN
END.
}
}
3 comments:
Nice post.
I think the image stretching is related to the DPI of the image files, although I haven't confirmed.
Hi,
I've posted solution for keeping form always opened on original Waldo's blog.
Hope it will help.
Cheers,
Viktoras
Hi Dave,
Just saw your post now. In the companies I have worked in we have always had a standard database naming, where the database name had this syntax:
area_application_dbtype
so the NAV database name could be:
global_nav_production or emea_nav5_development
This way we have been able to check if the database name included either production, development, test or training and display the db type accordingly. That mean that we didn't really need any external linked table to make it display differently if we are in a live/production or test/development environment.
Post a Comment