|
|
Brief Summary – M2M orphans records in the Quote Master and Quote Items tables when the quotes are in the Cancelled status.
As I mentioned last week, I came across a problem with orphaned records in my addressing tables. Rather than address my orphaned record problems piecemeal, I decided to create a script to check most of the major tables for orphaned records. However, when I started working on quote tables, I found almost 300 orphaned records in the Quote Items Table.
This gave me pause because I was worried that my script wasn’t correct, and that I would delete potentially viable data. After investigating further I came to the conclusion that M2M orphans cancelled quotes as a rule. What is particularly weird is that the M2M screen will actually show you the quotes. However, the data is worthless because you cannot Add and Carry from it.
How did you figure that M2M orphans cancelled quote records?
Well, by testing, of course. I took the following steps:
- Fired up my test server and opened the practice company containing the educational database.
- Manually deleted all of the Quote Master (QTMAST) and Quote Items (QTITEM) tables.
- Created a Prospect (PROS) and then created a quote for said prospect.
- Attempted to delete the prospect in Started and Open statuses and received the following errors.
 
- Changed the status to Cancelled and M2M allowed me to delete the prospect.
- Completed the previous steps using a Customer rather than a Prospect to the same effect.
I retrieved the SQL statements using Profiler while testing and found the culprit. I still intend to explain how to use SQL Profiler for M2M Administrators. If you look at the code below you’ll notice that it doesn’t mention Cancelled quotes.
SELECT @Status = RTRIM((SELECT TOP 1 fStatus
FROM Qtmast
WHERE fcustno = @CustNo
AND RTRIM(UPPER(fstatus)) IN('AWAITING APPROVAL', 'STARTED', 'OPEN')))Perhaps Consona developers intended the code to allow deletions of prospects and customers if they only had cancelled quotes, but the least they could do is clean up those records in the deletion process as well.
I’ll follow up in a week or so with the complete script to find orphaned records in your database.
Recently I got a call from another M2M Consultant who was attempting to install M2M on Windows 7. He simply could not get VBA to work. I asked him to run through the steps and I found his problem right away. He had made a mistake early in the process; I have made similar mistakes as well. I have ranted in an earlier article, about the confusing, tedious process of installing VBA for Made2Manage.
However, the consultant assured me he had followed the instructions properly. When I checked the instructions on the Made2Manage Version 6.0 DVD I found the following:
1. Copy the entire \Made2Manage Optional Modules\VBA folder and all its sub-folders from the DVD to a new VBA folder in your …\m2mwin\dtk folder. The m2mwin directory is the directory where the m2m.exe executable is installed and may be different from machine to machine.
2. In the m2mwin\dtk\vba folder, double-click the VBA6.msi file. This will install VBA.
Do not do this. Doing so will cause your VBA install to fail. The correct instructions should be to copy the contents of the VBA file from the install source, but not the VBA folder itself.
But Wait! There’s More!
When I checked the install instructions from the M2M Version 5.6 CD the instructions were incorrect as well. Doesn’t anyone check this stuff? M2M 5.6 has been out for approximately three years.
Anyway, the correct, albeit poorly written, instructions for M2M 5.6 (and presumably for 6.0 as well) are below and were taken from M2M Expert.
Steps to install VBA to ensure proper registry and functionality
If VBA is not registered properly, users will not be able to login to Made2Manage without errors. The following describes the way to install VBA for proper functionality.
1. Obtain the CD containing VBA for your current Made2Manage system.
VBA is version specific, so with each upgrade, each computer must have VBA re-installed. Please see note [1].
2. Navigate to the VBA sub-folder on the CD.
3. Copy the entire VBA folder files and sub-folders from the install CD to your local drive’s DTK sub-folder within the Made2Manage program folder.
You will be replacing the files and sub-folders within but not including the VBA parent folder from the CD’s VBA folder into the DTK root folder. Opt Yes to overwrite.
The Made2Manage program folder is where your shortcut points, which shows in the properties of the program icon. Alternatively, from the windows Start Bar, click the find files or folders menu option and search for the m2m.exe file. Within this program folder will be the DTK folder to designate as the target folder.
Example: c:\program files\made2manage\dtk
The source folder from the CD is the contents of the VBA CD folder, not including the actual VBA parent folder.
At the conclusion of the copy you will have the same local DTK folder (c:\program files\made2manage\dtk) with a set of files and folders under it which does not include a VBA sub-folder, just the contents of the CD VBA folder. E.g., c:\program files\made2manage\dtk\vba will not exist after the copy, but 7 other folders will exist, including c:\program files\made2manage\dtk\cc.
4. Drill to the DTK folder within the Made2Manage program folder on your local drive. Double click the VBA6.msi file to start the VBA install.
5. Copy the M2V.app file from the DTK sub-folder to the Made2Manage program folder. The target folder will be that containing the m2m.exe file. The source folder will be the DTK sub-folder.
Example: Copy file c:\program files\made2manage\dtk\m2v.app to c:\program files\made2manage
If given an option to replace, opt yes.
6. Copy the M2VInterface.exe from the DTK sub-folder to the windows system folder.
a. The system folder is specific to your operating system. It will usually be either c:\windows\system32 or c:\windows\system.
b. If you have both the system and system32 folders, check to see if the m2vinterface is already in one. If so, that is the one to replace.
c. If the m2vinterface file is in both system and system32, delete one occurrence and replace the other with that of the DTK folder.
d. If the m2vinterface isn’t in either, copy it to the system32 sub-folder
7. Double click the newly placed m2vinterface.exe file.
a. The double click should be done within the target windows system folder to which the m2vinterface was copied.
b. Nothing will appear to happen which is the correct result.
c. Double clicking the m2vinterface file will self register it.
8. Check M2MExpert to see if there are any VBA service pack or hot patch files.
For example, version v5.6 has a replacement m2v.app. Download and install or copy/replace any patch on M2MExpert.
9. Launch Made2Manage.
10. Open the User Management window ( USER )
Locate your user name record, modify, and check the box for VBA Developer. This will allow you to right click on the form and select components or macros
11. Repeat the installation process on any computer that needs to access or use VBA forms.
Any questions?
 The Princess Bride The other day I found myself in a meeting regarding a CRM package we are implementing. The implementation company had brought two project managers, a sales lady, and a geek who will actually assist in the implementation. I sat in on this meeting with some of our executives and sales people.
I was regaled with your typical clichés and buzzwords including:
- Deliverables
- Success Factors
- Action Items
- Methodologies
- Thinking outside the box.
The project manager who has PMP certification reads through slide after PowerPoint slide. Each slide includes numerous bullet points each having the same cheesy metal hitting sound effect. Their sales people are really bonding with our sales people because both groups live for this stuff.
At one point I literally laughed out loud because the PMP was listing possible risk factors to project success and he actually listed “Acts of God.” He then proceeded to explain to us what “Acts of God” were such as earthquakes, hurricanes, etc. As if there was a devastating earthquake, we wouldn’t expect the project to be delayed.
To be clear, I am not suggesting that the meeting wasn’t necessary. I’m just suggesting that the meeting should have been condensed into less than 10 minutes.
Anyway well into the second hour, I am barely clinging to consciousness. In an effort to stay awake, I look around the room and my eyes lock with those of their geek. The eyes are the windows of the soul… I could tell right then that I had a kindred spirit; the only other person in the room who recognized as a colossal waste of time. For some reason, my mind went back to the scene in The Princess Bride where Count Rugen takes Westley prisoner.
I just wish I could have messaged him, “We are men of action, meetings do not become us.”
I found more orphaned records recently when querying a M2M table with an order by clause. Once again, this occurs because in my opinion the M2M database is not properly designed. Here’s a quick walk-through on how I found it. Incidentally, the screen shots are from TOAD using M2M’s practice database.
A manager tasked me with cleaning up our customer and address tables which consists of looking for real world customers having multiple customer numbers in M2M. When looking into the address table, I used a script similar to:
SELECT fcaliaskey AS [CUST NO]
, fcaddrkey AS [ADD KEY]
, fccompany AS [Company]
, fccity AS [City]
, fcstate AS [STATE]
, fczip AS [Zip]
FROM M2MDATA66.dbo.syaddr
WHERE fcalias = 'SLCDPM' The reason I use SLCDPM is because I am looking for customers, if I was trying to clean up vendors the fcalias would be ‘APVEND’. Anyway, that script returns a dataset which looks like this:
With the exception of some missing zip codes, the data set looks fine. To simulate orphaned records I artificially introduced some blank fcaliaskey records using the following script:
UPDATE M2MDATA66.dbo.syaddr
SET fcaliaskey = ''
WHERE fcaddrkey = '0003' Incidentally, the fcaliaskey field should never be blank, but I found several records in my production database that were. Anyway, we receive the message that 4 rows are affected. However, if you re-run the query above, the dataset (that you can see on screen) looks the same. It’s only after you apply an Order By clause that you find the problem.
 Oops
Now for those who write SQL, this is not earth shattering information, but there’s a reason I’m sharing it with you.
How Do You Deal With Technical Hypochondria?
 What? Who?
Above I described how I found more orphaned records recently when querying a M2M table with an order by clause. Technically this is data corruption, but when people hear that term they tend to panic.
The Manager I was dealing with is what I call a technological hypochondriac. He is an old school accounting type who doesn’t trust those d*mned computers. He keeps paper records for everything, saves his adding machine paper tapes, spends weekends at work double checking even insignificant numbers, etc.
So, you’re the DBA. You’ve found orphaned records, records that cannot possibly be used in your ERP system because the field which would have linked them, in this case the customer number, is empty.
What do you do?
 Listen folks, I have been a computer geek a long time. I started with the early versions of DOS and have been using Microsoft operating systems for nearly 20 years. I used to know how to configure extended memory, expanded memory, etc in order to get various PC games to work in less than 1mb of memory. I still have a copy of Windows 3.1 and keep it as a memento along with my high school yearbooks and Star Wars figures. Yes as I type this I realize how geeky that sounds.
When in college, everyone tried to get me to switch to a Mac, I stuck with Windows. When I taught high school, I was once again enticed by the lure of Macs, and I persevered. For years, I had a running argument with my uncle every Christmas about PC vs. Mac. My opinion was always that Macs were for people who weren’t capable of using a PC. They were expensive, proprietary, and had less software available for them. Also, since I was an avid gamer, I remained with Windows because gaming on a Mac is laughable. I’ve argued on your behalf so many times I’ve lost count.
Them: PC’s get infected with viruses and spyware all the time.
Me: I patiently explain that since Windows is ubiquitous, they are the OS that is targeted by hackers. Suck it up and install anti-virus software.
Them: Windows crashes all the time.
Me: Mac has it easier because their OS only has to work on their hardware. Windows has to work with a myriad of different manufacturer’s products.
Them: PC’s are too complicated; Mac’s just simply work.
Me: Plug and play, USB, etc has made PC’s easier. You simply have to know what you should and should not do with a computer, like a car.
Windows XP’s success felt like some vindication of my fanboy devotion. It wasn’t a great OS, but it was better than Windows 98 and the abortion that was Windows Me. I buy your other products as well. I was an early purchaser of the original Xbox and have an Xbox 360. I use your peripherals – mice, keyboards, etc.
I am a PC, I have always been a PC, and I make my living with PC’s. I could have made one of those commercials for you. Until now, I had espoused that Mac users were elitist and lazy. However, this has finally changed.
 I had high hopes for Vista, but that turned out to be abysmal. My company wisely skipped Vista entirely in its software progression and will eventually move from XP to Windows 7 for this reason. I bought a high end Vista 64 machine for home use and have had so many driver and software incompatibility issues that I cannot imagine a non-geek owning one successfully. You (Microsoft) know there are millions of people remaining on Windows XP because Vista sucks. The fact that you won’t allow users to upgrade from XP to Windows 7 is further evidence of your hubris, and this attitude may eventually be your undoing.
 Also, I should mention at this point that while I do own Xbox 360, I have been through 5 of them. All of them died with the Red Ring of Death, over and over again. Being a fanboy, I dutifully replaced them under warranty, even when some of the replacements lasted less than a month. I have actually purchased 3 in addition to those replaced under warranty. I purchased one of them just a couple of months ago for my nephew for Christmas because his Xbox was replaced three times and finally died out of warranty. For years you denied there were problems with that product too, until the news coverage became overwhelming. Even through this, I continued to use your products and said little.
However, I am sick of this. I have made excuses, I have turned a blind eye, but I’m sick of it. You are losing to Apple. Until this point, you have plodded along; content to be second best and I have run out of excuses. Your dominance in the market has made you complacent and lazy. Commercials sway public opinion and they are killing you with them. Their commercials are funny, compelling, but worst of all – True. Your commercials, which as I said before sway public opinion, are inane at best and insulting at worst. What on earth were you thinking with those Jerry Seinfeld Commercials? They were pointless, but at least they were funny. You followed up with, “Windows 7 was my idea.” Do you really expect me to think that Windows 7’s improvements were actually your user’s ideas? Don’t you have legions of top notch engineers who are paid to have these ideas?
Times have changed. Macs are now produced on the same hardware as PC’s, and are not much more expensive. Macs are more stable. Computer gaming? With the exception of WOW, who plays games on PC’s anymore? While there are more options in Windows software, most of the truly great software products are already offered on Mac anyway.
It is time to wake up, Microsoft. We don’t actually need your products anymore. The alternatives are becoming more compelling all the time. Doesn’t it concern you that people who make a living with your software have already jumped ship? One of my big surprises after I joined Twitter is that some of the brightest stars in the SQL Server universe no longer use your operating system. Take a look:
How do they work with SQL Server? Well, in general they use virtual machines which run on the alternative OS.
The recent problem with my new laptop simply put an exclamation point on this. Now, I don’t know whose fault the software problem was, Microsoft or Lenovo. My point is that it doesn’t matter. Perhaps it isn’t fair, but if people have problems with Dell, Lenovo, HP, and other computers; the common factor is Microsoft.
Did I write this blog simply to jab at Microsoft? No. I sincerely want to remain a PC. That’s why I bought the Windows 7 laptop. Please get it right this time. It’s not too late. Stop producing annoying, bug ridden software and other substandard products.
Oh, and a video game system that didn’t die on a monthly basis would be nice too.
I have been promising to create a series of instructional videos regarding SQL, Crystal Reports, and Made2Manage for some time. I asked the SQL Community for suggestions for an excellent Laptop Configuration to record these videos.
I went with the IBM Lenovo Thinkpad T400 with several upgrades including more RAM, faster processor, and a SSD Hard drive. It arrived without incident and it looks great, it’s relatively light, etc.
I powered it up and answered the obligatory questions about windows startup, agree to some EULAs, etc. My first problem occurred approximately 10 minutes after start up. I attempted to use a Microsoft Bluetooth Mouse with the laptop so I turned on the Bluetooth and tried to add a device.
 Fail
Don’t they test this crap? I used a current Microsoft Mouse with Windows 7 and it fails right out of the box. Doesn’t Lenovo test these images for compatibility?
Google to the Rescue
Being a computer geek, I fired up Google, searched for “AcWinHlpr.exe,” and received exactly three sites on the entire internet referencing it. They were all in Japanese. I used the Google translator and none of them had anything to do with my problem. Try it yourself.
Lenovo Support to the Rescue
 I broke down and called Lenovo and waited on hold. I gave them all of my information, and the technician told me to restore the laptop to factory defaults. Think about that a moment… I had just taken the it out of the ^&*%*% box. However, when I flat out asked him, how he had come to the conclusion; he actually admitted that “when Google fails you, what else is there to do?” He instructed me that I could hit the ThinkVantage button and follow the prompts. I thanked him and hung up.
So Lenovo has built in a plethora of software to fix this stuff. I’ll just use that to fix it. I hit the ThinkVantage button and nothing happened. That’s right; the troubleshooting button wouldn’t work either. I restarted the laptop to no effect. I attempted to trigger it from the on screen icon and that didn’t work either.
Attempting to Create Restore Media
Rather than wait on hold with Lenovo again, I opened My Computer and found the expected restore partition on the hard drive. However, it wouldn’t allow me to begin a restore from there, only make media from it. It never ceases to amaze me that you can spend a small fortune on a laptop and they no longer give you a manual, nor will they throw in 50 cents worth of restore media. I attempted to create the restore media, which has two parts: recovery and application data. One of them failed in process, but I could not tell which one. I examined the contents of the DVD which had actually recorded properly, but it is not obvious which it was. So, started over and tried to create them one at a time so I would be able to label each one. I was then presented with the following message.
 Orly?
WTF. Really? Seriously? I can only make 1 copy of an operating system that is already crippled in that it will only work with this model of Lenovo laptop? Doesn’t the operating system need to be “validated” anyway? This is how Microsoft combats piracy?
I’m sure that counterfeiters in China, Russia, and elsewhere were really counting on using a ThinkPad laptop as their source of Windows 7. This is just silly.
So now I have DVD’s, whose individual purpose is not obvious. I was afraid to remove the recovery partition to reclaim space because I was not certain I could do another factory restore with the discs I have.
The good news is the full factory restore appears to have fixed the problem, though there is no rational reason why it should have. The laptop passed its full hardware and stress tests without incident.
After waiting on hold again with Lenovo they agreed to send me restore media. However, when I attempted to actually use the laptop, the “I” key on the keyboard would not work. Some of you may have seen my tweet:
!’m !nsanely !rr!tated. My Th1nkpad sh!pped w!th an !mpa!red ! key. !’m !mpatently wa!tng !ntently for !ts keyboard to be f!xed.
To their credit Lenovo sent a new keyboard, which I then had to replace myself. The new keyboard appears to work perfectly, and if nothing else goes wrong I should be posting videos soon.
I’m not complaining how Lenovo handled the issues; I’m just frustrated that the issues needed to be “handled” to begin with.
What disappointing “out of the box” experiences have you had?
As long as I have been working with the M2M Database I had always wondered one thing. Why on earth does M2M use 1/1/1900 dates? It’s fairly obvious that it was an artificial date when one wasn’t applicable, but why? Why not just allow your date fields to be null and be done with it?
For example, when I run the following query to return the Last Payment Date (fdlpaydate):
SELECT fdlpaydate, fcustno, fcompany FROM slcdpm I receive:
 Annoying Why Should I Care About 1/1/1900?
For me, the reason was simple. My executives hate the way it clutters reports. They’d much rather display a blank field if there isn’t a valid date. In the past, I would use a “Print When” option in Visual FoxPro or something analogous in Crystal Reports. In other words, I would hide the bogus date in the presentation layer. As I migrate to SQL Server Reporting Services, I wanted a more universal method to suppress these dates so they would be suppressed no matter which presentation medium I chose.
The 1900 Date is Actually a Null?
No, not exactly. I investigated this because I needed to know how to suppress it. When one looks at the create statement of any date field in the M2M database, the same constraint is used:
[fdlpaydate] DATETIME NOT NULL “Not Null” is the constraint. It turns out that the database returns the 1900 date because the value is actually ‘’ and not null.
How do we Fight the 1900 Scourge?
Well, first before implementing any code from this site, please read my disclaimer. What I’ve chosen to do is to create a User Defined Function (UDF).
CREATE FUNCTION ExtractDate(@DirtyDate DATETIME)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @CleanDate VARCHAR(10)
SELECT @CleanDate =
CASE
WHEN @DirtyDate = '' THEN ''
ELSE CONVERT(VARCHAR(10), @DirtyDate, 101)
END
RETURN @CleanDate
ENDYou’ll notice that I check the date for the empty string ‘’ and then “filter” out the meaningless dates. The convert statement returns a string in the typical Month/Day/Year format.
How do I Use the User Defined Function?
The only trick to using a UDF is remembering to reference the owner, in this case DBO, when calling it. Therefore, you would use the user defined function like this:
SELECT DBO.ExtractDate(fdlpaydate), fcustno, fcompany FROM slcdpm  The Results of Using the UDF.
Feel free to use this (after you’ve read and understood the disclaimer). However, you have made a change to your M2M database, and you should document doing so. It’s unlikely that such a change would affect your ability to migrate upward, but you can never be too careful. Document it, and be prepared to delete it if you have problems.
Any questions or suggestions?
Yesterday I explained the basics of how FastForms deals with SQL Server. Today I’ll discuss the skills they don’t teach you in M2M classes; how to manipulate SQL directly and save your company thousands in manual data entry.
How do I automatically populate my extension table?
Continuing our hypothetical situation from yesterday, our employees at Acme Inc. do not want to have to go through all of their records to update them. This is tedious and actually impossible if the sales orders have been closed. Therefore, Acme wants the color value to be automatically entered for all sales orders already in the system. How does one accomplish this? I thought you’d never ask.
If you take a look at the create table statement, which can be obtained by right clicking on the table and choosing Script Table as, Create To, New Query Editor Window; you will see the fields that were created.
CREATE TABLE [dbo].[SORELS_EXT](
[Identity_Column] [INT] IDENTITY(1,1) NOT NULL,
[Timestamp_Column] [TIMESTAMP] NOT NULL,
[FKey_ID] [INT] NOT NULL,
[COLOR] [CHAR](10) NULL, Our table has 4 fields. The Identity_Column and Timestamp_Column are automatically assigned. The FKey_ID is a foreign key to identity column of the parent table, and the Color field is obviously our data. Knowing all of this, how do we insert the correct data?
Crafting a SQL Insert Statement Step by Step
For simplicity’s sake, we’ll create the insert statement automatically in the same way we generated the create statement. This step may seem trivial, but this saves time when dealing with a large table.
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
([FKey_ID]
,[COLOR])
VALUES
(<FKey_ID, INT,>
,<COLOR, CHAR(10)>)
GOWhy aren’t Identity_Column or Timestamp_Column listed? As I mentioned before, Identity_column is an auto-number column and will generate an error if you attempt to manually assign it without using Set Identity_Insert On. We don’t’ need to bother with that. Timestamp_Column is also auto generated as well.
Therefore, we could insert one record at a time manually like this:
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
([FKey_ID]
,[COLOR])
VALUES
('12345'
,'Blue')
GOMake sure you delete the inserted records between insert attempts. The next step is to perform an easy insert from a select statement such as:
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
([FKey_ID]
,[COLOR])
SELECT Identity_Column,
'Blue' FROM sorels
GOThis inserts a record into the extension table for every record in the parent and inserts a color as well. Verify that it worked and delete the records. Next, flesh out your select statement like so:
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT](
[FKey_ID], [COLOR])
SELECT Identity_Column, CASE
WHEN LEFT(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
WHEN LEFT(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
ELSE 'White'
END
FROM SORELS SOR
GO The code creates all of the records in your extension table and the correct color.
What if I need to add fields to an already existing extension table? What is an “upsert”?
What if you have an existing extension table where some of the records already exist? Let’s say that Acme already had a field in the extension table called FuseLength. Some of the parts already have a value for FuseLength and some don’t. Well, that is a little more complicated. If you attempt to insert a record for color that already exists (has a value for FuseLength), you will receive an error similar to this:
Error Date and Time Removed SQL Server Database Error: Violation of PRIMARY KEY constraint ‘PK_SORELS_EXT’. Cannot insert duplicate key in object ‘dbo.SORELS_EXT’. 4 0
The script should update records that already exist and insert the others. It’s important to note that all FastForms extension fields are nullable, meaning that a value is not required. If you are using SQL 2000 or 2005, the simplest way to perform this is with two separate statements. The combination of them is often referred to as an “upsert.”
The first statement updates the existing records with the correct color:
UPDATE SORe
SET COLOR =
CASE
WHEN LEFT(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
WHEN LEFT(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
ELSE 'White'
END
FROM M2MDATA01.dbo.SORELS SOR
INNER JOIN
M2MDATA01.dbo.SORELS_EXT SORe
ON SOR.IDENTITY_COLUMN = SORe.FKEY_IDThen follows insert statement, which needs to check if an extension record already exists:
INSERT INTO [M2MDATA01].[dbo].[SORELS_EXT]
([FKey_ID],
[COLOR])
SELECT identity_column,
CASE
WHEN LEFT(sor.fpartno,2) = 'BL'
THEN 'Blue'
WHEN LEFT(sor.fpartno,2) = 'RD'
THEN 'Red'
ELSE 'White'
END
FROM m2mdata01.dbo.sorels sor
WHERE sor.identity_column NOT IN (SELECT fkey_id
FROM sorels_ext)
GOThis effectively inserts all missing records into the extension table. The preceding scripts work in all SQL versions beyond 2000.
How Can I “Upsert” with SQL 2008? What is the Merge statement?
I’m glad you asked. With SQL 2008 you may want to use the Merge statement.
MERGE Sorels_ext AS SORe
USING (SELECT SOR.identity_column
, CASE
WHEN LEFT(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
WHEN LEFT(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
ELSE 'White'
END
AS colors
FROM Sorels AS SOR) SOR1
ON (SORe.fkey_id = SOR1.identity_column)
WHEN MATCHED
THEN
UPDATE SET SORe.fkey_id = SOR1.identity_column, SORe.Color = SOR1.colors
WHEN NOT MATCHED
THEN
INSERT (
fkey_id, Color)
VALUES (
SOR1.identity_column, SOR1.colors);I know it looks complex, but once you understand the syntax, the Merge statement can be easier to write as well as more efficient for the server. The target table (table being acted upon) follows the MERGE statement, in our case Sorels_Ext. The source of the data follows the USING statement in our case Sorels. Notice that I aliased the CASE which determines color with the word colors to make things simpler to read. The ON clause specifies the link between the tables. The WHEN MATCHED statement specifies the action to occur if a record is found in both tables, and WHEN NOT MATCHED is executed when a record is missing from the target table.
This is executed as one statement and should theoretically be faster since the tables are only touched once.
In summary, in these two articles we’ve created a FastForms customization and automatically populated the fields in it using Insert, Update, and Merge.
Any questions or suggestions?
In this article I’ll focus on the T-SQL component of a successful FastForms customization involving extension tables. I tend to demonstrate things in very small steps, and often work that way as well. This would be a good time to repeat my standard disclaimer:
Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.
If you should decide to use any of the code from this site, make sure you test it thoroughly on a test company (or better yet a test server) before you ever use it on your live data.
 Acme Product Testing FastForm Field Creation Basics
In our example, we work for Acme Inc. which sells rockets. As the Admin, Acme has tasked you to create a method to track which color rockets are ordered. In the past Acme accomplished this with separate part numbers for each color, and each of their rocket part numbers begin with a color code. Acme wants to separate the color component from their part numbers.
I’m not going to discuss the basics as to how to create FastForms screen customizations. Made2Manage offers a downloadable class for that. For the sake of brevity, lets assume that the Admin has already created a combo box for color on the Sales Order Release Screen(SORELS) and populated it with the appropriate values. To create the FastForms extension table, click on the Data Tab of the FastForms Tool Box and expand SORELS as seen on Figure 1 below. Right click Extension Fields and select Add Field.
 Figure 1
Name your new field in the Field Properties and select a Character type of length 10 (figure 2 below). I chose 10 because most color names require less than 10 letters.
 Figure 2
M2M will create the table and field in the SQL database and give you the warning below. This is helpful, but it would be more helpful if the warning didn’t trigger every single time you added a field. After you create the field, assign it to the control under the Properties Tab.
 Figure 3 How do I link my extension table into reports?
The process is fairly simple once you understand how the extension tables work. In this case, a SORELS_EXT table was created and that table links to the SORELS table via the FKey_ID field. M2M also creates a stored procedure which handles extension table activities such as inserts and updates. The link for reporting would look like this:
SELECT * FROM SORELS SOR
LEFT OUTER JOIN SORELS_EXT SORe
ON SOR.IDENTITY_COLUMN = SORe.FKEY_IDIncidentally, I almost never use an inner join to an extension table for reporting purposes. If you do so, you will remove any records from your query that have no corresponding values in the extension table. I know that seems obvious, but I’ve helped many Admins who made that mistake and gotten erroneous report data back.
Tomorrow I’ll cover how to automatically populate your new extension table.
 I originally wrote this article some time ago, and then shelved it because I found that Jason Massie had already used the title, though his message was entirely different from mine. Since this is an article about plagiarism, I thought it ironic to plagiarize his title. However, with the recent uproar about plagiarism on SQL blogs, I am going ahead anyway.
If you, dear reader, are not aware of the problem, I’ll bring you up to speed with some links to the blog articles. The posts which originally caused me to write this were from Brent Ozar and Lee Everest, which lamented the problems of content theft and condemned those who engage in it. Brent’s article is excellent in that it informs the reader how to fight content theft.
Yesterday there was a flurry of Twitter and blog activity from the SQL Community regarding blog plagiarism. Todd McDermid, Denny Cherry, Jorge Segarra, and again Brent Ozar discussed blog plagiarism problems. Brent is even offering a bounty on people who report plagiarism of his work.
All of these guys are respected bloggers and DBA’s and I’m not going to argue with, nor attempt to disparage any of them. We all agree that content theft is theft and should be dealt with. However, my perspective is a little different.
If you will, picture me as a waiter in some swanky Hollywood restaurant. I’m going to acting classes and auditions at night (analogous to the Saturday SQL classes I’m regularly taking), trying to get my big break. When these three guys walk in there’s a flurry of activity and are of course given a VIP table. They’re talking about their films, trysts with their buxom costars, and Academy Awards they’ve won. As I listen to this while serving them, I am filled with envy and a renewed commitment to better myself.
During the main course they start complaining because the paparazzi are constantly following them around, stealing information and images from them, and lamenting all the work they have to do to keep this from happening. They complain about taxes on the millions they are making in film. Perhaps they’ve hired bodyguards, agents, accountants, etc all to protect their property both real and intellectual. All of this is such a hassle.
I am that waiter. I hope someday to be that well read. I hope to produce a blog worthy of content theft.
I guess my message is that you should be happy that you have to worry about it. You’ve made it. You’ve earned the right to chase these guys down who are stealing your content, because it’s worth stealing. I envy you, and you guys inspire me to study, blog, and raise my game in general. I only wish to give you some perspective.
Now, dear reader, please forgive me as I go back to waiting on this table. “Can I get you another bottle of Zima, Mr. Ozar?” 
Page 2 of 18«12345»...Last »
|
|
Popular Articles