Filling a field with a another field of a matching row

[chizuoka]

Ive got this table with Field B and Field C, and another FieldA on another Table. I want to match FieldC to XXXXX then with the matching record, take fieldB contents and put it in FieldA...

in SQL it would be something like

Insert into FieldA, value from FieldB where FieldC = XXXXX.

So sorry but i think this is something very simple... but my unfamiliarity with the concepts of the scripting in FM is giving me a bit of a learning curve. Hope someone can help.

[bcooney]

Have a look at the Lookup field entry option. It is used to copy a value from one table to another based on a relationship. Usually, you would not want to do this, but rather show the related field. However, I am assuming that your requirement is similar to an Invoice setup, where you might want to copy a price into an invoice line item when a productID matches a productID in a Products table.

[eos]

Is this a one-shot operation to update some missing values, or intended as part of a larger framework? Maybe you could tell us a bit more …

[chizuoka]

its not a one shot... what i want to do is actually this.. i have a table with Contacts. I would like to have a field which states who referred contactA i.e. contactB. Because its on the same table, I used a field which has a value list of the column for contact_Id which is unique. Thus when a user fills in the contact_id of the person who referred him, my system will automatically fill in the name in another column to verify its correct.

Is this the right way to go about doing this?

[comment]

Assuming each contact has at most one referring contact, define a self-join relationship as:

Contacts::ReferringID = Contacts 2::ContactID

Populate the ReferringID with the unique ContactID of the referring contact. To display the details (name, etc,) of the referring contact, place fields from Contacts 2 on the layout.

[chizuoka]

Let me re-explain...

I want to set a referrer (guarantor) for a certain contact. What i did is then

1) In the sales_management form, i allow the Guarantor_ID_no to be filled with a drop down value list consisting of Customer_contacts:ID_no

2) To make sure that this ID_no is correct, i would like to have the user cross check with a name,

3) Thus i would like to get the related Contact_name with the user input ID_No

4) I would like to take that contact_name and put it in to Guarantor_contact

Simple enough in SQL.... using a Where statement. Im stumped on how to do this in filemaker... im a newbie, and i do not yet clearly understand scripting concepts in FM so... the answer eludes me...

I hope someone can help me.

[chizuoka]

Is this possible in FM or do i have to go about doing this in a totally different way?

THanks!

[Ron Cates]

In your drop down, why don't you set your value list to display the name along with the id or instead of the id? And once you have the Guarantor_ID_no selected why not just display the name through a relationship? Pulling the name into another field is rather redundant when the name is available through a relationship using the Guarantor_ID_no. I believe you would create a TO of Customer_contacts and relate it from Guarantor_ID_no to Customer_contacts:ID_no.

Pretty sure comment was saying the same thing.

I think you are asking if this can be done the way you think it should be done instead of , What's the best way to accomplish my goal?

[chizuoka]

I would just like to have a visual confirmation... when i just use value list, it would show up while you are typing.. but when its already been entered.. just the id number would remain... thus i would like to fill a field next to it with the related name. If i were to create a TO and create the rship, what would be the source of the field to display the name? If im not wrong it would show the name of the person for which the record i am currently editing isnt it? Not the guarantor name.

[Ron Cates]

If i were to create a TO and create the rship, what would be the source of the field to display the name? If im not wrong it would show the name of the person for which the record i am currently editing isnt it? Not the guarantor name.

Not if you made the relationship linked to the guarantor ID, then it would show the guarantor name

Then just make sure to use the name field from Customer_contacts2 as in Customer_contacts2::name

Of course you could name Customer_contacts2, Guarators or something that makes more sence. Then use Guarators::name.

[Ron Cates]

You could even add the Customer_contacts2::name field right over the top of the Guarantor_ID_no field on your layout. You can make it non editable so that when you click in it will still be accessing Guarantor_ID_no behind it and triggering the drop down, but after selecting, once populated, the Customer_contacts2::name will show the name and hide the ID number behind it. For some systems this would be prefferable to showing the ID which may not mean anything to some users.