Relational value lists

[StrayCat264]

Apologies if this has already been addressed - it seems to me to be an obvious thing to want to do, but I can't find an answer.

Database A has a list of companies, each with (amongst other records) and company ref, and a main contact ref (both numbers). Database B has a list of contacts. Each contact has (amongst other fields) a company ref (number) a contact ref (number) and a name (text).

What I want to do: when entering the main contact into Database A, I want to present the user with a pop-up menu, consisting of contact names only ('Fred Bloggs, Andy Ellis, Jim Jnr') for example. When they pick, say, Andy Ellis, the corresponding reference number (eg 34) needs storing in the Main Contact Ref field. But the menu field should still say 'Andy Ellis'

I can't find any Filemaker facilities to allow this - any solutions I can find put the contact number in the popup menu & the field. Any suggestions? (Filemaker Pro 6)

Many thanks,

Andy

[Ugo DI LUCA]

Welcome to this Forum StrayCat,

You'd have to use a workaround.

Place 2 fields on layout (global or text).

The first field is the one you currently use to retrieve the company_ID list.

The second field is a lookup from relationship Company_ID::Company_ID that displays the selected Main Contact.

Place the second field just behind the first one. Then set the first one to be transparent (background and text).

When you'd select the value, you will be displayed the Main contact Name and the ID would be masked.

Another method would be to place the second field on top of the first one with no change to the apparance (no transparency). Set the first field with "no allow entry" and make a second field a button with script attached

Go to field (first field).

[reflous]

This work around works really well. The only problem I have with it is I'm using a value list in the field that holds the company_id and in that value list I have to use both the id and the name together in order to get the id into that value. I don't have to look at the ID anymore which is great, but when I select it I still have to select it by ID. Is there a way so that I can select the company by name and look at it by name but still hold the ref id in Database B?

[Ugo DI LUCA]

Possible but risky to work with "long text strings" that couldn't be indexed for a relationship. Besides, are you absolutely sure there aren't any company with same name.

There are workarounds though if the latter is not a problem. It requires a calculation for both side of your relationship, that will extract a text string from the Name field.

Example : The Big Apple Corporation of Wisconsin

---> Calc : TheBigonsin

When you select the Name field, a calculated "global" is populated to match the related record, and the ID (still hided) will come through by lookup.

Finally, if your list is huge, think of switching to a portal instead of a Value List.

[LaRetta]

Hi Andy,

You've explained your situation very well; thanks for making it easy for us!

Another option (if your Contact Names are unique) is to use a new relationship and Value List based upon your Contacts::ContactName and then use a lookup to auto-enter your ContactRef into Companies. If there is the possibility that the names might not be unique (in Contacts), you can create concatenated calc (text, index ON) and add ContactRef to it. Then reference that in your keys, something like:

LastName & ", " & FirstName & " - " & ContactRef

These steps must be performed in this order. It looks long but only because I cover each step. I've attached a simple demo file which is set up exactly as you've described. Open Companies (Database A).

Leave your existing Relationship in place (Companies:MainContactRef to Contacts::ContactRef). Then, all from within Companies, do the following:

Add a text field called ContactName. Select Validation &

ValueListNames.zip

[powerbookstuff]

hi,

[-Queue-]

I find that using _ as a concatenation separator works more nicely than a simple space because each field in the concatenation may already hold a space. It's much easier to identify which is which in, for example, "Company ABC_C123_Abbotsford CA" using Left, Middle, or Right and a Position function to determine where the functions should start/stop, especially if you need to extract info from a value list borrowed from a different file.

[LaRetta]

Hi Filemakerstuff,

Thanks for pointing that out. It would have been good if I mentioned the limitations of key fields in my original post. However, there are spaces. They just don't show, I guess.

There is a space after the comma and a space on each side of the '-'. My purpose was to make the Value List readable to the User - alpha on last name.

LaRetta

[no12Be]

Thank you LaRetta . This is what i looking for.

Now how can i set up that when i click a name then all the name and address fields for that record in the contact list db show up in fields on the menu db. How do I do this? Thanks

[LaRetta]

Hi no12Be,

when i click a name then all the name and address fields for that record in the contact list db show up in fields on the menu db.

You would use a portal to display fields from a related db (Contacts) on a layout in your Main (Companies) db. And you don't need to display the Contacts::ContactRef in the portal.

You might want to consider adding validaton to data entry in your Contacts::ContactRef field to be sure names remain unique, or change your MainKey join calcs by adding the ContactRef at the end, as indicated a few posts up.

So, to have your fields from Contacts show on your Main layout, go to Layout Mode and:

1) Create a portal. File > Insert Portal, and point to your Main Key relationship. This is your Primary Key between MainContactRef (ContactID) and ContactRef (Contacts::ContactID).

2) Show number of rows you wish to display (and select vertical scroll bar if you will have more than can be listed.

3) Resize the portal. Right-Click the portal to change the properties if necessary.

4) Insert > Field and select your MainKey relationship (always match your portal relationships to the field relationships).

5) Double-click the address field. Move it into the portal.

Be sure that your MainKey relationship (in File > Define Relationships) has clicked to 'allow creation of related records'. In this way, when you type into a blank row on the portal, you can add new Contacts from there.

One additional point here ... that demo was created for a specific post and the field names aren't very generic or understandable. Normally, I would call the Main ID field something like ClientID. I created the demo to directly match their field names to make it easier for them.

Best regards,

LaRetta

[no12Be]

Hi LaRetta,

I have try that but i didn't work.

1. I have a "contact list DB", i am using a "Contact management" from FM template. And I have a "Info DB" Which is I created.

2. * I open "contact list DB" then I insert a portal (i don't have any relationship).

* I click "new" and then i select "Info DB" file. I pick a field " customerID" for "contact list DB" and "Info DB". " customerID" field is a record with auto serial for each contact in "contact list DB", i start with "01".

3. Dumb Questions: can i move a portal over a name , address, company field. Or i have to bring a name, address, company over a portal.

4. * open a "info DB" i define a relatioship for this. I click "new" then i open a "contact list DB". I pick a " customerID" for both.

* Go to layout mode. I insert a "customerId" field then i Doing "Field Format". I pick a "popup list" then define value list, i click on new then i click " use value from field" then i click on " specify file" then i choose "contact list DB" then i select a "customerID".

5. I go to browse mode in "Info Db" then i click on "cusotmerID" field it show a "cusotmerID" which is "01" but nothing else.

What did i do wrong, Can you please corrct for me. Thank alots.

Best Regards,

No12Be

BTW: I did create a file in "excel" which is it had a info, buyer contact, supplier contact, company contact, label, account balance, checks, invoice and some fax cover sheet. "All of this informations link together".

Now i try to convert all this informations to Filemaker. Should I create all of that informations in 1 DB like Excel or should i do as seperate DB like contact in contact DB, Info In Info DB, Ect...

If I create a seperate DB are they link together like "excel"

Thank you so much.

No12Be