2 MonthSort Fields in the Same File?

[ArKay]

I have a layout that contains a Month field calculation MonthName(Date) and a MonthSort field calculation Month(Date) to find the creation date of all orders for a year, and display the total by month in a sub-summary by Monthsort. I want to add another search that finds the invoice date of all orders instead, so as an experiment I changed the (Date) to (InvoiceDate). It worked, but I'm unable to figure out a way to create a second set of MonthSort calculations at the same time as the original (Date). It seems as if I can't use anything like Month2, or MonthSort2 because Filemaker doesn't recognize the fields in a calculation. Is there a way to have two Month fields and two MonthSort fields in the same file, or another way to format the calculation?

Thanks.

[comment]

I can't use anything like Month2, or MonthSort2 because Filemaker doesn't recognize the fields in a calculation.

Why not?

[ArKay]

If I try this:

MonthSort2=Month2(InvoiceDate) I get a message that says "This function cannot be found", yet MonthSort=Month(Date) or MonthSort=Month(InvoiceDate) is recognized. It doesn't like the 2.

[comment]

It doesn't like the 2, because the name of the function is Month(), not Month2(). You can append a number to a field name, e.g. Month ( Date1 ) and Month ( Date2 ) - but the function is still the same.

BTW, you don't really need to calculate the month's name - you can custom-format the date field in the sub-summary part to display only the month's name. Also, the calculation Month (date) ignores the year, so records may not sort/group properly. I prefer to use =

Datefield - Day ( Datefield ) + 1

for this purpose, with result type set to Date.

[ArKay]

It doesn't like the 2, because the name of the function is Month(), not Month2(). You can append a number to a field name, e.g. Month ( Date1 ) and Month ( Date2 ) - but the function is still the same.

BTW, you don't really need to calculate the month's name - you can custom-format the date field in the sub-summary part to display only the month's name. Also, the calculation Month (date) ignores the year, so records may not sort/group properly. I prefer to use =

Datefield - Day ( Datefield ) + 1

for this purpose, with result type set to Date.

First of all, thanks for the speedy replies.

My files are set up for individual years, if that makes any difference in the way the calculations need to be formulated. In any case, I know that the calculation works when I change (date) to (invoicedate) but I'm still not clear on how I could use the same Month or Monthsort formulas twice. Since it won't recognize Month2(), how is the formula written using something other than Month?

Edited September 1, 2010 by Guest

[comment]

I am not sure where your difficulty lies. Say you have two date fields, named Date1 and Date2. And two calculation fields named Month1 and Month2.

Month1 =

[color:red]Month ( [color:blue]Date1 )

Month2 =

[color:red]Month ( [color:blue]Date2 )

I have colored function names [color:red]red, and field names [color:blue]blue. Filemaker allows you to name a field same as a function - this is convenient, but can be confusing at times.

My files are set up for individual years

That's something worth examining.

Edited September 1, 2010 by Guest

[ArKay]

One of the problems I was having, among many, is that this layout was created over 15 years ago, and I don't always remember why something is set up the way it is. You were exactly right. I was confused because the field name Month was also being in used in a formula, and I thought it was referencing the field name, as some formulas do. In this case it's just coincidental that I have a field name that is also used in a specific formula, and the field could be called Fred and the formula would still work. Now I get it.

I also finally figured out why the new formulas using (invoicedate) weren't working after checking them and rechecking them. It was my script. I had made a duplicate of the original that I used to "find orders when written", and forgot to make one minor change so it couldn't find "orders when billed". Only took me two hours to track it down, but at least I learned something while doing it.

As for having separate files for each year, I've been doing it this way since 1990. I've always felt that for the way I work it was easier to have orders from a particular year in one file. At the end of the year I just save a copy as a clone with no records and start again.

Thanks again for steering me in the right direction.