Jump to content

relation dropdown menu


zinneken

This topic is 6059 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

This topic is 6059 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.