4 Replies Latest reply on Feb 3, 2016 10:51 PM by chrislines

# Populating Related Fields Based on Calculation

Good evening Members

Seeking help please on populating related fields based on a calculation.

I have 'tblHeight' with fields kp_HeightID, HeightGroup, HeightClass, HeightName, and HeightDescription.

I have 'tblBonsai' with several fields including kp_BonsaiID, kf_HeightID, number field BonsaiHeightNow, and calculation BonsaiHeightNowCalculation. This calculation determines HeightClass using the following case statement:

Case (

BonsaiHeightNow < 9 ; " Poppy-Seed " ;

BonsaiHeightNow < 11 ; " Fingertip " ;

BonsaiHeightNow < 16 ; " Palm" ;

BonsaiHeightNow < 21 ; " One-Handed " ;

BonsaiHeightNow < 26 ; " One-Handed " ;

BonsaiHeightNow < 47 ; "One-Handed " ;

BonsaiHeightNow < 92 ; "Two-Handed " ;

BonsaiHeightNow < 123 ; "Four-Handed " ;

BonsaiHeightNow < 153 ; "Six-Handed " ;

"Imperial"

)

Result is text. The case statement seems to work and gives the results that I hoped for.

kf_HeightID, HeightGroup, HeightClass, HeightName, and HeightDescription reside in a tab control on layout 'Bonsai_Detail'.

So, I enter a number in BonsaiHeightNow. I get the case result text in BonsaiHeightNowCalculation.

Can I make the case result text populate HeightGroup, HeightClass, HeightName, and HeightDescription with the related data? How would I do it?

Thanks in advance for any assistance.

Regards

Chris

• ###### 1. Re: Populating Related Fields Based on Calculation

chrislines wrote:

Can I make the case result text populate HeightGroup, HeightClass, HeightName, and HeightDescription with the related data?

Short answer: Yes, you can do this.

Longer answer: What you should probably do instead is just establish a relationship between tblBonsai and tblHeight. Then you can simply show the related values on the current tblBonsai tab / layout. This is in keeping with basic database normalization principles - only store data in one place, rather than copying it across tables. (There are exceptions when this is either necessary or beneficial.)

I would also suggest you use a primary key for tblHeight rather than the name. That way, if the name ever changes, the relationship will remain intact.

HTH

Mike

• ###### 2. Re: Populating Related Fields Based on Calculation

Thanks Mike. Great help so far, but I remain unsure about my next step.

I do already have a (one-to-many) relationship between tblBonsai and tblHeight.

tblBonsai

kp_BonsaiID (n)

kf_HeightID (n)

BonsaiHeightNow (n)

BonsaiHeightNowCalculation (c)

... etc

tblHeight

kp_HeightID (n)

HeightClass (t)

HeightGroup (t)

HeightName (t)

HeightDescription (t)

... etc

I can see, via LAYOUT mode, in the tab control on layout Bonsai_Detail the following related fields: HeightClass (yellow icon), HeightGroup (yellow icon), and HeightDescription (yellow icon).

I also see, via LAYOUT mode, in the tab control on layout Bonsai_Detail the following fields: BonsaiHeightNow (which is the field I use to enter number of centimetres) and BonsaiHeightNowCalculation (which determines HeightClass).

I think this follows your suggestion. Still, I can't figure out the process that will make the calculation populate the fields HeightClass (yellow icon), HeightGroup (yellow icon), and HeightDescription (yellow icon). Would appreciate your advice on my next steps.

Thanks again, Mike, for your assistance so far.

Regards

Chris

• ###### 3. Re: Populating Related Fields Based on Calculation

Since I can't see your screen, telling me the icon is yellow isn't particularly helpful.

In which table are HeightClass and HeightGroup? They should be in tblHeight. Just show the fields from that table on the current layout (instead of trying to copy the data across).

• ###### 4. Re: Populating Related Fields Based on Calculation

Thanks Mike for your guidance. I have finally got it working. I appreciated your help.

Regards

Chris