MySQL: Display Users and Duration in Matrix Timesheethttps://www.joellipman.com/articles/database/mysql/mysql-display-users-and-duration-in-matrix-timesheet.html

27 April 2012 // Our working week starts on Monday 00:00 and ends on Sunday 23:59. Putting it together Your first query should loop through the users list, within the loop run the activities query and replace the string "STRING_TO_REPLACE_PER_ITERATION" with the User...

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

DataJumble - Shuffling characters in a data valuehttps://www.joellipman.com/articles/database/t-sql/data-shuffling-function.html

01 August 2012 VALUE SET @NewVal = SUBSTRING(LTRIM(RTRIM(CAST(@NewVal AS VARCHAR(30)))), 1, LEN(@OrigVal)); END ELSE BEGIN -- Loop through the characters passed WHILE @LoopCt

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

Search a database for a string (MySQL, T-SQL)https://www.joellipman.com/articles/database/search-a-database-for-a-string-mysql-t-sql.html

08 October 2012 Execstatus = 1 WHERE Tablename = @tmpTblname END SET NOCOUNT OFF GO MySQL Source: http://kedar.nitty-witty.com/blog/search-through-all-databases-tables-columns-in-mysql - Kedar at Nitty-Witty.com ## Procedure for search in all fields of all databases...

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

Stored Procedure to List Distinct Values and Countshttps://www.joellipman.com/articles/database/t-sql/stored-procedure-to-list-distinct-values-and-counts.html

26 October 2012 results CREATE TABLE #ListDistinctValuesAndCounts (myValue varchar(max), myColumn varchar(max), myCount int); -- Iterate through cursor1 OPEN Cursor1 FETCH NEXT FROM Cursor1 INTO @ColToProcess WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlToExecute = ' SELECT...

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

Search a database for a value and count matching rowshttps://www.joellipman.com/articles/database/search-a-database-for-a-value-and-count-matching-rows.html

05 November 2012 table to hold results CREATE TABLE #CountRecordsPerTablePerCol (myValue int, myTable varchar(max), myCount int); -- Iterate through cursor1 OPEN Cursor1 FETCH NEXT FROM Cursor1 INTO @TableToProcess WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlToExecute = '...

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

DataScramble - Randomizing data rowshttps://www.joellipman.com/articles/database/t-sql/datascramble-randomizing-data-rows.html

07 December 2012 AS VARCHAR(30)))), 1, LEN(@OrigVal)); END ELSE BEGIN IF ISNUMERIC(@OrigVal)=1 BEGIN SET @NewVal = ''; -- Loop through the characters passed WHILE @LoopCt

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

SSIS Convert a string into a datehttps://www.joellipman.com/articles/microsoft/ssis/ssis-convert-a-string-into-a-date.html

14 March 2013 a string (DT_WSTR) and another as a integer (DT_R8). Why? It took me a long time to figure this and it was only by trawling through columns that someone mentioned that maybe all the data in that column is not consistent? This made sense as I need to...

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

SSIS Skip Rows in Excel Source filehttps://www.joellipman.com/articles/microsoft/ssis/ssis-skip-rows-in-excel-source-file.html

04 April 2013 column of the data sheet (eg. F1 = B11) !!! Additional We add a conditional split task to the data flow which only passes through rows from the Excel sheet which are not blank/null. Browsing the net, I found this is sometimes also used for inserting...

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

Transferring Apps (purchases) from iPhone 4s to 5s on a Windows PChttps://www.joellipman.com/articles/apple/ios/transferring-apps-purchases-from-iphone-4s-to-5s-on-a-windows-pc.html

03 November 2013 you to set it up but you could do the below again at anytime (as I discovered). The steps below apply after having gone through the process of setting up the phone and finishing on the "Get Started" (including Touch ID). Ensure your old phone's iOS is...

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

SSRS: Performance Improvements: SELECT TOPhttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-performance-improvements-select-top.html

07 March 2014 environment. The requirement (amongst others) was that the report ran in less than a few seconds. Analyzing the report through the ReportServer databases (internal auditing) only showed how long the SQL query would take to recover the data and present...

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

SSRS Stop Scrolling Behindhttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-stop-scrolling-behind.html

08 April 2014 to work out: Open the Report in Design mode. Check that advanced mode is switched on as per the following image: Cycle through each static object and set FixedData to False (ensure all items are set to false or it will error): Save and test on the...

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

Joes Search Module (JSM)https://www.joellipman.com/component/content/article/joes-search-module-jsm.html?catid=40

09 March 2015 display search areas has been switched off in the admin panel. You should keep this enabled so that the filters are carried through to the next search: Go to Components > Search > Options > set "Use Search Areas" to "Yes" Save & Close Future...

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

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

07 September 2015 (eg. PNG to JPG -> means module has to re-download thumbnails into JPG format) - Fixed Bug: Weblink hit counter now works through Joomla! CMS - Date Uploaded: Wed, 13th Jul 2011 1.3.1- Modified code to work with Joomla! CMS versions 1.5.x - Removed...

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

Protecting your Joomla Administrator Sectionhttps://www.joellipman.com/articles/cms/joomla/protecting-your-joomla-administrator-section.html

23 October 2015 showing up at all in the stats (unless you were using it). Leaving this unchecked means that the crackers/hackers will get through ultimately whether it be by brute-force, DoS, Injection, etc. I'm not saying these are silver bullets, but they will slow...

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

Zoho Deluge - Update Creator from CRMhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-update-creator-from-crm.html

18 February 2019 The code to update a form (sample data for demonstration purposes). Note that the payload here is submitted through URL parameters: v_AccessToken = "123abc456def789abc123def456abc"; v_Criteria = "ID=1234567890123456789"; v_EndPoint =...

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

Autohotkey - Chrome Profiles in Alphabetical Orderhttps://www.joellipman.com/articles/automation/autohotkey/autohotkey-chrome-profiles-in-alphabetical-order.html

07 January 2020 a ListView with rows that can be double-clicked to open the profile. As an overview: Setup a standard AutoHotkey GUI Loop through Google Chrome Profile folders named "Profile 1", "Profile 2", etc. Scan the preferences file for JSON key "name" and...

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

Zoho Deluge: Duplicate/Clone a Recordhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-duplicate-clone-a-record.html

17 March 2020 of original invoice r_Invoice = zoho.crm.getRecordById("Invoices", p_InvoiceID); // start map m_Clone = Map(); // loop through field names for each v_FieldName in l_FieldApiNames { if(!isNull(r_Invoice.get(v_FieldName))) {...

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

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

28 August 2020 reminder (do not use): v_SearchName = v_SearchName.replaceAll("([&\'])","\\$1",false); Source(s) Zoho CRM - Get Records through a COQL Query Zoho CRM - Query API - An Overview

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

Expecting ZC_SUBFORM_250 expression found COLLECTIONhttps://www.joellipman.com/articles/crm/zoho/expecting-zc_subform_250-expression-found-collection.html

19 October 2020 ); // insert the collection into the subform on the current form input..insert( ); // or insert the row(s) into the subform through the record variable r_LeadDetails = Leads[ID == 1234567890]; r_LeadDetails..insert( ); Events I have used this task which...

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

Zoho Creator: Create a Widget which uses JavaScripthttps://www.joellipman.com/articles/crm/zoho/zoho-creator-create-a-widget-which-uses-javascript.html

24 October 2020 knowing anything about nodejs, as long as you know JavaScript and HTML/CSS you can use this: Execute the download and go through the installation process with the final step to move it to trash/bin. Open the terminal app on your Mac and type the...

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