User sqlplus with Lawson (Tips and Tricks)

Most of customers have by now switched over to a Windows / SQL server environment, but we still have several customers who have stayed on Oracle for their Database needs. This mostly stems from having the Oracle skill set in-house as there is really no other advantage to staying on Oracle once you have moved over to Windows.

Often when there are troubles with connecting to the application, it is relevant to test the connection the database from the server itself. Of course there are several ways to do this, but test fastest way is to do so directly from the command prompt in LID as it doesn’t require any additional setup or software. But it is easy to forget how this is done so we decided to write this quick article to document this very simple syntax.
The utility we’re going to use is called sqlplus and it should already installed on your LSF application server. Simply login to the server using LID and on the command prompt type in the following command:
sqlplus <username>/<password>@dbserverName
If you have the correct username and password, and the server is responding, you will get a SQL> prompt on which you can run any query you want. Here’s an example:
However if you type in the incorrect username:
And finally, if you have the incorrect server name or the server is not responding, the prompt will be suspended for several seconds and you will see the following message:
A few small notes about using sqlplus:
  • Be sure to use a semicolon to end your statements. Otherwise the application doesn’t know when to run your query.
  • Make sure the environment variable %ORACLE_HOME% is set correctly. ($ORACLE_HOME on Unix):
  • To exit sqlplus user the “quit” command
  • The SQL buffer contains the last statement you ran, and you can run the previous query again by simply typing “RUN”  and hitting enter.
  • User the LIST command to see a list of your most recently executed SQL commands.
  • “HELP INDEX” shows a list of possible commands
  • To launch a sql script simply put the “@” symbol in front of the file name and execute it. like: @script.sql or even @/path/to/script.sql
  • You can have a multi-line sql statement.
  • The “SHOW USER” command prints the name of the Oracle user you’re logged in as
  • The “SHOW ALL” command prints all the current settings to the screen.

Infor Lawson Query Wizard Troubleshooting

 

We had trouble with the Infor Lawson Query Wizard either timing out or returning a “No Data Found” message when loading in a .dme query.

This is a compiled list of troubleshooting suggestions for the Query Wizard found on Nogalis presentations/Infor documentation/Lawsonguru forums.

1. Limitations of the Query Wizard

 

  1. “No Data Found” Message

 

  1. Try increasing Data Response Timeout value to 9999 (min 30 max 9999) in Set User Preferences (green/yellow arrows)

If this fails, try experimenting with the different options for When encountering error messages

 

  1. On the Criteria tab, try changing Maximum Primary Records to Return to 0 (default 600, max 65520, 0 defaults to max 65520) and Maximum OTM Values to Return (max 600) fields

 

  1. Troubleshooting tips from Nogalis LawsonMSAddins PowerPoint

Querying Lawson Drill Around Comments

WHER ARE MY COMMENTS?!  Many Lawson screens have comment buttons that open a drill-around screen with different comment types.  If you have ever wished to query these comments for reporting or other user interfaces, you may have noticed that they are difficult to find.  Infor has “hidden” them in tables with the “L_H” prefix.  The naming convention is “L_HXXX” where “XXX” is the prefix of the table that feeds screen where you add the comments.  For instance, Requisition Header comments can be found in “L_HRQH”.  And APINVOICE comments can be found in “L_HAPI”.  Join the comments to your main record using the “L_INDEX” column.

I hope this tidbit has saved you some time!!

Running MSCM Install Scripts

Sometimes the install wizard for MSCM installs and updates doesn’t work quite the way you would hope.  In many cases, I have found that I needed to uninstall MSCM, run through the wizard without actually installing, and run the scripts manually so I can trap and fix errors.

All of these scripts are located at the MSCM install directory, in the cofig folder.

Here are the scripts that you would run in order to uninstall your current version of MSCM:

  1. uninstallmscm
  2. undeploymscm

If you are trying to install a new version, run the wizards to unpack the installer and generate the configuration files.  Then, run the install scripts in the following order:

  1. webspheregraft
  2. deploy
  3. installschedulertableprefix
  4. installmscm
  5. deploywithdb

Follow the instructions with each script, because in some cases, you will need to stop the application server and in some cases you will need to start it.

MSCM Scheduler Crash

MSCM uses a Quartz scheduler to bring transactions from Lawson into the MSCM database.  Sometimes on of these events will crash and cause the entire scheduler to crash.  If you notice that details are not making it into MSCM from Lawson, here are some troubleshooting steps:

  1. Check the mscm_server.log located at <WAS_HOME>\profiles\<appserver>\logs
  2. Search for the word “outbound” and start looking for error messages such as “AbortRecoveryException” around the outbound scheduler informational messages.
  3. If you see an aborted recovery, check the EVENT_HEADER and EVENT_DETAIL tables in your database for threads that may be hung (they might have a relatively old HH timestamp or create/last modified date)
  4. If you suspect any threads may be hung, one way to bypass them would be to se the RETRY_COUNT to 99 in the EVENT_HEADER and EVENT_DETAIL tables for that EVENT_HEADER_ID. Then restart MSCM and watch to see if your items start coming over into MSCM.

Creating an Email Contact Form in Grav/Docr

Here is a step-by-step guide for creating an email contact form in Grav/Docr

 

  1. Install “Email” Plugin if not already installed. The simplest way to do this is through the admin panel:

  1. Rename the Markdown file to form.md (instead of chapter.md for example) and include this code snippet (or similar) in the YAML portion at the top of the page below “title”:

Snippet:

form:

name: contact

fields:

–   name: name

label: Name

placeholder: Enter your name

autocomplete: on

type: text

validate:

required: true

 

–   name: email

label: Email

placeholder: Enter your email address

type: email

validate:

rule: email

required: true

 

–   name: message

label: Message

size: long

placeholder: Enter your message

type: textarea

validate:

required: true

 

buttons:

–   type: submit

value: Submit

–   type: reset

value: Reset

 

process:

–   email:

from: “{{ form.value.email }}”

to: “{{ config.plugins.email.to }}”

subject: “[Healthcheck Contact Form] {{ form.value.name|e }}”

body: “{% include ‘forms/data.html.twig’ %}”

–   save:

fileprefix: contact-

dateformat: Ymd-His-u

extension: txt

body: “{% include ‘forms/data.txt.twig’ %}”

–   message: Thank you for getting in touch regarding your healthcheck report!

–   display: thankyou

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Full example of form.md YAML:

We can place any page content below this YAML portion. Our contact form will show up on the bottom of the page after all page content.

  1. Inside the page folder (in which the form.md is placed), create a subfolder named “thankyou” with a file named “formdata.md” with this snippet:

title: Email sent

cache_enable: false

process:

twig: true

 

## Email sent successfully!

This will result in a “Email sent” success screen when the user clicks submit on the contact form:

  1. Final Step – Configuring Email Plugin settings. Full email configuration documentation can be found here (https://github.com/getgrav/grav-plugin-email/blob/develop/README.md)

 

Set-up for Google SMTP (Gmail)

  1. Enable IMAP in your Gmail by going to Settings -> Forwarding and POP/IMAP -> IMAP Access

  1. Enable “Less secure apps” in your user account settings (https://myaccount.google.com/lesssecureapps)

  1. From Admin dashboard, go to Plugin -> Email to configure settings:

Fill out configuration:

 

 

 

 

IIS Manager “Web Platform Installer” Troubleshooting

IIS Manager “Web Platform Installer” Troubleshooting

Nearly all guides regarding installing PHP in IIS recommend using the Web Platform Installer as it will take care of all the additional installs/configurations associated with PHP.

The Error:
When attempting to install PHP through Web PI, this RunPHP Helper error occurs, which stops all of the following installs as well:

This occurs because IIS attempts to download RunPHP Helper from https://sourceforge.net/projects/phpinstallermsi/files/zip/runphp.zip/download using Internet Explorer (IE). Unfortunately, Internet Explorer has issues accessing that site even if it will work perfectly fine in Chrome or other browsers.

The Solution:
Open IE and click the gear icon in the upper right and go to “Internet Options”:

Click on the “Advanced” tab:

Enable all the TLS options and click OK:

Test it out by visiting https://sourceforge.net/projects/phpinstallermsi/files/zip/runphp.zip/download using IE. If the page loads, the Web Platform Installer in the IIS Manager should now be able to install RunPHP Helper.

IP Designer Series – User Action Node – Action Task

Before you can assign work to users in the User Action node, you will need to create the appropriate tasks and add users to those tasks.

*Before you perform this process, you will require Process Server Administrator access.

Log into Rich Client and navigate to Start > Applications > Process Server Administrator > User Configuration > Tasks.

Click Actions > Create, or click the “new” button. Give the task a name, description, and select the inbasket you want the users of that task to have (default “Standard Inbasket”).

To add users to your task, you must first make sure the users exist in the user configuration. Navigate to Start > Applications > Process Server Administrator > User Configuration > Users. Search for your users that need the new task. If they do not exist, click the “new” button or Actions > Create to add them (they must be existing Landmark users).

On the Tasks screen, at the bottom left, Click Actions > Create to add users to the task. Search for your user, give them a start date of when you want the task to be effective, and an end date if the task is temporary for this user. Click “Filter Is Enabled” if you plan to use filters on this task. Select a notify option.

In your IPA process, you can add the tasks on the User Action node. This will determine who is notified (and who should take action) when that node is processed.

 

IP Designer Series – User Action Node – Action Subject

One of the properties options for a User Action node is “Action Reason”. You can require that the approver enter a subject, reason code, and comment. Before you set up the User Action Node, you will want to create your Action Subject in Rich Client. It is important to note that the user creating Action Subjects must have a role with the GlobalUIConfigAccess_ST security class.

Once you have the proper security, log into Rich Client and switch to the data area for which you are creating the subject. Go to Start > Configure > Application. Select “DataArea”.

Click “Add Subject” and give your subject a name.

After you add your subject, you can add it as a Reason Subject on the actions for your User action Nodes.

Grav – A Modern Flat-File CMS

09Grav is an open source flat-file CMS that we have started experimenting with for client documentation. Since it requires no database, it is a simple and great alternative to providing documentation without using separate .doc files. The search feature is especially nice for when you want to search through all documentation.

Demo site for GRAV rtfm skeleton: https://demo.getgrav.org/rtfm-skeleton/basics/overview

 

Here are some things we’ve learned along the way:

Installing Plugins:

Admin Panel and Editor are very important plugins to have.

Admin Panel creates an admin dashboard at URL sitename/admin. From here, you can install other plugins, edit pages, manage users, and change configuration settings.

Install by going to your GRAV root folder in a terminal and calling your php.exe with “bin/gpm install admin”


In our case, the php.exe used was within my xampp folder.

Editor is a very useful plugin that provides a “Edit” section in the admin sidebar that allows any authorized users to modify any PHP, Javascript, CSS, Twig, Markdown, and YAML files. This allows for easy editing of any file directly from the admin dashboard!

 

Install by going to your GRAV root folder in a terminal and calling your php.exe with “bin/gpm install editor”

 

CSS:
To modify the css, access user/themes/learn2/css-compiled/theme.css

In this case, we modified the file to left align all my elements by changing the #chapter ID since my page files are chapter.md. This can be achieved by changing these four “center” properties to “left”

(Before) Center aligned:

(After) Left aligned:

Another case is when we modified sidebar font-size by modifying #sidebar ul.topics > li > a in css-compiled/theme.css

Sidebar Logo (top left):
To change the sidebar logo from default GRAV image, access user/themes/learn2/templates/partials/logo.html.twig

To change the admin sidebar logo from default GRAV image, access: user/plugins/admin/themes/grav/templates/partials/logo.html.twig

Replace what is in the file with the SVG code of the image. This was done by converting a PNG to SVG using https://www.pngtosvg.com/

Example of what SVG looks like:

Sidebar:

To modify the sidebar on the left, access user\themes\learn2\templates\partials\sidebar.html.twig
In this case, we wanted to remove the check marks indicating that we had visited that page along with the “Clear History” button on the bottom of the sidebar.

This can be done by removing the relevant lines in the file.

Where to keep your images
We ran into an issue where the images in the images folder would disappear upon caching. This was due to the images folder being in the wrong place. Even though there is a default “images” folder in the root folder, this is not the place to store your images.

Per the official Grav documentation, the images folder should be placed within user/pages:

Do not put images in here:

Instead put them in user/pages/images as depicted here:

On the pages themselves (.md files), link to the images by going up one folder and accessing the images folder with the format (..\images\imagename.png):

 

Be careful when using underscores (“_”) in the .md file
In markdown, surrounding a word with underscores will result in italicizing that word. This could become an issue since many files we deal with use underscores to separate timestamps from the file name.

Take this line for example:

Based on that code, we want the page to look like this:

Instead we get an unwanted italicized word and the removal of an underscore between CODE and RELATION:

The problem can be fixed by “escaping” the underscore by putting a backslash in front of it. Keep in mind that even if we were to escape the underscore between CODE and RELATION, the Markdown language would then try to find the next underscore and would italicize all words in between the underscores (marked in green).

This code:

Will use the underscores boxed in green to italicize all in between them (and removes the underscore in front of “configuration”):

To resolve this, make sure to escape all underscores:

Grav File Uploads

In admin panel, we can add users with these 2 permissions (admin.login and admin.pages):

This creates a user who can log into the admin panel and can edit pages.

When these users go to edit the pages, there is a section that allows media uploads.

“Edit page” page:

These users can also access the “edit page” page through the edit links on the site itself:

These are the files types supported by Grav:

(Although csv is not listed here, it was able to be added as a xml media type and verified to be working)

Once you upload the file and save the page, anyone who has access to this “edit page” page can download/view the file by clicking on the eye “View” icon:

The uploaded files go into the same directory as the markdown page itself.

Clicking the + “Insert” icon will insert the file in the page like so:

Which looks like this on the page:


Clicking it displays the file on the page for .txt, .pdf, .json and .csv. Downloads it immediately for .docx files (these are the 5 I tested):

Issue: When clicking the “View” eye icon for .txt files, there is an error regarding permissions. (It works fine when actually inserted into the page and clicked on from there)

Clearing Cache

CLI commands:
cd ~/webroot/my-grav-project

bin/grav clear-cache