TOC field access problem...

[ron G]

Here is hoping that the forum has fixed their attachment problem:

 

 

In the Members Layout I have a field called (field--- yes, I know but I can't change it now.  It will screw up my imports).

Members::field collects information from a popup.  It works and the Members::field information shows on the Members layout.

------------------------------------

On a second layout, based on Meetings I have 2 portals:

 

   Portal 1 l is based on VisitorMeetingJoin.  It works.  I can pull information from Visitors as expected.

 

   Portal 2 is l based on MemberMeetingJoin.  It works EXCEPT that I can not get the MembersTOC::Field to show data???

 

Why can't I get information from MembersTOC (A table occurance of Members)

 

Thanks

 

Ron

[ron G]

Got it! 

Sure enough.... no more than 5 minutes after I posted this sitution I 'got it'.

 

In the Value List relationships I had failed to use the MembersTOC:: PK .  Therefore, I suspect, FM could not know which record I wanted MembersTOC::Field to bring over.

 

Now it works like a charm!  Hope this helps someone else who is befuddled and confused...

[LaRetta]

Hi Ron,

Why two tables of people ... Visitors and Members? Why not one table of People with a Type field? Do you not enter FirstName, LastName, Phone etc on both types of people? Rarely do I split people because 1) splitting 'like' values into different tables is almost as bad (relationally) as splitting 'like' values into different fields within same record and 2) most searching in a database involves searching for people.

You may know which table to search if a visitor calls but consider a new receptionist and Jack Smith calls. S/he does not know all staff so first they search Visitors but they are not there then they search Members. Not the best example but what if you want a portal showing upcoming birthdays for Members and Visitors? Better a single portal. Or to send them birthday card?

Point is ... Like things should usually be together as records in a table. Even employee's details I put in single People table and then their salary and other such details reside in Staff table. Just something to think about ... :-)

[ron G]

In retrospect I think you are right.  Simpler is always better. 

Originally, the app was just going to have Members and Candidates.  Then 'feature creep' took hold and Visitors were added.  Because much of the data is different for Visitors and there aren't that many of them I opted to put them in their own table.

 

So, if I read you correctly, I should have had one table (A PEOPLE table) with all the fields for Members, Candidates and Visitors and then just put the appropriate fields for each on their respective layouts?  If so, live and learn.  

 

(My Membership app was a FM learning experience.... ie, a free app that gave me 'real world' experience in FM development)   Thanks for the input.

[LaRetta]

What if Visitors become Members? As is you will need to move their data and potentially any child records as well.

So, if I read you correctly, I should have had one table (A PEOPLE table) with all the fields for Members, Candidates and Visitors and then just put the appropriate fields for each on their respective layouts?

Maybe. Name, address .. Information about a person, yes. Auxiliary Fields are fairly cheap if empty so it usually isn't a problem. If there are a lot of fields for one Type then I might split those aux fields off to a 1:1 table just to keep field definitions compact or if large text fields I will usually store them in another table to keep speed up.

In a way, People can be a supertype with generalized information about Staff, Vendors, customers etc. then other information unique to that entity relates to People. So a Staff table would hold its unique StaffID (auto-enter serial) and PeopleID as foreign key. Staff might hold their Account Name (never password), Title, DepartmentID, hiring information etc. but their people details are held in People.

It is no more difficult ... relate People::PeopleID = Staff::PeopleID and place the People fields directly onto your Staff layout to enter their name and address and then continue adding staff hire date etc. in the other fields. It is called 'cross placing' although I might have made that up ... Learned it a long time ago now, LOL.

Really these changes are pretty simple and since they eliminate moving data if a Visitor becomes a Member, and many other more complex scripting then it is worth the effort to restructure. The deeper you get into design the more complex to change scripts so the sooner the better. I've been stuck with structures, even some of my own, that had to wait until almost complete rewrite.

ADDED. Salary is not kept in Staff in case you are wondering because each pay rate is a record with date and reason for promo etc