Using a calculated field to access a separate lookup table

Hi,

I wish to use a calculated field to work out the formulation weight of compounds.

In the schema I have a structure table which contains columns for the MW, a salt code and salt stoichiometry. I also have a separate table with unique identifier code for each salt type which has information on MW and Formula.

I am looking to access the lookup table to pull the salt MW into the calculation using the salt code in the first table, but I don't really know where to start. I can access columns within the first table fine, but I don't know the code to access another table to allow me to do this.

I don't want to use a child table as the lookup table will be accessed by further tables so I would like the data in only one place.

Any help appreciated...

Dear Spencer,

 

in your case, there are two possibilities how to be able to create such a calculated field.

I. You will connect structure table and the lookup table by a relationship, which will allow you to use the fields from both table for a calculation. I do understand that you want to use the values from the lookup table for calculations in more tables than just the one structure table and you can do so by binding the lookup table to all the other tables necessary as there is no limitation for a number of relationships for a table.

II. You can create a SQL entity which will contain a SELECT SQL clause such as the following

SELECT APP.PUBCHEM_DEMO.CD_ID AS ID,APP.PUBCHEM_DEMO.CD_STRUCTURE AS "Pubchem_Structure",APP.PUBCHEM_DEMO.CD_MOLWEIGHT AS "Pubchem_MolWeight",APP.WOMBAT.CD_ID AS "Wombat_ID",APP.WOMBAT.CD_MOLWEIGHT AS "Wombat_MolWeight"
FROM APP.PUBCHEM_DEMO
INNER JOIN APP.WOMBAT
ON APP.PUBCHEM_DEMO.ID = APP.WOMBAT.CD_ID

In my opinion, both options have its advantages and disadvantages, I hope you will find these ideas helpful.

 

Best regards,

Karla

As I see it both solutions you propose require that the actual salt indicator is locked in a relationship (foreign key) and hence cannot be null.

I implemented the following trigger in a mySQL-based system to do the same. I use 3 tables for this:

a structure entity (STRUCTURES) with Structure_ID, where the parent weight (cd_molweight) is calculated based on structure drawn

an entity (BATCHES) with batch info (Batch_ID, Structure_ID), where salt data is entered (Salt, Salt_Equivalent), as well as a column for the total Mol Weight (Mol_Weight) calculated by the trigger. Structure_ID is used in a Many-to-One relationship from BATCHES to STRUCTURES. This relationship allows the parent weight from the STRUCTURES table to be reported here

an entity with salt definitions (SALTS), where occurring salts (Salt) and their weight (Salt_Weight) are listed

This ''before insert' trigger only adds the total molecular weight when adding a new row. In order to be able to change the salt and/or equivalents afterwards and reflect this in the total weight, you also need to define a 'before update' trigger using basically the same mysql code

 

CREATE TRIGGER `database`.`MOL_WEIGHT` BEFORE INSERT ON `BATCHES` FOR EACH ROW
BEGIN
declare tmp_weight float;
  set tmp_weight = (select SALTS.Salt_Weight from SALTS, BATCHES
    where SALTS.Salt = BATCHES.Salt and SALTS.Salt = NEW.salt limit 1) * NEW.Salt_Equivalent;

  IF (NEW.Salt_Equivalent is null)
  THEN
     set tmp_weight = 0;
  END IF;

  set NEW.Mol_Weight = tmp_weight +
  (select cd_molweight from STRUCTURES, BATCHES
      where BATCHES.Structure_ID = STRUCTURES.Structure_ID and
            BATCHES.Structure_ID = NEW.Structure_ID limit 1);
END

Best/Evert (with thanks to Mats Dahlberg)