left and rightwords on date and time fields

[stefangs]

a lot of calculations (and scripts too) busted for me when i moved files from fp5 to fp7 format, simply because the leftwords and rightwords function does not longer evaluate the same way.

for example, in fp5, the expression 10/5/2011 is three words, but in fp7 its only one word.

in time fields, it gets even better: in the fp5 world a time is three words, but in fp7, depending on the users system settings, the time might be 11:06:43 PM (two words) or 23:06:43 (one word).

maybe i don't see the forest for the trees, but how do you go about fixing calcs that depend on the proper number of words when you can't even be sure how many there will be?

thanks,

stefan

[comment]

how do you go about fixing calcs that depend on the proper number of words when you can't even be sure how many there will be?

That's a rather wide question. Speaking specifically about date and time: there is no good reason to apply text functions to data whose type is date or time. This was just as true in version 5 as it is now.

[stefangs]

That's a rather wide question. Speaking specifically about date and time: there is no good reason to apply text functions to data whose type is date or time. This was just as true in version 5 as it is now.

of course, that's a point well taken, but i often insert date or time in calculated text fields. sometimes, only part of that info is all i'm interested in. of course, there are better ways to do that.

in one case that i'm looking at right now, it has to do with the fact that changing a record, and adding a timestamp in a time field, lets me lookup data which i couldn't look up on record creation, because it depends on something being entered in another field.

less cryptically speaking, i want to look up a price in a line item based on quantity, which of course is entered only when the record is already created. so i use an extra relationship that looks up the price once the timestamp changes. somewhere in that formula only part of the timestamp was used as a key, hence the text-style manipulation of the time field.

its been so long ago and i have to really wrap my head around this code contraption again to see what makes it work, but i found the culprit to be the fact that the right/leftwords function does not work anymore the same way it did in FMD6.

stefan

[comment]

less cryptically speaking, i want to look up a price in a line item based on quantity, which of course is entered only when the record is already created. so i use an extra relationship that looks up the price once the timestamp changes. somewhere in that formula only part of the timestamp was used as a key, hence the text-style manipulation of the time field.

"less cryptically speaking" - LOL.

IMHO, if you want to look up a price based on quantity, then Quantity needs to be one of the matchfields of the relationship. Don't see what role a timestamp would play here.

[Vaughan]

To follow on from Comment regarding the mis-application of text functions to date (and time) fields, consider this:

Dates are stored internally as the number of days since 1 Jan 0001 -- Date( 1 ; 1 ; 1 ). So internally they are a number. FMP displays the date as a text string in the format specified by the field on the layout or the system preferences, so it will change on different computers: mm/dd/yyyy or dd/mm/yyyy or yyyy/mm/dd or who knows what.

If you need the month, use Month( date ). If you need the year use Year( date ). If you insert the date into a calculated text field and need it to be in the format 1 Jan 2011 then explicitly create the expressions to do this, otherwise it could change on different systems.

[stefangs]

If you need the month, use Month( date ). If you need the year use Year( date ). If you insert the date into a calculated text field and need it to be in the format 1 Jan 2011 then explicitly create the expressions to do this, otherwise it could change on different systems.

yes, that's exactly what i'm doing now. it's just a lot of converting of old habits!

stefan

[stefangs]

"less cryptically speaking" - LOL.

IMHO, if you want to look up a price based on quantity, then Quantity needs to be one of the matchfields of the relationship. Don't see what role a timestamp would play here.

i was asking myself that too. slowly it's coming back to me... consider this scenario: someone buys a quantity of 3 units of something during a project and books a room for a day. so i enter a quantity of 3 in the supplies line item and 1 in the room rent line item. customer gets a printout of that if he wants to. the next day he books again, but doesn't need any supplies. so on and so forth.

after a week or so i'm ready to write the invoice. meanwhile the customer has purchased 12 items of that supply thing and booked the room for 6 days. say i give a discount for the supply thing at a quantity of 10+ and for the room rent at 5+ days.

on invoice creation i want to give the discount, but because each quantity in the line items is less than the required discount quantity, i can't look up based on quantity.

therefore i have a script that i'm trying to figure out again. basically it groups all line items together by type and calculates the number of similar items to see if that triggers a discount, based on a lookup from the supplies table. to do that, i need to look up again, but without disturbing the quantity in the line items itself. somewhere here is where the timestamp came in. it forces a relookup of the total quantity and the discounts can be applied, if any.

quite a monster for my level anyway. it worked fine in fp5 but has broken in fp7 because of the text processing of date/time.

stefan

[comment]

somewhere here is where the timestamp came in

I am afraid I still don't see it. If you have a script to "finalize' the invoice, you could make it calculate the discount - with or without a relookup. See also:

http://www.filemaker.com/11help/html/scripts_ref1.36.55.html#1029621

[comment]

BTW, not that I think it's a good solution, but if you wanted a quick fix for the change in the way words are counted in version 7 and higher, you could replace:

LeftWords ( text ; 1 )

 

with:

 

Let ( [

fixText = Substitute ( text ; [ "/" ; " " ] ; [ ":" ' " " ] )

] ;

LeftWords ( fixText ; 1 )

)

[stefangs]

BTW, not that I think it's a good solution, but if you wanted a quick fix for the change in the way words are counted in version 7 and higher, you could replace:

LeftWords ( text ; 1 )

 

with:

 

Let ( [

fixText = Substitute ( text ; [ "/" ; " " ] ; [ ":" ' " " ] )

] ;

LeftWords ( fixText ; 1 )

)

that will make my kludge even kludgier - i love it! i think it's great for a quickfix though and actually something i was hoping for.

when the time comes for a complete rewrite i'll dump a whole bunch of things anyway. this whole invoice script thing, though it has worked, never really felt proper, so it's a good time to think about it and do it in a more elegant way. watch for more forum questions when the time comes. if it hasn't happened by 2018, you're all off the hook, however :-)

thanks again,

stefan