Calculation Based on Today's Date

[RaeC]

I'm using the FM Starting Point database under FM 13 Advanced for Mac.

 

As a freelancer, I track my monthly earnings very closely to ensure that I meet a target income goal for the month. I've been using a very lightweight app called 'Savings Jar' that lets me set a monthly target, enter earnings daily, and then displays how much I need to save per day to stay on track for this goal.

 

It's working well, but is redundant and I can't do anything else with this data that I am tracking anyway in FM Starting Point.

 

In FM Starting Point, I create a 'project' that is the current earning month. I then link all my invoices for the month to that project and I am able to get a running tally of my income for that month. I added an extra field in the project invoices tab to calculate where I am to my goal in relation to the income earned.

 

What I'm missing is the the calculation based on today's date of how much I need to earn per day to stay on target.

 

So if it's February 24th, I have four days left to earn the balance of my monthly goal, ie. monthly goal balance/4. The next day, I need that calculation to automatically update to monthly goal balance/3, then monthly goal balance/2, and so on. Is there a way to do this in Filemaker? I'm a basic user, but am able to muddle my way around so if this is doable, I'd need at least a hint of how to go about it.

 

I'm attaching an image that will hopefully make my question clearer.

 

 

Thank you,

 

Rae

[comment]

How exactly do you associate your "project" record with a month? If you have a field (of type Date) that holds the date of the first day of the month, then you can calculate the number of remaining days as=

Let (
monthEnd = Date ( Month ( MonthStart ) + 1 ; 0 ; Year ( MonthStart ) )
;
Case (
Get ( CurrentDate ) < monthEnd ;  monthEnd - Get ( CurrentDate )
)
)

and divide the balance by this number. Note that the calculation must be unstored.

[RaeC]

I just call the project a month and do all my transactions for that month as being tied to that project. The only date fields are that I can input a start and end date to the project:

 

 

I think I understand what you're saying with the calculation you provide. I will play around with it. Thank you!

 

Rae

[RaeC]

Looks like this is going to work exactly as I needed it to. Thank you so, so much!

 

Rae

[Lee Smith]

<strong class='bbc'>Automatic message</strong><br /><br />This topic has been moved from "FileMaker 13 General Discussion" to "FM Starting Point - General Discussions".

[comment]

The only date fields are that I can input a start and end date to the project:

 

You could have those calculated automatically from an auto-entered serial number. If, for example, your first record is for January 2014, you could make :

 

cMonthStart =

Date ( MonthID ; 1 ; 2014 )

and:

 

cMonthEnd =

Date ( MonthID + 1 ; 0 ; 2014 )

This is of course assuming that starting from the first month, you will have a record for each consecutive month.

[RaeC]

This is of course assuming that starting from the first month, you will have a record for each consecutive month.

 

Which isn't the case, unfortunately. I have some projects that aren't tied to the months.

 

But the calculation you gave me was more than enough to start on. I need to tweak it a bit to get it working perfectly, but considering my utter lack of FM skills, I am thrilled with how far it has gotten me! Thanks again!

 

Rae

[Richard Carlton]

Yes... FileMaker is a fun tool...and this is a great community. - RC

[RaeC]

I only got into FM because I was frustrated by the lack of invoicing software for the Mac market and FMSP seemed to have the most robust and customizeable solution. I was right. I'm only now, after over a year of playing with FMSP, starting to make fuller use of all its features.

 

Rae