bookmark_borderUpdate selected records

Yes, there’s an OOTB functionality

Problem is: it will use the current “view” and thus, most likely, allow to change all fields.

What if you want to restrict this update to certain fields only?

First design a new “view” only containing the fields they are allowed to change on mass. It’s actually a Form Design.

Configure Form Design\New…

After creation you can switch to the newly created view (or form)

Before: Standard (or default form)

After: my stripped down form

Basic idea:

An Client side UI action where we call the dialog, call the new “submit” button, handle updates on selected records.

Code: doStuff() is set in the Onclick field of the UI action

function doStuff() {
    try {
        var d = new GlideModalForm('my dialog`s title', 'your_table_name');
        //var d = new GlideDialogForm('my dialog`s title', 'your_table_name');

	d.addParm('sysparm_view','Test_For_Blog');

	d.addParm("sysparm_view_forced", true);
	d.addParm('sysparm_form_only','true');


        // in the fixSubmit we replace the submit button with our own "submit" button
        d.setOnloadCallback(fixSubmit);
	// used for GlideDialogForm
	//d.setLoadCallback(fixSubmit);

        d.render();

    } catch (e) {
        // log your error
    }

}

// this function will be called from the newly created button
// use this function to update your selected records as you please
function mySubmit() {
    try {
        // document from dialog in iframe
        var doc = document.getElementById('dialog_frame').contentWindow.document;
        // retrieve values entered in the dialog
        var sd = doc.getElementById('sn_customerservice_case.short_description').value;

        // g_list.getChecked has a comma separated list of sys_id of records you have selected
        var recordsToUpdate = g_list.getChecked().split(',');
        recordsToUpdate.forEach(function(thisElement) {
            var gr = new GlideRecord('sn_customerservice_case');
            // retrieve record
            gr.get(thisElement);
            // update it's values, maybe add a check if there WAS a value in the dialog, otherwise you'll write '' back to the records
            gr.setValue('short_description', sd);
            gr.update();

        });
        // remove the dialog from the DOM, to prevent a nasty message telling you you might lose entered data
        document.getElementById('dialog_frame').remove();
        // refresh the list
        g_list.refresh();
        return true;

    } catch (e) {
        // log your error
    }
}

// removing the original submit button as it creates a record which we don't need
// add our own submit button which calls the mySubmit function which updates records as needed
function fixSubmit() {
    try {
        // document from dialog in iframe
        var doc = document.getElementById('dialog_frame').contentWindow.document

        // hide original submit button as it always creates a new record which we don't need
        var btnSubmit = doc.getElementById('sysverb_insert_bottom');
        btnSubmit.style.display = 'none';

        // create our own submit button
        var btn = doc.createElement("button");
        btn.innerHTML = "our own Submit button";
        btn.type = "button";
        btn.name = "formBtn";
        btn.id = "mySubmitButton";
        // the mySubmit will not be on the iframe of the dialog but on the entire page
        // that's why we need to call it via parent.
        btn.setAttribute('onClick', 'parent.mySubmit()');
        // insert the newly created button where the original has been hidden above
        btnSubmit.parentNode.insertBefore(btn, btnSubmit.nextSibling);

    } catch (e) {
         // log your error
    }

}

The crucial thing to make d.addParm(‘sysparm_view’,’Test_For_Blog’); work was the next line:

d.addParm(“sysparm_view_forced”, true);

bookmark_borderget UNC path of fileserver file

If you work a lot with Sharepoint, and Windows, you’ll know: links to other files on mapped (S:) fileserver don’t work. You need the UNC path for this to work properly.

Getting UNC path on Windows is, sorry, a pain in the ass. Yes, there are “workarounds” there, like creating a link in an outlook email etc. Yes there’s VBScripts which use Internet Explorer to copy the path to your clipboard. But this doesn’t work in our environment due to some policies which prevent IE object being properly instantiated.

So I was looking for another solution and created this cute script:

# run without arguments will create a file called DropFileToGetUNCPath.lnk

# if you drop a file onto the shortcut it'll return the UNC path

if($args[0] -eq $null)

{

            # creating the shortcut to drop files later on

            $path = $pwd.path

            $script = $MyInvocation.MyCommand.Path

            $WshShell = New-Object -comObject WScript.Shell

            $Shortcut = $WshShell.CreateShortcut("$path\DropFileToGetUNCPath.lnk")

            $Shortcut.TargetPath = "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe"

            $Shortcut.Arguments = "-noprofile -file """ + $script + """"

            $Shortcut.Save()

}else{

            $file = $args[0]

}

 

$drive = $pwd.drive.name + ":"

# find UNC paths for directories

$drives = net use

$drive = ($drives -match ".*" + $drive + ".*")

#debug

#echo $drive

$parts = $drive -split "\s{1,11}"

#debug

#echo $parts

$windowsDrive = $parts[1]

$uncDrive = $parts[2]

$file -replace $windowsDrive, $uncDrive | clip

What the code does: it copies the UNC path (if possible, otherwise the normal path if from a local file) to clipboard

Put it into a file called something.ps1. In the screenshot the file is called getUNCPath.ps1

Execute the file with PowerShell. It’ll create a shortcut, named DropFileToGetUNCPath.lnk with Target …powerShell.exe (such that I can drop a file onto it) with some other parameters, one of them being the name of the script you are currently running and which will do the conversion.

You can put this script in any location and run it via PowerShell… it’ll create the file DropFileToGetUNCPath.lnk in that location.

Once you drop a file onto the shortcut it’ll create the UNC path and puts it into clipboard, ready to be pasted whereever you need it

As a matter of fact, if you Windows>Execute then Shell:sendto, copy the file into the Location which opens, you can “Send” any document and you’ll have it’s path in the clipboard thereafter.

bookmark_borderAdd a ribbon with custom image to Excel

There’s a tool out there which helps creating ribbons with custom icons. Google is your friend.

I don’t like tools very much hence I figured it out myself as I like to understand how it works.

Here I explain it plain and simple

HowTo:

Rename your Excel file to filename.xlsm.zip

Double click the new ZIP file. DO NOT Extract, just open!

Create a “customUI” folder

In the customUI folder you add an images folder with your custom images

Then you need to adjust/create some files.

The .rels one in the _rels folder in the root. Here you add the “reference” to your customUI

Something like this

<relationship id="someID" type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" target="customUI/customUI.xml">
</relationship>

For the second one you create a _rels folder within folder customUI and put a file with filename customUI.xml.rels with following contents in there:

<!--?xml version="1.0" encoding="UTF-8" standalone="yes"?-->
<relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
	<relationship id="deletePivot" type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" target="images/deletePivot.png"></relationship>
	<relationship id="stackedBarClear" type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" target="images/stackedBarClear.png"></relationship>
	<relationship id="stackedBar" type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/image" target="images/stackedBar.png"></relationship>
</relationships>

The id attribute (in bold), of the image reference, you then need to reference (bold below) those images in the last file we need to create.

This file name customUI.xml within the customUI folder contains the following content

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui"> 
	<ribbon> 
		<tabs> 
			<tab id="CustomTab" label="Service Now Tools"> 
				<group id="Breaches" label="Breaches Report"> 
					<button id="createReport" label="Create Report" image="stackedBar" size="large" onaction="createReport"></button>
					<button id="resetReport" label="Reset Report" image="stackedBarClear" size="large" onaction="resetReport"></button>
					<button id="deleteReport" label="Delete Report" image="deletePivot" size="large" onaction="deletePivot"></button>
				</group>
			</tab>
		</tabs>
	</ribbon>
</customui>

I am not 100% sure about the naming conventions of customUI.xml and customUI.xml.rels. I think the .rels file has to begin with the same as the customUI.xml file.

Once all done, remove the .zip ending and open the file in Excel.

The result is this:

new tab
your newly defined ribbon buttons with custom images

The only thing now you need to do is to add “code” in the file (kind of macros), ie. subs with the same name as defined in the customUI.xml in the onAction attributes

Public Sub CreateReport(ByVal control As IRibbonControl)
Public Sub DeletePivot(ByVal control As IRibbonControl)
Public Sub resetReport(ByVal control As IRibbonControl)

What I then did:

I saved the Excel file as Addin (.xlam). This way I can distribute the file to whom I want, Updates should be kind of a breeze and the buttons are available in EVERY Excel file you load.

bookmark_borderAdd shared mailbox to outlook

It is soooooooooooooooooo intuitive

First you need to select account settings, account settings

Then you select Change

Then you select Advanced settings (maybe “more settings…”)

Then you go to “Advanced” and “Add” and add the shared mailbox

Bloopers?

Why do I need to go to “my account” settings when I just want to add a shared mailbox?

Then why should I need to select “change” for something I want to add?

bookmark_borderGoogle Sheets API – batchUpdate

batchUpdate() works on values if you use sheet.values().batchUpdate() and works on sheet properties if you use sheet..batchUpdate()

So I wanted to be notified for my tradingBot (made in Python), if a certain KPI was met. On my Mac, no problem. I use Notify.

from notifypy import Notify

But I wanted to receive a notification even when I am not sitting at my computer. I figured with the help of Google I could achieve this. So I write to a Google Sheet some values and there I trigger an AppScript which sends me an email if KPI is met.

First attempt:

    values = [[datetime, last, change, gain, loss, rsi]]
    part1 = {
        'values': values
    }

    result = sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A2:F2",
        valueInputOption="RAW", body=part1).execute()


    values = [["insert into ticker (datetime, last, change,gain,loss) values ( '{}',{:f} ,{:f} ,{:f}, {:f} )".format(
        datetime, last, change, gain, loss, rsi)]]
    part2 = {
        "values": values
    }
    result = sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range="Sheet1!A5",
        valueInputOption="RAW", body=part2).execute()

execute() was called twice, for each row I update. Hence triggering the AppScript (which sends an email) twice. I always wondered why I receive duplicates. Couldn’t figure it out.

Then I received a warning from Google: too many emails sent, with some sort of Log

Google AppScript Log

So I saw: aha, it actually calls the “myFunction” twice. I immediately knew it had to do with how I update the sheet.

I quickly figured there’s a “batchUpdate” function. But documentation was awfully bad. Sheets does know 2 different kind of requests. One kind updates the sheet’s properties (colours/rows, design stuff). The other updates it’s values. BUT they don’t tell that very explicitly. By luck I figured it works like this:

    values = [datetime, last, change, gain, loss, rsi]
    part1 = {
        "range": "Sheet1!A2:F2",

        "values": [
            values
        ]
    }

    values = [["insert into ticker (datetime, last, change,gain,loss) values ( '{}',{:f} ,{:f} ,{:f}, {:f} )".format(
        datetime, last, change, gain, loss, rsi)]]
    part2 = {
        "range": "Sheet1!A5",
        "values": values
    }

    requests = {
        "valueInputOption": "RAW",
        "data": [

            part1, part2

        ]
    }

    sheet.values().batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                               body=requests).execute()

and the important part is the “.values()” part in the last line. I first tried without it and Google told me: don’t know range, don’t know data, don’t find fields and other stuff.

As soon as I added the .values() it worked seamless

Looking forward to your comments

bookmark_borderImage button press impression

You have an image button and want to add a button press impression?

Simple! Trust me: most sites explain this too difficult

<style>
    button {
        border: none;
        background: none;
    }

    button:active {
        transform: translateY(2px);
    }
</style>

<button>
    <image src="button.png"></image>
</button>

bookmark_borderMy impressions about Python

I didn’t (still don’t) like the fact very much that white space is part of Python’s syntax. Unless you have an editor which takes care automatically this can be quite frustrating. In the beginning, before using Visual Code, I used a simple text editor. And, as you may imagine, it was not always clear if tabs or spaces had been used. Python however is very strict on using same white space and same amount of white space when indenting.

I did have my problems referencing other modules in other directories, lost quite some hair getting this right and I still don’t think I’m doing it the perfect way.

Python did it’s job for my Trading Bot quite well. I like that it’s, unlike NodeJS, very much synchronous.

If feels rather lightweight, easy to install additional modules. On macOS however I run, not often, into problems with conflicts between different versions. With a little tweaking here and there (Path in console settings etc) I could get everything to work as I wanted

The biggest strength of Python I guess is handling strings and arrays. You can slice arrays in a multitude of ways.

I couldn’t get my head around creating a pip package though. This was way easier to achieve in NodeJS with npm.

Dealing with date/times and date/time differences is rather a pain, at first at least. Do some tests and you know how it works.

I never understood what I had to give self as first argument in a class method. That it can reference itself? Why not build that into the compiler? You cannot copy a method outside a class and paste it into the class without adjusting the signature.

def test(self):
    print("test")

Another thing I really was impressed with were column and row factories on db objects. You could literally create column_factory or row_factory to retrieve exactly the format from the db you needed. It was a bit complicated to setup my factories properly, but then it was just heaven. No need to “reformat” whole arrays or whatever. Just do some tests to see the results.

conn.row_factory = lambda cursor, row: row[0]  # will return data as a list

Another fantastic thing I’ve never seen in another programming language was __getattr__. This way you can “access” (or prevent access for that matter) properties of an object which are not defined as such, ie do not exist.

def __getattr__(self, name):
    return name.upper() + " does not exist"

Another, sometimes hand option, is to be able to return more than one value from a method. And you don’t need to adjust the signature, as the signature doesn’t define what’s returned.

def test():
    return 1, 2

Python, I think, I only used very basic modules, has a freaking great support for statistics and number crunching. There are different frameworks which achieve different stuff, down to neural learning.

The __del__ method as destructor exists, but I run into circumstances where it wasn’t always called. But if you use the concept of a ContextManager, you can basically achieve something similar, like a tear down (or destruct) method. I think on keyboard interrupts (depending on which ones) __del__ didn’t execute anymore. But by using the ContextManager this worked.

class ContextManager():

    def __enter__(self):
        pass

    def __exit__(self, ex_type, ex_value, ex_traceback):
        logic.tearDown()
        print("exit")

and
with ContextManager() as ct:
    ticker_ws.run_forever(sslopt={'cert_reqs': ssl.CERT_NONE})

In this example when the web socket client (ticker_ws) shuts down, the ContextManager is informed (exit) and you can run a method on your objects.

Some special types have weird names, see above (__exit__ or __enter__ or __name__ or __main__)

Using Visual Code IDE it’s rather easy to setup a debugging environment.

Verdicts

If you run just background tasks, lot’s of string/array editing and/or statistical analysis or other number crunching, then I think Python is quite a valid programming language

bookmark_borderWordPress experiences – Part I

This is continuous work, so check back once in a while.

Why I am into WordPress is a complicated story. Short version: I thought I could, and I did/do, create my own plugin. I setup my own Webserver with 2 wordpress instances and since then I made experiences, mostly frustrating ones. Why? Cause “component” based solutions, mostly, specially if you don’t want to search hours, only do the very minimal stuff the components are supposed to do, you however mostly want something slightly else.

Paginated posts by category

I wanted to be able to display on a page only posts for a certain category. First I asked on stackoverflow. Then I found, sort of, a workaround myself. It’s always workarounds. And in worst case you need to customise it too.

Tell Joe Average to apply proper CSS styles or update the theme accordingly to look it exactly the way you want.

After all the block I found can display a list of posts for a certain query, eg. to only show certain categories. So far so good. This “block” also let’s you do quite some customizing. How many to show, in columns/rows/gallery, which elements (date/title/preview/image) and even a “pagination block”. The pagination blocks let’s you paginate if there are more than the predefined amount of posts to display.

Pagination example

Interestingly I could not find the “Query pagination” block always. This gave me gray hair. First I thought it was related to my wordpress being setup in German, but it wasn’t. I think the whole “Query Loop” block needs be selected when trying to add the “Query pagination” block.

Posts by category in side bar

So I already run into the next problem trying to make my website nice. I would like to display, maybe a paginated list, of just blog titles in a sidebar. There are “side panels” (or side bars) possible in WordPress. But with predefined functionality, and, which is more depressing, they ALWAYS show. I can’t, as of my current knowledge, display different side panels on different pages.

Okay. after a while of doing research I figured: there are plugins which should let you do exactly that!

Installed 3 of them. One didn’t even show the settings pages. As a matter of fact my whole widgets page went white

Another one would let me place a per page/post widget, which is what I want. But it didn’t allow me to “customise” the widget’s. I could only use the default ones.

And the third one I couldn’t get to work. That one was supposed to do exactly what I want: display a side bar with only posts per category.

Still working on getting it to work though. Didn’t get it to work!

But with pur luck I found 2 other plugins which combined do exactly what I want!

The Category Posts Widget plugin let’s me create a widget (for the side bar)where I can chose to show only posts per category.

see the posts per category

The Content Aware Sidebars plugin let’s me create a “content” aware side par (or panel).

the PHP side bar will only display on the PHP page and posts with PHP category

I can create new “sidebars” which follow conditions. I will thus create a sidebar for each page where I want to display a list with posts for the category of that page.

Those sidebars then can be set to “replace” a standard (e.g. footer) sidebar. I can then under widgets add the Category Posts Widget to this newly created sidebar. Done!

Damn… For such a simple thing like 4 hours of research. I’m tired now.

bookmark_borderOther stuff

Improving blogger experience

Almost forgot to mention how I improved blogger experience.
I needed a TOC (Table of contents), such that a reader can quickly jump to the section he's interested in.
Blogger does NOT offer this out of the gate.
 
So I dug, dug deeper and I found 2 solutions which actually create a TOC. But neither was good enough. 
 
One only fetched only level of <H> tags, the other one fetched all but got lost as soon as there's more complicated HTML code between the <H> tags.
 
So I used some parts of both and improved upon it: You paste this in the HTML BEFORE everything:
 
<div id="myToc">
</div>
<hr />
<div id="myContents">
 
 
And you paste this AFTER everything
 
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script type="text/javascript">
countChapters = $("#myContents > h1, #myContents > h2").length
chapters = $("#myContents > h1, #myContents > h2")
flagLevel = false
var toc = ''
for (i = 0; i < countChapters; i++) {
chapter = chapters[i]
chapterTitle = chapters[i].textContent;

 

chapters[i].setAttribute("id", "chapter" + i);
if ('H2' == chapter.tagName) {
if (flagLevel) {
toc += "<li><a href='#chapter" + i + "'>" + chapterTitle + "</a></li>";
} else {
toc += "<ul><li><a href='#chapter" + i + "'>" + chapterTitle + "</a></li>";
}
flagLevel = true
} else {
if (flagLevel) {
toc += "</ul>"
flagLevel = false
}
toc += "<li><a href='#chapter" + i + "'>" + chapterTitle + "</a></li>";
}
}
 
document.getElementById("myToc").innerHTML = toc;
</script>
 
Then it will parse all H1/H2 tags and create a TOC at the top.
By fine-tuning the flagLevel thing you could even have it parse H3 tags.
 
Who's interested: let me know and I enhance the code for you!
 
This technique can actually be used on every HTML page
 
 

Install web server

This is an easy one on macOS, thanks to this
I had home-brew (brew) already installed. So I just needed to follow a few steps.
Apple delivers a built-in apache version, yet the default configuration is a bit not so straight forward. With the approach from above I had the web server running in a few minutes, pointing to my directories.
 

Make (bot) results accessible from afar

Here I needed to think/play a little bit. Years ago there were DynDNS services, for free. Nowadays they charge for their service. What they basically do is provide a hostname which is mapped to your dynamic public IP from your home router.
 
So I tried to build my own "rough" DynDNS solution.
 
I started with https://www.whatismyip.com and https://github.com/cheeriojs/cheerio a tool which lets you parse HTML on the server (and not on the client side). Here's a nice tutorial https://dev.to/diass_le/tutorial-web-scraping-with-nodejs-and-cheerio-2jbh.
 
On the way I learned that jQuery is a thing of the past and one should use React or Vue now, so I did a 30 minutes primer into Vue. Nice, but couldn't understand why/where it's better than jQuery. With Vue you have to "wrap" your tags into "v-" tags in order for it to work. Works well for applications, but it's a pain if you want to do very small things, like things I did with my visual tool to analyse charts.
 
Anyway: I got cheerio to read the contents of whatismyip.com and it took me almost 2 hours to figure, that they would not return my IP, as they realise the request is not from a user, but from a machine.
 
But there's other tools, like this one: http://ip-api.com/json Very straight forward. Returns my IP and that's all I need.
Now what to do with my IP? Store it on google drive? Do they have an API? After a few minutes tinkering I figured: I will use iCloud. Create a document there with a link to my "own" web server. And that's what I did.
Please don't judge me on this code: it's a prototype and does what it needs to do:
 
module.paths.unshift('/Users/michael/NodeJS');
const axios = require("axios").default;

 

url = 'http://ip-api.com/json'

 

const fetchHtml = async url => {
try {
const { data } = await axios.get(url);
return data;
} catch {
console.error(
`ERROR: An error occurred while trying to fetch the URL: ${url}`
);
}
};

 

async function doIt() {

 

var result = await fetchHtml('http://ip-api.com/json')
var path = '/Users/michael/Library/Mobile Documents/com~apple~CloudDocs/HTML/'
var ip = result.query
var fs = require('fs');
fs.writeFileSync(path + 'myServer.html', `</br></br></br></br></br><H1><a href="http://${ip}/heartBeats">myServer</a></H1>`)
fs.writeFileSync(path + 'myTool.html', `</br></br></br></br></br><H1><a href="http://${ip}:3000">myTool</a></H1>`)
}

 

doIt()
 
Important here is the first line. This is required as cron, which will run the script, does not know where my node modules are installed
 
If fetches the IP address from my router (via this URL), stores it in a link in a HTML file in a folder in iCloud where I can access it from anywhere in the world.
 
I only needed to to some port forwarding in my router. Port 80/3000 need to forward to my machine.
 
Interestingly, once I set it up, he, the router software, told me: my Mac will from now on have a static address. How nice!
 
Final piece: I created a a soft link in my web documents folder to the folder where I store my bot heart beats. So now I can call my server like:
 
http://my.ip.address/heartBeats
 
and it will show me the directory listing of my heartBeats so I can check from everywhere if my bots are running.