A relationship, getting data with if statement between two fields choices

[chuckcou]

I have attached a sample database as this is hard for me to explain.  But I will give it a go.  Any help, I would be very, very grateful as I can not figure this one out.

Outline:  Two Table-Product and Boxes
Primary Key:Boxes:pk_BoxName
Foreign Key: Product: fk_BoxName and fk_AltBoxName
Relationship: 

What I am trying to achieve--In the product table I am trying to fill in the Width, Height, Length by way of a calculation.

The catch or issue I am having--Case(
fk_BoxName  "" then use fk_BoxName to get box Height;  
fk_AltBoxName   "" then use fk_AltBoxName to get box Height;
"Error")

The issue is I need to check fk_BoxName and fk_AltBoxName use for the relationship.  Only one can have a value else "Error"

What I have tried:
Case ( 
fk_BoxName ≠ "" ; boxes::Height;
fk_AltBoxName ≠ ""; boxes::Height;
fk_AltBoxName ≠ "" AND fk_BoxName ≠ ""; "ERROR"
"ERROR"
 )
I also tried lookup for my calculation without any success.  My guess is lookup might be the way to go, but so far no go.

I have attached a sample database with both table and the above Case in the Product:Width.

Any help or suggestion would be very appreciated.  😎

See-->File Maker Pro File--> Sample.fmp12

 

 

Sample.fmp12

Edited November 27, 2018 by chuckcou

[comment]

I don't think this is a good starting point.

First, your relationship should be based on a meaningless BoxID, not anything that could possibly be modified later, thus breaking the relationship.

Next, the BoxName field in Boxes should hold the name of the box, and nothing else. You have fields for the dimensions of the box and that's where they should be stored.

Finally, you do not need to duplicate any of the fields in Boxes (other than BoxID) in the Products table. The information from the parent record in Boxes - including the box name audits dimensions - is easily available through the relationship. 

 

[chuckcou]

Ok, I get what you are saying.  So something like this would be better--

So this works for one, but not both.

Works if fk_BoxID has a value-

  How would it work if there is a value within AltBoxId.

My issue still remains:
The catch or issue I am having--Case(
fk_BoxId "" then use fk_BoxId to get box Height;  
fk_AltBoxId   "" then use fk_AltBoxId to get box Height;
"Error")

With the new method, I can see  Width for those products that have a value for fk_BoxId.  But if fk_BoxId is blank and there is a value in fk_altBoxId then the relationship needs to use that value.  (this was why I thought I would need width, height, and length within the product table.  I was thinking I could do a calculation with an if statement.)

New file attached Sample.fmp12

 

Sample.fmp12

[comment]

 

I am not sure I understand the basic situation here. If I am guessing correctly, you need to use two TOs of the Boxes table, instead of (or in addition to) two TOs of the Products table. Then you will be able to calculate, from the context of Products, something like =

Case ( 
not IsEmpty ( fk_BoxId ) ; Boxes::Height ;
not IsEmpty ( fk_altBoxId ) ; Boxes 2::Height ; 
"Error"
)

 

Or, if you prefer =

Lookup ( Boxes::Height ; Lookup ( Boxes 2::Height ; "Error" ) )

 

 

Edited November 27, 2018 by comment