relational database - FileMaker - Total SubSummary Values -
i have table records each representing appointment. have name of contactthe appointment with, , date. in table have field contains how many appointments each contact supposed have during day. there 12 entries each contact, because expected have different numbers during different months.
i able call data appropriate contactfor appropriate month. looks great in graph when count number of entries contact , put next expected number of entries related table.
the problem i'm running need add of expected appointments between of entities. so:
::contactname:: ::appointments:: ::expected:: contact 12 10 contact b 33 34 contact c 18 27 getting roll actual appointments easy, simple count summary field in subsubsummary section. of expected? because contacta had 12 appointments means there 12 records them, putting summary field expected column return 120 contact a's. instead, given dataset above, need calculation return 71. issue make sense? appreciated.
if following correctly, need divide amount of expected appointments between entries of group, total result. like:
sum ( entities::expected ) / getsummary ( scount ; entityid ) (this easier if knew names of tables , fields).
p.s. term "entity" has specific meaning in context of relational database. consider using term (e.g. "contacts").
added:
using example data, should see following results in above calculation field:
- in 1st group of 12 records: 10 / 12 = .8333333333333333
- in 2nd group of 33 records: 34 / 33 = 1.0303030303030303
- in 3rd group of 18 records: 27 / 18 = 1.5
when sum (using summary field defined total of calculation field), should 71 (or number near 71, due rounding errors).
note: in above calculation, scount summary field defined in appointments table count of [ field cannot empty ], , entityid field records sorted , grouped (and must local field).
Comments
Post a Comment