Newbie Questions

Re: Creating a unique number from Initials Date and Time

Potemkine — Wed, 24 Nov 2010 12:45:18 GMT

What is the type of your field? if it's a date time it's normal you have to set something like "YYYY:MM:DDTHH:MM"

Re: Creating a unique number from Initials Date and Time

oliviar — Wed, 24 Nov 2010 05:46:49 GMT

Thanks for all your help. I ended up getting this to work:

concat(substring(NAME, 1, 1), substring(substring-after(NAME, " "), 1, 1), number(substring(DATE, 3, 1)), number(substring(DATE, 4, 1)), number(substring(DATE, 6, 2)), number(substring(DATE, 9, 2)), concat(substring(now(), 12, 2), substring(now(), 15, 2)))

And this is producing AXYYMMDDHHMM.

However, even after everything is selected correctly and 100% filled in, the field still has those thick red lines around them. Why are they there? Any way to remove them?

Re: Creating a unique number from Initials Date and Time

Potemkine — Wed, 24 Nov 2010 03:20:05 GMT

Hi, it's easier if you have 2 fields one for the firstname and another for the last this formula is not full proof it only work for this pattern:"firstname lastname" concat(substring(field1; 1; 1); substring(field1; string-length(substring-before(field1; " ")) + 2; 1); substring(now(); 3; 2); substring(now(); 6; 2); substring(now(); 9; 2); You have to replace field1 by your name field

Re: Creating a unique number from Initials Date and Time

oliviar — Wed, 24 Nov 2010 02:14:52 GMT

Solution so far:

concat(number(substring(DATE, 3, 1)), number(substring(DATE, 4, 1)), number(substring(DATE, 6, 2)), number(substring(DATE, 9, 2)), concat(substring(now(), 12, 2), substring(now(), 15, 2)))

That is returning the date (from a date picker) and the time(now) as YYMMDDHHMM

The names are formated as "firstname lastname" in the NAME field.

I was able to get the FIRST initial of the persons name (from the NAME field), using: substring(NAME, 1, 1)

I assume I need to use substring after the space to get the first letter of the surname? However I have only been able to get the WHOLE last name from this.

Re: Creating a unique number from Initials Date and Time

Potemkine — Wed, 24 Nov 2010 01:18:55 GMT

Hi oliviar, I help you on the second part of your form because I'm not certain on the format of your name field.

For the pattern:YYMMDDHHMM, you can use:

concat(substring(now(); 3; 2); substring(now(); 6; 2); substring(now(); 9; 2); substring(now(); 12; 2); substring(now(); 15; 2))

Re: Creating a unique number from Initials Date and Time

oliviar — Wed, 24 Nov 2010 01:11:23 GMT

Alright, I worked out a little bit of it myself:

This is date in YYMMDD format:

concat(number(substring(DATE, 3, 1)), number(substring(DATE, 4, 1)), number(substring(DATE, 6, 2)), number(substring(DATE, 9, 2)))

But it will only work if the date comes from a date picker (called DATE in this case)

So I please need help on the TIME (now) expressed as just HHMM and the initials

Creating a unique number from Initials Date and Time

oliviar — Wed, 24 Nov 2010 00:57:20 GMT

I have been trying to construct a formula that take the initials of a persons name (from the 'name' field) and the DATE expressed as YYMMDD and the TIME expressed as HHMM. And concatenated it looks like ABYYMMDDHHMM

I keep ending up with a complete dog's breakfast of either errors or '12-2812:33' or '198,456,44' and other things that look like that.

Please help me construct this number