Jump to content
Sign in to follow this  
chuckcou

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

Recommended Posts

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: image.png.e86e6c6177dec47330a362aa3898d8c5.png

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 image.png.1e68620f31298306e9d6c8af634ff3a2.png "" then use fk_BoxName to get box Height;  
fk_AltBoxName image.png.1e68620f31298306e9d6c8af634ff3a2.png  "" 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

 

 

image.png

image.png

Sample.fmp12

Edited by chuckcou

Share this post


Link to post
Share on other sites

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. 

 

Share this post


Link to post
Share on other sites

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

image.png.bd64570b9bc71bd73fcc6f0d7c985dd1.png

So this works for one, but not both.

Works if fk_BoxID has a value-
image.png.36468f28ac21e482996d4e64c6311dbe.png

  How would it work if there is a value within AltBoxId.
image.png.0ee853e34525088bf13ab2856b014284.png


My issue still remains:
The catch or issue I am having--Case(
fk_BoxIdimage.png.1e68620f31298306e9d6c8af634ff3a2.png "" then use fk_BoxId to get box Height;  
fk_AltBoxId image.png.1e68620f31298306e9d6c8af634ff3a2.png  "" 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

 

image.png

Sample.fmp12

Share this post


Link to post
Share on other sites

 

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 by comment

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.