How do you suppress the display of a field in a list (for this case)?

I have a 3 tables: Person, Company, and Phone.

I would like to list all people with their phone numbers.

However, I need to format the display so that the name of each person and company appears only once. For example:

Acme Co___Alica Alton_____555-1000___office

_______________________555-0017___mobile

_______________________555-2381___home

__________Andy Andrews__555-1000___office

_______________________555-2381___home

Bixby Co___Barbara Boxer__555-2222___fax

_______________________555-0088___mobile

__________Bobby Baxter___555-0088___mobile

This is easy, if you make the phone numbers appear in a portal and slide the portal up. However, portal sliding works only in Preview mode. I want this to work in Browse mode, so I can place active buttons on the layout.

It turns out you can suppress the appearance of a value if it matches the same field in the previous record ONLY FOR FIELDS IN THE SAME TABLE. If the telephone number is pulled from a related table, it doesn’t work. If the company name is pulled from a related table, it doesn’t work. Suppressed fields must be in the same table.

In a calculation, how do I use the value from a field in the previous record when that field is a related field, not a field defined in the parent layout?

Clue: Neither GetNthRecord() nor Get(RecordNumber)-1 works in this case.

You need a relationship to the previous record's parent, which you can establish by:

GetNthRecord ( ParentID ; Get ( RecordNumber ) - 1 )

Then you can compare the record's parent value to the previous record's parent value.

That doesn't work.

My fault. I should have been more clear. I already tried that.

Your Layout displays fields from the current table A, and a field from a related table B. GetNthRecord() will not show you the related field in the previous record. For example, GetNthRecord(B::MyField; 2) returns the contents from Record number 2 in Table B. But what you want is the value from record 2 of Table A, which is displaying the current found set.

Unless I'm totally misreading it, the FileMaker 8 manual is clear on this. I'm just hoping someone has devised a workaround.

Perhaps you can be even clearer. Your schema above requires the layout to be based on the Phones table. The phones are grouped by People, then by Companies. You want only the first phone record in a group to show the related information from the parent record. Is that correct?

Yes, that’s correct. I’ll try to pare it down to bare bones, minimizing the formatting goal.

Table A ______Table B

======_______=====

Name________Name

Company_____PhoneNo

In a layout in Table B, you can display this list:

Company________Name________Phone

Obviously, you’re grabbing the company name through a relationship with Table A, keyed on the Name.

You can get fancy by displaying this list:

Company________NameSupressed___Phone

NameSupressed is a calculation that looks at the previous record for every line. If the person’s name hasn’t changed, it won’t display it again.

But this list doesn’t work:

CoSuppressed___Name________Phone

Why? Because Company is not in Table B. The nasty surprise is that a calculation requesting the value of Company for Record 2 will get it for Record 2 in Table A, not Record 2 in Table B. The result is an undefined value.

I am sorry, but when you use terms like Table A and Table B, I get confused. I also don't see why you need Name in both tables. Let's keep it simple, with only 2 tables, CONTACTS and PHONES. The relationship is:

CONTACTS:ContactID = PHONES::ContactID

Now, in the PHONES table, define an unstored calculation field PreviousContactID =

GetNthRecord ( ContactID ; Get ( RecordNumber ) - 1 )

Now define a relationship:

PHONES::PreviousContactID = PrevContact::ContactID

PrevContact is a new TO of CONTACTS.

Now you have 2 relationship from PHONES - one to the owner of the phone, another to the owner of the previous phone, and you can compare the two.

Thanks for the time and suggestions, really [he said sheepishly].

I've found a much simpler solution: use Lookup instead.

It doesn't require the creation of any TO or Relationship -- just 3 calculation fields:

A Lookup() that brings in the value of Company from the related table Contact.

A GetNthRecord() -- unstored -- that gets the value of that same field from the previous record.

An If() -- unstored -- that compares the two.

I feel like a moron for not persevering with Lookup() in the first place.