T-SQL concatenate an incremental row numberhttps://www.joellipman.com/articles/database/t-sql/t-sql-concatenate-an-incremental-row-number.html

24 May 2013 The output expected by the customer is the following: EMP -------------------------------------------------- EmpNumber QualificationRef 001 00101 001 00102 002 00201 -- Qualification Ref is a unique number using the -- Employee Number concatenated with...

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

Zoho Deluge: Text (SMS) reminder using Twilio API (LocalSense)https://www.joellipman.com/articles/crm/zoho/zoho-deluge-text-sms-using-twilio-api-localsense.html

16 December 2021 a text message using Twilio API in Zoho Deluge. Why? The use-case here is that we want local sense dialing where specific numbers are used both for outbound and inbound but local to the customer and we want to remind our customer that they have an...

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

Zoho Recruit: Rollup Number of Recruit Candidates to CRM Accounthttps://www.joellipman.com/articles/crm/zoho/zoho-recruit-rollup-number-of-recruit-candidates-to-crm-account.html

19 October 2022 on a workflow when a Candidate is modified, and it tallies all the candidates belonging to the same school and updates some number fields on the matched CRM account. Why? For reporting purposes. Although Zoho Analytics can use data imported from both...

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

MS Excel: Convert a text to a numberhttps://www.joellipman.com/articles/microsoft/excel/ms-excel-convert-a-text-to-a-number.html

15 November 2013 This is a quick article on how to convert some cells in Microsoft Excel to number values... Why? OMG. Seriously Microsoft! I have spent an hour trying to convert a column of currency values to a number using Microsoft Excel 2010. Since when did MS Excel...

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

Autohotkey Count Number of Files/Folders in a Directoryhttps://www.joellipman.com/articles/automation/autohotkey/autohotkey-count-number-of-files-folders-in-a-directory.html

04 June 2013 So this is an article to list methods of retrieving the number of files in a folder/directory. Why? Why can't we just use a loop and file pattern native to the Autohotkey programming language: UserFolder:="C:" -- UserFolder := RegExReplace(...

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

Zoho Deluge: Get the Week Number if Week starts on a Mondayhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-get-the-week-number-if-week-starts-on-a-monday.html

06 July 2023 A quick article on determining the week number of the year if your week starts on a Monday and not a Sunday. Why? I think it's the US system which generally says that a week starts on a Sunday but in the United Kingdom, the week generally starts on a...

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

Prefix Line Numbers in a Text Filehttps://www.joellipman.com/articles/automation/ms-dos/prefix-line-numbers-to-a-text-file.html

18 July 2012 Prefixing lines with their respective line number in a text file using a Microsoft Windows Operating System... I've just googled this as I couldn't remember how I did this last time and a number of people showing off their MS-DOS batch skills have...

• Type: Article
• Author: Joel Lipman
• Category: MS-DOS
• Language: *

ZohoDeluge: Check Shipment Status via DHL APIhttps://www.joellipman.com/articles/crm/zoho/zohodeluge-check-shipment-status-via-dhl-api.html

21 March 2023 A quick note here on how to connect to the DHL API and check on a package given a tracking number. Why? In our megafunction to generate a customer, a product/item, a sales order, an invoice, a package slip/delivery note, a shipment order; as well as...

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

Select unique values in Microsoft Excel columnhttps://www.joellipman.com/articles/microsoft/excel/select-distinct-values-in-microsoft-excel-column.html

03 July 2012 this hidden away in a forum: Quick Count =INT(SUMPRODUCT((A3:A1000"")/COUNTIF(A3:A1000,A3:A1000&""))) This returns the number of unique values in the range A3 to A1000 and excludes the blank/empty cells. Display all Unique Found this note on one of...

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

Cheat Sheet for mySQL vs t-SQLhttps://www.joellipman.com/articles/database/cheat-sheet-for-mysql-vs-t-sql.html

31 January 2013 CHAR_LENGTH(string)LEN(string) DATALENGTH(string) Dates / Times Weekday NameDAYNAME(now())DATENAME(dd, getdate()) Weekday NumberDAYOFWEEK(now())DATEPART(dw, getdate()) Month NameMONTHNAME(now())DATENAME(mm, getdate()) Month...

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

XML Schema Referencehttps://www.joellipman.com/articles/web-development/xml/xml-schema-reference.html

13 August 2014 constraint sequence Specifies that the child elements must appear in a sequence. Each child element can occur from 0 to any number of times simpleContent Contains extensions or restrictions on a text-only complex type or on a simple type as content and...

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

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

12 September 2015 installing it. Preparation: Backup your Joomla v2.5.x database and website files Note the CURRENT super user identification number "ID" and alias (name) Ensure all items are "checked-in" (Do a "Global Check In") Install new Joomla: Install Joomla! CMS...

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

ZohoCRM: Get All eBay Active Listingshttps://www.joellipman.com/articles/crm/zoho/zohocrm-get-all-ebay-active-listings.html

01 March 2023 as "fn_GetEbayActiveProducts" without any arguments. Something to note in the below is that it asks you to set an estimated number of products that are in your listing, it will then generate the number of pages it thinks it has to loop, so try and give...

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

Php convert filesizes to bytes kb mb gbhttps://www.joellipman.com/articles/web-development/php/php-convert-filesizes-to-bytes-kb-mb-gb.html

03 January 2013 Gb" How? Source: PHP Share: http://www.phpshare.org function formatSizeUnits($bytes) { if ($bytes >= 1073741824) { $bytes = number_format($bytes / 1073741824, 2) . ' GB'; } elseif ($bytes >= 1048576) { $bytes = number_format($bytes / 1048576, 2) . '...

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

Increase Recent Documents Number in Windows XPhttps://www.joellipman.com/articles/microsoft/windows-os/increase-recent-documents-number.html

28 July 2011 Just a quick note here. This is an article based on the REG file from Kelly's Korner (@www.kellys-korner-xp.com/xp_tweaks.htm). I'm not fond of downloading REG files and running them even if I have checked what it's doing. I'm putting a note here just...

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

Joes Quicklist Weblinks (JQW)https://www.joellipman.com/component/content/article/joes-quicklist-weblinks-jqw.html?catid=40

03 July 2011 your weblinks and then login to front-end as an administrator to generate ALL the thumbnails. Then customize and reduce the number of listed weblinks to what you want. Or set up a module which only the authorized "service group" has access to...

• Type: Article
• Author: Joel Lipman
• Category: Product Documentation
• Language: *

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

23 August 2013 AS VARCHAR(10)), 2, 1000) AS Items FROM Employees_Attendance_Table JOIN master..spt_values n ON n.type = 'P' AND n.number...

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

Zoho Analytics: Determine profits from invoices and purchase ordershttps://www.joellipman.com/articles/crm/zoho/zoho-analytics-determine-profits-from-invoices-and-purchase-orders.html

03 November 2022 calling this dataset "My Purchase Order Items" SELECT DISTINCT po."Purchase Order Date" AS "PO Date", po."Purchase Order Number" AS "PO Ref", poi."Product ID" AS "PO Product ID", poi."Item ID" AS "PO Item ID", poi."Item Name" AS "PO Item Name",...

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

Database Error: Unable to connect to the database: Could not connect to MySQLhttps://www.joellipman.com/articles/database/mysql/database-error-unable-to-connect-to-the-database-could-not-connect-to-mysql.html

25 September 2009 NO modifications whatsoever) I start to get the "cannot connect to MYSQL." I contacted my webhost, and the problem is the number of MYSQL database queries. It goes up to about 200,000 in the first few minutes of use and then after that every page load...

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

Change default order of weblinkshttps://www.joellipman.com/articles/cms/joomla/change-default-order-of-weblinks.html

15 November 2009 by url description => sort by description date => sort by date added (most recent or oldest first) hits => sort by hits (number of times clicked on) published => obviously by whether they're published or not... no idea why u would checked_out => sort by...

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