Re: formula question, nested positions

[Helpful Harry]

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

[Helpful Harry]

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)

[Helpful Harry]

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)

[Helpful Harry]

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)