Custom Line Number Functionality using Automation Script | IBM Maximo

We have a custom Object, LE_WPP, which has multiple lines and a foreign key from its parent Object, LE_WPPAYMENTID. We also have an attribute called Linenum.

We will execute the attribute launch point when the foreign key (LE_WPPAYMENTID) inserts by default, when we click on the new row button.

Launch Point Type: Attribute

Object: LE_WPP

Attribute: LE_WPPAYMENTID (Foreign Key)

Script:

vWPPAYMENTID=mbo.getString('LE_WPPAYMENTID')

vWPP=mbo.getThisMboSet()

vWPP.setWhere("LE_WPPAYMENTID = "+ vWPPAYMENTID+"") 

vWPP.setOrderBy("LE_PROGRESSID , LINENUM ");

#vcount=vWPP.count()

maxLineNum  = 0

vWPP1=vWPP.moveFirst()

Asset Deletion Query Sql | IBM Maximo

To delete asset totally from system including all related tables in Maximo System.

Single Asset Delete Query Example:

delete from AMCREWTOOL where ASSETNUM='WAP-0071';

delete from AMCREWWOTL  where ASSETNUM='WAP-0071';

delete from AREASAFFECTED where AFFECTEDASSETNUM='WAP-0071';

delete from ASSET  where ANCESTOR='WAP-0071';

delete from ASSET  where ASSETNUM='WAP-0071';

delete from ASSET  where PARENT='WAP-0071';

delete from ASSET where PLUSTALIAS='WAP-0071';

delete from ASSETANCESTOR  where ANCESTOR='WAP-0071';

delete from ASSETANCESTOR  where ASSETNUM='WAP-0071';

delete from ASSETAUDIT  where ASSETNUM='WAP-0071';

delete from ASSETCALIBRATION  where ASSETNUM='WAP-0071';

delete from ASSETFEASPECHIST  where ASSETNUM='WAP-0071';

delete from ASSETFEATURE  where ASSETNUM='WAP-0071';

delete from ASSETFEATUREHIST  where ASSETNUM='WAP-0071';

delete from ASSETFEATURESPEC  where ASSETNUM='WAP-0071';

delete from ASSETHIERARCHY  where ASSETNUM='WAP-0071';

delete from ASSETHIERARCHY  where PARENT='WAP-0071';

delete from ASSETHISTORY  where ASSETNUM='WAP-0071';

delete from ASSETLOCCOMM  where ASSETNUM='WAP-0071';

delete from ASSETLOCRELATION  where SOURCEASSETNUM='WAP-0071';

delete from ASSETLOCRELATION  where TARGETASSETNUM='WAP-0071';

delete from ASSETLOCRELHIST  where SOURCEASSETNUM='WAP-0071';

delete from ASSETLOCRELHIST  where TARGETASSETNUM='WAP-0071';

delete from ASSETLOCUSERCUST  where ASSETNUM='WAP-0071';

delete from ASSETMETER  where ASSETNUM='WAP-0071';

delete from ASSETMNTSKD  where ASSETNUM='WAP-0071';

delete from ASSETOPSKD  where ASSETNUM='WAP-0071';

delete from ASSETSPEC  where ASSETNUM='WAP-0071';

delete from ASSETSPECHIST  where ASSETNUM='WAP-0071';

delete from ASSETSTATUS  where ASSETNUM='WAP-0071';

delete from ASSETTOPOCACHE  where SOURCEASSETNUM='WAP-0071';

delete from ASSETTOPOCACHE  where TARGETASSETNUM='WAP-0071';

delete from ASSETTRANS  where ASSETNUM='WAP-0071';

delete from ASSETTRANS  where FROMPARENT='WAP-0071';

delete from ASSETTRANS  where TOPARENT='WAP-0071';

delete from ASSETWORKZONE  where ASSETNUM='WAP-0071';

delete from AUTOATTRUPDATE where ASSET='WAP-0071';

delete from CI  where ASSETNUM='WAP-0071';

delete from COLLECTDETAILS  where ASSETNUM='WAP-0071';

Send Email from Automation Script, Multiple ways | Maximo

 1. Here’s a simple, famous 3-line code snippet to send an email. This example uses a communication template, but the body (MESSAGE) can be dynamically modified as needed

    ctMboSet = mbo.getMboSet("$commtemp","COMMTEMPLATE"," TEMPLATEID ='COE_SCHEDULED' ");

    ctMboSet.setQbeExactMatch("true")      

    ctMboSet.reset()

    ctMbo = ctMboSet.getMbo(0)

    ctMbo.setValue("MESSAGE", txtres)

    if(ctMbo is not None):

        ctMbo.sendMessage(mbo,mbo);

Adding Timestamp upon Cell Modification | Excel

Enabling a Timestamp in Excel Upon Cell Modification without Overwriting Existing Timestamps

To achieve this, follow these steps:

Step 1: Enable Iteration in Excel Options

Navigate to Options -> Formula -> Iteration and ensure the option is enabled.

Step 2: Adding Timestamps to Column B on Change in Column C

Suppose your values are in Column C, and you want to track timestamps in Column B. Apply the following formula in Column B:

=IF(C2<>"", IF(B2="", CONCAT(MINUTE(NOW()), ":", SECOND(NOW())), B2), "")

This formula will only update the timestamp in Column B if a change is made in the corresponding cell of Column C, ensuring it doesn't overwrite existing timestamps.

A Guide to Installing and Configuring SSH Tunneling | Windows

Introduction:

Secure communication and data protection are paramount in today's digital landscape. One effective way to ensure this is through the use of SSH tunneling. In this guide, we'll walk you through the process of installing, setting up, and utilizing SSH tunneling on a Windows operating system. This technique will provide you with a secure and encrypted channel for your data transmission needs.

Step 1: Install OpenSSH as a Windows Optional Feature

1. Begin by installing OpenSSH as an optional feature on your Windows machine.
2. Windows will automatically create a new firewall rule to permit inbound SSH access over port 22.

Note that, if you manually download and install OpenSSH, you'll need to create an inbound rule to allow SSH port access.

Step 2: Start and Enable the SSH Service

1. Open PowerShell as an administrator

2. Start the SSH service using the following command 

Start-Service ssh

How to Download Videos Shared on Microsoft SharePoint and 365 | Microsoft

Is it possible to download a video shared over Microsoft SharePoint or 365 when we can watch it but are unable to download it?

Introduction:

Have you ever encountered a situation where you're able to watch a video shared on Microsoft SharePoint or 365, but you can't seem to find a way to download it? In this guide, we'll walk you through a simple solution that empowers you to download these videos using the versatile media converter, ffmpeg.

Solution: Using FFmpeg Video Converter

FFmpeg is a powerful and universal media converter that can handle a wide range of media tasks, including converting and transcoding videos. Follow these steps to download videos that you can watch but can't download directly from Microsoft SharePoint or 365:

ffmpeg is a universal media converter. It can read a wide variety of inputs - including live grabbing/recording devices - filter, and transcode them into a plethora of output formats.

Search and Replace function DB2 SQL

A function to replace all occurrences of a string in a source string with a new string.

• Using Db2 REPLACE() function with literal string example

REPLACE(source_string, search_string, replace_string )

SELECT 

    REPLACE('IBM DB2','DB2','Db2') result

FROM 

    sysibm.sysdummy1;

RESULT :  IBM Db2 

Excel Formulas FILTER, SEQUENCE, TEXTJOIN, TRANSPOSE | Excel

 

Filter using the formula, instead of lookup.

=FILTER(OUTPUT, CONDITION)

EXAMPLE: 

FILTER(A1:A10, B1:B10=C1)

OUTPUT WILL BE SPILLED DOWN. 

TRANSPOSE to spill this output in columns, instead of down in the rows.

=TRANSPOSE( FILTER(OUTPUT, CONDITION) )

EXAMPLE: 

=TRANSPOSE( FILTER(A1:A10, B1:B10=C1) )

TEXTJOIN for a spill in one cell with a separator like a comma.

=TEXTJOIN(",", FILTER(OUTPUT, CONDITION) )

EXAMPLE: 

=TEXTJOIN(",", FILTER(A1:A10, B1:B10=C1) )

SEQUENCE:

to have a list of numbers 

=SEQUENCE(row, column, start, interval)

example: 

1 row and 10 columns, left to right

=SEQUENCE(1,10)

1 column and ten rows, top to down, 

=SEQUENCE(10,1)

1 column and ten rows, top to down, starting from 5 and the interval is 2

=SEQUENCE(10,1, 5,2)

To extract data for the last month (day 1st to day 30th) from a database in DB2 || IBM DB2

To extract data for the last month (day 1st to day 30th) from a database in DB2, 

Use the below condition in WHERE clause :

TRANSDATE >= THIS_MONTH(CURRENT DATE - 1 MONTH)

AND TRANSDATE < THIS_MONTH(CURRENT DATE)

Umar Adil

Special Automation Scripts NEW, SAVE,DUPLICATE,AFTERDUPLICATE | Maximo

 <MBONAME>.NEW

 <MBONAME>.DUPLICATE

 <MBONAME>.SAVE

Introduced in 7613.

 <MBONAME>.AFTERDUPLICATE

IBM Maximo Autoscripting Guide – OBJECTNAME.DUPLICATE Script (ibm-maximo-dev.github.io)

Restrict a User to Delete an Attachment from an Approved PO | Maximo

The requirement is to lock the record so that nobody can delete a record if the purchase order is approved.

And the solution is with a small automation script on DOCLINKS object:

object: DOCLINKS

event: BEFORE SAVE

Type: Object Launch Point

Script: Jython

owner=mbo.getOwner()

if owner and owner.getName()=="PO" and owner.getInternalStatus()=="APPR" 

    service.error("","Purchase Order is approved. ")

Also ideal to control it via the signature option, create a new signature option from the application designer and grant access using the relevant security groups then just use it in your code. thanks to Steven Shull.

owner.sigopGranted("DELETEATTACH"): 

Add plus Sign and inverted comma (+") in each line in variable | BIRT

 for beginning of the line, Find and Replace as below:

^p

^p+"    

 for End of the line, Find and Replace as below:

^p

    "^p

Standalone Lookup Using Relationship - Birt Report Parameter (Without New Attribute) || Maximo

There was a requirement of Month Parameter to be added on report runtime. The Month was already handled in the report query but we need standalone lookup just for month selection. But Maximo didn't allowed standalone lookup without linking attribute to the parameter. 

So we ended up with the below solution to add add Standalone Lookup Using Relationship - Birt Report Parameter (Without New Attribute).

Steps:

• Add new ALN Domain with Month Values e.g. JAN, FEB, MAR....

• Add following new Lookup changes in LOOKUPS.XML file with where clause = "DOMAINID = 'ZZMONTH' "

Extract First or Last Date of the Week | Date functions in DAX | Power BI

 Extract the First Day of this Week

1. Go to DATA, Add new columns, 

Get Current Date Time

= TODAY()                        // current datetime

Get Current Day, Month, MonthName, Quarter, Year etc

= TODAY() // current datetime

= mytable[datecolumn].[Quarter]

= mytable[datecolumn].[Day]

= mytable[datecolumn].[Month]

= mytable[datecolumn].[Year]

Format a date using formula

todayname = format(today(),"dddd")            //format to extract the Name of the date.

Get a Week Number of a Date

weekDay= WEEKDAY(TODAY()) //return the day number, it can take 1,2,3 as parameter to start the count from saturday, sunday or monday.

Get the First Day of the Week

firstDayofThisWeek = today() - WEEKDAY(TODAY()) +1 //today - number of day +1

Get the Last Day of the Week

lastDayofThisWeek = today() - WEEKDAY(TODAY()) +7 //today - number of day +7

Get the First Day of the Month

firstDayofThisMonth = STARTOFMONTH(TODAY()) //START OF MONTH

Get the Last Day of the Month

lastDayofThisMonth = ENDOFMONTH(TODAY()) //END OF MONTH

Populate a table/column with dates in a range

ourdates = calendar(date(2022,01,01) , date(2022-01-31) ) //first and last day as parameter

Delete Outbound Error Messages from Message Reprocessing Application | IBM Maximo

Deleting error messages requires two actions. 

First the DELETEFLAG must be set to 1, then the status changed to RETRY. 

Both of these are done transparently when deleting a message in the Message Reprocessing application. In an escalation they must be done in the correct order.

Warning: Be very careful when using this escalation to delete held messages. If valid transactions are sent in while this escalation is active, and they fail due to a correctable error, they can be deleted.

In the Escalations application, create a new escalation.

Applies To: MAXINTERROR

Condition: status=status='HOLD' and queuename = 'jms/maximo/int/queues/sqout'

Add an escalation point and check the Repeat box.

Add an action. Use the lookup arrow to Go To the Actions app to create the Action.

Create a new Action with the following values:

Object: MAXINTERROR

Type: Set Value

Value: 1

Parameter/Attribute: DELETEFLAG

Save and Return with Value to the Escalations application.

Bulk Update any Attribute from List View | Maximo

Update multiple records or even the child records from the list view.

for instance, if you want to update any record in PR table, just write the attribute name

but for Child records like PRLINE; use the standard relationship like PRLINE.orderqty

1.  Create a non-persistent object NP_BULKUPDATE with 2 fields ATTRIBUTEVALUE, ATTRIBUTENAME.

<dialog id="NP_BULKUPDATEDIALOG" label="NP_BULKUPDATEDIALOG" mboname="NP_BULKUPDATE">

    <section id="NP_BULKUPDATEDIALOG_110">

        <sectionrow id="NP_BULKUPDATEDIALOG_111">

            <sectioncol id="NP_BULKUPDATEDIALOG_112">

                <textbox dataattribute="ATTRIBUTENAME" id="NP_BULKUPDATEDIALOG_114" />

                <textbox dataattribute="ATTRIBUTEVALUE" id="NP_BULKUPDATEDIALOG_115" inputmode="required" />

            </sectioncol>

        </sectionrow>

    </section>

    <buttongroup id="NP_BULKUPDATEDIALOG_120">

        <pushbutton default="true" id="NP_BULKUPDATEDIALOG_121" label="OK" mxevent="dialogok" />

        <pushbutton id="NP_BULKUPDATEDIALOG_122" label="Cancel" mxevent="dialogcancel" />

    </buttongroup>

</dialog>

2. create a dialog in application designer and add the above 2 fields into any application, let's say in PR application.

3. Develop an OLP script on NP_BULKUPDATE before Add and good to go. 

from psdi.mbo import MboConstants

from psdi.webclient.system.beans import ResultsBean

# to get the non persistent object

mbo = mboset.getMbo(0)

vATTRIBUTENAME = mbo.getString("ATTRIBUTENAME")

vATTRIBUTEVALUE = mbo.getString("ATTRIBUTEVALUE")

# get AppInstance object to retrieve UI properties list view

app = service.webclientsession().getCurrentApp()

# get the MboSet from the app

parentSet = app.getResultsBean().getMboSet()

#service.error('',str(app))

# this is True if the Select Records check boxes are displayed

isTableSelect = app.getResultsBean().getTableStateFlags().isFlagSet(ResultsBean.TABLE_SUBSELECT_ON)

vParent = parentSet.moveFirst()

while (vParent):

    # if Select Records is displayed we have to take action on selected records only

    # if Select Records is NOT displayed we have to take action on all records

    if vParent.isSelected() or not isTableSelect:

        vParent.setValue(vATTRIBUTENAME, vATTRIBUTEVALUE)

    vParent = parentSet.moveNext()

parentSet.save()

Extension to my confirmation dialog upon deletion post but got the idea from Bruno's article which is here:

Confirmation dialog before Deleting PR Item, and Save It In Worklog | Maximo

Add a confirmation dialog before deleting any line in PR application. So that user will have to enter the reason of deletion and it will be saved in worklog with all the related details like who deleted, reason of deletion, what was the item number and quantity at the time of deletion.

1. add one non-persistent attribute let's say NP_REASON in the PR object.

2. Make/add a dialog in PR.XML, add NP_REASON attribute 

<dialog id="deletedialog" label="deletedialog">

<section id="1567883350896">

<sectionrow id="1567883359616">

<sectioncol id="1567883366027">

<statictext align="left" id="1597323707588" label="Are you sure, you want to delete this line?"/>

<textbox dataattribute="PRNUM" id="1567883419910" inputmode="readonly"/>

<textbox dataattribute="iTEMNUM" id="1597321688221" inputmode="readonly"/>

<textbox dataattribute="NP_REASON" id="1567892172806" inputmode="required"/>

</sectioncol>

<sectioncol id="1567883367821"/>

<sectioncol id="15678833596162"/>

</sectionrow>

</section>

<buttongroup id="mydialog_2">

<pushbutton default="true" id="mydialog_2_1" label="OK" mxevent="dialogok"/>

<pushbutton id="mydialog_2_2" label="Cancel" mxevent="dialogcancel"/>

</buttongroup>

</dialog>

3. In PRLINE tab using application designer, edit the delete button properties and modify the event property to the newly created dialog (deletedialog) as below:

event: deletedialog

4. Write an ALP automation script on PR.NP_REASON attribute, the body is as below:

vWORKLOG = mbo.getMboSet("PR.WORKLOG")

line = vWORKLOG.add()

line.setValue("LOGTYPE","CLIENTNOTE")

line.setValue("DESCRIPTION",str("Item:")+mbo.getString("itemnum")+str(" Qty:") + mbo.getString("ORDERQTY") +str(" UnitCost:") + str(mbo.getDouble("unitcost"))+str(" Reason:") + mbo.getString("NP_REASON"))

line.setValue("SITEID","KAUST")

line.setValue("RECORDKEY",mbo.getString("prnum"),2L)

line.setValue("CLASS","PR",2L)

mbo.delete()

mbo.save()

  

SQL to find the System Properties | Maximo

Some times we restore our production database to other development environment, but its a best practice to disable few things first before we run the application. one of the examples is to stop the email/communication templates. 

2 tables are handling System Properties in Maximo:

1. SELECT * FROM MAXPROP;

2. SELECT * FROM MAXPROPVALUE;

Below example to find the SMTP host which is used to send emails from Maximo. We can update this property to disable the emails. 

SELECT MAXPROP.PROPNAME, MAXPROP.DESCRIPTION, MAXPROPVALUE.PROPVALUE

FROM MAXPROP join MAXPROPVALUE ON MAXPROP.PROPNAME = MAXPROPVALUE.PROPNAME

WHERE MAXPROP.PROPNAME = 'mail.smtp.host'

Enable Maximo Activity Dashboard for Maximo 7.6 | Maximo

There is one system property as below, just set it to True

mxe.webclient.activitydashboard = true

And use this URL to access Activity Dashboard:

http://<hostname>:<port>/maximo/webclient/utility/profiler/PerfMon.jsp

 

 Enabling PerfMon will significantly degrade server performance and so it should not be left enabled any longer then necessary to troubleshoot performance problems, especially on a production server.

Reference: https://www.ibm.com/support/pages/node/1133601

Inventory Issued Year to Date (YTD) and Reset Annually | Maximo

 

This Year to Date (YTD) attribute in Inventory application shows us the count of issuance of the item during the whole year. 

It's one of the task of Store Manager; to reset it and roll the counter one year down. 

Maximo gives us 3 counters, Last year , 2 Years ago and 3 years ago as depicts in the right image.

There is an option is the Select Action menu as below:

Implicit Variable of Domain Synonym List in Relationship Where Clause - SQL | Maximo

Another way in the SQL condition while creating Relationships in Maximo:

get all synonym values of CLOSE,CAN from work order status

 :&synonymlist&_wostatus[CLOSE,CAN]

is equivalent to

SELECT VALUE FROM SYNONYMDOMAIN WHERE DOMAINID = 'WOSTATUS' AND MAXVALUE IN ( 'APPR')

Usage:

Let's make a relationship and enter this condition within a where clause;

Other useful bind variables are here:

&USERNAME&, &APPNAME& and other special bind variables you can use (ibm.com)

Add a Hyperlink in Help Menu | Maximo

To add an external Hyperlink to the Help Menu in Maximo.

1. Export MENUS.xml from Application Designer application. 

2.Open it in any text editor and search for <menu id="HELP">

3. Now add this line at your choice as below: 

    <menu id="HELP">

        <menuitem event="maximohelp" id="tophelp" image="menu_icon_apphelp.gif" label="IBM Knowledge Center" licensekey="EAMTOPHELPMENU" value="mxe.help.maximohelplink"/>

        <menuitem event="apphelp" id="help1" image="menu_icon_apphelp.gif" label="Help"/>

        <menuitem id="IBMesupport" label="IBM Electronic Support">

            <menu id="IBMesupportsub">

                <menuitem event="loadlink" id="esupportmam" label="Asset Management Online Support" link="http://www.ibm.com/support/docview.wss?uid=swg21418666&amp;ibmprd=tivmx7"/>

                <menuitem event="loadlink" id="esupport3" label="IBM Support Assistant" link="http://www.ibm.com/software/support/isa?ibmprd=tivmx7"/>

                <menuitem event="loadlink" id="esupport4" label="Support Portal" link="http://www.ibm.com/support/entry/portal?tivmx7"/>

                <menuitem event="loadlink" id="esupport5" label="My Notifications" link="http://www.ibm.com/support/mynotifications?ibmprd=tivmx7"/>

                <menuitem event="loadlink" id="esupport6" label="Cloud and Smarter Infrastructure Software Training and Certification" link="http://www.ibm.com/software/tivoli/education/index.html?ibmprd=tivmx7"/>

                <menuitem event="loadlink" id="IBMcorp" label="IBM Corporation" link="http://www.ibm.com"/>

                <menuitem event="loadlink" id="Google" label="Google Search" link="http://www.google.com"/>

            </menu>

4. Import again into Application designer, and you are done. 

Reference: https://www.ibm.com/support/pages/how-hyperlink-new-browser-window-maximo-title-bar

Call Maximo Script from Rest API (http call) (Create SR Example) | Maximo

 Create a simple script in Maximo and Call the Maximo Rest API request to post/get the data.

Let's create a simple automation script, it will create an SR with minimal information. 

1. create a script in automation script application with below code:

from psdi.server import MXServer;

srSet = MXServer.getMXServer().getMboSet("SR", MXServer.getMXServer().getSystemUserInfo());

newSR = srSet.add();

newSR.setValue("TICKETID", request.getQueryParam("ticketid"));

newSR.setValue("DESCRIPTION", request.getQueryParam("description"));

srSet.save();

2. Now send below  request from Postman or any other tool:

http://localhost/maximo/oslc/script/createjsonsr?_lid=maxadmin&_lpwd=maxadmin&ticketid=123&description=test ticket

or We also can send the data in payload

POST http://localhost/maximo/oslc/script/createjsonsr

MAXAUTH <encoded 64bit>

{

    "ticketid" : "123456",

    "siteid" : "BEDFORD",

    "description" : "test SR from http call"

}

 

parameter appended in query string after (?)

request.getQueryParam("variable")

To get the user info from the request

request.getUserInfo()

read and parse the request to get the payload. 

res = JSONObject.parse(output)

vId = res.get("id")

mbo.setValue("description",vId)

SurveyMonkey Integration – Call External API from Automation Script | Maximo

In the continuation of my last post "integration with Survey Monkey through Zapier". 

I had another requirement to implement a customization to directly call the external API of Survey Monkey from Maximo.

Below are the 2 steps procedure, first is the list of APIs we are going to call in a sequence from Maximo Automation Script, and then the detail code I used.

Prerequisites: 

• Survey Moneky Account, Token
• Design Survey and enable "Embed First Question" in the Email
• Create an Email collector 
• Get the id of the newly created Email Collector

A. Three APIs we are going to call for this project from Automation Script: 

1. https://api.surveymonkey.net/v3/collectors/{{COLLECTOR_ID}}/messages
1. {

     "type": "invite",

     "subject": "Survey for the Ticket {{myextrafield}} {{CustomData1}}",

     "is_branding_enabled": true,

     "embed_first_question": true

   }
2. https://api.surveymonkey.net/v3/collectors/{{COLLECTOR_ID}}/messages/{{MESSAGE_ID}}/recipients
1. {     "email": "abdulqadeer.el@gmail.com",     "custom_fields": {    "1": "c1"},     "extra_fields": {     "myextrafield": "1234"   }    }
3. https://api.surveymonkey.net/v3/collectors/{{COLLECTOR_ID}}/messages/{{MESSAGE_ID}}/send

1. { }

B. Jython code can be implemented on any object, for testing I am using SR object before-save trigger. 

Invoke End Point from Automation Script | Maximo

 To read from any webservice/rest as a client, just use below code to call any existing endpoint with http handler. 

from java.util import HashMap,Calendar

from com.ibm.json.java import JSONObject

response=service.invokeEndpoint("SURVEY-endpoint name",map,body1)

body = JSONObject.parse(response)

mbo.setValue("description",response[:200])

#mbo.setValue("description",body.get("data")[1].get("email"))