Zoho Deluge: Date from a String (dd/MM/yyyy to yyyy-MM-dd)https://www.joellipman.com/articles/crm/zoho/zoho-deluge-date-from-a-string-dd-mm-yyyy-to-yyyy-mm-dd.html

01 September 2021 v_TestDate.toList("/").get(0); info v_DelugeDate; info v_DelugeDate.toDate(); info v_DelugeDate.toString("E, d MMM yyyy"); Yields: 07/01/1975 01-Jul-1975 Tue, 1 Jul 1975 ********************* 1975-01-07 07-Jan-1975 Tue, 7 Jan 1975 *********************...

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

Zoho CRM & Zoho Books: Get Books Currency and Tax IDshttps://www.joellipman.com/articles/crm/zoho/zoho-crm-zoho-books-get-books-currency-and-tax-ids.html

24 September 2021 v_BooksCurrencyID = m_Currencies.get(ifnull(r_AccountDetails.get("Currency"),"GBP")); info v_BooksCurrencyID; Yields something like: { "EUR": "123456789012345678", "GBP": "234567890123456789", "USD": "345678901234567890", } 234567890123456789 Get Taxes...

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

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

12 October 2021 = l_ProductIDs.get(l_ProductIDs.size() - 1); } // // output info "[" + l_ProductIDs + "]"; info l_ProductIDs.size(); } Yields something like: [ 1234567890123, 2345678901234 ] 2 Well that produces a lot of ID numbers delimited by a comma. However, to...

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

Zoho Deluge: Today, Tomorrow, Day After but Skip Sundayhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-today,-tomorrow,-day-after-but-skip-sunday.html

02 November 2021 dd-MMM-yy"); info v_Tomorrow.toString("EEEE dd-MMM-yy"); info v_NextDay.toString("EEEE dd-MMM-yy"); // // yields: // Saturday 30-Oct-21 // Monday 01-Nov-21 // Tuesday 02-Nov-21 Note that Sunday is day 1 of the week. Saturday is day 7 of the week. Here's...

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

Zoho Creator/Deluge: UnPredictable exception, Invalid statement foundhttps://www.joellipman.com/articles/crm/zoho/zoho-creator-deluge-unpredictable-exception,-invalid-statement-found.html

17 November 2021 first subform of this record for each r_SubformRow in r_Record.mySubform2 { v_StartTime = r_SubformRow.Start_Time; } } } // yields // UnPredictable exception, Invalid statement found Line:(20) However, I'm not bad at producing errors so here's another...

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

Zoho Deluge: Calculate start and end of Daylight Savings Timehttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-calculate-start-and-end-of-daylight-savings-time.html

07 December 2021 yyyy-MM-dd HH:mm"); info "End Date (Next Year): " + v_DST_EndTime.toString("EEEE, yyyy-MM-dd HH:mm"); Yields: Start Date (Current Year): Sunday, 2021-03-14 02:00 End Date (Current Year): Sunday, 2021-11-07 02:00 Start Date (Next Year): Sunday,...

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

Zoho Deluge: a HTML Entity Decoderhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-a-html-entity-decoder.html

27 January 2022 "Father & Sons"; v_DecodedString = thisapp.DataCleansing.fn_DecodeHtmlEntities(v_TestString); info v_DecodedString; // yields: Father & Sons A more comprehensive list v_HtmlEntityNames =...

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

ZohoDeluge: Inserting a new line character in a CSVhttps://www.joellipman.com/articles/crm/zoho/zohodeluge-inserting-a-new-line-character-in-a-csv.html

01 March 2023 = "Test.csv"; f_CSVFile = l_CsvFileRows.toString("\n").toFile(v_CSVFilename); // // sometimes works but sometimes yields: // Me,Myself,I\na,b,c Works on systems where the above doesn't work: l_CsvFileRows = List(); l_CsvFileRows.add("Me,Myself,I");...

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

Oracle SQL - Convert given list (CSV) into Rowshttps://www.joellipman.com/articles/database/pl-sql/oracle-sql-convert-given-list-csv-into-rows.html

30 August 2011 suffixed a comma to but I want to display this as a rows in a table. This is for Oracle PL/SQL. My List: 1, 2, 3, 4, 5, 6 Yields: 1,2,3,4,5,6 Using this snippet SELECT EXTRACT (VALUE (d), '//row/text()').getstringval () AS AppNo FROM (SELECT XMLTYPE (...

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

MySQL day of week ending on Fridayhttps://www.joellipman.com/articles/database/mysql/mysql-day-of-week-ending-on-friday.html

26 April 2012 WeDate -- OR -- DATE_ADD( givenDate, INTERVAL ( 7 - DAYOFWEEK( DATE_ADD(givenDate, INTERVAL 1 DAY) ) ) DAY ) AS WeDate -- yields -- 2012-04-27 -- this Friday -- 2012-05-04 -- next Friday -- 2012-05-04 -- next Friday Not sure why this works, thinking...

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

Ordered List of over 1000 Itemshttps://www.joellipman.com/articles/web-development/css/ordered-list-of-over-1000-items.html

08 February 2013 So many people asking this when the solution is a bit of aesthetic styling. ol{margin:4px} -- yields 995. 996. 997. 998. 999. 000. 1000 Ordered list resets to zero after the ninth item Ordered List Maximum

• Type: Article
• Author: Joel Lipman
• Category: Cascading Stylesheets
• Language: *

Convert Decimal (Person Days) to Time in Excelhttps://www.joellipman.com/articles/microsoft/excel/convert-decimal-person-days-to-time-in-excel.html

13 February 2013 days = 30 minutes or 0.14 person days = 1 hour. 0.04166667 =(ROUND(B5/0.07, 0)*30)/1440 -- now format to [h]:mm:ss -- yields 1:00:00 0.04166667 =(ROUND(B5/0.07, 0)*0.5)/24 -- now format to [h]:mm:ss -- yields 1:00:00 Note that the first formula is...

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

Zoho Deluge: Push a value to a datetime field in CRMhttps://www.joellipman.com/articles/crm/zoho/zoho-deluge-push-a-value-to-a-datetime-field-in-crm.html

10 September 2021 // OR (2023) v_CrmTime = v_CreatorTime.toString("yyyy-MM-dd'T'HH:mm:ssXXX"); info v_CrmTime; // yields 2021-09-10T12:49:31-07:00 and vice-versa CRM to Creator Depends on the format of the datetime in your Creator field but standard would be: v_CrmTime =...

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

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

04 February 2025 for the road..."; info v_Test4.toLong(); } catch(e) { info "Couldn't convert 'Another...' to an integer"; } } } } // yields: // "Couldn't convert 'Foo' to an integer" // "Couldn't convert 'Bar' to an integer" // "Couldn't convert 'Everyday...' to an...

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

Using a HTML form and PHP to upload a filehttps://www.joellipman.com/articles/web-development/php/using-a-html-form-and-php-to-upload-a-file.html

17 September 2009 . " "; The key of the above array is the name of the variable you passed in your HTML form. If the above yields NO empty data (and size is not 0) then it was successful. If not, then either your system isn't accepting the file type you are trying to...

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

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

04 February 2011 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 (fill and borders) have been applied:

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

Before and After, Highs and Lowshttps://www.joellipman.com/articles/microsoft/ssrs/before-and-after-highs-and-lows.html

03 August 2011 BETWEEN '01/01/2010' AND '08/01/2011' GROUP BY DATEPART(dayofyear, ItemDate), DATEPART(year, ItemYear), ItemName // yields something like ItemName DayOfYear YearRun Counter --------------------- -------------- ------- ------- Report 001 181 2010 134...

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

No rows returned in Oracle causes SP to failhttps://www.joellipman.com/articles/database/pl-sql/no-rows-returned-in-oracle-causes-sp-to-fail.html

24 January 2012 FROM student_accounts WHERE student_accounts.studentID = trim(:p_myparameter) , '???') FROM DUAL; --> Yields error ORA-00936 -- Not Erroring: SELECT NVL( (SELECT student_accounts.student_username FROM student_accounts WHERE student_accounts.studentID =...

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

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 "\t" . $this_fri . "\t" . $this_sat . "\t" . $this_sun . "\t" . $this_t . "\t" . $this_t2;...do some more here ???... } } Yields -- Note this data matches the brief as we've converted the seconds to time. -- Using MySQL to convert rather than PHP so as...

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

Prefix Line Numbers in a Text Filehttps://www.joellipman.com/articles/automation/ms-dos/prefix-line-numbers-to-a-text-file.html

18 July 2012 the maximum length [=number of lines] of the files you will be using this function on). What you should end up with: -- yields Comparing files H:\temp.txt and C:\TEMP.TXT ***** H:\temp.txt 1: This is line 1 2: This is line 2 3: This is line 3 *****...

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