T-SQL: Parse an XML valuehttp://mail.joellipman.com/articles/database/t-sql/t-sql-parse-an-xml-value.html

27 March 2014 Looping -- From resultset of $i ,assigning it to variable $j using let clause, -- Getting top two employees by using where & order by clause. SELECT Event_XML.query('for $i in /STAFF/student let $j:= $i where ($j/@ID)

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

SSIS: How to loop through multiple flat files as data sourceshttp://mail.joellipman.com/articles/microsoft/ssis/ssis-how-to-loop-through-multiple-flat-files-as-data-sources.html

13 June 2014 This is an article to demonstrate a quick step-by-step on having an SSIS package loop through a directory/folder of files in order to populate a database table. We could add each file as a separate connection manager but this is inefficient and not...

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

mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connecthttp://mail.joellipman.com/articles/database/mysql/mysqldump-got-error-2049-connection-using-old-pre-4-1-1-authentication-protocol-refused-client-option-secure-auth-enabled-when-trying-to-connect.html

28 July 2014 I want to export for offline backup. I have to raise and log a formal change request and follow a workflow process in order to make a change on a live system to a service user account... Especially one used by the scripts to access the database-driven...

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

XML Schema Referencehttp://mail.joellipman.com/articles/web-development/xml/xml-schema-reference.html

13 August 2014 Elements Element Explanation all Specifies that the child elements can appear in any order. Each child element can occur 0 or 1 time annotation Specifies the top-level element for schema comments any Enables the author to extend the XML document with...

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

A quick run through of setting up an export process in SITShttp://mail.joellipman.com/articles/web-development/xml/a-quick-run-through-of-setting-up-an-export-process-in-sits.html

21 August 2014 stutalk usr_code=«your_user_code» smg_code=CAPCHG xpt_code=7day tracemess=3 limit=600 tot Run the monitors in order of DOT, EOT and TOT Your target database table should now be populated... » Read the below "Testing" steps if you aren't getting a result...

• Type: Article
• Author: Joel Lipman
• Category: Extensible Markup Language
• Language: *

Convert XML UTF-16 to JSON UTF-8 with PHP cURLhttp://mail.joellipman.com/articles/web-development/php/convert-xml-utf-16-to-json-utf-8-with-php-curl.html

28 March 2015 others could have taken a several minutes if you knew where to look. My aim was to convert UTF-16 received XML to UTF-8 in order to convert XML to JSON and that has been achieved in part. It's 6am and I'm off to bed.

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

Joes Word Cloud (JWC) Downloadhttp://mail.joellipman.com/component/content/article/joes-word-cloud-jwc-downloads.html?catid=92

07 September 2015 IDs - Added Joomla! Admin Option: Sort Words By (shuffle, alphabet, frequency) - Added Joomla! Admin Option: Sort Words Order (ascending, descending) - Prefixed language file references with JWC to avoid conflicts with 3rd-party apps. - Restructured...

• Type: Article
• Author: Joel Lipman
• Category: Downloads
• 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 `created_by_alias`, `modified`, `modified_by`, `publish_up`, `publish_down`, `images`, `urls`, `attribs`, `version`, `ordering`, `metakey`, `metadesc`, `access`, `hits`, `metadata`, `featured`, `language`, `xreference`) SELECT a.`id`, ( SELECT x.`id`...

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

Zoho CRM: APIv2 using PHP & cURLhttp://mail.joellipman.com/articles/crm/zoho/zoho-crm-apiv2-using-php-curl.html

30 January 2019 rewritten this code a few times and want to store the finalized version (following updates) making it as generic as I can in order to apply it to any client. How? Firstly, you will need to browse to https://accounts.zoho.eu/developerconsole and register...

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

MS Excel - Split Workbook into separate files per sheethttp://mail.joellipman.com/articles/microsoft/excel/ms-excel-split-workbook-into-separate-files-per-sheet.html

11 February 2019 into separate files per sheet. Why? The Excel file in question was about 36000 rows and had a file size of about 11Mb. In order for an import process to work, the import would only accept XLS files no greater than 1Mb. So our script has to split a...

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

Zoho Deluge - Counting in a Map dataTypehttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-counting-in-a-map.html

02 September 2019 Why? I'm aiming to automate a process which counts the number of products allocated to an account or in total for a purchase order. Let us assume we have the following in a form/report called "Stock_Upload": RowID Name SKU Account Email 0001 Test...

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

Zoho Creator: Button on Report for Merging Multiple Selected Recordshttp://mail.joellipman.com/articles/crm/zoho/zoho-creator-button-on-report-for-merging-multiple-selected-records.html

06 December 2020 quote into one single quote. Why? I've written this article because I keep forgetting on how to do this. I have a specific order of steps on how I do this, you may find other ways but this is one that works for me. How? So here's an overview and then...

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

Windows OS: Alt Codes / Symbols / Special Charactershttp://mail.joellipman.com/articles/microsoft/windows-os/windows-os-alt-codes-symbols-special-characters.html

03 June 2021 numbers you might ask? Well every character is built of a collection of digits that are to be entered in their specific order. Search for "Alt Codes" or "Symbol Codes" in a search engine for a full list. Meanwhile, here's some that you may need...

• Type: Article
• Author: Joel Lipman
• Category: Windows OS
• Language: *

ZohoCRM: zoho.crm.searchRecords only returns certain recordshttp://mail.joellipman.com/articles/crm/zoho/zohocrm-search-records-only-returns-certain-records.html

29 July 2021 of the records but omits others. Why? The use-case was that we were creating a custom related list on a Zoho Finance > Sales Orders module but it wasn't finding all the Quotes related to this Sales Order. How? The quick answer is buried in Zoho...

• 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 = "0"; v_CrmOppID = "0"; v_OwnerName = ; v_AppLinkName = ; v_FormLinkName = ; v_ViewLinkName = ; if(!isnull(salesorder.get("salesorder_id"))) { v_CreatorSoID = salesorder.get("salesorder_id"); } v_SearchCriteria = "Sales_Order_ID==\"" + v_CreatorSoID +...

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

Zoho CRM & Zoho Books: Get SalesPersonshttp://mail.joellipman.com/articles/crm/zoho/zoho-crm-zoho-books-get-salespersons.html

30 August 2021 that I don't spend so long in trying to find sales persons in Zoho Books. Why? My use case is that I want to create a Sales Order in ZohoBooks based on one in ZohoCRM and wanted to assign the sales person. How? So after an hour or so trying to get the...

• 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 In any way that you do this, OnEdit will populate the form with the record data and trigger each field in the displayed order. Method #3: Use an eTag I use to use an eTag for a database-driven system I created to store the record details of properties....

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

Zoho Creator: eBay: Get Item Transactionhttp://mail.joellipman.com/articles/crm/zoho/zoho-creator-ebay-get-item-transaction.html

22 January 2022 This is the function to get the line item order/transaction from eBay if you give it the eBay Item ID as a parameter. Why? Mostly for debugging but here's the code that will quickly get the XML of a GetItemTransactions request to eBay. How? You'll need...

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

ZohoCRM: Template: not being able to edit a text boxhttp://mail.joellipman.com/articles/crm/zoho/zohocrm-template-not-being-able-to-edit-a-text-box.html

25 January 2022 cause/outcome would be for your case but worked for us. This documents the steps taken to edit/modify the header on a sales order inventory template. Why? A client raised an issue that they had previously hard-coded the address of their organization...

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

Zoho CRM & Creator: Download attachment and upload to Creator file fieldhttp://mail.joellipman.com/articles/crm/zoho/zoho-crm-creator-download-attachment-and-upload-to-creator-file-field.html

11 March 2022 v_NoteAttachments = " "; m_SortCriteria = Map(); m_SortCriteria.put("sort_by","Created_Time"); m_SortCriteria.put("sort_order","desc"); l_Attachments = zoho.crm.getRelatedRecords("Attachments","Contacts",v_CrmContactID,1,200,m_SortCriteria); for each...

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