Month: January 2012

How do you easily add 1 to EA00000032?

Marky Roden Productive or Lazy?

We have a primary key in the Oracle table which is of the format EA00000032 and I need to increase it by one every time I insert a new row.

I could:

  • remove the EA
  • convert 00000032 into an integer (32)
  • Add 1 (33)
  • convert to string
  • Add 6 zeros back

And this is all well and good but when you roll from 99 to 100 or 999 to 1000 you have to compute the length of the string and add the appropriate number of zeros to make it back up to the 8 characters

  • WAY too many steps

So here’s a much more efficient way of doing it avoiding the need to compute the length of 32, 99 or whatever

  • Start with a billion (100000000)
  • add everything to the right of the EA +1 –> 00000099 +1 (100000100)
  • EA +the back 8 characters of the new number


sEXP_AUTH_ID=”EA00000099″
sEXP_AUTH_ID=”EA”+Right(CStr(100000000+Clng(StrRight(sEXP_AUTH_ID, “EA”))+1), 8)

Productive or Lazy?

How to reference a table in a powerpoint slide using VBA

Marky Roden PowerPoint, VBA

I needed to find out how to populate a table cell in a PowerPoint using VBA.

As in the example on creating PowerPoint files I posted I used a macro
recorder to figure out the VBA and then used Google to find someone
else who converted the code before 🙂

<script language=vbScript>
Sub CreatePPT()
    Set wApp = CreateObject(“PowerPoint.Application”)
    wApp.visible=true

    pptLoc=document.getElementById(“pptLoc”).value
    set objPres = wApp.presentations.open(pptLoc)  

    //Where The name of the Table is “Table 7”

    objPres.Slides.Item(1).Shapes(“Table 7”).Table.Cell(2,2).Shape.TextFrame.TextRange.Text = “10/20/03”

End Sub

</script>

Using jQuery Selectors in XPages

Marky Roden jQuery, XPages

The Problem

There was a question asked on the XPages development forum yesterday asking why a jQuery selector will not find an element.

When you add an editable field to the XPage this is the source code

<xp:inputText id=”inputText1“></xp:inputText>

so you would naturally think the selector for that would be

$(“#inputText1“)

and you would be wrong…….in XPages.

The XPages part of the Domino server creates a dynamic id on the fly and the actual HTML generated for the field is:

<input type=”text” id=”view:_id1:inputText1″ name=”view:_id1:inputText1″>

so then the selector would be

$(“#view:_id1:inputText1“)

and you would be wrong again

No colons!!

jQuery selectors do not understand id’s with colons (:) in them because jQuery uses the colon for a number of  things including the CSS3 standard for selecting nodes.

The Answer

Well there are a number of ways you can work this:

Use a class selector instead:

Domino does not re-name class names on the fly, so as long as you use unique class names you can get away with…….

<xp:inputText id=”inputText1″ styleClass=”c_inputText1“></xp:inputText>

creates the following HTML

<input id=”view:_id1:inputText1” class=”c_inputText1” type=”text” name=”view:_id1:inputText1” />

And then the selector is simple

$(“.c_inputText1“)

Use a wildcard

Using a wildcard selector will allow you to hit the expected id because XPages prepends its necessary information so you can still look for inputText1. Be careful though if you fields are called 1InputText1, 2InputText1 etc then this selector will catch ALL matches

$(“*[id$=inputText1‘]”)

Using Client Side Javascript (SSJS) to Dynamically obtain the fieldname

You can get the dynamically created fieldname using “#{id:inputText1}”

The CSJS alert(“#{id:inputText1}”;

creates the following JavaScript in the XPage automagically

alert(“view:_id1:inputText1“);

But as we previous saw we cannot use a selector for an id with a colon in it. We can overcome this by “escaping” the colon character and making jQuery use the who string using a “\\”

This will not work $(“#view:_id1:inputText1“)

But this will $(“#view\\:_id1\\:inputText1“)

So I wrote the following function to allow conversion of the id: to something usable by jQuery

function x$(idTag){
  //replace the colon
  idTag=idTag.replace(/:/gi, “\\:”)
  return idTag;
}

which is utilized in this manner:

$(“#”+x$(“#{id:inputText1}”)).css(‘border’, ‘3px solid yellow’);

I would like to try and simplify this further and wrap the jQuery object. A future project maybe.  x$(“#”+{id:inputText1})

Sample Database

A sample database is attached here….wordpress does not like zip files so open the word document and the zip file is in there……

jQuerySelectorDatabase

Update 02/13/2012

Check out the simplified function jQuery selector function for xPages – x$(“#{id:inputText1}”)

Problem solved – I can’t submit my timesheet

Marky Roden Problem solved

Problem: I cannot submit my timesheet

At my company we use Deltek’s suite of CRM tools including Time and Expense. We have been getting help desk calls for users being unable to submit their time sheets. They have been intermittent and we have been unable to spot a pattern in browser usage or user type. I was assigned to the problem yesterday and this morning the helpdesk started lighting up with the issue.

Submitting my time sheet through Firefox did not work, but it worked using Internet Explorer. So that immediately discounts the possible cause of being 1) application is broken 2) network is causing the issue.

I do not have administrative access to the machine so there was only a limited number of things I could do from my desktop – So I loaded up my trusty HTTPFox plugin for Firefox to see if there was any issues with the POST or response. This is what I got back from the server when I hit Save…

<TITLE>ISAPI plug-in Error Message</TITLE><H2>Message from the ISAPI plugin:</H2><P>
Cannot write 2068 bytes to tmp file C:\WINDOWS\TEMP\_wl_proxy\_post_2656_119: GetLastError() 112
<HR><I>Build date/time: Apr 20 2009<I>
<P><HR><B>Change Number:</B> <I>1211636<BR>

This was sent to the Deltek helpdesk and their response was “It looks like a permission problem. I have encountered this issue before and I assigned full permission for all users on the c:\windows\temp\_wl_proxy directory of the IIS server.“

So immediately I responded and said – nope it cannot be a permissions issue because that would not explain it being an intermittent problem. That is a scary solution to the problem and would merely mask any underlying issues if something was causing an authentication issue….

So what else would cause an inability to write to the server?

Is the disk full?

Answer: Yep – the C drive is full to capacity 

That makes complete sense because:

  • It is intermittent because Windows is trying to recycle temp space but cannot do it fast enough
  • It is worse in the morning because there are more people trying to hit it

 

Conclusion: Gather all the information possible before attempting to solve the problem and don’t always assume that the obvious answer is the correct one .

 

Learning to think…..

Marky Roden Just Marky, Thinking process

My Ph.D did many things, including increasing my alcohol consumption capacity, but the life lesson which has served me to this day, is teaching myself how to solve problems/think.

The stated problem in research chemistry is simple – I have a target chemical I want to make from my starting material – so how do I go from here to there? Being an experimental air-sensitive chemist, with a finite amount of starting material (which took you 3 weeks to prepare) means that you don’t have any room for mistakes.

So you plan – When you have an experiment which is going to have 6 stages and take weeks to perform, you don’t want to screw it up……so you:

  • Do your research
    • Has anyone done something similar?
    • When you are writing up your research paper you will need references to prove you checked?
    • Has anyone created a cool technique which will help make you more efficient?
    • How do I break that bond and add that chemical?
    • etc
  • You process the information and hypothesis on solutions to the problem
  • Write up your plan and discuss with your boss (presenting your work, hypotheses and predictions)
  • Make sure all materials are available
  • Collaborate with your peers and see if they think you are being stupid or realistic
  • Execute the experiment
  • Document to procedure
  • Document the results (Write a paper for your peers to use in their experiments later)

It is not that different from being a programmer and it forced me to become a lateral thinker.

There are many problem solving strategies but my favorite is TRIZ

Solving problems is what gets me out of bed in a morning and it forces me to learn which puts me to sleep happy.

Automated PowerPoint slide creation

Marky Roden PowerPoint, VBA

Something which has bugged me for years is automation of PowerPoint creation. Unlike Word and Excel which can be created with HTML. PowerPoint just sucks when it comes to formatting. This week I completed my 6 years journey on repeatedly trying to automate the creation of PowerPoint slides.

VBA has always been something I have liked to play with because it generates a LOT of functionality customers expect from their applications. I have used the user’s locally installed MS Word to function as a spell checker. I have used Excel through the web and MS Excel client to create graphs and pivot tables.

I have a customer who semi-annually inputs all their project information into a Notes database. By entering into into a web based application it exposes the information to other internal customers who can then edit it for accuracy.

This amounts to around 150 projects and each of these projects has to be briefed at least once to the senior leadership using a standard briefing template. This is currently a copy and paste job and wastes a significant amount of man hours.

My problem – automate the brief creation from the information in the database. I tried the following.
Knowing that Office files can be saved as HTML files; I tried to save the brief as HTML and then try to create the HTML using a LS agent. Unfortunately when PowerPoint saves as a web page it generates a multude of files including macro files. Creating all these files is not practical, time consuming on the server and all round.

Using Office 2003 you can record a Macro. Which allows me to record a VBA version of what I do to the template. I tried to build a template from scratch using the macro recorder. This provided the means but generated a significant amount of VBA code which would take me eons to build the 20+ pages necessary and then I would still have to convert to something usable.

Finally I basically compromised and came up with a solution which does require a modicum of interaction from the user – but still saves them hours of work. The user has to download the template to their machine, using a File Upload Control tell me where it is, and I will load it using vbScript, and fill it from the database. This is not total automation but sometimes an 80% solution is a whole lot better than 0% !

Here is an example article