Groups | Search | Server Info | Login | Register

Groups > comp.databases.filemaker > #3516

Re: formula question, nested positions

Show all headers | View raw

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

Back to comp.databases.filemaker | Previous | Next — Previous in thread | 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