One of my users came to me with a problem last week, and it’s fairly common. They run a M2M Report that has the data they want, but when they export it, some of the data is missing.
This can happen for a couple of reasons. Tables can be added to a report layout and those changes are never made to the report program file (prg). If a field isn’t listed in the prg file, it cannot be exported.
The user came to me and asked why the part description was not showing up in Received Goods Report (RPREGO). When I looked at the prg file, I saw the field listed.
LCSQL = 'SELECT rcmast.fReceiver AS freceiver_a, rcmast.fcompany';
+ ', rcmast.fpono, rcmast.ftype AS ftype_a, rcmast.fdaterecv';
+ ', rcitem.fitemno, rcitem.fac,rcitem.fpartno, rcitem.fpartrev, rcitem.fcudrev';
+ ', (case when inmast.fluseudrev IS NOT NULL AND inmast.fluseudrev = 1 then rcitem.fcudrev else rcitem.fpartrev end) AS fcdisprev' + ;
+ ', rcitem.fdescript';
+ ', rcitem.fqtyrecv, rcitem.fcategory, rcitem.fmeasure, rcitem.fsokey';
So, if rcitem.fdescript is in the program file, why doesn’t it export? Well, according to M2M it’s a Visual Foxpro limitation and memo fields cannot be exported.
How do we fix this? Well, my preferred method would be to extract the SQL statement from the report and deliver it via Crystal Reports or SQL Reporting Services, neither of which have this limitation. For reference, the SQL statement is below:
SELECT RCM.fReceiver AS [Recv No],
RCM.fcompany AS [Vendor],
RCM.fpono AS [Our PO No],
RCM.ftype AS [Recv Type],
RCM.fdaterecv AS [Recv Date],
RCI.fitemno AS [RCI No],
RCI.fac AS [Facility],
RCI.fpartno AS [Part No],
RCI.fpartrev AS [Part Rev],
WHEN INM.fluseudrev IS NOT NULL AND INM.fluseudrev = 1
RCI.fdescript AS [Description],
RCI.fqtyrecv AS [Qty Recvd],
RCI.fcategory AS [Category],
RCI.fmeasure AS [U/M],
RCI.fsokey AS [SO No],
RCI.fsoitem AS [SOI No],
RCI.fsorelsno AS [SOR No],
RCI.fjokey AS [JO No]
FROM dbo.rcmast RCM
JOIN dbo.rcitem RCI
ON RCI.freceiver = RCM.freceiver
LEFT OUTER JOIN dbo.inmast INM
ON INM.fac + INM.fpartno + INM.frev =
RCI.fac + RCI.fpartno + RCI.fpartrev
However, this user really wanted to be able to use the Received Goods Report from directly within Made2Manage. This too can be accommodated by editing the program file with the following:
cast(rcitem.fdescript as varchar(20))
This code simply changes the field type to a character field and those will export to Excel. Keep in mind that the field is only 20 characters in my example so you will need to change the number based on how many characters you need.
Yes, I know I eluded to this in an article several months ago, but I’m hoping one of my readers is already using this in production. I’m going to be testing this extensively for the next week to determine whether we can migrate to it.
So, are any of you using Version 5.6 or earlier with SQL Server 2008 in production?
In an earlier article, I challenged my readers to come up with the widest variety of solutions to a problem I posed, and discuss what I thought was the best solution. I apologize that it took me so long to get back to this, but I have been ridiculously busy lately. Anyway, on to the “solutions.”
- Use ECM Auditing to discover the problem users and educate them not to make this mistake again. This was suggested by Scott and seconded by Stephen. While I am committed to user education, I would prefer to make it impossible to make a mistake than trust someone not to make them.
- Use Form Customizer (FCUST), which Andrew suggested. However, after I investigated this, I found out that you cannot disable any controls, only make them invisible which was one of the prerequisites.
- Use FastForms to deal with this. However, you cannot lock a control with FastForms either. One idea I tested was to create a pull down control over the M2M Terms pull down making it impossible to click on the original. The FastForms pull down would be locked. However, this is not fool proof because a determined user could tab into the underlying Terms pull down manually and change it. I also tried to draw a FastForms frame over it to block the pull down, but wasn’t successful.
- Events and Actions. There may be a way to do this via E & A, but I couldn’t figure out how to do it. I’ll be writing an article soon on Events and Actions and the pitfalls associated with it.
- There are several different ways to solve this via Visual Basic for Applications (VBA). One could use VBA to draw an object, such as a pull down in an manner similar to the FastForms solution, over the original pull down making it impossible to click on. However, this solution has the same problem as number 3.
- Another VBA method involves using the programatic change event on the pull down itself. You could then pop up a warning box telling the user that they are not allowed to change the value.
- Yet another VBA method involves gotfocus and setfocus. You could simply write code for the gotfocus event of the terms pull down that immediately sets the focus to the next control in the tab order. This would effectively keep the users from editing it.
- Use a SQL Script. This is the most fool proof method as far as I am concerned as a savy user could disable VBA, FastForms, and even Events and Actions on their local machine. The SQL script would be assigned to a nightly job on your server which detects values in your Sales Order Master (SOMAST) table that are inconsistent with your Customer Table (SLCDPM). If it finds any, it would e-mail the manager in charge to fix that error. This solution could also be used with any of the above as a back up as well.
- Simply use VBA to lock the control on the initialization of the form (INIT). This was the method I originally chose, and the method first offered by Dennis Boyle. However, our code varies slightly. Mine is shown below.
If Trim(Eval("FDecrypt(oUser.fcUName, oSession.cEncryptKey)")) = "D.STEIN" Then
ThisForm.m2mpageframe1.page3.cbofprodcl.Enabled = True
ThisForm.m2mpageframe1.page3.cbofprodcl.Enabled = False
My hope is that this little exercise will illustrate for you that there are almost always several solutions to a problem, and that all of them have their pros and cons. Now that I’ve listed mine, do any of you have any other suggestions for this problem or challenges you’d like to submit for further “contests?”
Since not everyone who reads this blog is on M2M’s email distribution list, I thought I would share this with you. Last week I received an e-mail which was as follows:
As a result of speaking with many customers regarding their current
budget limitations, we regret to announce our decision to cancel this
year’s customer conference scheduled for October 4-7 at the Rosen
Shingle Creek Center in Orlando, Fla.
We understand that the economy is affecting all businesses, and that
it’s hard to justify any training opportunity. A high percentage of you
told us that you would like to join us this year in Orlando, but that
budgets are limited. Based on your feedback and our attendance
projections, we don’t feel we can hold the quality conference we
promised you. In particular, one of the most popular aspects of the
event is your opportunity to network with fellow users and product
experts, and we don’t believe we’ll have enough customers in Orlando to
provide a valuable enough experience for you. *
*As a company, we take pride in providing quality products, services and
experiences, and I assure you that this is the only reason we’re
cancelling the event. Consona is not in any financial distress and
remains one of the most profitable and stable software vendors in the
industry. In fact, it’s costing us more to cancel the conference than it
is to hold it.
For customers who have already registered, a separate communication will
be sent to you with further instructions regarding your conference fee.
Consona Connect 2010 will be held October 12-15 at the MGM Grand Hotel
in Las Vegas, Nevada. We look forward to seeing all of you there.
Please contact your customer account manager with any additional questions.
The Consona Connect Team
I found it interesting that Consona felt it necessary to mention that they are not in financial trouble. Those of us who have attended a few of these know that Consona usually reassures customers in presentations that they are financially healthy.
I can’t help but wonder how low the pre-registration numbers actually were.
Well, maybe I’ll meet more of you next year.
Dashboards are a hot topic these days and I need to investigate the available options. I had a Qlickview demonstration in person, but I’d like to talk to any of you who are using it.
So, are any of you folks using it? Would you mind telling me your opinions of the product?
I went home to Michigan over the Memorial Day Weekend. While I was there I had dinner with a SQL Ninja who I’ve mentioned before, Brent Ozar.
I met him almost exactly a year ago when I came across his blog. He had an excellent article on Perfmon, and I was attempting to use it to troubleshoot problems I was having on my M2M server. I read the article and followed up with questions regarding it, as well as questions about the future of SQL Server and how I could gain a level of mastery with it.
He has been more than helpful and has suggested different avenues for me in SQL. Anyway, the reason this post has been so long in coming is I wanted to make it more than just a “look at me with this famous guy” post. I’d like to share a few tips on how to get help from others, not just in a technical sense, but in other areas such as career advancement.
- Obviously be polite. These people are not paid support and have no obligation to help you. Further, if you don’t receive a response, do not chastise them for not responding. This happened to me about 6 weeks ago. Someone sent me an e-mail asking for help with Crystal Reports and M2M. However, my spam filter caught it. When I went through my spam filter a little over a week later, I had three messages from this person and each was more rude than the last. Obviously, I didn’t bother helping this person.
- Do your best to help yourself. If you send someone a bunch of basic questions about invoicing they may very well tell you to watch the instructional videos on M2M Expert instead of helping you. I’ve always loved the following picture:
It’s worth noting that Google is of limited value for M2M Questions.
- Ask for direction, rather than solutions. You’re more likely to get assistance if you show a willingness to experiment and learn. If you want someone to do your job, you’re likely to be rejected.
- If you get help and it is ultimately successful make sure to tell your benefactor and thank them again. In fact, if you found the answer yourself, still thank the person for taking the time to try to help you. You’ll be more likely to get help in the future.
- Offer friendship first. If at all possible, network with others before you need help.
- Form as complete a question as you possibly can. When you ask a question, and I have to ask you four separate questions before I can answer it, this does not bode well for you.
- Ask in a convenient way. If possible, send an e-mail as opposed to calling. Calling interrupts a geek’s normal work flow, and e-mails can be read and answered at our convenience.
Now, I should mention that Brent is one of the most helpful people I know in the business, so I could have made any number of mistakes and he still would likely have helped me. However, why lessen your chances?
What do you think? Do you disagree with my tips? Do you have anything to add?
I follow a lot of forums, blogs, and other sites to further my SQL knowledge. We’ve all run into CAPTCHA over the years. The idea of course is that they are easily solved by humans, yet difficult or impossible for computers to solve.
However, it seems like it’s becoming more and more difficult to read them lately. I suspect that the sites have to develop more and more difficult CAPTCHA algorithms to counteract hacker programs which attempt to read them.
For example, the other day I happened by this article at Coding Horror, which is one of my favorite blogs. Jeff Atwood is amazing and if you haven’t read his stuff, do what I did, start at the beginning and read almost everything. Anyway, in that article he complains about the byzantine ways that software is priced and how he purchased too much memory for his SQL Server because SQL Server Standard is capped at 32gb.
I was going to reply with quip about how he should have hired (or at least asked) Brent Ozar, another genius blogger who I read religiously. However, when I attempted to do so, I was faced with the following:
Umm… outtakes krthella???
I dare you to tell me what that last “word” is. I can almost hear you saying, “Well, refresh and get another one.” However, these things are supposed to be easy and I had to refresh three times before I got one I could read. Instead of leaving my comment, I decided to blog about it instead.
Then I went to Craigslist because I run into CAPTCHAs there too and tried to list one of my old video games for sale. I ran into this:
Did you gues Corcillo bequeath? I did, but was incorrect.
I realize these sites need protection against spammers, but aren’t we reaching the usable limits of CAPTCHA if users can’t easily read them?
Yesterday I got a request from management to give full permissions to the SYCSL screen to one of my users. I of course complied, but then got a call the manager asking why the user did not have permission to change the status of the requests. I responded that she did and asked if she had re-started M2M for the rights to take effect.
Well, after looking more closely at the user permissions screen, I came across the following:
Notice the module below the screen.
This screen is non-standard in that its “Change Status” permission is assigned via the SYCSLS module. I seem to remember there being other exceptions to the rule as well.
Can you folks help me by naming any that you know of?
There are a couple of ways to achieve automatic login of Made2Manage. However, I first want to say that I think this is a bad idea and I don’t allow this at my company for security reasons.
The simplest method is to make your M2M user name match your active directory user name. I haven’t done this in quite some time, but I do believe it still works. The problem with this was recently pointed out by one of my readers because his AD name is longer than what M2M allows for a user name. Therefore, he can’t auto login because they don’t match.
However, there is another alternative (that once again I don’t suggest) in this case. Navigate to your M2MWin.ini file and open it in Notepad. You can find it in your C:\windows\ directory. When you do so, you will notice a list of settings for M2M. Down the list you will find a section called [System]. Below that enter the following:
Username = User
Password = pass
Of course, you would substitute your user name and password for those entries and do not include quotes. This should allow you to log in to M2M on that computer automatically.
In my opinion, the only valid use of auto login is for computers that are in a locked room. A good example be an executive at your company who uses M2M infrequently and forgets his password.
What do you folks think? Do you know of other clever uses for the M2MWin.ini file?
Your accounting manager comes to you with a problem. Being the stellar M2M Professional that you are, you immediately don your cape and tights. What? You don’t wear tights? Umm.. me neither… really…
Anyway, she explains to you that the terms, which are dictated by accounting, are being changed by some Sales Order Administrators that enter the orders. She can’t tell exactly why, but when this happens product is shipping out to companies who are on credit hold. This results in your company essentially giving away free product.
Your mission, should you choose to accept it.
Therefore, she asks you affect a couple of changes. First, she doesn’t want the terms to be changed at the SO level. You must stop this from happening. Second, she would prefer that the terms still be able to be seen on that screen, just not manipulated.
Now, I’m sure most of us can do it in one way or another. For the sake of this exercise, we’ll assume that you have all of the M2M Optional Modules, but you aren’t required to use any of them. I would like you folks to comment with a proposed way to accomplish this. I want to see how many ways we can come up with.
Also, each person can only list 2 total. That way, we don’t have the first person spoiling the “game” by listing every conceivable way in the first comment. Thinking for a few minutes, I’ve already come up with 6 different ways to achieve this myself.
So, without further adieu — are you up to the challenge?