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 002 1.00 2000-02-18 002 1.00 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...

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

Excel: Find values in one column that are not in anotherhttp://mail.joellipman.com/articles/microsoft/excel/excel-find-values-in-one-column-that-are-not-in-another.html

06 March 2014 and now that I have a new list, I want a quick way to see what values are in the old column and which ones are new... Why? Consider the 3 following columns in an Excel spreadsheet: Old New Exists in Old? --------- --------- -------------- 123456 234567...

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

ReCaptcha alternative: Integrate PlayThru in JComments 2.3 for Joomla 2.5http://mail.joellipman.com/articles/cms/joomla/recaptcha-alternative-integrate-playthru-in-jcomments-for-joomla.html

24 March 2014 used by the jComments extension is easily automated and no longer blocks spam comments. How? So I have come up with what I consider a pretty stable solution. I adapted it from various legacy solutions when using Google's ReCaptcha. Note: There is a...

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

ReCaptcha alternative: Integrate PlayThru in JComments 3.0 for Joomla 3.2http://mail.joellipman.com/articles/cms/joomla/recaptcha-alternative-integrate-playthru-in-jcomments-3-0-for-joomla-3-2.html

27 April 2014 ReCaptcha is overrun by click farms and is unable to authenticate engaging visitors. How? So I have come up with what I consider a pretty stable solution. I adapted it from various legacy solutions when using Google's ReCaptcha. Note: There is a Joomla...

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

Basic Android App using Google Maps and Current Locationhttp://mail.joellipman.com/articles/google/androidos/basic-android-app-using-google-maps-and-current-location.html

10 October 2014 mMap.addMarker(new MarkerOptions().position(new LatLng(latitude, longitude)).title("You are here!").snippet("Consider yourself located")); } If you are prompted to import the classes then agree by clicking OK: If you are not prompted, you should see...

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

Migrating from Joomla 2.5.x to 3.4.xhttp://mail.joellipman.com/articles/cms/joomla/migrating-from-joomla-2-5-x-to-3-4-x.html

12 September 2015 to document the migration of a Joomla! CMS running version 2.5.11 to an instance of Joomla! CMS version 3.4.1. I would consider the below instructions an ALPHA version as it is considered unstable for official use... Saying that this is how I upgraded...

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

Foreign Characters create symbols in PHP and MySQLhttp://mail.joellipman.com/articles/web-development/php/foreign-characters-create-symbols-in-php-and-mysql.html

25 January 2016 = html_entity_decode(htmlentities($my_description, ENT_COMPAT,'UTF-8', true), ENT_COMPAT, 'UTF-8') Other things to consider: Save all PHP files as UTF-8 and not ANSI Ensure all text fields in the database are set to a UTF-8 charset (eg. utf8_general_ci)...

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

Zoho Templates - Font size is inexplicably tinyhttp://mail.joellipman.com/articles/crm/zoho/zoho-templates-font-size-is-inexplicably-tiny.html

22 February 2019 but these were red herrings as I have other templates that work fine. How? The error is one of those that you wouldn't consider relevant in this case, after all, all styling was removed... Here are two screenshots to demonstrate the issue: Screenshot...

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

Zoho Deluge - First Monday of Month or Last Tuesday of Monthhttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-first-monday-of-month-or-last-tuesday-of-month.html

27 September 2019 the month and that Tuesday happens before today, then we want the last Tuesday of the next month. In the following code, consider that the date field we want to set is called "Switch_Over_Day". For the first Monday, we are going to select the first day...

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

Zoho Creator: Retrieve record with case-insensitive queryhttp://mail.joellipman.com/articles/crm/zoho/zoho-creator-retrieve-record-with-case-insensitive.html

21 November 2019 Thought I'd put an article here to remind me how to make the retrieval of a record case-insensitive. Why? Consider that I have the following creator table: Product_Name Product_SKU -------------- ------------- MyProduct1 TEST01 Myproduct2 TEST02...

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

Zoho CRM/Creator - Common Errors & Gotchashttp://mail.joellipman.com/articles/crm/zoho/zoho-crm-creator-common-errors-gotchas.html

06 February 2020 app does not match the redirect used in the authorization request. They need to match in both irrespective of the value. Consider trying to get authorized and checking the Redirect URI passed in the URL and then updating your app with the same value,...

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

Zoho Deluge: Associate/Link an Invoice to a Sales Orderhttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-associate-link-an-invoice-to-a-sales-order.html

10 March 2020 info r_Update; Update 2023 If the above doesn't work, consider making an API call to the following endpoint: m_Params = Map(); m_Params.put("invoice_ids", ); m_Params.put("organization_id", ); r_Associate = invokeUrl [ url:...

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

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 // or v_UnixSeconds = v_MyDate.unixEpoch(); info v_UnixSeconds; To normalize +0530 to include the colon +05:30, consider the following: // Normalize to +HH:MM offset = "+0530"; offset = offset.replaceAll("([+-]\d{2})(:?)(\d{2})", "$1:$3", false); Error:...

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

Zoho Deluge: Search Records with Special Characters (COQL)http://mail.joellipman.com/articles/crm/zoho/zoho-deluge-search-records-with-special-characters-coql.html

28 August 2020 the part that took me about 8 hours and the key point above is to replace the ampersand with its unicode equivalent. I consider myself familiar with Transact-SQL, Oracle PL/SQL and MySQL all of which have various ways of escaping the ampersand (except...

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

ZohoCRM: Process all records of a modulehttp://mail.joellipman.com/articles/crm/zoho/zohocrm-process-all-records-of-a-module.html

24 August 2021 and delete the "Processed" field. [Optional conditionally] Reactivate any workflows you deactivated in step 1. Sample Code Consider the following standalone function which loops through every contact and deletes the duplicate lead (if a lead exists with...

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

Zoho Deluge: Date from a String (dd/MM/yyyy to yyyy-MM-dd)http://mail.joellipman.com/articles/crm/zoho/zoho-deluge-date-from-a-string-dd-mm-yyyy-to-yyyy-mm-dd.html

01 September 2021 a date into a SQL format or from largest denominator to smallest in "yyyy-MM-dd" will usually handle the date better. Consider the following: v_TestDate = "07/01/1975"; info v_TestDate; info v_TestDate.toDate(); info v_TestDate.toString("E, d MMM...

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

Zoho Creator: Prevent Endless Loops On User Input of a Fieldhttp://mail.joellipman.com/articles/crm/zoho/zoho-creator-prevent-endless-loops-on-user-input-of-a-field.html

19 September 2021 // // now compare to stored eTag if(v_eTag != input.eTag) { input.eTag = v_eTag; // do the main code that takes into consideration all the fields triggering a change //... } An extra layer: To go one step further, and only if you notice the form going...

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

Zoho CRM: Get Unique Values of a Text Fieldhttp://mail.joellipman.com/articles/crm/zoho/zoho-crm-get-unique-values-of-a-field.html

08 October 2021 ]; // // output info r_Coql; Additional If you wanted the unique values of a picklist/dropdown, then consider an invokeUrl looping through the fields settings in CRM. Source(s): Zoho CRM Developer Docs API v2 - Get Records through a COQL Query...

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

Zoho Creator/Deluge: UnPredictable exception, Invalid statement foundhttp://mail.joellipman.com/articles/crm/zoho/zoho-creator-deluge-unpredictable-exception,-invalid-statement-found.html

17 November 2021 other reasons but I will add to this article if I encounter any. This is simply how I fixed this error on this occasion. Consider the following code which loops through some Creator records, then loops through one of the subforms in each record: //...

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

ZohoCRM & Xero Real-Time Invoices: Receive Webhookhttp://mail.joellipman.com/articles/crm/zoho/zohocrm-xero-real-time-invoices-receive-webhook.html

10 January 2022 server detailed further in this article. Solution #2: Using Third-Party Server If this doesn't work, you will need to consider using a third-party server (my example here is a LAMP server): Complete webhook setup by passing 'Intent to receive' required...

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