A field which calculates data from a subset of records

Discussion created by RobThatcher on Oct 19, 2016
Latest reply on Oct 19, 2016 by RobThatcher



apologies for what must be a very simple question. I am building a database to organise testing data for compounds (university) and would like to be able to calculate compound activities for certain subsets of the data.


To explain more fully. My database is organised as follows;




     - unique compound ID

     - structure

     -activity in cell line A (mean, standard error of mean and n number- 3 fields)

     - activity in cell line B, cell line C etc.


Assay Experiment

     - unique experiment number

     - specific details of experiment

     - One cell line used for each experiment


Assay_Entry (this is a join table between Compounds and Assay Experiment)


     -auto-generated serial no

     - Compound_ID_fk


     -Experimental response (a number)


So when I have entries for compounds I can add an experiment, specify the details and then use a portal to add records to the join table so I have unique assay entries which relate to a specific compound and specific experiment. I can add a portal to the Compounds Layout which only shows assay_entries for that compound and filter so that it shows only entries in a specific Cell Line.


What I would like to do is also to average the experimental response for a given cell line, calculate the SEM and show the number of entries. I know that I can do the average by using a sub summary field but I can't figure out a way to do a custom calculation.


Any advice much appreciated!