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