On Fri, 3 Jun 2022 17:23:28 +1200, Helpful Harry wrote:
I think I've got it, but I'm still not sure I fully understand, so
sorry if this is wrong.
1. The above formula does nothing for days "d". I'm guessing
you left that out to simplify the formula for posting here,
so I've ignored that part.
prfectly rigght
2. The first part (hours) works fine.
Left(t;Position(t;":";1;1)-1)
Entering 12:34:56 returns 12
correct
3. The second part (minutes) has an issue, which is obviously
where your problem is.
Left(Middle(t;Position(t;":";1;1)+1;99);
Position(Middle(t;Position(t;":";1;1);99)&":"; ":";1;1)-1)
Entering 12:34:56 returns empty text.
Yes, but why
4. Breaking that down into the separate parts.
a) The first section works fine
Middle(t;Position(t;":";1;1)+1;99)
Entering 12:34:56 returns 34:56
correct
b) The next Middle function is missing the +1, like in a),
to skip over the first : after the hours. Adding in that +1
means it is
Left(Middle(t;Position(t;":";1;1)+1;99);
Position(Middle(t;Position(t;":";1;1)#+1#;99)&":"; ":";1;1)-1)
Entering 12:34:56 then return 34 instead of empty text.
omg, yes - so it did not take the 34:56 part, but used :34:56 instead.
Now I see why it always failed, since it found the initial : first
Possibly that's not a full formula since it won't work with the other
example from you second post of
48 -> 48:00
it instead returns 0:48:00 because FileMaker can't know if "48" is
meant to be hours or minutes. (Or maybe that example wasn't correct
anyway.)
That's why I had to apply both the time(h,m,s) function, which converts
my time to h:mm:ss format, while I can not define a time format for the
text field, which would show h:mm only, and a GetTextAsTime for the
next calculation field.
I think it's a complicated way to enter times. You would probably be
better to either train the data entry person to enter full times in the format dd:hh:mm:ss (e.g. 48 hours would be 0:48:00:00) or by using
separate entry fields for Days, Hours, Minutes, and Seconds, and then combining those.
Of course things can be made more foolproof by splitting data entry to
as many fields as options may be required. But that's for an "advanced"
user, who is myself.
I sometimes permit weired data entry, e.g. for a material database,
which does compare prices per litre, sqare meter and meter, where I can
enter prices with + or - (assuming with or without sales tax, default is
with), prices per lot (e.g. 19,95/5, which is 4 per piece), prices with
square meteres (29,99/qm or 29,99qm or 29,99m�), prices per kg or ton
etc. Currency comes to mind, too.
A typical problem are number entries here, where the local delimiter in
Germany is "," and "." for thousands, while I prefer "." for decimal and
" " for thousands. So a simple custom function translates decimal commas
to dots.
Or I use an isodate function to convert from d.m.y to yyyy-mm-dd. This
does accept d.m as well as "about 1900", "before 1850" etc. So I have to
use text field many times, to be more liberal than the strict field
definitions of time or date fields do permit.
So thanks a lot for taking your time to find this annoying bug here -
not because I needed it, but because it drove my crazy to overlook the
obvious mistake I had made.
Oh, and one of the things I still miss is proper regex handling im FMP -
this could be one of the reasons to upgrade from my ancient FMP11 to a
newer version.
What drives me crazy here is the SEPA banking system, which does add
random blanks within the purpose text.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)