Have you checked to see if this part of your calculation:Let ([dow = DayOfWeek (Time_calltoqueue_ts)] ;Is assigning the correct result to dow?
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?!
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:
to see if it returns the correct number.
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:
I'd still use a calcuation with
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?
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.
This is the dataviewer from a layout that is tied to the CoreHours table:
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.
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?
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.
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:
- With the file closed, select Recover from the File Menu.
- Select "Use advanced Options"
- Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
- 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...
- The recovered copy may behave differently even if recover reports "no problems found".
- Recover does not detect all problems
- Recover doesn't always fix all problems correctly
- 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).