T-SQL Record Separatorhttp://mail.joellipman.com/articles/database/t-sql/t-sql-record-separator.html

11 October 2011 [pretendField]='elephantastic' -- insert some empty rows to blank out later as separators (select from your existing table, needs to contain data to order by so that it's the last row of each day -- if grouping by day -- basically insert a row that when...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

MySQL last year week month day trend periodshttp://mail.joellipman.com/articles/database/mysql/mysql-last-year-week-month-day-trend.html

31 October 2011 for the past day, week, month and year (the below examples count all accounts irrespective of being activated or not). It needs to pick up trends as well and compare for example todays, with yesterdays up to the same hour. How? I used to use a lot more...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: en-GB

T-SQL example of Case-Sensitive Soundexhttp://mail.joellipman.com/articles/database/t-sql/t-sql-example-of-case-sensitive-soundex.html

10 October 2012 identified the remaining values as having the same SOUNDEX value. Adding the COLLATE option straight after the column that needs to be case-sensitive returned the correct results: SELECT DISTINCT StudentDetail COLLATE Latin1_General_CS_AS FROM...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Battery Constantly Drained on iPhonehttp://mail.joellipman.com/articles/apple/battery-constantly-drained-on-iphone.html

27 November 2012 empty arrow next to the battery indicator tells you that location services is enabled but it is attached to a reminder that needs to constantly monitor your whereabouts in order to "remind you". The reminder here was to charge the phone but it was the...

• Type: Article
• Author: Joel Lipman
• Category: Apple
• Language: *

Error 1065: Query was Emptyhttp://mail.joellipman.com/articles/database/mysql/error-1065-query-was-empty.html

13 December 2012 all required values to escape have been escaped. In all my cases this has been the percent sign (%) but I'm listing what needs to be escaped in SQL for future use: Character Description \0 An ASCII NUL (0x00) character. \' A single quote (“'”)...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

Improve Default Joomla Search http://mail.joellipman.com/articles/cms/joomla/improve-default-joomla-search-heuristics.html

07 May 2013 the following to the core joomla search module PHP template so that the first search is based on the above heuristics: This needs to be put alongside the other hidden variables in the module, core joomla file is at /modules/mod_search/tmpl/default.php...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

T-SQL Conversion failed when converting the varchar to data type inthttp://mail.joellipman.com/articles/database/t-sql/t-sql-conversion-failed-when-converting-the-varchar-to-data-type-int.html

14 May 2013 84 3 7749 63 4 B110 92 -- so we have 1 director, 2 managers, and 3 tape-monkeys Now suppose we had a convoluted query which needs to join the two tables so that we get the "MapOut" value: SELECT r1.MapIn ( SELECT (COUNT(e2.EmpType)-1) AS MyCount FROM...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Split a row into multiple rows based on a column valuehttp://mail.joellipman.com/articles/database/t-sql/split-a-row-into-multiple-rows-based-on-a-column-value.html

23 August 2013 2000-02-19 002 1.00 2000-02-20 003 1.00 1999-02-25 003 1.00 1999-02-26 Some thing(s) to consider: User running the SQL query needs permission to read the master database. If run by a service account, ask a DBA to emulate the user for testing. the Items...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

Creating a custom form field type for Joomla XML fieldsethttp://mail.joellipman.com/articles/cms/joomla/creating-a-custom-form-field-type-for-joomla-xml-fieldset.html

25 October 2013 selectable options restricted to the logged-in user. This is specified in the XML file of the custom Joomla component and needs some SQL dependent on some dynamic variables. Why? In Joomla, the XML type of "sql" is extremely limited. The component I'm...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: en-GB

Client Services: Website Development Agreementhttp://mail.joellipman.com/static-items/client-services-website-development-agreement.html

15 November 2013 need to be agreed: Parallel running with legacy systems – testing and confirmation that both systems are operational, data needs to be entered in both systems. (this is only necessary if this is an upgrade or replacement of an existing system). Warranty...

• Type: Article
• Author: Joel Lipman
• Category: Static Items
• Language: *

Generate a Timesheet in MySQL http://mail.joellipman.com/articles/database/mysql/generate-a-timesheet-in-mysql.html

05 December 2013 I also worked on creating a template for the site I just made; I started at 9:45 and finished at 12:00") The final system needs to specify every day of the week and divide all the days activities into the morning and afternoon slots. All days and time...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *

SSIS Multiple Lookups in onehttp://mail.joellipman.com/articles/microsoft/ssis/ssis-multiple-lookups-in-one.html

05 August 2014 Our transform package will convert the data and output it to the target system. For our package to decode what "M" means, it needs to look this up in a table on the original server. We had around 12 lookups to do, so you could do this: But we didn't...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

Google Authentication - OAuth 2.0 using PHP/cURLhttp://mail.joellipman.com/articles/google/google-authentication-oauth-2-0-using-php-curl.html

31 October 2018 *.apps.googleusercontent.com $CLIENT_SECRET = ''; // expecting alphanumeric string $STORE_PATH = ''; // expecting *.json - needs to be writeable by system account $SCOPES = array($GAPIS_AUTH . 'userinfo.email', $GAPIS_AUTH . 'userinfo.profile'); // add...

• Type: Article
• Author: Joel Lipman
• Category: Google
• Language: en-GB

Google Drive API v3 - OAuth2 using Service Account in PHP/JWThttp://mail.joellipman.com/articles/google/google-drive-oauth-using-service-account-in-php.html

02 February 2019 on your server (JSON downloadable from Google) $api['keys']['private']['file'] = '.json'; // Location to store access token (needs be writeable) $api['jwt']['token']['file'] = '/access_token.dat'; 2b. Specify the impersonator Now for testing purposes...

• Type: Article
• Author: Joel Lipman
• Category: Google
• Language: en-GB

Convert Past Date to Time Ago in PHPhttp://mail.joellipman.com/articles/web-development/php/convert-past-date-to-time-ago-in-php.html

16 May 2019 know there are so many examples out there that do this perhaps better but this one I understand and can customize to fit my needs. This PHP function accepts as parameter a SQL date (or date format that strtotime() can convert) and outputs the largest...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

Zoho Deluge: Convert Xero Date (Unix Timestamp) to Standard Date Stringhttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-convert-xero-date-unix-timestamp-to-standard-date-string.html

04 May 2020 reusable, and also need to account for when timezone is not included in the Xero date/time: // REMINDER: Zoho Deluge Tryout needs backslashes to be escaped, CRM regex does not // if timezone is included (can be negative or positive): // deluge parse and...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho Creator: Two submit buttons on a non-stateless form with 2 different redirectshttp://mail.joellipman.com/articles/crm/zoho/zoho-creator-two-submit-buttons-on-a-non-stateless-form.html

27 July 2021 = insert into [ = = = ]; } Done: Additional Remember that an integration field on the interface is returned as a string and needs to be converted to a number to be assigned, eg: r_Details = myForm[ID == 1234567980123456789]; r_Details.Account =...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho CRM & Zoho Books: Custom Related Lists Delugehttp://mail.joellipman.com/articles/crm/zoho/zoho-crm-zoho-books-custom-related-lists-deluge.html

30 July 2021 either Zoho Books or Zoho CRM. For demonstration purposes, these have been simplified and will need to be adapted for your needs. Note that I have used 2 URL methods of linking to the Creator app and some data formatting that I tend to use regularly for...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Xero API: Send Multiple Invoiceshttp://mail.joellipman.com/articles/crm/xero/xero-send-multiple-invoices.html

15 August 2021 in API call to Xero... apparently it can accept up to 60 invoices in one call. How? Crazy simple solution, your JSON needs to have the key "Invoices" and the list of invoices to create: {"Invoices":[... list of invoices...]} If sending just the 1: {......

• Type: Article
• Author: Joel Lipman
• Category: Xero
• Language: *

Zoho CRM: Permission Denied for Quote Conversion Mappinghttp://mail.joellipman.com/articles/crm/zoho/zoho-crm-permission-denied-for-quote-conversion-mapping.html

02 September 2021 using ZohoBooks), we disabled the CRM Invoice module. How? Therein lies the reason. For the quote conversion mapping, it needs the CRM Invoice module as well. Step by step fix: Login to ZohoCRM, go to Setup Under "Customization", select "Modules and...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *