Zoho Deluge - Some Useful Regular Expressionshttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-some-useful-regular-expressions.html

31 May 2019 will remove any non-digits: v_MyString = "Hello World 123"; v_MyFormattedString = v_MyString.replaceAll("[^0-9]",""); // yields 123 The following is used in searches to escape special characters with a backslash: v_MyString = "Joe's \"Amazing\" Skill &...

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

Zoho Deluge: Regex Rounding and/or Removing Trailing Zeroshttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-regex-rounding-and-or-removing-trailing-zeros.html

08 February 2022 with a single backslash: v_Test1 = 12.300; info v_Test1.toString().replaceAll("(\\.\\d*?[1-9])0+$", "$1", false); // yields 12.3 // same expression again but with a different value v_Test2 = 12.000; info...

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

ZohoCRM to ZohoBooks API: Rounding the cents or pennies when creating an invoicehttps://joellipman.com/articles/crm/zoho/zoho-books/zohocrm-to-zohobooks-api-half-a-cent-or-penny-off-when-creating-an-invoice.html

20 March 2025 v_Rate = 12345.545; info "Rate: " + v_Rate; // built-in rounding v_Rounded = v_Rate.round(2); info "a) " + v_Rounded; // Yields: 12345.55 // Regexed v_RegexedNumber = v_Rate.toString().replaceAll("^(\\d+\\.\\d{2})\\d*$", "$1"); info "b) " +...

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

SSRS / MDX Date Querieshttps://joellipman.com/component/content/article/ssrs-mdx-date-queries.html?catid=75&Itemid=165

03 August 2011 August 2011 @ 11:46: This Week: -- Start Date (US format - mm/dd/yyyy) =DateAdd("d", -(WeekDay(Today(),2))+1, Today()) // yields: 8/1/2011 -- End Date (US format - mm/dd/yyyy) =DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())) // yields:...

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

Zoho CRM/Creator - Common Errors & Gotchashttps://joellipman.com/articles/crm/zoho/zoho-crm/zoho-crm-creator-common-errors-gotchas.html

06 February 2020 r_Response = invokeurl [ url :v_DataEndpoint type :POST parameters:m_CreateRecord headers:m_Header ]; info r_Response; // yields: { "code": 0, "message": "The item has been added.", "item": { "item_id": "2124100000000081031",.... }} info...

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

CharIndex Reverse - find occurrence starting from end of string in TSQLhttps://joellipman.com/articles/else/database/t-sql/charindex-reverse-find-occurrence-starting-from-end-of-string-in-tsql.html

28 January 2013 @Haystack = 'String1.String2.String3.String4'; -- I want "String4" from Haystack SET @Delimiter = '.'; PRINT @Haystack; -- yields "String1.String2.String3.String4" SELECT REVERSE( @Haystack ); -- yields "4gnirtS.3gnirtS.2gnirtS.1gnirtS" SELECT...

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

DBMS Random Referencehttps://joellipman.com/articles/else/database/oracle-pl-sql/dbms-random-reference.html

21 August 2012 Alphanumeric with upper case alphabets) P (for Printable characters only) Usage select dbms_random.random from dual; -- yields random number, eg. 68489408 select dbms_random.value from dual; -- yields values between 0 and 1 select...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: *

Zoho Deluge - Multi-line Variable Assignments and Expressionshttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-multi-line-statements-using-semi-colon-termination.html

02 January 2026 "\"FirstName\":\"John\"," + "\"LastName\":\"Doe\"" + "}"; m_InputData = v_InputData.toMap(); info m_InputData.get("ID"); // yields "TEST-1001" This pattern can be useful when building request payloads for API calls or preparing structured data for reuse...

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

A for loop within a for loop in MS-DOShttps://joellipman.com/component/content/article/a-for-loop-within-a-for-loop-in-ms-dos.html?catid=51&Itemid=165

03 October 2013 following loops through directory for any file beginning with Reference FOR %%A IN ('DIR InitializingFile*') DO ECHO %%A -- yields: InitializingFile_123.csv InitializingFile_456.csv -- the following loops through directory and separates by underscores...

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

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

13 January 2020 HH:mm:ss"); v_ExpiryTime = "2020-03-25T21:41:07+00:00"; info v_CustomTime.toTime(); info v_ExpiryTime.toTime(); // yields // 25-Mar-2020 20:41:07 // 25-Mar-2020 00:00:00 Example of splitting date-time value and then comparing: v_CustomTime = "2020-03-25...

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

Zoho Creator: Change Radio into Tabshttps://joellipman.com/articles/crm/zoho/zoho-creator/zoho-creator-change-radio-into-tabs.html

22 October 2021 "Tabs": v_Css = ""; v_Css = v_Css + ".zc-Tabs-label{display:none !important;}"; v_Css = v_Css + ""; input.Note_CSS = v_Css; Yields Then aligning these radio options horizontally: v_Css = ""; v_Css = v_Css + ".zc-Tabs-label{display:none !important;}";...

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

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

06 July 2023 } // Monday, 26-Jun-2023 1 26 // yields: 26 // // Sunday, 02-Jul-2023 7 27 // yields: 26 // // Monday, 03-Jul-2023 1 27 // yields: 27 // // Sunday, 09-Jul-2023 7 28 // yields: 27 Originally, I had this formula which seemed to do the same thing but...

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

How to Display Report Execution Time in SSRS (milliseconds)https://joellipman.com/component/content/article/how-to-display-report-execution-time-in-ssrs.html?catid=75&Itemid=165

17 November 2011 + " minute(s)" + ", " + CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)" // yields // 0 hour(s), 1 minute(s), 2 second(s) This isn't what I want... It wasn't accurate enough and always saying 0 seconds was just confusing...

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

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

11 October 2011 DATENAME(dw, StartDate) AS 'Day' FROM Timetable ORDER BY StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC -- Yields /* Day ---------------- Monday Monday Monday Tuesday Tuesday Wednesday Thursday Thursday Thursday Thursday Friday Friday */...

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

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

23 August 2013 in the new system. What we have: SELECT EmployeeNo , DaysOffSick , DateOfSickness FROM Employees_Attendance_Table -- yields EmployeeNo DaysOffSick DateOfSickness -------------- -------------- ---------------- 001 1.00 1997-11-17 002 3.00 2000-02-18 003...

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

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

12 August 2014 the code lookup will fail: -- If CODE=0000 and 0000 does not exist in table to lookup name, -- Using &G[COD.SRS:•|•] -- yields: -- But if CODE is not populated, -- Using &G[COD.SRS:•|•] -- also yields: The code is not blank. Doing the above, we have no...

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

Zoho Deluge - Regex to Strip all non-numeric charactershttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-regex-to-strip-all-non-numeric-characters.html

25 March 2019 in Zoho and I can remove the letters with removeAllAlpha(): v_PaymentTermsAlphaNum = v_PaymentTerms.getAlphaNumeric(); // yields CreditNote30Days vPaymentTermsNum = v_PaymentTermsAlphaNum.removeAllAlpha() // yields 30 But just to demo a regex and a one...

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

Zoho Deluge - Get English Ordinalhttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-get-english-ordinal.html

08 November 2018 d") + v_EnglishOrdinal + " of " + v_NextRenewalDate.toString("MMMM, yyyy"); info v_ContractDateFormatted; // yields Tuesday, 6th of November, 2018 Method #5: if a list contains: v_NextRenewalDate = '2018-11-06'; v_EnglishOrdinal =...

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

Zoho Deluge - Determine Quarter from Date with Fiscal Yearhttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-determine-quarter-from-date-with-fiscal-year.html

31 January 2020 { v_Quarter = (v_Month.toLong() / 3).ceil(); info "Month: " + l_MonthNames.get(v_Month-1) + " :: Quarter: " + v_Quarter; } Yields: Month: January :: Quarter: 1 Month: February :: Quarter: 1 Month: March :: Quarter: 1 Month: April :: Quarter: 2 Month:...

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

Zoho Deluge: Convert Xero Date (Unix Timestamp) to Standard Date Stringhttps://joellipman.com/articles/crm/zoho/zoho-deluge/zoho-deluge-convert-xero-date-unix-timestamp-to-standard-date-string.html

04 May 2020 v_StartIndex); v_UnixSeconds = v_XeroTime.subString(v_StartIndex, v_EndIndex); info v_UnixSeconds.toLong().toTime(); // yields 15-Apr-2020 17:00:00 Chained v_XeroTime="/Date(1586995200000+0000)/"; v_ZohoTime =...

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