Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #3518

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

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