Excel - Check a column for values found in another columnhttps://joellipman.com/articles/microsoft/excel/excel-check-a-column-for-values-found-in-another-column.html

14 February 2019 double hyphen/dash which converts a boolean true to 1 and boolean false to 0. IsNumber( is a function returning a boolean. Returns false if a cell value has been formatted to a text type. Returns true if the cell value is a number AND the cell data type...

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

Zoho Deluge - Generate 5 Letter Booking Retrieval Codehttps://joellipman.com/articles/crm/zoho/zoho-deluge-generate-5-letter-booking-retrieval-code.html

09 May 2019 This is a quick article on how to generate a 5 letter code from several functions: one which returns 5 randomly selected characters and another two which convert a number to 5 letters. Why? Zoho doesn't have a function to generate random numbers or...

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

Zoho Deluge - Get distance between two coordinateshttps://joellipman.com/articles/crm/zoho/get-distance-between-two-points-in-zoho.html

11 June 2019 then try this standard function. Note this has only been tested in Zoho Creator rather than Zoho CRM. How? This function returns the distance as the crow flies. Update 2020 Not sure exactly when but Zoho have made available this function in Creator: =...

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

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

21 November 2019 the product exists because it is searching with case-sensitivity enabled. It tries to insert into the product table, but returns the error "Result: Error on Execution" > "Error details: Execution Failed Due to invalid expressions insert statement is...

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

Zoho Deluge: Handle Commas between Quotes in a CSV (and New Lines)https://joellipman.com/articles/crm/zoho/zoho-deluge-handle-commas-between-quotes-in-a-csv.html

26 August 2021 commas in a CSV file that were enclosed between two double-quotes. I've added to this the handling of new lines or carriage returns in between a pair of double-quotes. Why? Our use case is that we were trying to loop through rows of a CSV file which...

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

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

08 October 2021 type :POST parameters: m_Params.toString() connection: "joels_coql_connection" ]; // // output info r_Coql; But this returns something like the following (I've reduced the 200 records to about 6 of them): { "data": [ { "Currency": "EUR", "id":...

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

Zoho Creator: Public URL of an Image field / Upload to Shopify APIhttps://joellipman.com/articles/crm/zoho/zoho-creator-public-url-of-an-image-field-upload-to-shopify-api.html

17 October 2021 to eBay's Picture Services successfully, Shopify would not accept any links I gave it. Why not use the links that eBay returns? Well eBay returns URLs to images that have been resized to 400x400 (even with tweak to return 800x800). I wanted a way to...

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

Zoho CRM/Deluge: Get TimeZone Based on GeoCoded Address (Lat/Lng)https://joellipman.com/articles/crm/zoho/zoho-crm-deluge-get-timezone-based-on-geocoded-address-lat-lng.html

15 December 2021 builds up the address from a Lead record to geocode into latitude and longitude coordinates to feed a third-party API which returns a timezone. Why? Our use-case is that we have a field called "Customer's Time Zone" on the lead record in ZohoCRM. It...

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

SSRS Use T-SQL Like with a Parameterhttps://joellipman.com/articles/microsoft/ssrs/ssrs-use-t-sql-like-with-a-parameter.html

04 April 2011 been so obvious to everyone else that nobody bothered to write an article on it... till now. Situation I have a report that returns room bookings based on a user and given a date range. The problem is that there are a few thousand users and Microsoft's...

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

Zoho Deluge - MD5 functionhttps://joellipman.com/articles/crm/zoho/zoho-deluge-md5-function.html

19 November 2018 but found a shortcut playing with the base64encode function: v_Etag = zoho.encryption.base64encode(v_Combined_Fields); // returns error Error on Execution Execution Failed Due to invalid expressions insert statement is terminated Line:(102) Value of the...

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

Zoho Deluge - Get User Timezone, Country and Locationhttps://joellipman.com/articles/crm/zoho/zoho-deluge-get-user-timezone-country-and-location.html

03 June 2019 1000 per day | Incorrect Location]: v_Url = "http://api.db-ip.com/v2/free/"+ zoho.ipaddress; v_Response = getUrl( v_Url ); Returns something like: { "ipAddress": "147.188.254.175", "continentCode": "EU", "continentName": "Europe", "countryCode": "GB",...

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

ZohoCRM GetRelatedRecords INVALID_DATAhttps://joellipman.com/articles/crm/zoho/zohocrm-getrelatedrecords-invalid_data.html

30 January 2025 the CRM. The cause of the problem is the same as covered in my article Joel Lipman - ZohoCRM: zoho.crm.searchRecords only returns certain records. Consider the following code: /*...

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

Zoho Deluge: Nested Catch Statementshttps://joellipman.com/articles/crm/zoho/zoho-deluge-nested-catch-statements.html

04 February 2025 send it a bit of code in HTML and we want a PDF file returned. I have severaly instances of an API which accepts HTML and returns a PDF file. Nothing complex but issues with one of the servers being down, or inaccessible due to certificate expirys,...

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

Zoho CRM: Client Script Confirmation Box and Popup Mailerhttps://joellipman.com/articles/crm/zoho/zoho-crm-client-script-prompt-and-popup-mailer.html

06 February 2024 // get the ID of this record var v_ThisRecordID = $Page.record_id; // get the full name of this contact (not sure why this returns both first and last) var v_ContactName = ZDK.Page.getField('Last_Name').getValue(); // get the email of this contact var...

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

GDPR Privacy Policyhttps://joellipman.com/static-items/gdpr-privacy-policy.html

14 February 2019 or otherwise. Site Policies, Modification, and Severability Please review our other policies, such as our Shipping and Returns policy, posted on this site. These policies also govern your visit to JoelLipman.com. We reserve the right to make changes to...

• Type: Article
• Author: Joel Lipman
• Category: Static Items
• Language: *

Parameters not being used in report processinghttps://joellipman.com/articles/microsoft/ssrs/parameters-not-being-used-in-report-processing.html

07 January 2011 ORDER BY Date ASC, Room ASC, tt.[StartTime] ASC, tt.[FinishTime] ASC Importantly, note the following: SET DATEFORMAT dmy returns the date in European format irrespective of the regional settings of either the client or the reporting server. Removed...

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

Formatting a date in an MDX queryhttps://joellipman.com/articles/database/t-sql/formatting-a-date-in-an-mdx-query.html

11 January 2011 Format(Fields!Date.Value,"d")=3,"rd", Format(Fields!Date.Value,"d")=23,"rd") Can anyone say what I did wrong with this? It returns "#Error".

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

Alternate row background colour in Reporting Serviceshttps://joellipman.com/articles/microsoft/ssrs/alternate-row-background-colour-in-reporting-services.html

04 February 2011 -- where "Field1" is a column in my dataset and "DataSet1" is my dataset -- and Count(Fields!Field1.Value, "DataSet1") returns the maximum number of rows retrieved Yields something like: This is the bottom left of a report where both the above examples...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: *

MySQL: Find records in one table that are not in another.https://joellipman.com/articles/database/mysql/mysql-find-records-in-one-table-that-are-not-in-another.html

25 February 2011 this article when I work this out without using two SQL scripts. Currently I've returned to the process where one SQL query returns all possible results and the PHP script runs another SQL query for each row to check the ID does not exist in the audit...

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

PHP & MySQL Search Enginehttps://joellipman.com/articles/web-development/php/php-a-mysql-search-engine.html

09 March 2011 comments (and everything in between) $str = str_replace(array("\r\n", "\r", "\n", "\t"), ' ', $str); // remove carriage returns, line feeds, tabs and double-spaces # for mediawiki $str = str_replace('=', ' ', $str); // replace any equal signs with a...

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