Using Calculated Fields for Status or Resource in FileMaker

Typically, you’ll want to map status and resource to editable fields in FileMaker so that you can reschedule events in DayBack by dragging them to new statuses and resources. However, there are times when you might prefer to use calculated fields instead. For example, you might want to base the color on an If [] statement or combine several fields into one resource field.

This technique is also useful if you want to edit a related value instead of a local field in the event. For example, you might want to change the status of a related job when you're updating the status of the job's delivery record.

Using Auto-Enter Fields

The simplest approach is to make your fields auto-enter calcs instead of using actual calculation fields. This allows the fields to remain editable, while still applying your calculations to clean up any edits.

Using Calculations: changing DayBack's Edit Script

If you can’t use auto-enter calcs, you can map these fields to calculation fields directly. However, you’ll need to modify one of DayBack’s FileMaker scripts to handle what happens when edits in DayBack attempt to modify your calculated field. Without this modification, you would encounter a 201 error indicating that the field can’t be modified.

Script Modification Steps

  1. Locate the Script:
    • Edit the script "Create Edit Delete Event - DayBack" and look for the comment "RESOURCE" around line 196. If you’re working on a status field, look for "STATUS" around line 212.
  2. Choose From Two Options:
    • Ignore Edits: If you want to ignore edits to your calculated field, disable the "Set Field" step immediately following the comment "Set the field."
    • Edit Another Field: If you want to update another field (such as the ID of your resource), modify the "Set Field" step to adjust the desired field instead. Remember that "$value" will be the name of the status or resource selected in DayBack. If you're setting an ID, you’ll need to find the corresponding ID for that name within this script (or in a subscript) before setting the ID field here.

A few things to keep in mind

  • Handling Multiple Values: DayBack supports multiple values for both Status and Resource. When this occurs, "$value" will be a return-delimited list containing those values.
  • Simplifying Field Setting: You don't need to use Set Field By Name unless you want flexibility in pointing DayBack at different tables. Once you’re working within your specific table, you can directly set the field you need.
  • Managing Multiple Calendars: If you have multiple calendars with different resource or status fields, you might want to determine which calendar is active before deciding which field to edit. The script provides access to the $calendarName and $table variables, allowing you to conditionally set fields based on the current calendar. For example, if only one of your tables has a calculated status, you can test for that table and set the job’s status instead of the delivery status when working with job-delivery records. Here's an example script snippet for such a scenario: