Lookup Based On Other Field 1

[Shanksta]

Seems I'm the only one with questions

I did this in FM6 and cant seem to replicate it in FM7.

Basically what I have:
I have 2 tables, a CUSTOMER table and a PROJECT table. Every project has a customer associated with it. They are related using customer ID which is unique to each customer on the customer table but obviously not in the project table.

What I want to do is this:
CUSTOMER table has a COMPANY field and a NAME FIELD. What I want to do is in the PROJECT layout, have a drop down for all the companies, and then in the contact drop down show only the contacts that re related to that company

Any idea?

 

[Shanksta]

Anyone have any help????????

I need this badly and it seems ridiculous that FM6 was able to do this but not FM7!

 

[JeanW]

Shanksta, I'm still on FM 5, don't have 7 yet....
But I think - believe- if this works on 6 it can work on 7...
Are you sure you pointed to the right relationship ?
Just guessing now....
Because you're talking about custmer 'table' and project 'layout'....
FM 7 is more strict in relationship definition than FM6...

 

[NorthStarDA]

hey shanksta, i wrote a quick example of what i think you're trying to do, its at

http://www.northstarusa.net/examples/dependant_dropdown.zip

its a pretty simple example, 2 relationships, 2 value lists.

let me know if it helps

 

[Shanksta]

NorthStar you own.

 

[Shanksta]

BTW -

Why do you need two seperate relationships versus the one that FM6 used. I saw in the example files they have several instances of tables all over them place. I am used to Access where I never did that.

When would you do that?

Thanks again!

 

[NorthStarDA]

well you have to have 2 relationships because you generating 2 value lists, neither of which have values coming from the table you are viewing the value lists in- 1 relationship to pull the company names, then the name field has to be on a separate relationship company name --> company name to get the list of names for that company.

maybe in fm6 the first value list was not 'related' values, but simply values from field, then only 1 self-join relationship is needed for the second value list. im not sure how you were doing it before so i cant answer that with anything but a guess.

there should be 3 table instances in the example, i put in an extra instance becuase when you relate 2 tables together twice, FM7 likes to combine the criteria into 1 relationship with multiple WHERE statements(the best i know to explain it)-- extra table instances in the relationship view is a quick way to get around that.

im a little disappointed that the relationship view in fm7 resembles access so much, i only think that adds to confusion when it doesn't work the way access does.

 

[Shanksta]

NorthStar -

I found one problem.
I have several contact people for one company. When I assign the project to that person it shows up beautifully on that layout.

When I hit my button to go to that persons contact layout (which has a portal listing current projects) the project does not show up. It seems that FM attaches only the first name on the list to the project.

EX.

This is what I see on one layout:

Project 310
Company: X
Contact Person: 4 (out of 7 contact people)

But when I go to contact persons 4 layout I dont see it!

NOTE:
I found that it does not actually add it to person #1 because they are person number one, rather it just gives the project to the contact person that was entered first for that company. So in the example file you posted, if I assigned a project to jand doe it would give to joe schmoe.

 

[Shanksta]

WAIT NEVERMIND:

It assigns it to everyone from that company!!!!
ARG!!

 

[Shanksta]

Hi again,

I was able to fix the problem with projects showing up for all contacts and I have encountered just one last problem.

When I make a project from the project layout it does not show up in the portal on that contacts page. However when I make a new record on the portal and press my button to go to the selected record it works just fine. Also it will not let me take a project and switch it between one customer and another - if I do that then it shows up in no ones portal.

 

[juststo]

Northstar, I a similar problem. Can you re-post the example. It won't pull up. Thanks.