Number of records in set?

Title

Number of records in set?

Post

Newbie here -- well, plenty of programming experience, but new to Filemaker and having lots of problems with FM's scripting language.

Here's what I want to do in plain English. I have an Invoice table with two fields for Client and Contact. The Client field gets its data from the related Client table, and the Contact field gets its data from the Contact table that is related to the Client table. When I create a new invoice, I select a name from the Clients popup. This action fills the Contacts popup with only those contacts related to the selected client. I then select a contact from the Contacts popup. All well and good and working as expected.

But if the client I select has only one Contact associated with it, I'd like the contact field on the Invoice layout to be automatically filled with the contact name from that single Contact record. Saves a bit of effort.

How can I determine the number of records in a current set of related records? Get (Found Count)? If so, then where do I find and how do I use the returned value? The following doesn't work, probably because I am not specifying the related table for the found count. (Should be the Contacts table.)

Get ( foundcount ) counts the records in your current layout's found set of records, not a set of related records in another table.

Count ( contacts::SomeFieldThatIsNeverEmpty )

will count the related records in contacts. A "count of" summary field defined in contacts could also be used to return the number of contacts.

On trick you might consider is to use auto-enter field options on Contact to enter data from the contacts table. This will enter data from the first related record in contacts. If there is only one related record or the first related record is the desired contact, there will be no need to select a contact--and no scripting was needed to do so.

Get ( foundcount ) counts the records in your current layout's found set of records, not a set of related records in another table.

Count ( contacts::SomeFieldThatIsNeverEmpty )

will count the related records in contacts. A "count of" summary field defined in contacts could also be used to return the number of contacts.

On trick you might consider is to use auto-enter field options on Contact to enter data from the contacts table. This will enter data from the first related record in contacts. If there is only one related record or the first related record is the desired contact, there will be no need to select a contact--and no scripting was needed to do so.

Try using both of Phil's ideas and bring over to the invoice layout a calculated Label from the contacts file, as Contact(3) or the like, where the Auto-enter would default to the field. The label would be calculated in the Contacts Table"

Thanks for the replies, guys! Started with the auto-enter trick. Works just fine. Also adds a name when the related set includes multiple records, however, so I'd like to try the other suggestions, too. Would prefer that no name be entered when the related set contains multiple records.

An auto enter calculation can be set up that counts the relate records and only auto-enters a name if the count is 1.

If ( Count ( Contacts::ID ) = 1 ; Contacts::ID

I tried entering that equation (changing ID to Contact) as a "Calculated value" in the Auto-enter section for the Invoices::Contact field, but it didn't work. No name was entered/changed in the Invoices::Contact field. Did I misinterpret where the equation should go or how it works?

By default, if test refers to a field that doesn’t yet contain a value, If returns an empty result. To override this functionality, deselect the Do not evaluate if all referenced fields are empty checkbox.