Decision Analytics > LawsonGuru > LawsonGuru Letter > 2005

October 2005

The LawsonGuru Letter is a free periodic newsletter containing provocative commentary about issues important to the Lawson Software community. The LawsonGuru Letter is published by-and is solely the opinion of-John Henley of Decision Analytics. Visit Decision Analytics at https://www.danalytics.com. For subscription information, see the bottom of this message.
The LawsonGuru Letter is not affiliated with Lawson Software.

The LawsonGuru Letter is a free periodic newsletter containing provocative commentary about issues important to the Lawson Software community. The LawsonGuru Letter is published by--and is solely the opinion of--John Henley of Decision Analytics. Visit Decision Analytics at https://www.danalytics.com.To subscribe, send an email to: mailto:letter-subscribe@lawsonguru.com To be removed from the subscription list, send to: mailto:letter-unsubscribe@lawsonguru.com

Decision Analytics is an independent consultancy, focusing on Lawson technical projects, and specializing in customization/modification, data conversion, and integration/interfaces (including BCI/Mercator). Please visit https://www.danalytics.com for more information.

Decision Analytics. Integrating Lawson with the Real World.

The LawsonGuru Letter is not affiliated with Lawson Software.

In this issue:
1. Guest Spot: Creative ProcessFlow Solutions
2. Where Have All The (Lawson) People Gone?
3. Reporting, Part 14: Crystal Effective Date Reporting
4. Reader Feedback
5. Worthwhile Reading
6. Lawson Tips & Tricks

This month I'm turning the Guest Spot back over to David Williams from Paradigm ERP. David has been kind enough to share his ideas with us before, and this month he's back with some creative ideas for Lawson ProcessFlow. Thanks David!

1. Guest Spot: Creative ProcessFlow Solutions
(by David Williams, Paradigm ERP)

Using ProcessFlow as your Job Scheduler
If you’re familiar with ProcessFlow, you already know it includes a Scheduler to allow the scheduling of recurring flows. What you may not know is that you can design a flow to use the WebRun node to kick off your Lawson reports and update jobs as well.
How is that useful, you ask? Did you know that you can use an AGS call within your flow to update the parameters of your reports, etc.? For example, if you want to run a daily IC220 – Transaction History Report, you can create your flow so that the date range is updated via an AGS call and then processed using the WebRun node.
Daily, weekly and monthly tasks can be set up to update the parameters and then processed by designing a creative solution using ProcessFlow. Now how useful is that?
Add the TDay variable from the Start node:

Define the AGS node to update the IC220 date fields with the TDay variable:

Use the WebRun to kick off your IC220:

Now add this flow to the ProcessFlow Scheduler to run on your schedule:

Lawson Add-Ins Type Updates
Have you ever wondered if you can perform the same functions in ProcessFlow that you’re allowed to do with the Lawson Add-Ins? In Add-Ins, for example, you can update PO lines, but can you do that in ProcessFlow? The short answer is yes, you can.
It may take innovative thinking but if you view the AGS call produced by Add-Ins and then duplicate it, you can perform the same function by revising the AGS call structure to include fields or Function Codes not shown in ProcessFlow.
Now that’s a creative solution!
ProcessFlow AGS call duplicating the Lawson Add-In for line level updates.
Note how the FC is referenced as LINE-FC1 – each line level item would need the “1” appended to the field name.

Using logancall.select
This may be a little technical for the average user, but if you’re not afraid of JavaScript then you can revise (or create) an HTML summary display used by the ProcessFlow Inbasket to limit the records being displayed.
My current client wants to approve requisitions by line (a new feature in Lawson 8.1). Since Lawson doesn’t provide an HTML summary display for this, I had to revise the current recsum.htm file so it could be used by the ReqLineApproval service.
I thought it would be useful for the client to see all of the lines on the requisition in the Inbasket summary display (even if they were approving by line). I also thought they would like to see only the remaining lines to be reviewed when they finished with the line they were on.
To do this, I added this line to the DME section of the JavaScript: logancall.select = “status=1” so that lines not already processed would only be displayed.

function GetReqDtlInfo()
{
var logancall = new DMEObject(prodline, "REQLINE")
logancall.out = "JAVASCRIPT"
logancall.field = "company;req-location;req-number;line-nbr;item;description;tran-unit-cost;quantity;entered-uom;status;item-location.allocatable;item-location.onorder-qty"
logancall.index = "rlnset1"
// Using the logancall.select limits to lines with status of released
logancall.select = "status=1"
logancall.key = m_Company + "=" + m_ReqNumber
logancall.func = "displayRecLineSum()"
DME(logancall, "REQLINE")
REQLINEwin = self.REQLINE
}

By expanding on these ideas, I’m sure you can think of other creative solutions as well.

2. Where Have All The (Lawson) People Gone?

I have had a number of clients recently who were (and still are) looking to fill various Lawson-related positions, and simply can't find anyone. Even in DC--a major metropolitan area.
It's kind of scary, since there really don’t seem to be any people looking for new jobs. I‘d think that with all of the economic gloom and doom, outsourcing and offshoring that we've had over the past few years that there would be a huge pool of available resources, but that really doesn’t appear to be the case.
Which means that people with Lawson knowledge are either in high demand, or that there is such a low demand for Lawson skills that nobody’s is available. Same issue, viewed from two different angles I guess.
Are you seeing the same thing? Maybe I’m missing something. How are staffing your open Lawson-related positions? Are you using a recruiter? Do you use monster.com, hotjobs.com or another career site? Or, do you use a Lawson-related site like lawsonjobs.com?
In any event, I’m curious how you’re handling your job openings and searches, so let me know what you’re doing. Send me an email at letter-comments@lawsonguru.com.

3. Reporting, Part 14: Crystal Effective Date Reporting

I’ll be blunt. This article is not for the feint of heart. If you turn the clock back a few months, you’ll remember that the topic was Crystal subreports (see https://www.danalytics.com/guru/letter/archive/2005-06.htm). I told you that I’d share a technique for creating effective date reports from Lawson data. In particular, this is useful for reporting from HR history data, where you want a report to reflect the value of a piece of data at a given point in time, not its current value. For example, you might want to a report listing your employees their employee status as of a given date.
Creating an HRHISTORY Subreport
As I’ve said before, one of Crystal Reports’ more powerful features is the ability to include subreports, or simply, a report within a report. With subreports, related reports can be combined into a single report. The technique I’ll demonstate uses a subreport for each field from HRHISTORY for a particular employee and then passes back the value via a “shared variable” from the HRHISTORY subreport to the main report--for each employee:

The subreport is linked by company and employee, from the main report to the subreport:

Inside the HRHISTORY Subreport
The subreport itself selects the HRHISTORY records that are less than or equal to the “as of” date passed from the main report, based on the company and employee passed from the main report:

Using Formulas with Shared Variables

Passing the data value back to the main report requires creating a shared variable.

One formula is set up in the subreport, and returns the value associated with the highest date of the records that met the record selection (which, remember, filtered out the records after the “as of” date). This means that the value that is returned to the main report is the value effective “as of” the desired date:

The detail section is suppressed for all but the desired record:

A second formula is set up in the main report to retrieve the value from the shared variable. The result of that formula is then what is shown on the report:

Deciphering HRHISTORY

If you’re reporting off of the HR history data (HRHISTORY), you also have to deal with two other important issues.
First, you need to make sure you’re system is set up to track history on the desired field. If it’s not, you’ll have to turn it on (and no, if you turn it on, Lawson won’t magically go back and add the missing records!). The history tracking flag is set up on HR10.1:

The second part is a little more difficult. Records are stored in HRHISTORY using a FLD_NBR to designate which field is being stored:

The “map” of field names to numbers is stored in the PADICT table. Rather than repeatedly dumping out the PADICT table, I created a simple Crystal report as a reference when creating reports from HRHISTORY:

Building a “Subreport Library”
With a little practice, you can save and reuse these subreports from report to report, and you’ll find that in a short period of time, you can quickly build up a library of subreports based on HRHISTORY, simply by changing the desired field number! You’ll be able to plug in Process Level, Department, etc. to create true effective date-based reports. The other table you’ll want to take a look at is PRRATEHIST, which stores salary rate changes by effective date.
Performance Impact

Remember when I said that this technique isn’t for the feint of heart? Well that also applies to the performance of the reports. You have to understand that in Crystal subreports execute as separate reports within the execution of the main report. That means that, for every occurrence of the section containing the subreport(s)—which in this case would probably be the employee detail section, the subreport(s) will execute. So, if you have 10,000 employees, then each subreport contained in the detail section will execute—yes—10,000 times.
This is probably acceptable if you only have a couple of subreports embedded in the detail section. But if you use this technique a lot (and it really only looks harder than it is!), you will likely run into cases where you have a number of effective date-based subreports (e.g., Process Level, Department, Status, and more) within a main report. And, you’ll pay a performance penalty to execute the report. If that is the case, you’ll have to decide if the need to have the information itself is more important than the time spent waiting for it to execute.

Summary
There are, of course, other ways to accomplish this type of effective date reporting. If you’re adept at writing SQL queries, you can also accomplish the same results (with dramatically better performance) by writing a huge SQL statement that includes embedded HRHISTORY subqueries. But as you can see, this technique does have its value. And, in the process, I think you’ll learn a heck of a lot about Crystal and using subreports!

4. Reader Feedback

I received this email the other day, which reminded me of why I keep publishing the LawsonGuru Letter:

“I cannot thank you enough for the publication of the LawsonGuru newsletter. Our Lawson team is very small, and I just don't have the time to figure out the code to fix all of the little ends that Lawson leaves hanging....
I truly appreciate that Lawson gurus are willing to share the tips and tricks that they've learned so I don't have to reinvent the wheel. I save the newsletters and refer back to them often, usually implementing the tips quickly. They are fixes that we've needed to work on but which have fallen to the bottom of the "wishlist". I just can't express enough Thanks for the service that you offer with the publishing of this newsletter. Keep up the great work!"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- QUOTE OF THE ISSUE –
“An investment in knowledge always pays the best interest.”
-- Ben Franklin
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

5. Worthwhile Reading
Rethinking the Midmarket
Microsoft is redesigning its business applications for midsize companies with an employee's role in mind.
Information Week, September 12, 2005
http://www.informationweek.com/story/showArticle.jhtml?articleID=170701898

Contract Sadness
Too many CIOs cut enterprise software deals that look fabulous to the CEO and CFO but commit the people who do the real work to a nightmare of unrealistic expectations.
CIO Magazine, September 15, 2005
http://www.cio.com/archive/091505/schrage.html

Integration’s New Strategy
Old concepts and new technologies have recently converged to generate a new strategy to improve IT responsiveness while reducing integration costs. It’s the integration layer, and it may put an end to all those complaints about IT’s slowness and inflexibility.
CIO Magazine, September 15, 2005
http://www.cio.com/archive/091505/integration.html
Dream Catalog
New software presents a single, up-to-date version of product information.
CFO Magazine, September 2005
http://www.cfo.com/article.cfm/4315510/c_4334841

6. Lawson Tips & Tricks
Share your tips. Send them to mailto:letter-tips@lawsonguru.com.

(This month's tip comes from the Lawson Knowledge Base)
Why doesn't my manual payment show up on AP150?

Q: I entered manual payment information on an invoice in AP20.1 and released the invoice and ran AP150. Why doesn't my invoice schedule for payment?

A: If you attach a manual payment to an invoice in AP20 and release the invoice the payment has been processed and will not be scheduled by AP150. Just run AP170 to issue the payment.

The LawsonGuru Letter is a free periodic newsletter containing provocative commentary about issues important to the Lawson Software community. The LawsonGuru Letter is published by--and is solely the opinion of--John Henley of Decision Analytics. Visit Decision Analytics at https://www.danalytics.com.To subscribe, send an email to: mailto:letter-subscribe@lawsonguru.com To be removed from the subscription list, send to: mailto:letter-unsubscribe@lawsonguru.com

Decision Analytics. Integrating Lawson with the Real World.