ExplodedKey ( ID ; StartDate ; EndDate )

Combines a text constant (ID) with each date in a date range to make a multi-key that has the text constant on each line

Average rating: 2.5 (195 votes) Log in to vote

Darren Terry - Show more from this author
Pacific Data Management, Inc.
http://www.pdm-inc.com

Sample input:

ID = "Darren"
StartDate = "4/1/2004"
EndDate = "4/3/2004"

Sample output:

Darren 731672
Darren 731673
Darren 731674

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

Case ( StartDate = EndDate ; ID & " " & GetAsNumber ( StartDate ) ; 

Let ( CurrStart = Case ( StartDate + 1 = EndDate ; EndDate ; StartDate + 1 ) ;

ID & " " & GetAsNumber ( StartDate ) & "<cr>" & 

ExplodedKey ( ID ; CurrStart ; EndDate )
)) /*<cr> is the carriage return symbol */

This custom function takes a date range and a text constant (ID) and constructs a multi-key that has the ID on every line. It builds the multi-key to contain each of the dates in the date range from StartDate to EndDate.

This function could be easily modified to allow a text constant to be appended to each line of a return-separated list of values.

Comments

		Michael, Guilford ,CT Aug 23, 2012
Good Morning I have try to use the example for ExplodedKey But it did not work Please advice... Regards Michael Shakaid cell: 925-639-0761 mshakaid@gmail.com

		rtet, tet Jul 7, 2015
etetetrte

		Jerry Friedman, California Aug 18, 2018
Brilliant--thank you. I adapted this for numbers instead of dates and it works marvelously.

		Preben Carlsen, Structure Mar 17, 2023
Hi, interesting function but I can't see to get it working. @Jerry Friedman mentions a solution. Care to share? Or do author Michael have revised code? Thanks

		Preben Carlsen, Structure Mar 17, 2023
Sorry, author is Darren

		Darren Terry, Pacific Data Management, Inc. Mar 17, 2023
Hi Preben: I'm sorry you're having trouble getting the function to work. What seems to be the problem? It works fine for me here in FMP 19.6.3. One thing to realize is that you need to substitute the string "" for the carriage return symbol in FM. This forum wouldn't accept the carriage return (pilcrow) symbol when I entered this function, so I had to do that.

		Darren Terry, Pacific Data Management, Inc. Mar 17, 2023
Ugh. The string "" should be substituted with the pilcrow.

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

ExplodedKey ( ID ; StartDate ; EndDate )

Comments

Support this website.

Support this website.