Best-Practice: How to set a certain field in each related FileMaker record to a certain value

Question

I have a FileMaker script which calculates a value. I have one record from table A from which a relation points to n records of table B. How to best set the same value for the same field on all those n B-records?

Just doing Set Field [B::Field; $Value] will only set the value of the first of all related records. What works however is the following:

Go to Related Record [Show only related records; From table: "B"; Using layout: "B_layout" (B)]
Loop
    Set Field [B::Field; $Value]
    Go To Record/Request/Page [Next; Exit after last]
End Loop
Go to Layout [original layout]

Isn’t there a better way to accomplish this? I’m sort of rebelling against the fact that in order to set some value (model) programmatically (controller), I have to create a layout (view) and switch to it, even though the user is not supposed to notice anything like a changing view.

Answer 1

FileMaker always was primarily an end-user tool, so all its scripts are more like macros that repeat user actions. It nowhere near as flexible as programmer-oriented environments. To go to another layout is, actually, a standard method to manipulate related values. You would have to do this anyway if you, say, want to duplicate a related record or print a report.

So:

1. Your script is quite good, except that you can use the Replace Field Contents script step. Also add Freeze Window script step in the beginning; it will prevent the screen from updating.

2. If you have a portal to the related table, you may loop over portal rows.

3. FileMaker plug-in API can execute SQL and there are some plug-ins that expose this functionality. So if you really want, this is also an option.

I myself would prefer the first variant.

Answer 2

It depends on what you're using the value for. If you need to hard wire a certain field, then it doesn't sound like you've got a very normalised data structure. The simplest way would be a calculation in TableB instead of a stored field, or if this is something that is stored, could it be a lookup field instead that is set on record creation?

What is the field in TableB being used for and how?

Cheers, Nick