Missing portal rows

[lorenb]

I'm pretty new at this stuff but have a layout for Companys (Companys table) with a portal for contact names (People table) related thru a CompanyName field in each table.

Problem is, in a company with multiple contact names, only the first name (first when viewing People in Table View) shows up in the portal. When I first set it up all the names were showing but since then (several days) something has changed.

I have double checked the name spellings in both tables and the relationships.

I need help.

lorenb

[LaRetta]

Hi lorenb,

Table view does not show portals; it would always only show the first person according to the sort of the relationship or by natural creation order if the relationship isn't sorted ... portal sort is ignored in table view because portals are ignored in table view. [color:red]*

BTW, I would suggest you create unique, auto-enter serial numbers for your companies (called a primary key in your company table)and use that to relate the tables as:

Companies::CompanyID = People::CompanyID or, as many name it:

Companies:__pk_company_ID = Persons::_fk__company_ID

Surprisingly, companies can be bought out or renamed and your relationships will break if based upon Company name. Every table should have a unique, auto-enter serial beginning with pk (meaning primary key) and then the related tables would contain an fk for that company (meaning foreign key), if the need the company information.

If you have further questions, please ask away ... there are many wonderful people on this forum who are willing to assist!

UPDATE: [color:red]* I have not verified if this is still true in vs. 11 but I believe it is.

Edited March 31, 2010 by Guest
Added update and re-phrased a sentence for clarity

[efen]

Base your relationships on unique identifiers/serial numbers, which are not seen and cannot be "changed" in any way, rather than on names

[lorenb]

You're right, LaRetta, I should have a PK, and I will.

What I meant is when in form view on the Companys table the portal to the People table only shows one name, and that is the first name in table view of the People table. Hope that makes sense.

I have double checked the spelling in both tables and, as I said, it worked before.

lorenb

[LaRetta]

Hi lorenb,

If on form view in Customers then the fields in your People portal are probably too high. If they hit the portal top line (or are even 1 px above it) then you will get only one row showing. Grab the fields and drag them down a few px and check again.

If the fields are above the portal then it is the same as if you placed the child fields directly on the form layout.

[lorenb]

Hey LaRetta, that did it!

I did tweek the portal a little and left the fields just a couple pixels too high.

Thanks again. Maybe you can help me on another problem. This solution is a port from an Access file that I have developed over about 10 or so years. On the same form view (Companys) as we have been discussing I have 5 portals - People, Contact History, Ticklers (to do's), Quote Headers and Quote Detail.

In Access the current portal record is indicated by a little right-pointing arrow head. FMP doesn't give me any indication which is the current record in a portal (I assume it's the first row). I want the Quote Detail portal to show only the records that match the quote number for the current Quote Header record.

In Access I run a sub() that sets a field on the main form to the current Quote Header record quote number then the Quote Detail portal displays records that match that number.

Can I do something similar with FMP?

Thanks, lorenb

[LaRetta]

Here is a sample of how you can handle it. Notice that the final selection of the header detail doesn't need a portal because the result is always only ONE final matching record.

Check conditional formatting on the fields. Also note the auto-enter (Replace) on the global quote detail in Customers. If script triggers are available, they can properly clear the globals when the records are changed otherwise you should control record navigation via script and clear the gQuoteHeaderID yourself. If you don't clear it and you change records, it could display data from the selected header in the center portal.

CompaniesDemo.zip

[lorenb]

Thanks LaRetta, I'm going to work on this over the weekend and will report back.

lorenb