Backup Tips

Who hasn't lost data in a hard drive crash or through some other hazard of technology? There are many ways to backup your data these days, and so many reasons (family photos, financial files, etc.) to make sure you have backups in place.Carbonite was recently discussed among my entrepreneur friends as a great online service, although I haven't used it myself.
If you are averse to leasing things that you can own, as I am, you can buy your own device, hook it up to your router, and schedule a nightly backup using any of a number of free programs. It puts the onus on you to manage the backups, but pays for itself in 2-3 years of payments to an online backup service. We have a Buffalo LinkStation drive, and use free GFI Backup software. I recently priced a Buffalo backup device with 1 TB of storage on amazon.com for $139.

Regardless of the route you choose, keep in mind:

• Even the best backup device can go bad. A backup is only a backup if the data exists in two places. Moving your data to the device instead of copying it is no better than leaving it on your hard drive without backing up. (I'm sure that online services mirror/backup their own servers since it is their business, so this is less critical if using a service.)
• The backup is only as good as the setup. If you don't include the right files and directories, you're out of luck. Different applications save data in different places on your hard drive, so if you have questions about this, it is best to have someone technical help with setup.
• Setup email confirmations in your backup software, and read them when you receive them each morning! I see it happen far too often that, when you need a backup, you discover that the job stopped working 3 days/weeks/months ago, and no one noticed. The backup services likely monitor this for you.
• Make sure you know how to recover files so you aren't scrambling when you need them. After setup and periodically from then on, rename a file and restore from backup to make sure that things are working as expected and that you know/remember how to restore. There's nothing worse than being up against a deadline and losing valuable time because of a technical problem. Being comfortable with your backup and restore procedures can help!

Where Does the Time Go?

In the interests of improved time tracking, I recently started using an online application called Cashboard. With the free version, you can have one client a two active projects, and if you want to track your personal time, this is just what you need. For example, in addition to my billable clients and their projects, I have one project called 'Starlight Admin' with tasks like checking email, invoicing, payroll and taxes, and another called 'Volunteer' where I track the time I spend on PTA projects or volunteering for the church. The results are only as valuable as the input is accurate, but you can get a free widget that sits on your desktop and makes it even easier to track time with a few button clicks. And, although I haven't used this feature, you can also track time using your iPhone. If you are using this for your business, it is still fairly inexpensive (starting at $10/month) - and with the paid version, you can put your branding on reports such as invoices, and other notifications and documents.
I am very conscious of providing quality service, and part of that for me is taking pains to give clients value for their money and making sure they don't feel "nickel-and-dimed". If something takes longer than it should because I'm researching or make a mistake, I still choose not to bill for the extra time, but I can show it on the invoice as non-billable time or assign it to an internal non-bill task ~ as opposed to wondering at the end of the week where the hours went.

JQuery Tablesorter - Save Sort Order

I had a request from my client to save the order of the columns on a complex view of data so that when they select a record and go to a new window to edit that record, then come back, they can see the data in the same order as it was without having to re-click the appropriate column headers. I could find bits and pieces of code, but nothing presented as a full solution. So, I've documented my steps here.

Once I had the code working as I wanted, I put the submit() code into a php window. I tried to call the code in the onunload event of the window so that I didn't negate the benefits of using jquery to sort without having to refresh the window each time. However, once I did that, the code executed about 90% of the time - having to do with asynchronous processing of the windows, meaning that while the php code was executing, the next window came up and stopped the code from running. This, even though I included all the right statements in my php code (ignore_user_abort(true); and set_time_limit(0);). I was left with a choice between forcing the user to click a button to save the sort settings or to save behind the scenes every time the user changed the sort order. In the interests of performance, I went with the button, thought not very happily. I also added pagination, which came with a new set of issues in that you can't combine hidden rows with pagination. I will write a follow-up post down the road.

Feel free to comment. I apologize for having to replace the < and > signs with * in order for the text to render properly. I imagine when I redesign my website (coming soon!) and convert to WordPress I'll be able to format this in a way that is more user-friendly.

Setup a table to hold the sort order (1st 3 columns are the primary key; I didn't put that here):

CREATE TABLE Sort_SS (
   pract_id int(11) NOT NULL,
   frm varchar(50) NOT NULL,
   col int(11) NOT NULL default '0', -- the column number, starting w/ 0
   sortfl tinyint(1) NOT NULL default '0', -- 0 for ascending, 1 for descending
   sortorder int(11) NOT NULL default '0' -- the order of column headers selected when multiples
)  

To your form:
Add a hidden variable to the form to hold the javascript array/sortList when the list changes:

*form method=post name="ListView" action="../../includes/saveSort.php"*
  * !--critical to set the value="" to avoid a loop--*
  *input name="arySort" type=hidden value=""*
  * !--I am calling saveSort from multiple forms, so pass a form name--*
   *input name="frm" type=hidden value="SavedSessions"*
*/form*

Add code to the head tag to read the table and create the sortlist:

*?php
   //using a php wrapper found here:
   //http://www.ricocheting.com/scripts/php_mysql_wrapper.php
   $sql="SELECT col, sortfl from Sort_SS where pract_id = $pract_id and frm='SavedSessions' order by sortorder";
   $rows = $db->;fetch_all_array($sql);
   $cols = array();
   $cols['col']=array();
   $cols['sortfl']=array();
   foreach($rows as $record){
      array_push($cols['col'], $record['col']);
      array_push($cols['sortfl'], $record['sortfl']);
   }
?*

*script type="text/javascript" id="js"*
   var cols = new Array();
   *?
      for ($i=0; $i< count($cols['col']); $i++) {
         echo 'cols['.$i.']= ['. $cols['col'][$i] .','. $cols['sortfl'][$i] .'];';
      }
   ?*
   $(document).ready(function() {
      $("#sessions").tablesorter({
         sortList:cols
      });
   });

To jquery.tablesorter.js - add at the start of the function multisort:

function multisort(table,sortList,cache) {
   //populate the form variable for use in the submit - checking for > "" is
   //CRITICAL and keeps it from submitting when the form first loads and getting into a loop
   if(document.ListView.arySort.value > ""){
      document.ListView.arySort.value = sortList;
      document.ListView.submit();
      return;
   }else{
   document.ListView.arySort.value = sortList;
}
//then the rest of the function follows as is...
if(table.config.debug) { var sortTime = new Date(); }}

And at the start of the function appendToTable:

function appendToTable(table,cache) {
//if you submit and return from the multisort, you might end up here and error w/out this stmt
if(!cache){return;}
//then the rest of the function follows as is...
if(table.config.debug) {var appendTime = new Date()}

Finally, here is my saveSort.php file:

*?php
   session_start();
   if(!session_is_registered(myusername)){
      header("location:http://myurl/Login/");
   }
   require("./config.inc.php");
   require("./Database.class.php");
   $db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
   $db->connect();
   //Makes Session Variables available to this page
   require "./common_variables.php";
   //Retrieve the hidden form variable (using PHP).
   $sort = $_POST['arySort'];
   $frm = $_POST['frm'];
   //clear out existing records for the practitioner
   $sql = "DELETE FROM Sort_SS WHERE pract_id=$pract_id and frm='$frm'";
   $db->query($sql);
   $aryChars = array("[","]");
   //get rid of the brackets so is comma delimited list
   $sort = str_replace($aryChars, "", $sort);
   //then break out the list
   $arySort = explode(",",$sort);
   $col = '';
   $sortorder = 0;
   //preset these static values for the insert statement
   $data['pract_id']=$pract_id;
   $data['frm']=$frm;
   //loop through the array and get the values to add
   foreach ($arySort as $value) {
      if(is_numeric($value)){
         if($col==''){
            $col = $value;
         }else{
            $data['col']=$col;
            $data['sortfl']=$value;
            $data['sortorder']=$sortorder++;
            $col = '';
            $db->query_insert('Sort_SS', $data);
         }
      }
   }
?*

Dynamics GP Series Posting Issue

I recently had a sticky issue with a client's posting in a particular company. Sometimes and for some users the batches would post through the GL; other times they would not. After checking the obvious setup for series posting and making sure everything is set to post through the GL, I found a couple of KB articles that are relevant. Article 1 didn't solve our issue, so I dug deeper and found the second article.

One note on using the 'ALL' option for the series posting - it isn't very self-explanatory but when you click apply, it rolls down the options selected to the postings for that series and then resets to the default options. By default, it posts at the transaction level and not the batch level. In general, I would skip the 'ALL' option and look at the individual types of batches for the series.

Article 1: There are a few General Ledger windows that the user will need to have access to in order to post through the GL successfully from the subsidiary module. Access to these windows can be given at Setup >> System >> Security. Choose your User ID, Company, Product of eEnterprise or Dynamics, Type of Windows and the Series of Financial. Give the user access to the following: Batch ID, Batch Entry, and GL Open Files. Setting security on these windows only for GL is the minimum security for a user to be able to post series through the GL.

Article 2: Note: Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.
SYMPTOMS: Consider the following scenario. You post transactions from a submodule. The posting is set up to post to and to post through General Ledger in Microsoft Dynamics GP. In this scenario, General Ledger is not updated by the transactions. Note: The submodule batches are available in batch entry. Additionally, the batches can be posted, and they will update General Ledger.

CAUSE: This problem occurs if the Posting Definition Master table (SY00500) or the procedures that are associated with this table are damaged or corrupted.

RESOLUTION: To resolve this problem, re-create the Posting Definition Master table. To do this, follow these steps.

1. Make sure that no other users are logged on to Microsoft Dynamics GP.
2. Post all the batches that you can in Microsoft Dynamics GP.
3. In Microsoft Dynamics GP 9.0 and in earlier versions, point to Maintenance on the File menu, and then click SQL.
4. In the Database list, click the company database, and then click Posting Definitions Master.
5. Click to select the following check boxes: Drop Table, Create Table, Drop Auto Procedure, Create Auto Procedure
6. Click Process. Note: The Posting Definition Master table contains information about all unposted batches in the company except for Payroll. After you drop and then re-create the Posting Definition Master files, you cannot see any one of your batches in the company until you complete the remaining steps.
7. Perform the check links process for the files in the corresponding modules. To run the check links process, in Microsoft Dynamics GP 9.0 and in earlier versions, point to Maintenance on the File menu, and then click Check Links.
8. Click the appropriate module file, and then click OK. Use the following list to determine the module files:
   
   -- Payables Management: Payables Transaction Logical in the Purchasing series Note: If you use Multicurrency for Payables Management, you must run the check links process on the Payables Transaction History Logical file before you run the check links process on the Payables Transaction Logical file.
   -- Purchase Order Processing: Purchasing Transaction Logical in the Purchasing series
   -- Receivables Management: Receivables Open Transaction in the Sales series
   -- Invoicing: Invoice Work File in the Sales series
   -- Sales Order Processing: Sales Work File in the Sales series
   -- Inventory: Inventory Transaction Work file in the Inventory series
   -- Bill of Materials: Bill of Materials Transactions file in the Inventory series
9. If you use the Project series, you must run the check links process for this module based on the transaction types that are used in the existing batches. To do this, in Microsoft Dynamics GP 9.0 and in earlier versions, point to Maintenance on the File menu, and then click PA Check Links. 
10. 
    
    Click the appropriate file, and then click OK. Use the following list to determine the transaction files:
    

    -- Timesheet Batches: PA Timesheet Transactions

    -- Asset Log Batches: PA Asset Log Transactions

    -- Expense Log Batches: PA Expense Log Transactions

    -- Inventory Transfer Batches: PA Inventory Transfer Transactions

    -- Purchase Order Batches: PA Purchase Order Transactions

    -- Vendor Invoice Batches: PA Vendor Invoice Transactions

    -- Employee Expense Batches: PA Employee Expense Transactions

    -- Billing Batches: PA Billing Time & Materials Transactions
11. If any batches exist in General Ledger, reconcile the batches in the Reconcile Financial Information window. To do this, in Microsoft Dynamics GP 9.0 and in earlier versions, point to Utilities on the Tools menu, point to Financial, and then click Reconcile.
12. If transactions exist in the Quick Journal Entry window, open the transactions in the Quick Journal Entry window, and then save the transactions again.
13. If you have batches in Inventory, you must click to select the Post To GL check box for these batches, and then repost the batches to General Ledger.

 Notes:

• Batches will be available in the company after you run the check links process and then reconcile the batches.
• This process will set any recurring batches to single use. You must change the frequency of any recurring batches in the Batch Entry window before you post the batch. If you post to General Ledger from another module by using the batch date as the posting date, the posting date for batches in all modules are set to the user date. If this date is incorrect, you must change the date in the Batch Entry window.