Groups | Search | Server Info | Login | Register

Groups > comp.databases.filemaker > #3519

Re: formula question, nested positions

Show all headers | View raw

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. 

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