script to set value of related fields?

[Archit3kt]

I've got a "companies" table related to a "contacts" table via these fields: (companies::k_ID_company) related to (contacts::kf_ID_company)

In the "contacts" table I created a "Company Name" field that's a drop-down menu, with the values list from (companies::company_name). I want to make a script (triggered by selecting a company from the list) that finds that company's record in the "companies" table, takes the value from the ::k_ID_company field, and sets the value of contacts::kf_ID_company to match.

In effect, the user would create a new record in "contacts", then select what company the person works for, and upon selecting that company it assigns the correct k_ID number so it shows that company's record in a portal. what commands should I use? Thanks for the help!

[comment]

Why not select directly from a value list of company ID's, also showing the company name? Then you won't need a Company Name field in Contacts.

[Archit3kt]

That sounds way better I'm wondering about one thing, I want to allow user to both create a new record in (companies: or select a pre-existing record from a list of the ones already there, so I made a portal showing (companies: from the record detail view in (contacts:. I can see that making a new record would just be typing into a field in the portal, and trigger a script upon record creation in (companies: to get the value from (companies::k_ID_company) and copy that value into (contacts::kf_ID_company) to match so the records relate...but how should I handle giving user a drop-down list of companies in addition to that? which would also copy from (companies::k_ID_company) to (contacts::kf_ID_company)...

[comment]

I am afraid I don't quite follow, esp this part:

...but how should I handle giving user a drop-down list of companies in addition to that? which would also copy from (companies::k_ID_company) to (contacts::kf_ID_company)...

Creating a new company is one thing, selecting an existing company is another. I'd suggest you make this distinction very clear to the users as well; for example, they could select from a portal showing all (existing) companies, and have a "Add" button underneath the portal.

[Archit3kt]

Creating a new company is one thing, selecting an existing company is another. I'd suggest you make this distinction very clear to the users as well; for example, they could select from a portal showing all (existing) companies, and have a "Add" button underneath the portal.

exactly, right on mate. I know how to script an "add" button (or the user could simply start typing into the fields of a blank record, and I'll script it to relate the records in the background). But what I still don't understand is how to script it so the new record in (companies: is related to the current record in (contacts:, you know, like in contacts:: to notes:: in the starter solution......wait.....wait......OMG I can just open the starter solution and STEAL THAT SCRIPT......dohhhhhh

[Archit3kt]

ok, so here's where I am....I like the "Add Note" script in the starter solution, I can see how it relates records:

if Contacts and Notes are related through the field ID_Contact it sets the value from that field in the Contacts table into a local variable, and then it creates a note in the Notes table, and pastes that value into it's ID_Contact field, and the note and the contact are then related.

my relating tables are Contacts and Companies, via a field that's an auto-generated ID number. If I wanted to do it exactly like the starter solution, the user would be viewing a record in Companies, and through a portal they create a new record in Contacts, and the script copies the ID number into the new record, relating them (just like the Add Note script). BUT I want the user to be viewing a record in Contacts, and they create a new Contacts record from there, and after creating it they select what company the contact is with, and the script needs to look in that "chosen record" and copy the ID number into the Contacts record's field to relate it.

[comment]

If I wanted to do it exactly like the starter solution

I don't know what "the starter solution" is.

[Archit3kt]

Here's the "Add Note" script for the template solution. I want to modify it, but don't know the right commands to swap out. The script is based on a two-table database, with "Contact Management" and "Notes" tables. The relationship is between Contact Management::k_ID_Contact and Notes::kf_ID_Contact. A single record in Contact Management may have any number of related records in the Notes table, displayed through a portal when viewing record detail in Contacts. here's the script for the "Add Note" button in that layout:

==============================

Set Variable [$ID_Contact; Value:Contact Management::k_ID_Contact]

Freeze Window

Go to Layout ["Notes_Utility" (Notes)]

New Record/Request

Set Field [Notes::kf_ID_Contact; $ID_Contact]

Commit Records/Requests [skip data entry validation; No dialog]

Go to Layout [original layout]

Go to Field [Notes::Text]

==============================

I want a script that does almost the same thing. Let's continue with this example instead of the Companies/Contacts one I said before. this is what I want:

1) The user would be viewing in Record Detail mode in the Notes table, NOT the Contact Management table, and they create a new record.

2) At this point the new record is blank, and not related to any records in the Contact Management table yet.

3) I want a button, or a script triggered by a drop-down list field being confirmed, that allows the user to choose an existing record in Contact Management from which to "get value of related field", and paste it into its related field.

4) with my limited knowledge of this stuff, I'm imagining a drop-down list that has the values from something like a "Contact Name" field in the Contact Management table (so it's always updated if we add new records in that table) and upon selecting one of those, a script will "get value" of the relating field in the record the user chose, and paste that value into the new, blank record, thus relating them. This must be possible! Sorry for the long-winded questions, I'm not real experienced here.

[comment]

I want a button, or a script triggered by a drop-down list field being confirmed, that allows the user to choose an existing record in Contact Management from which to "get value of related field", and paste it into its related field.

Assuming you have this relationship (as you should have):

Contacts::ContactID = Notes::ContactID

the simplest method is to format the Notes::ContactID field as a drop-down list or a pop-up menu, using values from the Contacts::ContactID field, also displaying values from the Contacts::Name field. No scripting or "pasting" is required.

[Archit3kt]

Assuming you have this relationship (as you should have):

Contacts::ContactID = Notes::ContactID

the simplest method is to format the Notes::ContactID field as a drop-down list or a pop-up menu, using values from the Contacts::ContactID field, also displaying values from the Contacts::Name field. No scripting or "pasting" is required.

golden!! how do you make a list use the ContactID field values but also display the values from contacts::name field?

[comment]

See:

http://www.filemaker.com/11help/html/edit_layout.10.24.html#1029124