CODEWITHSUNDEEP
filemaker
Greg
Greg

Reputation: 47164

FileMaker - How to have a calculated field reference two tables sort of?

Hiy guys,

Let's say I have these three tables*:

ClownOrders
------------
_pk_order_id
status

ClownOrderLines
----------------
_fk_order_id
_fk_clown_car_id
amount

ClownCars
---------------
_pk_clown_car_id

I want to provide a calculated column in the "ClownCars" table that shows the total amount for all orders for this car, where the order status is not "canceled".

Thoughts so far

I created a new calculated field in "ClownCars" called zz_g_canceled that simply contains the constant "canceled".

Then I created a new calculated field in "ClownOrderLines" called c_orderstatus that is ClownOrders:status.

Then I created a new table occurrence of "ClownOrderLines" called "OpenClownOrderLines" and linked it to "ClownCars" by this criteria:

ClownOrderLines._fk_clown_car_id = ClownCars._pk_clown_car_id and ClownOrderLines.c_orderstatus != ClownCars.zz_g_canceled.

Finally I added this calculated field to ClownCars called totalOrdered which is sum(OpenClownOrderLines::amount)

But the new column "totalOrdered" always shows "?" now.

*subject matter changed to better hold your interest

Upvotes: 1

Views: 2349

Answers (1)

Paul Hutton
Paul Hutton

Reputation: 236

You cannot use unstored calculated fields on the downstream side of a relationship.

In this case, you are trying to look at the OpenClownOrderLines table occurrence from ClownCars with OpenClownOrderLines::c_orderstatus in the relationship.

You could make c_orderstatus a stored value (a Text field, or Stored Calculation) but would then have to be careful to update any order lines whenever an order's status changes.

(For example, if status changes are scripted, you could just include it in the script, but this risks forgetting if you ever set the status any other way.)

A better approach is to have the order line table calculate the amount, counting it as zero if the order has been cancelled.

Then the car table can safely sum all order lines, knowing that any cancelled ones won't add to the total:

ClownOrderLines::c_amount_if_open = If ( ClownOrders::status ≠ 'Cancelled' ; amount ; 0 )

ClownCars::totalOrdered = Sum ( ClownOrderLines::c_amount_if_open )

Upvotes: 3

Related Questions