11 Replies Latest reply on Apr 11, 2013 9:42 AM by philmodjunk

# calculation worked in FM11 not in FM12?

### Title

calculation worked in FM11 not in FM12?

### Post

Hello all,

I have a formula that is using a relation to return a number from another table.  It worked in FM11.  I've set it up the exact same way.  The tables are related by hour of day (see below).  Then based on what day it is, the calculation returns a value from that day of the week at that hour.  I've tried a number of things, and think the problem is with the relation.  Both fields being related are a number.  Here's the formula:

Let ([

dow = DayOfWeek (Time_calltoqueue_ts)] ;

Case ( dow = 1 ; CoreHours::Sunday ;
dow = 2 ; CoreHours::Monday ;
dow = 3 ; CoreHours::Tuesday ;
dow = 4 ; CoreHours::Wednesday ;
dow = 5 ; CoreHours::Thursday ;
dow = 6 ; CoreHours::Friday;
dow = 7 ; CoreHours::Saturday ; "Error"))

The calculation result is a number as is the value in the other table.  I've tried putting a related field for the day of the week on the Main Table and it comes up blank.  I've tried changing the THEN part of this calculation with a number instead of the related field to test my logic on this calculation and the formula will result correctly.

• ###### 1. Re: calculation worked in FM11 not in FM12?

Have you checked to see if this part of your calculation:

Let ([

dow = DayOfWeek (Time_calltoqueue_ts)] ;

Is assigning the correct result to dow?
• ###### 2. Re: calculation worked in FM11 not in FM12?

Thanks Phil,

I have.  Verified it by putting a string in place of the related field and it returned the string appropriately.  If you follow what I am after with the calculaiton, then another test that I did would make sense.  I took the related field (Tuesday at the 6th hour) and tried to put it in the layout (of the main table) and it came up empty.  By doing this, I am bypassing this calculation to show the data that would be filtered appropriately by the calculation.  What could be wrong with the relation?!

-Geoff

• ###### 3. Re: calculation worked in FM11 not in FM12?

I don't see how that confirms that dow is getting the correct value.

I'd test that in the data viewer (FileMaker advanced) or I'd enter this in it's own calculation field:

DayOfWeek (Time_calltoqueue_ts)

to see if it returns the correct number.

• ###### 4. Re: calculation worked in FM11 not in FM12?

I did it in the data viewer as well.  But what I did was in a record that was on a Tuesday I changed the calcution like this:

Let ([

dow = DayOfWeek (Time_calltoqueue_ts)] ;

Case ( dow = 1 ; CoreHours::Sunday ;
dow = 2 ; CoreHours::Monday ;
dow = 3 ; 1111
dow = 4 ; CoreHours::Wednesday ;
dow = 5 ; CoreHours::Thursday ;
dow = 6 ; CoreHours::Friday;
dow = 7 ; CoreHours::Saturday ; "Error"))

When I went back to the record, this calculation returned 1111, as it was supposed to.
• ###### 5. Re: calculation worked in FM11 not in FM12?

I'd still use a calcuation with

DayOfWeek (Time_calltoqueue_ts)

That would allow me to check the value for this field over many different records at the same time.

And if you use that data viewer in the same context to check the values of each of the CoreHours fields shown in your calcualtion, what do you see?

• ###### 6. Re: calculation worked in FM11 not in FM12?

I've used the calculation in the past with DayName.  I was trying something new.  The problem appears to be with my relationship.  I've tried it with DayofWeek and DayName.  The dataviewer is coming up blank on all of the 'related' fields.  It's a single number (0-23) in both fields that are related. The relation is pretty straight away, not complicated.  I'm sure I'm missing one tiny little thing.

• ###### 7. Re: calculation worked in FM11 not in FM12?

This is the dataviewer from a layout that is tied to the CoreHours table:

• ###### 8. Re: calculation worked in FM11 not in FM12?

DayofWeek returns a number 1, 2 ,3, 4 with a Sunday date returning a 1, Monday is 2 and so forth...

DayName returns text: "Sunday, Monday, ..." to name the day of the week for that date.

I've focused on the value assigned to dow because the field used as the paramater may not always have a valid value and if it doesn't, your calcualtion will fail for certain records.

But it definitely looks like a relationship issue--that's why I asked you to check that part of your calculation. Why the relationship isn't working is your next sleuthing task. It could be an error in Manage | database | Relationships, the table occurrence "context" specified for your calculation or in the data entered in the match fields that are used in this relationship.

• ###### 9. Re: calculation worked in FM11 not in FM12?

So I've checked the relationship (above).  Both fields on either side of the relationship only contain the numbers 0-23 and are defined as number fields.  What else am I missing?  What else can I check?

• ###### 10. Re: calculation worked in FM11 not in FM12?

In utter frustration with this, I made another table, copied the data over to that table, and made the same relationship and it worked.  So for some reason, I couldn't get that initial relationship to work.  I was finally able to get something else set up exactly the same way to work.  Thanks for the help.

• ###### 11. Re: calculation worked in FM11 not in FM12?

Your original file may have been damaged or just the index on that field may have been corrupted.

Try Recover on your original file and see if the recovered copy works. (Do this even if recover does not report fixing any problems.)

If recover does not report fixing any problems and and the copy works, try using advanced recover options to just rebuild your indexes without making any other changes to your file--this is the one "safe" case where I will use a recovered copy.

If you have FileMaker 11 or newer, you can use Advanced Recovery options to rebuild your file's indexes:

1.           With the file closed, select Recover from the File Menu.
2.
4.
5.           Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
6.
7.           The recovered copy of the file will be identical to the original copy except that it has completely rebuilt indexes.

Things to keep in mind about Recover:

While Recover almost always detects and fully corrects any problems with your file...

1.           The recovered copy may behave differently even if recover reports "no problems found".
2.
3.           Recover does not detect all problems
4.
5.           Recover doesn't always fix all problems correctly
6.
7.           Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.

And here's a knowledgebase article that you may find useful: What to do when your file is corrupt (KB5421).