Jump to content

Auto-update calculation field?


This topic is 2844 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I'm new to filemaker Pro 13, well to Filemaker full stop.

 

I entered a calculation field that having put the date for the next MOT [Vehicle check]... it will compare that date to the current date and then tell me how many days I have left until the MOT needs to be renewed. I can then use this in a portal on my Dashboard layout that gives an overview of things in the business.

 

The trouble is the calculation field doesn't automatically recalculate unless I enter and exit the "date field called "MOTDue", but obviously I want it to update as one day changes to the next or at least when I open the program.... but probably the former.

 
 
Here's the calculation wrapped in a logical function.
 
If ( MOTDue - Get(CurrentDate)  < 0 ; "Overdue by "  & MOTDue - Get(CurrentDate)  &  " days"; "Due in "   & MOTDue - Get(CurrentDate)  & " days" )
 
This should give me the days coming up to an MOT... then the days overdue if it runs over.
 
Any thoughts?
Link to comment
Share on other sites

1 ) You need to set the calculation field to unstored.

2 ) You could simplify the calculation as

Let (  
numDays = MOTDue - Get ( CurrentDate ) ; 
Case (   
  numDays = 0 ; "Due today" ;  
  Case ( numDays < 0 ; "Overdue by " ; "Due in " ) & numDays & " days"  )
)

which will also give you a nicer message for MOTdue = today.

Link to comment
Share on other sites

Hi Karlos,

 

Seeing a result of plural when singular result always looks odd to me ( 1 days ) as does a minus ( Overdue by -3 days) so you might wish to consider these small tweaks:

Let ( [
numDays = MOTDue - Get ( CurrentDate ) ;
days = Abs ( numDays )
] ;
Case ( 
  not numDays ; "Due today" ;
Case (
  numDays < 0 ; "Overdue by " ;
  "Due in " ) & days
& " day" & Case ( days > 1 ; "s" ) 
)
)

Link to comment
Share on other sites

1 ) You need to set the calculation field to unstored.

2 ) You could simplify the calculation as

Let (  
numDays = MOTDue - Get ( CurrentDate ) ; 
Case (   
  numDays = 0 ; "Due today" ;  
  Case ( numDays < 0 ; "Overdue by " ; "Due in " ) & numDays & " days"  )
)

which will also give you a nicer message for MOTdue = today.

 

That certainly looks tidy Eos and it works, thanks! Not the first time you've helped me out... appreciated.

 

Hi Karlos,

 

Seeing a result of plural when singular result always looks odd to me ( 1 days ) as does a minus ( Overdue by -3 days) so you might wish to consider these small tweaks:

Let ( [
numDays = MOTDue - Get ( CurrentDate ) ;
days = Abs ( numDays )
] ;
Case ( 
  not numDays ; "Due today" ;
Case (
  numDays < 0 ; "Overdue by " ;
  "Due in " ) & days
& " day" & Case ( days > 1 ; "s" ) 
)
)

Hey LaRetta... that work too, appreciated, I'm spoilt for choice here, but how to I learn these function myself, is there a course or book you can point me to?

Link to comment
Share on other sites

FM Help, first and foremost; read the entire thing through at least three times at first.  Secondly, live here at FMForums.

 

Although I had Fortran and Cobol background and had prior relational training, I was just as green as anyone else coming to FileMaker.  FMForums has been my training ground.  When someone posted, I would follow what they need and think about the problem and create calculations, reading the functions in Help to see what might handle it.  Then when someone answers, I would break their calculation down into its parts, reading FM Help about each function used and creating calculations of each part (now we have Data Viewer).  I also asked a lot of questions (and still do).

 

In my opinion, FMForums is the best training ground because, without a problem, a solution is useless and it won't stick as well.  There are some great training opportunities around; you can Google search for them.  Richard Carlton and Tim Dietrich both have beginner training, I believe.  You might also contact a developer you like and see if they can take you on, one-to-one.  Taking a newbie one-on-one allows us to gear training to their speed and background and puts them on the fast track.  

 

And never be too proud to ask a question here if you don't understand.  Stephen started this forum so that we can help each other and folks here are very nice and wish to assist so take advantage of it. :-)

  • Like 1
Link to comment
Share on other sites

 I'm spoilt for choice here

 

Unsurprisingly, both LaRetta's and my version employ the same logic, but you should use hers, since she went the extra mile by catering for singular/plural and getting rid of the sign for a negative number – something I forgot when writing my version from memory without testing it.

Link to comment
Share on other sites

FM Help, first and foremost; read the entire thing through at least three times at first.  Secondly, live here at FMForums.

 

Although I had Fortran and Cobol background and had prior relational training, I was just as green as anyone else coming to FileMaker.  FMForums has been my training ground.  When someone posted, I would follow what they need and think about the problem and create calculations, reading the functions in Help to see what might handle it.  Then when someone answers, I would break their calculation down into its parts, reading FM Help about each function used and creating calculations of each part (now we have Data Viewer).  I also asked a lot of questions (and still do).

 

In my opinion, FMForums is the best training ground because, without a problem, a solution is useless and it won't stick as well.  There are some great training opportunities around; you can Google search for them.  Richard Carlton and Tim Dietrich both have beginner training, I believe.  You might also contact a developer you like and see if they can take you on, one-to-one.  Taking a newbie one-on-one allows us to gear training to their speed and background and puts them on the fast track.  

 

And never be too proud to ask a question here if you don't understand.  Stephen started this forum so that we can help each other and folks here are very nice and wish to assist so take advantage of it. :-)

 

Thanks LaRetta.

I have instigated your calculation and it works great but do not yet understand how and why, thus I will take your advice and break it down.

I'm certainly glad I posted on FMForums first, for not only are my issue resolved quickly but members are willing to suffer the novice, apprecited.

Link to comment
Share on other sites

Unsurprisingly, both LaRetta's and my version employ the same logic, but you should use hers, since she went the extra mile by catering for singular/plural and getting rid of the sign for a negative number – something I forgot when writing my version from memory without testing it.

Thank you Eos.

And having taken your prompt I have instigated LaRetta's code which works great.

 

Thank you for your time and the other fixes you have not only offered but which quickly resolved other FM issues.

Link to comment
Share on other sites

Hi Karlos,

 

This is an informal website and it isn't required to quote someone's entire post.  As for the calculation, here is a jump start on it:

 

----------

Let() gives you the opportunity of declaring temporary variables.  You would want to do this if you have functions or pieces that you need to use more than once.  In your opening calculation, for example, you had:  MOTDue - Get(CurrentDate) three times.  So instead, we made up a variable name (numDays) which only has to be evaluated ONCE and then we can refer to IT in the calculation.  This improves efficiency and decreases the clutter/length in a resulting calculation.  Same applies to the Let() variable called days - since we reference it more than once, we create a sandbox variable of it up in Let().

 

Let looks like this when you have more than one variable to declare:

 

Let ( [

temp variable = whatever ;

temp2 variable = something else 

] ;

here you put the actual Let() calculation

)

 

Case() is just like If() except it allows more than two options.  We use Case() mostly for simplicity because it is more flexible.  So in human speak, the rest of the calculation would read as:

 

Case ( test ; result ... and there can be several tests and several results

 

Case ( not numDays )

translates to ... If there are no number of days, produce "Due today"

otherwise run next test

Case ( numDays < 0 ; "Overdue by ; "Due In" ) ... "Due In" is the default result

& days & " day" ... this is called a concatenation which simply means the ampersand joins two parts together

----------

 

As for whose code you use, we all realize we can't always think of everything every time we respond.  We count on each other to fill in if we miss something or if they have better/different ideas, we WANT others to step in and provide alternatives; in this way we ALL learn.  It is the Master Mind principle and a community is greater than any one individual, if they are free to speak up.

 

I discovered FMForums in 2002 when asked to design a solution for a large business that needed a cross-platform database.  I still remember asking my first question and I have never left.  I read the other forums also but this is the best forum out there - without doubt. :-)

  • Like 1
Link to comment
Share on other sites

Hi LaRetta

Hi Karlos,

 

This is an informal website and it isn't required to quote someone's entire post.  As for the calculation, here is a jump start on it:

 

----------

 

I was getting ready to say this too.

 

I would add to this.

 

Karlos, thank you for making a post that showed that you made an effort to workout it out before you posted. Not only did you give it an appropriate subject line, you did a good job in describing what you felt was not working,  and then you posted what you had tried.

 

Good Posting!

 

:yay:  :chili:  :yay:  :chili: 

 

Lee

Link to comment
Share on other sites

Hi Karlos,

 

This is an informal website and it isn't required to quote someone's entire post.  As for the calculation, here is a jump start on it:

 

 

 

Thanks LaRetta, your expounding of 'Let' and 'case' have been very helpful indeed... and I shall file them both and refer to them often until they sink-in.

Appreciated.

 

Karlos, thank you for making a post that showed that you made an effort to workout it out before you posted. Not only did you give it an appropriate subject line, you did a good job in describing what you felt was not working,  and then you posted what you had tried.

 

 

Thanks for the encouragement Lee, I'll try to keep that format but can't promise to get it right every time. :)

Link to comment
Share on other sites

This topic is 2844 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.