relation dropdown menu

[zinneken]

I'm trying to make a many to many relationship database as follows:

An address can have several persons

An address can have several companies

when changing an address, the information should be available to each person and company that is on this address

when creating an address, the information should be checked so that there are no 2 addresses the same

So I figured I need a table for the persons, a table for the companies, a table for the addresses and a table that specifies the links between them. I hope so far I'm not off the track with my thinking ...

Then I tried to create a single layout that allows for me to create an address for a person working at a company, and that checks the address is unique.

However, in the layout I created I can't select the company (the dropdown list for the company in the portal of layout "people" doesn't show up).

How could I get this baby going?

v2.fp7.zip

[Fenton]

I think you're on the right track, using a join table. But I think you've overstepped, putting the Company Name field (of the Companies table, enterable!) into the AddressLinked portal of the Person. It seems convenient perhaps; but it doesn't work.

You cannot see the particular company a person works at, of the multiple companies at 1 address, by looking from People thru their AddressLink via ONLY the person ID. You will just see the 1st company assigned to that address. What you would need to do is ALSO specify the CompanyRef, for that AddressLink record. So that's the field that should be enterable in the row, and have the drop-down. Then you can show the Company Name.

You cannot create the Company via this portal. Because the CompanyRef ID must be entered first; and there is no CompanyRef yet. You likely need a small script to create the company.

This is an interesting question, especially the idea that you link people to a company thru the address, but via a dedicated link table record. It may also suggest a method for our recent discussion of the "one table for companies and people."

But I have to wonder. I can see the address table's usefulness in some ways (shopping centers, office buildings with 1 address; but wouldn't they have "suites"; where would you put that?). But you are going to have to be careful with your interface. Otherwise when a company moves, if a data entry person modified their linked address, they would effectively move every business at that address to the new one.

Whereas what they likely needed to do, in the case the Address was shared, was to create a new Address record AND replace its AddressRef into the company's join record. You'll need dedicated routines for data editing to make that happen, while still making it look easy and natural. It ain't gonna just happen in the portal. I'd suggest a little pop-up window.

Edited November 2, 2007 by Guest

[zinneken]

I think you're on the right track, using a join table. But I think you've overstepped, putting the Company Name field (of the Companies table, enterable!) into the AddressLinked portal of the Person. It seems convenient perhaps; but it doesn't work.

You cannot see the particular company a person works at, of the multiple companies at 1 address, by looking from People thru their AddressLink via ONLY the person ID. You will just see the 1st company assigned to that address. What you would need to do is ALSO specify the CompanyRef, for that AddressLink record. So that's the field that should be enterable in the row, and have the drop-down. Then you can show the Company Name.

You cannot create the Company via this portal. Because the CompanyRef ID must be entered first; and there is no CompanyRef yet. You likely need a small script to create the company.

This is an interesting question, especially the idea that you link people to a company thru the address, but via a dedicated link table record. It may also suggest a method for our recent discussion of the "one table for companies and people."

But I have to wonder. I can see the address table's usefulness in some ways (shopping centers, office buildings with 1 address; but wouldn't they have "suites"; where would you put that?). But you are going to have to be careful with your interface. Otherwise when a company moves, if a data entry person modified their linked address, they would effectively move every business at that address to the new one.

Whereas what they likely needed to do, in the case the Address was shared, was to create a new Address record AND replace its AddressRef into the company's join record. You'll need dedicated routines for data editing to make that happen, while still making it look easy and natural. It ain't gonna just happen in the portal. I'd suggest a little pop-up window.

Fenton, Thanks for your comments. I wanted to wait to post here until I figured it all out, but it's taking my little brain more time to implement your suggestions.

Yes, you're right regarding the suites, I kind of had this in mind for the reference table (P.O. boxes, Suites, departments, etc. configured in the link table rather then in the address table itself), but I think that it may be better to make a separate table for this. I'm hopeful that by adding a date function you could keep a history of, when and where (part/department of) a company moves while never having to change an address, just add an address. I'll keep working on this and post samples back when I figure it out.

[zinneken]

... What you would need to do is ALSO specify the CompanyRef, for that AddressLink record. So that's the field that should be enterable in the row, and have the drop-down. Then you can show the Company Name...

An additional question:

Most people, if not all (and definitely me), can't remember what several hundred reference numbers stand for. I understand it is the company ref that needs to be entered (because you could have potentially 2 companies with the same name but in different countries/activities). So in terms of data entry, how can I enter a reference number while I don't know what the reference number stands for without changing layout? For example through a popup window with a popup value list "company names" that puts the "company ref" of the in the popup selected company into a specified field, but how would I do that?

[Fenton]

In Value Lists you have the option to [x] Also show values from a 2nd field. Turn that on and show the company name also. Nearer the bottom there is an option to (•) Sort by 2nd field. Normally you would turn that on also.

However, there is a problem with that. If you choose that, FileMaker uses the index of the 2nd field for the sort. Indexes do not have duplicates. Hence, if you have the same name twice, as you say, then it will ONLY show the 1st one; even though you're also showing the ID.

The cutest option is to [x] Only show values from the 2nd field, so the ID doesn't show at all. Which of course has the same problem.

When you think about it, it would be pretty useless if FileMaker could show both "same names". Because the user would have no criteria to choose between them; since, as you say, the ID is meaningless to them.

So, if you expect duplicate names, as you would if you do not include the Branch location in the name, there really are no good options for using a drop-down list. In this situation I'd recommend a filtered portal instead, on a dedicated "choice" layout; possibly a little window. You type a few words of the name, into a global, and the portal shows the matching results. A portal has the option to show several fields, such as the Branch, or Location; it does not need to show the ID.

Company names present another problem, which is that you may want to match on ANY word in the name, not just the first. So you need a calculation to split the name's words into separate lines. And, you might want to filter out such words as "a" and "the", etc., EXCEPT if they are the 1st word.

Here is an (old) example of a filtered portal.

http://www.fmforums.com/forum/showtopic.php?tid/118628/post/118628/hl//fromsearch/1/#118628

[zinneken]

In Value Lists you have the option to [x] Also show values from a 2nd field. Turn that on and show the company name also. Nearer the bottom there is an option to (•) Sort by 2nd field. Normally you would turn that on also.

However, there is a problem with that. If you choose that, FileMaker uses the index of the 2nd field for the sort. Indexes do not have duplicates. Hence, if you have the same name twice, as you say, then it will ONLY show the 1st one; even though you're also showing the ID.

The cutest option is to [x] Only show values from the 2nd field, so the ID doesn't show at all. Which of course has the same problem.

When you think about it, it would be pretty useless if FileMaker could show both "same names". Because the user would have no criteria to choose between them; since, as you say, the ID is meaningless to them.

So, if you expect duplicate names, as you would if you do not include the Branch location in the name, there really are no good options for using a drop-down list. In this situation I'd recommend a filtered portal instead, on a dedicated "choice" layout; possibly a little window. You type a few words of the name, into a global, and the portal shows the matching results. A portal has the option to show several fields, such as the Branch, or Location; it does not need to show the ID.

Company names present another problem, which is that you may want to match on ANY word in the name, not just the first. So you need a calculation to split the name's words into separate lines. And, you might want to filter out such words as "a" and "the", etc., EXCEPT if they are the 1st word.

Here is an (old) example of a filtered portal.

http://www.fmforums.com/forum/showtopic.php?tid/118628/post/118628/hl//fromsearch/1/#118628

Thanks Fenton, You're of great help (and I'm a little slow on this project).

I tried to find a logical way to still go through a simple popup window, but your suggestion of a filtered portal is the best. However, it requires scripting like I've not done so far and I'm struggling badly. The example helps, but still. At least now I know what to work on.

Will revert back here when I got a script & filtered portal working the way they needs to.

[Fenton]

It doesn't really require scripting to create a relationally filtered drop-down list. It requires a relationship and a value list. Maybe my example is too complex, as it's more for using a filtered portal in a "choice" window. There should be several other examples here on the forums for a conditional value list.

[Søren Dyhr]

It's the second time around in few days where dynamic valuelist are expected or presumed to require scripting, how does this occure??

http://fmforums.com/forum/showtopic.php?tid/191270/post/272185/hl//fromsearch/1/#272185

--sd

[Vaughan]

"It's the second time around in few days where dynamic valuelist are expected or presumed to require scripting, how does this occure??"

My wife, after watching me wrestle a new door into its hinges and fettle it to make it hang correctly, now assumes that every handyman job involves "wiggling" with a screwdriver and hammer.

[Søren Dyhr]

Not the same thing! - Deliberate actions to distinguise one self from a stereotype with planted misunderstanding, isn't the same thing as a parvlovian effect, I do actually here instead sense an eagerness to learn... the later just is slightly mislead, where the former makes shenanigans.

--sd

[zinneken]

Neckbreaking for a week now trying to find a solution to this one.

I'd like to keep relationships based on reference/serial numbers rather then actual names. However, based on a relationship through reference numbers I don't seem to get to show the actual name without using a portal. There must be a more simple way, like using a calculation or other.

Can a value be looked-up or calculated from one table to the next based on a relationship without using a portal?

test.fp7.zip

[comment]

Can a value be looked-up or calculated from one table to the next based on a relationship without using a portal?

It can be looked up, it can be calculated (those two are not the same when the original value is modified) - or it can be simply displayed.

test.fp7.zip

[zinneken]

It can be looked up, it can be calculated (those two are not the same when the original value is modified) - or it can be simply displayed.

My god, so simple and me looking for it for days ... feeling kind of stupid. Thanks comment!