The LawsonGuru Letter is a free periodic newsletter providing provocative commentary on 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 http://www.danalytics.com. For subscription information, see the bottom of this message.

The LawsonGuru Letter is not affiliated with Lawson Software.

---

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!

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.

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

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 http://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!

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!"

(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.