The following expression queries the Depth Related Exploratory Hole Information table and queries whether the current location type is Cable Percussion, if it is, it will return CP, if it isn’t, it will then query if the location type is Rotary Core, if it is, it will return RC, else it will return other.
if([DepthRelatedExploratoryInformation.Type]='CP','CP',if([DepthRelatedEx-ploratoryInformation.Type]='RC','RC','Other'))
The following expression is slightly more complicated as it queries the Field Geological Descriptions table and queries whether the child record in the soil or rock tables is soil, if it is, it will return Soil, if it isn’t, it will then query if the child record is Rock, if it is, it will return Rock, else it will return Other.
if(countrecords([Aggregate.FieldGeologicalDescriptionsSoil.AdditionalDescription]) > 0, 'Soil', if(countrecords([Aggregate.FieldGeologicalDescriptionsRock.AdditionalDescription]) > 0,'Rock','Other'))