Geeks on Campus

I may be a little late to the party with having an online file share keeping files in sync between computers.  But now that I’m using it, I love it!  I know there are other options such as Dropbox but since I was already using Google Docs, for documents and spreadsheets, it was seamless to use Google Drive for other files also.  Google Drive is great and I recommend it all the time to people as an MS Office replacement.

I eventually followed Google’s prompting and downloaded Google Drive for my desktop at work and laptop at home.  That’s when the fun started.  I have projects that I work on both at work and at home.  I’ve tried the USB flash drive and uploading the files to a server each time I finished working on them but I would inevitably forget and have version control issues.  With Google Drive for PC I have a folder setup to sync and just save my development files in that folder.  It takes only seconds each time I modify a file for it to automatically upload to Google Drive.  Then when I use the other machine it automatically downloads any files that changed so I’m always working on the latest version.  It even has a little indicator graphic on the file’s icon to show that it has been synced.  This has been a real time saver and helped me to finish projects faster by not needing to wait until I can retrieve the latest version of a file.

P.S.  With smaller files like PHP and HTML changes are synced in just seconds (or less).

I’ve been meaning to write a blog post about unions in PS query for some time now, but I didn’t have a good example to work with until today.  So the scenario is that you want to list out all of the items from record (table) A and the count of their occurrences in record B.  As long as each entry in record A has at least one match in record B that’s fine.  You would just use a standard join and put the COUNT aggregate on a field in record B.  But what to do when there are no matching rows in record B and you don’t want to lose any items from A?

The first idea is to just use a left outer join from A to B and count a field in B.  That works mostly fine except you will get a count of 1 for items that have no rows in record B.  To mitigate the confusion this would cause you can output an additional field from the B record.  That way the additional field from B will only display with the items that have at least one row in B and the others with a count of 1 you will know are really 0.  Given the example of counting the occurrences in STDNT_AWARDS (B) of the item types in ITEM_TYPE_FA (A):

Item    Descr          Count   Year
1234   Pell Grant   1,234   2014
2345  ABC Grant            1
3456  Fed Loan     2,345  2014

You can see that no details from the A  record are lost and the “ABC Grant” has no matching B rows because the year field is blank.

Or you could use a Union

I think a cleaner solution would be to use a Union.  You would create the query first using a standard join and count.  That would result in output such as:

Item    Descr          Count
1234   Pell Grant   1,234
3456  Fed Loan     2,345

Then you would add a Union to the query.  This is done using the “New Union” link at the bottom of the Query Manager page (left of the Return to Search button).  It will look like a new query has been started, but it will say “Working on selection Union 1” at the top.  Select your A record and choose the same fields in the same order as the original query.  * An important point is that the union must have the same number of fields of the same type and in the same order as the main query or you will get an error *  Then create an Expression with a type of number and in the Expression Text box just enter the number 0.  Use this Expression as a field.  Finally add a Does not Exist subquery to the union to only select A rows that have no match in the B record.  Then your output will look like this:

Item    Descr          Count
1234   Pell Grant   1,234
2345  ABC Grant            0
3456  Fed Loan     2,345

*Note that the query will use the field names and sorting specified in the Top Level Query.

That should do it.  Hopefully knowing how to use Unions in PS Query will help you write better queries.

*Update* Do note that adding a Union to your query has the effect of checking the “Distinct” box, you won’t get duplicate output.

If you’ve had the same e-mail address for more than a couple of months than you are probably already receiving a lot of  email.  Some of those emails are useful, but most of them are probably unwanted.  Well, here are four daily emails that I think you should voluntarily sign up for to save some money.

1.  GROUPON

Groupon is one that you probably already know about.  With Groupon you will get a daily e-mail with deals specific to your area.  A typical Groupon deal would be for something like 50% off a round of miniature golf, or dinner and a show.  I like the Groupon emails because they are for discounts on things that I wouldn’t otherwise think to check out.  I’ve saved on things like Laser Tag, paintball, mini-golf, and others.

2.  Best Buy

You are most likely already familiar with Best Buy.  But you may not know about their Deal of the Day.  Best Buy features items that you typically see in their stores and on their website.  It’s not unusual to see CDs for $2.99, DVDs for $4.99, and Xbox games for $8.99.  There are also computes, TVs, and such other items.

3.  Kindle Daily Deals

Kindle Daily Deals features discounts of 50%-80% on Kindle e-books.  I have purchased several good books for my Kindle at $0.99 and $1.99 from these daily deals.  Even if you are not currently looking for something to read, keep an eye on these deals and you may get a good deal on your next e-book.

4.  Woot Daily Digest

The Woot Daily Digest shows what is for sale on all of the Woot sites.  At Woot each site (main, kids, sports, home, etc)  sells one item a day.  The discounts offered make it worth reviewing the digest each day.  The main Woot site often offers electronics or computer equipment.

If you don’t want to sign up for these daily deals as e-mails, I’m pretty sure they are all available on Twitter also.

This may be old news to some of you geeks, but I’m pretty excited to figure out how to do running totals in PeopleSoft Query. And, it’s actually pretty easy! In the following example I’m listing Federal Pell Grant disbursements for the aid year with a running total. The first step is to build your query as normal, select the fields you’d like on the output and filter on whatever criteria is correct.  For this example I’ve selected one item type and one aid year.  Next, select the Expressions tab and click on Add Expression. Don’t forget to check the Aggregate Function check box. Here is my expression:

You can see that you are using the SUM function with the field you want the running total for. Then after OVER specify your ORDER BY fields as you would in any other aggregate. Click OK then the Use as Field link to include it in your output. Your output should looks something like this:

Hopefully you can make good use of running totals in your queries.

Good Luck.

In his manifesto How To Be Legendary author Johnny B. Truant gives “The surefire formula to become Legendary”.  Step 2 is simply “Ship”.  At least for me, when I don’t have a specific deadline, this can be the most challenging step.  When is something totally done?  Could it ever be truly perfect?

Well I’ve taken Johnny’s advice/instruction to heart and shipped my first PHP programming project.  I’ve written a Section 089000 3-Part Louver Specification Writer.  This was a really interesting project for me.  I used some CSS for expanding and collapsing sections that I’d never used before, of course the application is written in PHP which is still new to me, and it outputs a file in Rich Text Format (.rtf).  The syntax of the RTF file may have ended up being the most challenging part.

Just to give you an idea, here is a selection of the RTF file code:

\pard{\*\pn\pnlvlbody\pnf1\pnindent0\pnstart1\pnucltr{\pntxta.}}

This web application may not be terribly interesting if you aren’t an architect needing to specify a louver.  However I still think it’s kind of cool.  The application builds the correctly formatted text based on the options selected then outputs that text in RTF.

I’m sure I could have spent several more weeks adding more features and tweaks.  But I’m glad that decided to ship this web application and will be able to start on another project.

So, tomorrow makes 1 year ago that I graduated.  I already mentioned that I’ve been learning PHP this year.  I’m also trying to teach myself how to play guitar, we finished our basement at home, and I’ve done a bunch of reading.

PHP
In my prior post I linked to the phpacademy site which is the primary resource for learning PHP.  My first PHP project is mostly finished.  It has been a good experience.  I’ve not only learned the PHP language, but also the RTF file specification as the application outputs an RTF file.

Source: Wikipedia - Author: Derek K. Miller

Guitar

Since graduation I decided that I needed a hobby, so I chose to start learning guitar.   My main sources of instruction for this endeavor are Marty Schawtz’s youtube channel and justinguitar.com.  Due to all of the other items on this list, I haven’t had the time to learn and practice that I would like.  I can play a couple of simple songs and chords.  I hope to have much more time for this in 2013.
Basement
One big reason I haven’t done more on the guitar is that I was working on finishing my basement during much of the same time.  This was my first attempt at many of the tasks including laying the carpet on the floor and stairs.  I did a lot of youtube browsing to learn about wiring and electrical outlet and carpeting stairs.

ReadingI started out the year reading the Furies of Calderon which is the first book in the Codex Alera series by Jim Butcher.  It is a fantasy series along the lines of the Lord of the Rings.  If you are not into fantasy, this may not be your cup of tea.  I’m currently reading the 5th book in this 6 book series and have really enjoyed it.  A book I think anyone would really like is Wool (pictured at right).  I specifically read Wool Omnibus Edition (Wool 1 – 5) on my Kindle.  It is a great story and I look forward to reading the prequels and sequels.  I’ve already recommended this book to several people.  They will thank me later, and so will you!

Trello
Finally I want to put a plug in for Trello.  Trello is the new web application we are using here in the office to manage our tasks and projects.  I really like the visual style of Trello and the drag-and-drop interface.  The video below does a good job of explaining how Trello works.  I don’t know why they don’t have it on their web site anymore.

Happy Holidays!

So I’ve decided that it’s time to move beyond ASP classic for web programming.  I am working on teaching myself PHP.  I’ve chosen to learn PHP because that is the language we use for our applications in the office and I find the code fairly easy to read.

I’ve been using Notepad++ as a text editor for several years and find that it is also very good for writing PHP code.  If you set the language as PHP from the beginning it will do contextual highlighting for you which is very nice.  My main resources for leaning the language are the phpacademy and php.net web sites.  The phpacademy site links to over 500 video tutorials on PHP programming.  Each video is fairly short and does a good job of explaining a given function or construct.  I mainly use the php.net site to get the syntax for functions.  The code examples that others leave in the comments can be helpful too.  And of course there is the MySQL reference manual if that is your database of choice.

Here is an example of one of the videos:

http://www.youtube.com/watch?v=gDgbYmIdRuA&feature=share&list=EC960338B143E7F889

I just submitted my first session proposal for a conference!  I submitted a session called “Fast SAP processing with jobsets, 3Cs, service indicators, online appeals, and web services” for the Alliance13 conference this March.  I will be attending the conference regardless of if I am presenting or not.  This will be my second Alliance, having attended the 2011 conference in Denver.  Wish me luck!

I’m working on a project to (pop) update a field in PeopleSoft based on a file from an outside source.  The goofy part is that the file does not have a student ID (not surprising), and the first 5 characters of the SSN are masked (more surprising).  So then how do I do a one-to-one match in order to do the update?  What I’ve come up with had a couple of Excel and PS Query tips that I thought were worth sharing.  The first thing I did was create some new columns in the Excel file that is the source data.  To get just the 4 “good” characters of the SSN, I used the RIGHT function.

Equation:  =RIGHT(B4,4)
Translation:  Give me the last 4 characters from the string (text) in cell B4
Example:  ***-**-1234  = 1234

The name in the source data is listed in this format, Last (comma) (space) first (space) middle initial.  To get just the last name from that string I first used the FIND function to get which character position the “,” is in.

Equation:  =FIND(“,”,C4,1)
Translation:  This looks weird because it is a comma that I’m looking for.  The syntax is FIND then what you are looking for in quotes, what cell to look in, and what character position to start looking from.  So this equation says find the comma in cell C4 and start looking at the first character.
Example:  Student, John A = 8  (the comma is the 8th character in the string (name))

Then I use the LEFT function to get the last name.

Equation:  =LEFT(C4,G4-1)
Translation:  In this example G4 has the results of the Find function.  So this says give me the beginning (Left) characters of the string in cell C4 and stop one character before the number G4 (G4 -1).
Example:  G4 = 8 (see above)  Student, John A = Student (left 7 (8-1) characters in the name)

Next I wanted to use the first three characters in the first name.  I only wanted the first three because of the variability of how first names are recorded.  I used the MID function for this.

Equation:  =MID(C4,G4+2,3)
Translation:   MID is like LEFT and RIGHT except it lets you specify a starting point and an ending point.  This equations says: starting two characters after the comma (remember G4 holds the position of the comma and a space follows the comma, G4+2) give me the next three characters.
Example:  G4 = 8  Student, John A = Joh (three characters starting after the comma and the space)

So, those are fields that I am going to match on in PeopleSoft; the last four of the SSN, the last name, and the first three letters of the first name.

We (thankfully) have a process that allows us to upload a file to PeopleSoft to use in a query.  I uploaded this input file and used some criteria with expressions to do the matching from the file to the PEOPLE_SRCH record to get the student ID.  The last name field is a simple EQUAL TO criteria then I compare the last four of the SSN in the input file to the expression SUBSTR(B.NATIONAL_ID,-4).  The function SUBSTR typically works like the LEFT function in Excel where you specify a field and a number to get the first given number of characters in that text.  However (HOT TIP) if you use a negative number, as I did, it will work like the RIGHT function in Excel and give you the last given number of characters in that text.

For the first name comparison I used the expression SUBSTR(B.FIRST_NAME_SRCH,1,3).  When used with two number parameters SUBSTR works like Excel’s MID, so this expression gets the first (because the first number parameter is 1) three characters of the first name.

I double checked the results and using these three criteria I am able to get the student ID for those in the input file.  While you may never have this same exact situation, hopefully you’ll find parts of this post useful in other projects.

Cheers,
John

Around two months ago a Firefox update caused the browser to stop working well with PeopleSoft.  Sadly the same thing had happened with Chrome not long before that.  IE and Safari both appear to still work well with their current versions.  If you, like me, would like to continue to use Firefox there is a work-around.  You can download Firefox version 3.6 from portableapps.com and use it with PeopleSoft.  Since this is not a current version I would not recommend using the browser with external web sites.  This “portable” 3.6 version will not harm your “current” Firefox version, but they cannot be running at the same time.  I use Firefox 3.6 as my primary PeopleSoft browser, IE as my secondary browser (when logged into two environments at the same time), and Chrome for external sites.

** I just checked the new Firefox 12 and it is the same story of super slow page loads.  A random update broke it and I keep hoping a random update will fix it.

** Update 2:  Make sure you turn off auto-updates in your 3.6 version in order to stay at that version and not get bugged about updates.

** Update 3:  This issue has been fixed for us.  Thank you Casey!  I’m told the issue had to do with SSL ciphers.  PeopleSoft is now working with the current versions of Firefox and Chrome.