I 'suspect' this is a relationship problem ... ?

[ron G]

I am running FM 12 v1 Advanced in OSX....

My app has these tables:

Members:

PK_MemberID

FK_TypeID

LastName

FirstName

... etc

MemberType:

PK_TypeID

FK_MemberID

MembershipType (holds text as in 'Regular', 'Honorary' etc...)

YNMMDues (User selects 'Yes' or 'No')

YNPMDues

etc..

Each member can only have 1 membership TYPE at a time. Each membership type is held by many members.

\(Sounds like 1 to many to me)

I have a layout based on Members.

I have a portal on that layout based on MemberType

Problem: If I have a cartesian (X) join between the portal and the underlying table, the portal will show records from the underlying table. But, with a cartessian join, "Allow creation of records in the underlying table..." is grayed out.

If I create another TOC instance of Members (Members 2) and run the portal off that instance it works as expected. But, if I attach the same relationships using Members, it does NOT work? Huh.

If I try and produce a 1 to many relationship, (based on TYPEID or MemberID, the records in the portal disappear (but remain in the underlying table in a different layout? ?)

I can get this to work with a cartesian join by scripting a add/edit/delete situation to affect the underlying table in a different layout. But, it seems so "kludgey".

I would like to have the user to be able go the blank row in the portal and just add a record... like any other portal relationship.

What am I missing?

(I know this is a complex situation but I post this i the hope that someone will have had a similar situation)

Thanks for reading

Ron

[comment]

Each member can only have 1 membership TYPE at a time. Each membership type is held by many members.

(Sounds like 1 to many to me)

it sounds like a many-to-many to me - but perhaps I don't understand the "at a time" part. Usually, "one membership type at a time" means "many membership types over time".

[ron G]

it sounds like a many-to-many to me - but perhaps I don't understand the "at a time" part. Usually, "one membership type at a time" means "many membership types over time".

Sorry for not being clear. Each member has, as part of their membership information a popup that shows membership Type that comes from the fields in the MemType Table. As members progress through the organization their membership Type changes. For example, a new member might have a Type of "Yearly". But, after several years he may buy a life membership. In this case, his membership would change to "Life Member". Yes, a member can have ONE membership at a time but may have severeal membership Types over the life of his membership.

Does this situation fit the need to have a many to many? Or, is it more like a farmer can have many pigs but a pig can be owned by only one farmer (at a time) ... resulting in a one to many?

[comment]

Each member has, as part of their membership information a popup that shows membership Type

IIUC, the member's type is given by the type of his/hers most recent membership. Therefore, it should be calculated, not selected.

Does this situation fit the need to have a many to many?

I believe so. You are looking at something like:

Members -< Memberships >- MembershipTypes

where Memberships is also serving as a join table between Members and MembershipTypes. This is assuming there are fields in MembershipTypes that uniquely describe a type; otherwise you could settle for a value list.

[ron G]

I don't see a way to have the Membership Type 'calculated'. 

 

When a member chooses a membership Type, there are no other factors other than that members 'choice'.  Once the member 'chooses' the Type, the secretary selects it from a dropdown.

 

How can this be 'calculated'?

[comment]

When a new membership is created, the secretary can select the type of that membership - not the type of the member. Otherwise you are liable to have a discrepancy between the two.

[ron G]

When a new membership is created, the secretary can select the type of that membership - not the type of the member. Otherwise you are liable to have a discrepancy between the two.

For example, the membership Types are:  Yearly, Honorary, Life.  When a member joins he usually starts by choosing a Yearly membership.  However, after several years of making yearly dues, the member may choose "Life" membership (which is an annuity which exempts yearly dues from being paid).  That choice can be made at any time. 

 

Because the membership Type can be changed at any time, I don't see how to make it 'calculated'?  What would be calculated 'on'?   In words, what would the calculation equation look like?  I am confused by the proposition to make the Type calculated....  thanks

[comment]

In words, what would the calculation equation look like?

 

Assuming the relationship follows chronological order, it could be =

Last ( Memberships::Type )

[ron G]

The relationship is a equality relationship:  PK=FK.

[ChuckEye]

If a user only has one member type at a time, wouldn't it make more sense to use a related field rather than a portal? I'm relatively new to this, but my understanding was that portals were best used for the many side of a one-to-many.

[doughemi]

I assume the portal is used because ron G wanted to see a historical record of the status of the member. (At least that's why I use a portal in MY membership solution.)