Field which displays another record's field contents (same table)

[cruijff]

Hi guys, I have a problem I'm struggling with:

 

I have a table named Manufacturings (parent key field = Manufacturings::manID)  which has a field named Manufacturings::man_prev, which displays the unique other manID (entered manually) with whom the Manufacturing is linked to for warehouse reasons.

 

For example:

 

Table: Manufacturings

 

field1 = manID (manuf_012) - parent key

 

field2 = man_prev (manuf_010) - entered manually

 

field3 = warehouse_qty (1200)

 

 

I would like to create on the table Manufacturings (e.g. manID = manuf_012) a field which shows the warehouse_qty of manuf_010, i.e. field 4 = linked_qty

 

I know I could make a long script along the lines of:

 

set variable $var = manufacturings::man_prev

go to layout (manufacturings)

performs a find on field manufacturings::manID using $var

set variable $qty = warehouse_qty

go to layout (manufacturings)

set field manufacturings (linked_qty = $qty) 

etc..

 

but I'd like to know if there's a faster (and more clever) way to do this, maybe via a calculation field.

 

I hope my post is clear..thank you very much!

[LaRetta]

 

I would like to create on the table Manufacturings (e.g. manID = manuf_012) a field which shows the warehouse_qty of manuf_010, i.e. field 4 = linked_qty

 

but I'd like to know if there's a faster (and more clever) way to do this, maybe via a calculation field.

 

Faster and more reliable than script is to use a relationship.  If you go to your graph and create a second table occurrence of Manufacturing then join them as:

 

Manufacturing::manID = Manufacturing 2::man_prev

 

Then select your linked_qty (be sure and specify it is the from Manufacturing 2) and place it directly onto your Manufacturing layout.

 

If I have misunderstood your need, please say a bit more about your existing setup.  It feels like it might be still denormalized but I cannot say for sure without seeing your file.  We'll help you out regardless.  :-)

[cruijff]

Thank you LaRetta, I'll give it a try! I don't know why I didn't think about this..

[Rick Whitelaw]

Man_prev is manually entered however. I would never allow a user-entered field to be a match field between tables.

[LaRetta]

I understand your concern, Rick, but we ( every day ) enter IDs which are used in match fields … that is how we select the foreign key in most of our tables.  :-)

 

Added … however, I was assuming a pop-up was used for ID selection so you are correct in cautioning against free-text entry of an ID.

Edited November 13, 2013 by LaRetta

[Rick Whitelaw]

Using a drop-down list in the process of creating records in a related table and effectively "manually" selecting, or creating, a foreign key is something we all do. Text entry of the FK is, for me at least, a no-no. Simple text entry of any field in a related table from the parent layout creates the FK automatically in any case. That's what I aim for, whether or not via script.

[LaRetta]

But I do not believe we are creating related records but rather cruijff is simply assigning a foreign key ( man_prev) to an existing record in the Manufacturing table.  That is a completely acceptable action although pop-up should be used as mentioned or otherwise validate/protect the id.  

[comment]

create a second table occurrence of Manufacturing then join them as:

 

Manufacturing::manID = Manufacturing 2::man_prev

 

Shouldn't it be the other way around?

[LaRetta]

Table they are on is Manufacturing and the value they wish to display is the value from the ...

 

oops. You are correct, thank you.  

[cruijff]

Thank you all guys.

 

As comment pointed out the correct relationship is Manufacturing::man_prev = Manufacturing 2::manID

I created a field named linked_qty and set it as related record showing the field Manufacturing2::warehouse_qty and all seems fine. (edit: is there another way to do so? I experienced problems in the past displaying a related record on other related tables).

 

With "manual" insertion I did not mean that the FK is entered actually manually , I meant to say it is not an automated act, I have set it as pop-up from a value list, no worries!

[comment]

I created a field named linked_qty and set it as related record showing the field Manufacturing2::warehouse_qty and all seems fine. (edit: is there another way to do so?

 

I don't see why you can't simply put the field Manufacturing2::warehouse_qty on your layout (assuming this is for display only).

[LaRetta]

Then select your linked_qty (be sure and specify it is the from Manufacturing 2) and place it directly onto your Manufacturing layout.

 

The ability to 'cross place' fields is extremely powerful.