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

11 October 2011 or made of symbols) separates two sets of data from within the same select query based on a column that's different. So for example, I have data like the following: SELECT DATENAME(dw, StartDate) AS 'Day' FROM Timetable ORDER BY StartDate ASC,...

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

Basic Oracle Stored Procedure Structurehttps://joellipman.com/articles/database/pl-sql/basic-oracle-stored-procedure-structure.html

16 January 2012 ] IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [stored_procedure_name ]; Example CREATE OR REPLACE PROCEDURE sp_get_studentdetails_from_ad ( c_test out sys_refcursor, p_STUDENT_ADNAME in varchar2 ) IS BEGIN...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

Connect to Joomla database in standalone scripthttps://joellipman.com/articles/cms/joomla/connect-to-joomla-database-in-standalone-script.html

17 August 2012 developer installing my component will do so on a website which uses different login details for their Joomla database. The example below demonstrates how to get these details and extract data from the database. How? Based on a script I found on...

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

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

14 March 2013 This is a quick article on how I got time conversion working in an SSIS package which read from a text file. Note that the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one...

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

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

04 April 2013 you skip rows and specify column datatypes. You don't get that with Excel but you can still control the data range. How? My example is that I have an excel sheet where the first 10 rows are descriptions to the column, and then I have 38 columns ("A" to...

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

Reorder Columns in a Tablehttps://joellipman.com/articles/database/reorder-columns-in-a-table.html

22 July 2013 TABLE table_name MODIFY COLUMN misplaced_column BIT(1) NOT NULL DEFAULT b'0' AFTER other_column; -- OR A MORE COMPLETE EXAMPLE -- ALTER TABLE table_name MODIFY COLUMN misplaced_column VARCHAR(13) NOT NULL DEFAULT 'DefaultString' COMMENT 'A Comment'...

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

Batch Process to rename multiple files using Windows DOShttps://joellipman.com/articles/automation/ms-dos/batch-process-to-rename-multiple-files-using-windows-dos.html

09 August 2014 What? A quick article on how to rename multiple files using the command prompt and a bit of string manipulation. This example will rename files which contain the string " (Copy)" and replace it with nothing (so removes it). The challenge here is the...

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

SITS: Export field code and namehttps://joellipman.com/articles/web-development/xml/sits-export-field-code-and-name.html

12 August 2014 the code and the lookup value in our XML to our Staging environment. Why? Let's take the country of birth of a person as an example as it is expected to be a long list and where just using the parameter &S is not workable. Instead I want to use &G[] and...

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

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

02 February 2019 return $output; } 5. Get Minutes Remaining on cached token So usually an access token will last for 60 minutes, in this example, we are going to store the token value so it is reused until it is about to expire. Firstly, we need to check how many...

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

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

18 February 2019 the record. dataMap is the map() to post. connectionLinkName is the connection link obtained previously (as a string). For example: v_RecordID = "1234567890123456789".toLong(); m_Params = Map(); m_Params.put("FieldToUpdate", "ValueToUpdateWith");...

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

AutoHotkey - MS Windows 10 - Open Apps on Multiple Monitors and Desktopshttps://joellipman.com/articles/automation/autohotkey/autohotkey-ms-windows-10-open-apps-on-multiple-monitors-and-desktops.html

18 July 2019 down some notes here on how I've achieved this across multiple monitors... and now multiple desktops. How? The following example is assuming you have 3 monitors and 3 desktops. I'm going to cover the basic code to: 1) get all monitors, 2) open an app on...

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

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

21 November 2019 = r_ProductCreate.ID; } else { // DON'T insert into Product table v_ProductID = l_ProductDetails.ID; } What Happens: In the example above, the count to see if "Myproduct1" exists counts as zero (0). It doesn't think the product exists because it is...

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

Zoho Deluge - Compare time and currenttime with timezonehttps://joellipman.com/articles/crm/zoho/zoho-deluge-compare-time-and-currenttime-with-timezone.html

13 January 2020 to Europe/London (You can use an abbreviation such as GMT but this doesn't seem to check if daylight savings is in effect): Example of taking a CRM date-time value (expiryTime) and appending toTime() prematurely - DO NOT USE v_CustomTime = "2020-03-25...

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

Zoho Deluge: Setup an API Connection for InvokeURLhttps://joellipman.com/articles/crm/zoho/zoho-deluge-setup-an-api-connection-for-invokeurl.html

12 August 2020 type: GET connection: "joelconnector" ]; How? So in the following example, we are going to setup a connection in Zoho Books on an EU datacenter: First determine what datacenter your client is using Register the App Setup the Connector How do I determine...

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

Zoho Creator: Set Up Custom Domain for Customer Portalhttps://joellipman.com/articles/crm/zoho/zoho-creator-set-up-custom-domain-for-customer-portal.html

17 November 2020 is a quick article I use to list step by step on how to set up a custom domain for a Zoho Creator customer portal. In this example, we are going to use a subdomain of a company which has the domain joellipman.com. Why? This took me a bit of going...

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

Zoho Creator: Add a subform while creating a recordhttps://joellipman.com/articles/crm/zoho/zoho-creator-add-a-subform-while-creating-a-record.html

14 July 2021 first and then add the subform afterwards (insert into [...]) but it can be done all in one go. How? Here's a use case example which you will need to adapt to your own but It should do enough (spacing has been added for demo purposes and to make it...

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

Zoho Creator: Receive JSON via a Shopify Webhookhttps://joellipman.com/articles/crm/zoho/zoho-creator-receive-json-via-a-shopify-webhook.html

15 July 2021 Item to Shopify API if you want information on setting up an integration from Zoho Creator to Shopify API. Why? In this example, we want a webhook that whenever an order is made in Shopify, it tells our Zoho Creator app. Note that at the time of this...

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

Zoho Creator / Shopify: Get all Active Productshttps://joellipman.com/articles/crm/zoho/zoho-creator-shopify-get-all-active-products.html

12 October 2021 // // your shopify details v_ClientID = ""; v_ClientSecret = ""; v_ShopID = "example.myshopify.com"; v_ShopifyURL = "https://" + v_ClientID + ":" + v_ClientSecret + "@" + v_ShopID; v_ShopifyApiVersion = "2020-01"; // // loop through 2500 records...

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

Zoho CRM & Zoho Writer: Button to Merge Template, Send, and Attachhttps://joellipman.com/articles/crm/zoho/zoho-crm-zoho-writer-button-to-merge-template,-send,-and-attach.html

21 October 2021 the file. First Create a CRM connection I'm going to create a Zoho OAuth connection with open scopes just to get a working example; I can then reduce the scopes permissions afterwards when locking it down for security: Login to ZohoCRM > Setup >...

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

Zoho Creator/Deluge: Calculating with Timezone Offsethttps://joellipman.com/articles/crm/zoho/zoho-creator-deluge-calculating-with-timezone-offset.html

07 December 2021 key trick I used was to actually use a single-line text to store the customer's requested time. Let's use the appointment example mentioned above for a customer in UTC+1 @ 4pm their time. The server is on Eastern time UTC-5 and the agent is in Los...

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