Jump to content
Sign in to follow this  
StrayCat264

Relational value lists

Recommended Posts

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

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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. crazy.gif

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. smile.gif

Best regards,

LaRetta

Share this post


Link to post
Share on other sites

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

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.