Zoho Deluge - Some Useful Regular Expressionshttps://joellipman.com/articles/crm/zoho/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
• Language: *

Zoho Deluge: Regex Rounding and/or Removing Trailing Zeroshttps://joellipman.com/articles/crm/zoho/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
• Language: *

SSRS / MDX Date Querieshttps://joellipman.com/articles/microsoft/ssrs/ssrs-mdx-date-queries.html

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

CharIndex Reverse - find occurrence starting from end of string in TSQLhttps://joellipman.com/articles/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

Zoho CRM/Creator - Common Errors & Gotchashttps://joellipman.com/articles/crm/zoho/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
• Language: *

DBMS Random Referencehttps://joellipman.com/articles/database/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: *

A for loop within a for loop in MS-DOShttps://joellipman.com/articles/automation/ms-dos/a-for-loop-within-a-for-loop-in-ms-dos.html

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

T-SQL Record Separatorhttps://joellipman.com/articles/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

How to Display Report Execution Time in SSRS (milliseconds)https://joellipman.com/articles/microsoft/ssrs/how-to-display-report-execution-time-in-ssrs.html

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

Split a row into multiple rows based on a column valuehttps://joellipman.com/articles/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/web-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 - Compare time and currenttime with timezonehttps://joellipman.com/articles/crm/zoho/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
• Language: *

Zoho Creator: Change Radio into Tabshttps://joellipman.com/articles/crm/zoho/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
• Language: *

Zoho Deluge: Get the Week Number if Week starts on a Mondayhttps://joellipman.com/articles/crm/zoho/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
• Language: *

Zoho Deluge - Regex to Strip all non-numeric charactershttps://joellipman.com/articles/crm/zoho/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
• Language: *

T-SQL concatenate an incremental row numberhttps://joellipman.com/articles/database/t-sql/t-sql-concatenate-an-incremental-row-number.html

24 May 2013 AS QualificationRef FROM Qualifications q INNER JOIN Employee e ON RIGHT('000' + CAST(e.ID AS VARCHAR), 3)=q.Employee -- yields EmployeeNo QualificationRef -------------------------------- 001 00101 001 00101 Almost useful... but the qualification...

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

Generate a Timesheet in MySQL https://joellipman.com/articles/database/mysql/generate-a-timesheet-in-mysql.html

05 December 2013 NOW() ) + 6 DAY ) ) AS ThisDate, DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDay -- yields -- ThisDate ThisDay -- ----------- -------------- -- 2013-12-02 Monday -- 2013-12-03 Tuesday -- 2013-12-04 Wednesday --...

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

Add attribute xsl:nil=true on empty elements using XSLThttps://joellipman.com/articles/web-development/xml/xslt/add-attribute-xsl-nil-true-on-empty-elements-using-xslt.html

18 August 2014 format (yyyy-mm-dd) to European format (dd/mm/yyyy)? See the following: -- assuming value of DATE_OF_BIRTH is blank or null yields // -- errors How? I could correct this using SSIS but with all the data in XML, we might as well stick with the technology...

• Type: Article
• Author: Joel Lipman
• Category: XML Stylesheet Language Transformations
• Language: *

Zoho Deluge - Get English Ordinalhttps://joellipman.com/articles/crm/zoho/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
• Language: *

Convert Past Date to Time Ago in PHPhttps://joellipman.com/articles/web-development/php/convert-past-date-to-time-ago-in-php.html

16 May 2019 5 seconds ago // then $v_Str = "17 minutes ago" Or how about $v_Str = getTimeAgo('-1 week 2 days 4 hours 2 seconds'); // yields "1 week ago" $v_Str = getTimeAgo('last Monday'); // yields "3 days ago" (it's Thursday) $v_Str = getTimeAgo('first day of...

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