Hi,

where is the bug in my formula?

I have a TEXT field where I want to enter a TIME,
but as an entry format which accepts

12d3
as 12 days, 3 hours

(that's why I have to use it as a text field)

and time with the format 12:34 as
12 hours, 34 minutes
(instead of 12 minutes and 34 seconds)

So I created a case formula on field "t" which takes

PatternCount(t;":");
Time(
Left(t;Position(t;":";1;1)-1);
Left(Middle(t;Position(t;":";1;1)+1;99);
Position(Middle(t;Position(t;":";1;1);99)&":"; ":";1;1)-1);
0)

That's a simplifed example which ignores seconds.

What's wrong with this formula?
Middle(t;Position(t;":";1;1)+1;99)
takes the content of e.g. 34 from 12:34,
or 34:56 from 12:34:56

I then want to find the position from this minutes-seconds substring up
to the next ":". For that reason I add another ":" to the position
string which makes sure that at least one ":" is found.

I must have got lost somewhere within these nested positions - but I do
not find where that happened.

I can use
PatternCount(t;":");
Let(
[h= Left(t;Position(t;":";1;1)-1);
ms= Middle(t;Position(t;":";1;1)+1;99);
m=Left(ms;Position(ms&":";":";1;1)-1)];
Time(h;m;0)
);
as expected. But I wonder where my error was in the formula before.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2022-06-01 13:39:11 +0000, Martin Τrautmann said:

Hi,

where is the bug in my formula?

I have a TEXT field where I want to enter a TIME,
but as an entry format which accepts

12d3
as 12 days, 3 hours

(that's why I have to use it as a text field)

and time with the format 12:34 as
12 hours, 34 minutes
(instead of 12 minutes and 34 seconds)

So I created a case formula on field "t" which takes

PatternCount(t;":");
Time(
Left(t;Position(t;":";1;1)-1);
Left(Middle(t;Position(t;":";1;1)+1;99);
Position(Middle(t;Position(t;":";1;1);99)&":"; ":";1;1)-1);
0)

That's a simplifed example which ignores seconds.

What's wrong with this formula?
Middle(t;Position(t;":";1;1)+1;99)
takes the content of e.g. 34 from 12:34,
or 34:56 from 12:34:56

I then want to find the position from this minutes-seconds substring up
to the next ":". For that reason I add another ":" to the position
string which makes sure that at least one ":" is found.

I must have got lost somewhere within these nested positions - but I do
not find where that happened.

I can use
PatternCount(t;":");
Let(
[h= Left(t;Position(t;":";1;1)-1);
ms= Middle(t;Position(t;":";1;1)+1;99);
m=Left(ms;Position(ms&":";":";1;1)-1)];
Time(h;m;0)
);
as expected. But I wonder where my error was in the formula before.

I don't really understand what data you're entering or what you're
wanting out of it, but it is VERY VERY easy to mess up complicated
nested functions by accidentally placing the ) or ; in the wrong place,
for example.

Usually I create completely separate calculation fields to extract the different pieces of data. They are much easier to debug since you can
see what each one is actually extracting.

Helpful Harry

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thu, 2 Jun 2022 10:44:14 +1200, Helpful Harry wrote:

I don't really understand what data you're entering or what you're
wanting out of it, but it is VERY VERY easy to mess up complicated
nested functions by accidentally placing the ) or ; in the wrong place,
for example.

I want to enter data such as
t:
3d23 -> 97:00
12:34 -> 12:34
48 -> 48:00

I want to add this data to another field

timestamp = get(currentTimestamp)+t

Actually, I had to use get(currentTimestamp)+GetAsTime(t)

Yeah, nested functions can become VERY easy to mess them up.

But case functions are easy to debug, usually,

by changing

case(

patterncount(t,":"),
mytextfunction(t),
[...]
t)

to

case(

patterncount(t,":"),
mytextfunction(t)
&" x= " & step1
&" y= " & step2,

patterncount(t,":"),
mytextfunction(t),
[...]
t)

It's easy to add the same trigger before, for debugging purposes.

My problem is that my nested text functions do not show the proper ":" position. They should indicate
position("34" & ":",":",1,1) -1 = 2
for left("34",2) = 34
or
position("34:56" & ":",":",1,1) -1 = 2
for left("34:56",2) = 34

Put the position function returns 0.

Usually I create completely separate calculation fields to extract the different pieces of data. They are much easier to debug since you can
see what each one is actually extracting.

But they make it much more complicated when you have to select fields
from dialogs, e.g. for sort, import or references,
even if I prefix each calculation field name with ©.

So I prefer to put much within a single calculation field and simplify
it by defining my own functions.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2022-06-01 13:39:11 +0000, Martin Τrautmann said:

Hi,

where is the bug in my formula?

I have a TEXT field where I want to enter a TIME,
but as an entry format which accepts

12d3
as 12 days, 3 hours

(that's why I have to use it as a text field)

and time with the format 12:34 as
12 hours, 34 minutes
(instead of 12 minutes and 34 seconds)

So I created a case formula on field "t" which takes

PatternCount(t;":");
Time(
Left(t;Position(t;":";1;1)-1);
Left(Middle(t;Position(t;":";1;1)+1;99);
Position(Middle(t;Position(t;":";1;1);99)&":"; ":";1;1)-1);
0)

That's a simplifed example which ignores seconds.

What's wrong with this formula?
Middle(t;Position(t;":";1;1)+1;99)
takes the content of e.g. 34 from 12:34,
or 34:56 from 12:34:56

I then want to find the position from this minutes-seconds substring up
to the next ":". For that reason I add another ":" to the position
string which makes sure that at least one ":" is found.

I must have got lost somewhere within these nested positions - but I do
not find where that happened.

I can use
PatternCount(t;":");
Let(
[h= Left(t;Position(t;":";1;1)-1);
ms= Middle(t;Position(t;":";1;1)+1;99);
m=Left(ms;Position(ms&":";":";1;1)-1)];
Time(h;m;0)
);
as expected. But I wonder where my error was in the formula before.

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.

2. The first part (hours) works fine.
Left(t;Position(t;":";1;1)-1)
Entering 12:34:56 returns 12

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.

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

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.

[ Note: I've highlighted the extra +1 with two #, which need to be removed ]
[ before it will work in a FileMaker Pro calculation. ]

Without the extra +1 that part of the formula, when entering 12:34:56
is trying to get the minutes from ":34:56" sub-text using the first :
it finds, which of course results in the empty text.



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.)

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.


Helpful Harry :o)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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)

On Fri, 3 Jun 2022 19:33:29 +1200, Helpful Harry wrote:

It's very very easy to miss something like that. That's why I tend to
use separate Calculation fields for each step, which I can put on a
"debug" layout to more easily see what is actually being calculated
rather than what I think is being calculated.

One you know it's doing what it is supposed to be doing, you can always copy-paste the formulas over to a nested version if wanted (but making
sure that version is working properly before deleting the separate
ones).

Perfectly true - always a pleasure to have you within these discussions

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2022-06-03 06:03:22 +0000, Martin Τrautmann said:

On Fri, 3 Jun 2022 17:23:28 +1200, Helpful Harry wrote:

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

It's very very easy to miss something like that. That's why I tend to
use separate Calculation fields for each step, which I can put on a
"debug" layout to more easily see what is actually being calculated
rather than what I think is being calculated.

One you know it's doing what it is supposed to be doing, you can always copy-paste the formulas over to a nested version if wanted (but making
sure that version is working properly before deleting the separate
ones).

Helpful Harry :o)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2022-06-03 08:10:20 +0000, Martin Τrautmann said:

On Fri, 3 Jun 2022 19:33:29 +1200, Helpful Harry wrote:

It's very very easy to miss something like that. That's why I tend to
use separate Calculation fields for each step, which I can put on a
"debug" layout to more easily see what is actually being calculated
rather than what I think is being calculated.

One you know it's doing what it is supposed to be doing, you can always
copy-paste the formulas over to a nested version if wanted (but making
sure that version is working properly before deleting the separate
ones).

Perfectly true - always a pleasure to have you within these discussions

Unfortunately, with FileMaker Inc.'s rather silly decision to drop the
ability to make standalone/runtime solutions, FileMaker Pro has become
less popular as a development tool, which is probably why it has become
very quiet around here. :o(


Helpful Harry :o)

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Sat, 4 Jun 2022 10:00:13 +1200, Helpful Harry wrote:

Unfortunately, with FileMaker Inc.'s rather silly decision to drop the ability to make standalone/runtime solutions, FileMaker Pro has become
less popular as a development tool, which is probably why it has become
very quiet around here. :o(

No, it's the general tendency to use web forums instead. Usenet is dead.

To me online talks or web forums suck. They are so ineffective to use.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)