Groups | Search | Server Info | Login | Register

Groups > comp.databases.filemaker > #3515

formula question, nested positions

Show all headers | View raw

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.

Back to comp.databases.filemaker | Previous | Next — Next in thread | Find similar

Thread

formula question, nested positions Martin Τrautmann <t-usenet@gmx.net> - 2022-06-01 15:39 +0200
  Re: formula question, nested positions Helpful Harry <HelpfulHarry@BusyWorking.com> - 2022-06-02 10:44 +1200
    Re: formula question, nested positions Martin Τrautmann <t-usenet@gmx.net> - 2022-06-02 08:18 +0200
  Re: formula question, nested positions Helpful Harry <HelpfulHarry@BusyWorking.com> - 2022-06-03 17:23 +1200
    Re: formula question, nested positions Martin Τrautmann <t-usenet@gmx.net> - 2022-06-03 08:03 +0200
      Re: formula question, nested positions Helpful Harry <HelpfulHarry@BusyWorking.com> - 2022-06-03 19:33 +1200
        Re: formula question, nested positions Martin Τrautmann <t-usenet@gmx.net> - 2022-06-03 10:10 +0200
          Re: formula question, nested positions Helpful Harry <HelpfulHarry@BusyWorking.com> - 2022-06-04 10:00 +1200
            Re: formula question, nested positions Martin Τrautmann <t-usenet@gmx.net> - 2022-06-04 09:37 +0200

csiph-web