1 2 Previous Next 17 Replies Latest reply on Feb 1, 2013 4:21 PM by philmodjunk

# Calculation script based on a number field

### Title

Calculation script based on a number field

### Post

I'm trying to create a script that will calculate the total number of referrals (volume) from the previous 12 months.  I currently have one table with each record being one year and with each month in a separate field. I can't figure out how to make it work, so I'm assuming that means I've set my tables up wrong.

Just wondering what the best way to do this might be.

I currently have:

Table
Annual

Fields
Vol_Jan, Vol_Feb, Vol_Mar, Vol_Apr, etc.
Vol_Total 12 months

I was hoping to have a calcuation that was basically

Case(Get(CurrentDate)=Jan; Vol_Dec+Vol_Nov+Vol_Oct...Vol_Dec;Get(CurrentDate)=Feb;Vol_Jan+Vol_Dec...)

I can tell it's all wrong but can't tell how to set it up correctly. Please help!

• ###### 1. Re: Calculation script based on a number field

Why not one record for each month instead of one record for each year?

Each record can store the month and year in a pair of fields or you can use a date field with 1 for the "Day" part of the date to record the month and year for that record.

That makes counting your values via relationships, summary fields and aggregate functions pretty straight forward. You probably would not need any script, just a calculation and/or a relationship to get this total.

• ###### 2. Re: Calculation script based on a number field

You don't need a script, just a calculation field that will give you the ytd total on the same record.  If you have all totals for that year on one record then your calculation would be Vol_Jan+Vol_Feb+Vol_March  and so on for the every month.  You wouldn't need a case or if statement because you would just go to that years record.

For example you would have the following fields on your layout.

Year : 2012

Vol _Jan :

Vol_Feb:

Vol_Mar:

.....so on

You could have a quarter 1, 2, 3, 4 calculation also.

YTD :

If you had several years you would just perform a find on the year and then it would take you to that record.

• ###### 3. Re: Calculation script based on a number field

A Calculation wouldn't help because I need the 12 month total on a separate monthly report, and it needs to track the most recent 12 months of volume, not just the year's volume (ie this month would show Dec 2011 - Nov 2012).

I think doing a separate record for each month is what I'll have to do, but I think there's a few conflicts.

1. I need one report to show all 12 months (Jan-Dec) in one portal row (12 months of income is directly below it), and I'm not sure how that's done or if it's possible. My limited experience is only with different fields in one portal row, not repeating the same field.

2. If no referrals are made in a month, no record would be created. Wouldn't this cause it to calculate the last 12 entries, not months? Is there a way to get around this? Data entry will be done on a large scale, so the user doesn't have time to enter "0".

• ###### 4. Re: Calculation script based on a number field

A Calculation wouldn't help because I need the 12 month total on a separate monthly report, and it needs to track the most recent 12 months of volume, not just the year's volume (ie this month would show Dec 2011 - Nov 2012).

This can be done with a calculation and included on your report. The fact that the previous 12 month range will change with each new month is something that can be handled in a relationship.

1. Why would you need it as a single portal row? A horizontal portal method--12 1 row portals placed side by side, each showing the record for one month can give you such a row of monthly figures.

2. You can create a record for the month with a volume of zero via a script that runs automatically, if such is needed but both a date range find--might be used for some reports and a date range based relationship need not have such a "place holding" record. But if you want to chart the numbers, then you'll want the place holder record.

Here's an example of how a calcualtion could give you a total over the previous 12 months:

Add a date field, MonthYear, to your table where the month and year figures identify the month and year and give the day as 1 so that you get a date for the first day of the month for each month.

Define two unstored calculation fields in a table you want to use to link to this set of monthly records for reporting purposes:

cPrevMonth:   Let (  T = Get ( CurrentDate ) ; date ( Month ( T ) - 1 ; 1 ; Year ( T ) ) )
cPrevMonth12: Let (  T = Get ( CurrentDate ) ; date ( Month ( T ) - 12 ; 1 ; Year ( T ) ) )

Define a relationship from this table linking it to your table of Monthly referrals:

Report::cPrevMonth12 < MonthlyReferrals::MonthYear AND
Report::cPrevMonth > MonthlyReferrals::MonthYear

Then you can either refer to a summary field from Monthly totals that computes the toal of Vol, or define this calculation field in Report to get your total over the last 12 months:

Sum ( MonthlyReferrals::Vol )

• ###### 5. Re: Calculation script based on a number field

Never thought of doing multiple portals in a row (adoy!). As for the rest, I'm still new at this so I'll have to try it out tonight and post back later. Thank you!

• ###### 6. Re: Calculation script based on a number field

I'm trying to implement your suggestions, but I can't figure out how to make the day = 1 without the user having to imput the date. I created date field MonthYear and I've made it auto-enter as creation date and attempted to do a calculated value of Day(Get(CurrentDate)=1) but it's returning strange dates.

How do I make the day = 1?

• ###### 7. Re: Calculation script based on a number field

Option 1: Have a number field for month and a number field for year, then MonthYear is a calculation field such as:

Date ( Month ; 1 ; year )

Set to return "Date" as it's return type.

Option 2: Make MonthYear a date field on the layout and use this auto-enter calcualtion to change the day to "1":

MonthYear - day ( MonthYear ) + 1

Clear the "do not replace existing values" checkbox.

You can format MonthYear to display only the month and the year so the user could use the drop down calendar to pick any date and the system secretly changes the day to 1.

• ###### 8. Re: Calculation script based on a number field

Would this still give me the problem of the user having to manually enter the date for each record? Is there a way to use creation date or get current date to make this automatic? Other than the date, the user would never view the record by itself, only in the portal showing all 12 months. I attached an image of the report layout (not sure if my user status will allow it to post). Unfortunately our client requires this precise layout.

If we didn't need the last 12 month total on a separate report, setting it up by year perfectly fits our needs.

• ###### 9. Re: Calculation script based on a number field

Would this still give me the problem of the user having to manually enter the date for each record? Is there a way to use creation date or get current date to make this automatic?

Yes, you can use this auto-enter form of the calculation that I have already posted:

Let ( T = Get ( CurrentDate ) ; T - Day ( T ) + 1 )

• ###### 10. Re: Calculation script based on a number field

After reviewing everything with the users, we decided it would be better to just calculate the volume by year. So now I'm trying to get it working and I'm already stuck again (Argh!).

On my Annual_Office table I need 2 fields. 1 - Current Year Referral Totals, 2 - Previous Year Referral Totals. I basically need to add any referrals together for each separate k_ID_Contact. One office could have 4 dentists and I need to add them all together by year (Year field is located in Annual_Dentist as are the referrals).

I posted a pic of my DB graph. I'm not sure how to get this done. Help would (again) be appreciated.

• ###### 11. Re: Calculation script based on a number field

In which table are you counting referrals?

Without knowing that detail, you can use Year ( DateField ) to extract the four digit year as a number. A calculation field with that expression can be used as a match field to a number field in the related table where you specify a number or it can be an unstored calculation field that use Year ( Get CurrentDate ) ) to use the current year as the matching year value. And you can get the previous year by subtracting 1 from this value.

Table1::gYear = ReferralsByYear::cYear

Is one option and you can include additional pairs of match fields to limit the relationship to those matching a specific value in a field in Table1.

• ###### 12. Re: Calculation script based on a number field

In table Office I am calculating referrals from table Dentist_Annual. The tables are connected: Office -> Dentist -> Dentist_Annual

Both Office and Dentist tables have their own individual serial numbers (with their children matching).

Each Office could have multiple Dentists, and each Dentist will have multilpe Annual Volume Totals (obviously one per year). Each Dentist_Annual record currently has  a Year field in which the user enters the four digit year (ie 2013).

I need one field (Office::Current Year Total) to calculate all related annual totals for the current year in Dentist_Annual.

I need a second field (Office::Past Year Total) to calculate all related annual totals for the previous year in Dentist_Annual.

Okay, I'm learning as I go (after reading File Maker Pro 11: The Missing Manual) and I'm not a developer, so can you explain in simpleton words  how to set up a match field?

• ###### 13. Re: Calculation script based on a number field

First, you can remove Dentist_Annual from your database. You do not need it.

What you do need is one or more new Tutorial: What are Table Occurrences? for your Dentists table.

In Manage | Database | relationships, make a new table occurrence of Dentists by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box. Let's name it DentistsbyThisYear.

We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

Office::K_ID_Contact = DentistsByThisYear::K_ID_Contact AND
Office::cThisYear = DentistsByThisYear::cYear

Define cThisYear as an unstored calculation field with this expression: Year ( Get ( CurrentDate ) )
Define cYear as Year ( Creation_Date )

be sure to select "Number" as the return type for both of these fields.

Now Count ( DentistsByThisYear::K_ID_Contact ) will provide a count of all records created during the current year that link to the current Office record.

You can repeat the same steps, but use cPreYear, defined as Year ( Get ( CurrentDate ) ) -1 in place of cThisYear in the additional relationship.

Note 1: If you have a "count of" summary field defined in Dentists, you can refer to that field to get this same count without adding calculation fields to Office.

Note 2: If you used a global number field in place of cThisYear, you can use a single relationship and yet get totals for any year you enter into this global field. This simplifies your relationships, but limits you to seeing only one such total at a time.

• ###### 14. Re: Calculation script based on a number field

Currently, the Dentist_Annual table holds records for each month's referrals, the quarterly averages, and the annual total and averages for each individual dentist. Each year is a new record.

I will make a duplicate TO if necessary, I just want to be sure we're on the same page. Would your suggestion above provide a Count (as in there were 3 Dentists with referrals) or would it provide the actual sum (as in there were 15 referrals that came from the 3 dentists)? I need the sum.

1 2 Previous Next