AnsweredAssumed Answered

How to use calculation with values from related table

Question asked by david1981 on Oct 1, 2018
Latest reply on Oct 2, 2018 by user19752

I have a table A where user puts in values from 0 - 60 in field Aa, and field Ab is a calculation between two time stams resulting in a number in minutes. Based on these two values I want to calculate field Ac.

In table B (related table) I have 209 records that is fixed and not editable by users, this table consists of fields Ba with numbers of increments of 3 from 6 - 60 (6, 9, 12, 15..., 60), field Bb with various times in minutes, field Bc containing a letter from A - Z.


There is several records with Ba 6, each containing different times in minutes. So for example:

record 1 in table B: Ba = 6, Bb = 25, Bc = A

record 2 in table B: Ba = 6, Bb = 40, Bc = B

record 18 in table B: Ba = 12, Bb = 35, Bc = F


The task I want to accomplish:

record in table A: Aa = 4,3, Ab = 23, Ac = Calculation: Find closest Ba record rounding up (6) (this should result in a list of all records in Table B with Ba = 6), Then in that list find closest number in record Bb rounding up ( should result in a unique record with Ba = 6, Bb = 25), Then get value Bc in this record and but it in Ac.


Is this posible in filemaker, maybe I have over complicated this, but this functionality is crucial to my project. Any help is appreciated